Re: What is this SQL for, take a guess?
- From: "Ethan Post" <post.ethan@xxxxxxxxx>
- To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Mar 2006 08:10:18 -0600
I guess my last minute edit of my SQL broke it, but I do have a
version that works. Someone wanted to tune it, who would do that, this
is art! Anyway there were no correct answers as to the exact purpose
of it so no free i-pods today.
Here is the corrected SQL. So what is it used for? Well it returns the
line number of the primary BEGIN statement in every procedure and
function in a database along with the name of the procedure or
function. This can be used to automatically add debug/instrumentation
calls. I suppose someone will point out to me that there is some X$
view which does this already but if not then there you have it.
Tom's presentation on instrumentation at Hotsos inspired me to finally
write a little package for that purpose and I have a bunch of code to
add it to. Seems to be working pretty well.
select
owner,
name,
next_line line,
module
from
(select
owner,
name,
line,
lead(line, 1) over (order by name, line) next_line,
module,
lead(module, 1) over (order by name, line) t1
from
(select
owner,
name,
type,
line,
decode(instr(module, ' '), 0, module,
substr(module,1,instr(module,' '))) module
from
(select
owner,
name,
type,
line,
trim(decode(substr(module,1,8),'FUNCTION',substr(module,10), module))
module
from
(select owner,
name,
type,
line,
decode(substr(module,1,9),'PROCEDURE',substr(module,11), module)
module
from
(select owner,
name,
type,
line,
replace(upper(trim(translate(text,'('||chr(10)||chr(13)||chr(9),'
'))), ' ', ' ') module
from all_source
where owner='SCOTT'
and
type in ('PACKAGE BODY','PROCEDURE', 'FUNCTION')
)
where (module not like '%CODE AND
ALTERNATE
PROCEDURE/REV CODE.%'
and (module like 'PROCEDURE %' or
module =
'BEGIN' or module like 'FUNCTION %'))))))
where module <> 'BEGIN' and t1='BEGIN';
On 3/16/06, Ethan Post <post.ethan@xxxxxxxxx> wrote:
>
>
>
>
> I am pretty proud of this little bitty. Anyone want to take a swipe what it
> could be used for? Oh and it will up in 'ur quest spotlight right out.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
