Guang
You asked this a while ago, and no replies have shown up on the list:
>>I want to know their "Start_line" and "End_line" (the LINE column value in USER_SOURCE view) of each function and procedure in the package body. How do I get that?
Here is a sample query that gives start and end line numbers:
select * from (
select -- list of PK_A/ PK_B functions and procedures - may miss procedures with no arguments?
pfp.package_name
, pfp.progtype
, pfp.object_name
, pbody.line startline
, lead(pbody.line) over (order by pfp.package_name, pbody.line) endline
from
(
select package_name
, object_name, decode(min(position),0,'FUNCTION','PROCEDURE') progtype
from user_arguments
where package_name in ('PK_A', 'PK_B')
group by package_name, object_name
) pfp
inner join user_source pbody on pbody.type = 'PACKAGE BODY' and pbody.name=pfp.package_name
and upper(pbody.text) like '%'||pfp.progtype||'%'||pfp.object_name||'%'
and pbody.text not like '%;%' -- exclude forward reference declarations
) where endline != startline
Limitations:
- only covers packages
- no attempt has been made to deal with overloaded functions/procedures (which have two or more definitions in the same package)
- no attempt is made to exclude any comments that might confuse the query
- it is assumed that the declarations have the program type (function|procedure) and the name in the same line
I'm sure there must be a neater solution, but given those restrictions it does seem to work. Let me know if you have any problems with it...
HTH
Regards Nigel
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 19 2006 - 09:41:26 CST
