Tom,
For documentation purposes, we are trying to write some SQL to extract the source code of functions and procedures, one by one, from an Oracle package body.
What we have done so far is as follows:
Create a function that returns the package body of a package as a CLOB by combining the individual lines from the ALL_SOURCE for a package to a CLOB. Takes the package name as a parameter and returns a CLOB
FUNCTION GetPackageBody(PackageName IN VARCHAR2)
RETURN CLOB
IS
C CLOB;
l_in_comment boolean default FALSE;
l_comment_pos int;
l_text varchar2(4000);
BEGIN
DBMS_LOB.CREATETEMPORARY(C,TRUE);
FOR I IN ( SELECT TEXT
FROM ALL_SOURCE
WHERE NAME = PackageName
AND TYPE = 'PACKAGE BODY'
ORDER BY LINE
)
LOOP
L_TEXT := (I.TEXT);
IF L_TEXT IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND(C,LENGTH(L_TEXT),L_TEXT);
END IF;
END LOOP;
RETURN C;
END;To simplify things we then created a view that has the package name and CLOB field and is limited to packages of a specific schema
create or replace view package_script as
select object_name AS PKG_NAME,
GetPackageBody(object_name) PKG_BODY
from all_objects
where owner = 'SchemaOwnerName>'
and object_type = 'PACKAGE';All of the above works fine.
The last bit is then to find and read the source code for each function/procedure in the package.
The following SQL will read from the package_script view how many functions/procedures there are in the package, that is the LEVEL, and report the position in which they start.
SELECT Q.*
FROM
(SELECT X.*, BODY_LINE AS "START"
<b>--- LEAD(BODY_LINE, 1, 0) OVER (ORDER BY PKG_NAME, PKG_NO) AS "STOP"
-- LEAD(BODY_LINE, 1, 0) OVER (ORDER BY PKG_NAME, PKG_NO) - BODY_LINE AS "CHARS"</b>
FROM
(
WITH BODY_COUNT AS (
SELECT PKG_NAME, GREATEST( REGEXP_COUNT(PKG_BODY , 'FUNCTION'), REGEXP_COUNT(PKG_BODY , 'PROCEDURE')) CNT
FROM PACKAGE_SCRIPT
)
SELECT T."LEVEL",T."LEVEL" AS PKG_NO, T."PKG_NAME",T."PKG_BODY",T."BODY_FUNCTION",T."BODY_PROCEDURE", GREATEST(BODY_FUNCTION, BODY_PROCEDURE) BODY_LINE
FROM
( SELECT LEVEL, AT.PKG_NAME , AT.PKG_BODY, INSTR(AT.PKG_BODY,'FUNCTION', 1, LEVEL) AS BODY_FUNCTION,
INSTR(AT.PKG_BODY,'PROCEDURE', 1, LEVEL) AS BODY_PROCEDURE
FROM PACKAGE_SCRIPT AT, BODY_COUNT BC
WHERE AT.PKG_NAME = BC.PKG_NAME
CONNECT BY LEVEL <= CNT
) T
) X
) QThis SQL also performs ok, it is not the quickest but still acceptable.
Then we incorporated to the query the maths to get the start and end position of each function/procedure using analytics function LEAD and that killed the performance.These are in bold in the above SQL and currently commented out.
The questions we have are:
1) Is there a better way to extract the source code for the individual functions/procedures from a package body?
2) Why does the performance drop so dramatically, and is there a way to “fix” the SQL to perform better?
Thanks,
Kari
Oh boy. I'm not sure where to start with this one.
The fact you're doing this worries me that your PL/SQL is not in source control. You have saved your PL/SQL in files which you place in source control, right?
Next up, if you're trying to generate documentation along the lines of Javadoc... this isn't the way to do it!
Add appropriate comments to your source code. Then you can extract it using DBDoc in SQL Developer:
https://www.thatjeffsmith.com/archive/2012/03/javadoc-for-the-database-a-la-dbdoc-via-sql-developer/ Finally there are many flaws in the SQL to find the start/end of procedures. Firstly it assumes UPPERCASE KEYWORDS. Which may not be the case!
You're also going to have problems with nested units - functions/procedures defined in the declaration of another proc/fn.
Instead of this, check out PL/Scope. This gives you lots of details of how your PL/SQL fits together. Depending on what you're trying to do, this may give you want you're looking for anyway.
Here's a quick example showing how to extract details for a package:
create or replace package pkg as
procedure p ( p int );
end;
/
create or replace package body pkg as
procedure p ( p int ) as
l int;
function f2 ( p int )
return int as
retval int;
begin
return retval;
end f2;
begin
l := f2 ( p );
dbms_output.put_line ( l );
end;
function f1 ( p int )
return int as
retval int;
begin
return retval;
end f1;
end;
/
select lpad(' ', level*2, ' ') || name as name,
type,
usage,
line,
col
from user_identifiers
start with usage_context_id = 0
and object_name = 'PKG'
and object_type = 'PACKAGE BODY'
connect by prior usage_id = usage_context_id
and object_name = 'PKG'
and object_type = 'PACKAGE BODY';
NAME TYPE USAGE LINE COL
PKG PACKAGE DEFINITION 1 14
P PROCEDURE DEFINITION 2 13
P FORMAL IN DECLARATION 2 17
INT SUBTYPE REFERENCE 2 19
L VARIABLE DECLARATION 3 5
INT SUBTYPE REFERENCE 3 7
F2 FUNCTION DECLARATION 4 14
F2 FUNCTION DEFINITION 4 14
P FORMAL IN DECLARATION 4 19
INT SUBTYPE REFERENCE 4 21
INT SUBTYPE REFERENCE 5 14
RETVAL VARIABLE DECLARATION 6 7
INT SUBTYPE REFERENCE 6 14
RETVAL VARIABLE REFERENCE 8 14
L VARIABLE ASSIGNMENT 11 5
F2 FUNCTION CALL 11 10
P FORMAL IN REFERENCE 11 15
F1 FUNCTION DECLARATION 15 12
F1 FUNCTION DEFINITION 15 12
P FORMAL IN DECLARATION 15 17
INT SUBTYPE REFERENCE 15 19
INT SUBTYPE REFERENCE 16 12
RETVAL VARIABLE DECLARATION 17 5
INT SUBTYPE REFERENCE 17 12
RETVAL VARIABLE REFERENCE 19 12