Re: What is this SQL for, take a guess?

From: Ethan Post <post.ethan_at_gmail.com>
Date: Thu, 16 Mar 2006 08:10:18 -0600
Message-ID: <357b48a90603160610q2c363bd9wf1db9652a157e9a4@mail.gmail.com>

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_at_gmail.com> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 16 2006 - 08:10:18 CST