Re: logging the input values the procedure/function is called with
Date: Sun, 3 May 2009 08:20:06 -0700 (PDT)
Message-ID: <779371.88886.qm_at_web80603.mail.mud.yahoo.com>
> Also the problem stack trace or owa_util.who_called_me
> is they would not give the name of the member in a
> package, it just gives me the package name. This is
> another thing I wanted to findout: if the caller is a
> packaged function or packaged procedure, how do i get
> the name in the form of package.procedure/function.
Owa_util.who_called_me has lineno. You can find the function or procedure inside the package based on that, something like
with x as (select line, lag(line)over(order by line) prevline,
substr(text, 1, 80) text
from dba_source where owner = '&1' and name = '&2'
and regexp_like(text,'^ *(PROCEDURE|FUNCTION) ','i')
order by 1)
select line, text from x where &3 between prevline and line;
Replace my substitution variables as follows:
&1: your package owner &2: package name &3: lineno from owa_util.who_called_me
I only spot-checked the code on a few examples. You may need to adjust the query part as necessary. If your database is pre-10g, replace regular expression functions with a function built on owa_pattern.[a]match, or run from 10g through a DB link. (I can give you full 9i examples.)
Yong Huang
-- http://www.freelists.org/webpage/oracle-lReceived on Sun May 03 2009 - 10:20:06 CDT