Re: getting the current line in PL/SLQ functions

A copy of this was sent to paul cluiss <paul_cluiss_at_intervoice.com> (if that email address didn't require changing) On Thu, 14 Oct 1999 12:59:41 -0500, you wrote:

>Hello everyone,
>
>I would like to know if anyone knows how to programmatically determine
>the line number of the package at the current point of execution. For
>all you 'C' programmers out there, this is roughly equivalent to the
>__LINE__ and __FILE__ macros, which will give the line number and
>filename of the point at which they are invoked. I would like to use
>this to help me pass some debugging information from a PL/SQL package
>back to a Pro*C client over an Oracle pipe. As is, my PL/SQL code is
>totally ignorant of the current line.
>
>Thanks,
>
>Paul Cluiss
>Dallas, Texas

goto the url in my signature. there i have a howto:

Who Called Me -- within PL/SQL find out who called you

The little script you get has a routine "who_called_me" that reports the calling source owner, name, type and linenumber. Additionally, there is a small function "who_am_i" that reports back who you are. Therefore

who_am_i == __FILE__

from C. Using a tweak of who_am_i, you can write your own "what_line_am_i_on" to get the __LINE__

Also -- you might want to look at the article "fine grained access control" there -- not for fine grained access control but to get access to the utility "debug" we have. A synopsis is:

<quote>
One utility I use frequently when writing predicate functions is a simple ‘debug’ package. This package, authored by Christopher Beck – also of Oracle (clbeck_at_us.oracle.com), allows us to instrument our code with ‘print’ statements. This package also allows us to liberally put statements like:

          create function foo …
          as
             …
          begin
               debug.f( ‘Enter procedure foo’ );
               if ( some_condition ) then
                   l_predicate := ‘x=1’;
               end if;
             
               debug.f( ‘Going to return the predicate "%s"’, l_predicate );
               return l_predicate;
          end;

So, debug.f works similar to the C printf function and is implemented using UTL_FILE. It creates programmer managed trace files on the database server. These trace files contain your debug statements – things you can use to see what is happening in your code. Since the database kernel is invoking your code, debugging it can be hard. Having these trace files can save lots of time. The scripts you can download (see below for that link) contain this debug package and comments on setting it up and using it. </quote>

the debug.f output generated can be viewed in realtime using "tail -f" on unix. Also, it includes the source code filename and linenumber and timestamp of the message. So the above might generate debug that looks like:

991011 141814 (      FOO,   6) Enter procedure foo
991011 141814 (      FOO,  11) Geting to return the predicate "x=1"

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Oct 14 1999 - 14:22:41 CDT