Home » SQL & PL/SQL » SQL & PL/SQL » Find procedure name (Oracle 9i (unix))
Find procedure name [message #411699] Mon, 06 July 2009 06:37 Go to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member

Hy,

I want to generate a procedure name from a line number and a package name.

for example:

package xx_test

p_first_proc

package body xx_test

p_first_proc
-- lines 1 to 250 --
p_other_proc
-- lines 252 to 300 --
p_another_proc
-- lines 303 to 500 --

If I have as input line 266 and package name xx_test the output should be p_other_proc. Keep in mind that there could be comments in the code (all_source) and that there could be procedures in procedures.

Thank you !

Kind regards,

Mike Verkimpe.
Re: Find procedure name [message #411703 is a reply to message #411699] Mon, 06 July 2009 06:44 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Look at the %_SOURCE data dictionary views.
Re: Find procedure name [message #411708 is a reply to message #411699] Mon, 06 July 2009 06:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That sounds like a really bad idea.

What happens if you need to amend a procedure to add or remove a lot of code from it?

Re: Find procedure name [message #411711 is a reply to message #411708] Mon, 06 July 2009 06:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
JRowbottom wrote on Mon, 06 July 2009 12:53
That sounds like a really bad idea.

What happens if you need to amend a procedure to add or remove a lot of code from it?



Well, obviously you would simply view the entire procedure scan down untill you found the procedure name, take a note of the line number then feed the new line number into the procedure. Either that or the package will never change, never ever, nevereverevereverever, well unless we need to make changes.... Or maybe they could log the code changes in a table, and from that calculate what the new line numebr would be....
Smile
Re: Find procedure name [message #411714 is a reply to message #411699] Mon, 06 July 2009 07:12 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
you can use a self made look up table for that Smile
Re: Find procedure name [message #411747 is a reply to message #411711] Mon, 06 July 2009 09:29 Go to previous messageGo to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member

Okay,

but how do you make the difference between commented lines and procedures inside procedures?

Try building a select for that ...

Kind regards ,

Mike Verkimpe.
Re: Find procedure name [message #411786 is a reply to message #411699] Mon, 06 July 2009 14:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yet another "I want to build my own parser" question in disguise.
If you really want to be sure, and you have all the tricky stuff like the word procedure in comments or inner procedures, there is only one way to be sure: write your own parser.
Ok, there _is_ another way: tell us why you want it, we might know of an alternative way to do so.
Re: Find procedure name [message #411793 is a reply to message #411786] Mon, 06 July 2009 15:15 Go to previous messageGo to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member
I just want to create a logging function that logs trace information or errors.

I use the format_call_stack to get the calling program unit. But for packages I just get the package name and a line number.

So I don't want to create my own parser

Kind regards,

Mike Verkimpe.
Re: Find procedure name [message #411822 is a reply to message #411793] Tue, 07 July 2009 00:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
mikeverkimpe wrote on Mon, 06 July 2009 21:15

So I don't want to create my own parser

You may not want to, but that is pretty much what is required based on what you are asking for.

You probably need to go down the lines of:
Load the source code of the package into an associative array.
Loop through each line of code starting from line 1 up to the passed line parameter value.
assess each line as to whether it is a comment, a line of a procedure or a line of a procedure within a procedure.
As you loop through procedure lines, log the procedure name into a variable, then remove it when that procedure is 'ended' until you come to the required line number:

pseudo code
BEGIN
FOR i in AssocArray LOOP
   If left(line,2) = '/*' Then
    l_comm = 'Y'
   END IF
   If Right (line,2) then
    l_comm = 'N'
   If l_comm = 'N' then
     If L_proc = 'N' Then
       if left (line,9) = 'PROCEDURE'then
         l_proc = 'Y'
         l_proc_name = extract name of proc
       end if
    End If
     if line = 'END '||l_proc_name then
      l_proc = 'N'
     end if
    if _line_no = p_line_no then
      return l_proc_name
    end if
End

The above is a very rough idea and will require an awful lot more logic that that (Testing for '--', Leading/trailing spaces, Comment characters within text delimeters, excessive use of carriage returns, character case, extra spaces and a whole host of other problems that I can't think of at 7am).
Why not just build an exception handler into each of your procedures to do the job that you need?


Re: Find procedure name [message #411928 is a reply to message #411822] Tue, 07 July 2009 05:35 Go to previous messageGo to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member

I know how to do it ... i was just wondering if anyone created a function like that before...

kinds regards,

Mike Verkimpe.
Re: Find procedure name [message #412115 is a reply to message #411928] Tue, 07 July 2009 16:57 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
No offense intended (genuinely) but if you know how to do it, then why don't you?
Previous Topic: Difference between Execute and DBMS_SQL
Next Topic: Tuning a query
Goto Forum:
  


Current Time: Sun Dec 04 18:52:37 CST 2016

Total time taken to generate the page: 0.05945 seconds