Re: Help: Is there any way I can catch the sql that calls the function from inside the function?

NewRacGuy (nospam) wrote:
> <yong321_at_yahoo.com> wrote in message
> news:1131736179.441319.69890_at_g47g2000cwa.googlegroups.com...
> >
> > There're two alternatives depending on what you really want to achieve.
> > One is to use dbms_utility.format_call_stack inside your function
> > instead of query of v$sql. The other is to generate SQL trace in the
> > function and read the trace file (using external table e.g.) and find
> > the cursor right before the SQL call inside the function.
> >
> > Yong Huang
> >
> Mr. Huang,
>
> Thanks for your help. I am using Oracle 9204, but I do not mind upgrade it
> to 10G if you can prove it works in 10g.
>
> The method you suggested did not work.
>
> One way I am going to try on Monday is to use Fine Grained Auditing, and
> try to select from dba_fga_audit_trail, it might contains my sql there.
>
> Somebody else suggested me to use PRAGMA AUTONOMOUS_TRANSACTION, which I
> need to do some research. Do you have any idea about that?
>
> Thanks

Indeed neither of the methods I suggested really works. Format_call_stack only shows the function name prepended with schema name. But you want the complete SQL that calls this function. SQL trace is still of some use because it tells you the calling SQL that has the function name in it. Isn't that your ultimate goal, regardless from where you get that SQL?

I don't see how fined grained auditing or autonomous transaction is relevant to your request. Your calling SQL and the SQL inside the function are both in library cache (v$sql e.g.). But the difficulty is how to associate them. If you find a good answer, please post back.

(Some failed test. In my first run of SQL trace, on 9.2.0.1.0, I see "select testfunction(..." with "dep=0 uid=65", where 65 is my user_id, followed by "SELECT sql_text from v$session a,v$sql..." with "dep=1 uid=65". I thought these critera were enough for me to find the calling SQL. But the dep=1 SQL, the SQL inside the function, only shows up in trace once in a session. Even flushing shared pool makes no difference. But the calling SQL always shows up, which you can use.)

Yong Huang Received on Sun Nov 13 2005 - 23:15:06 CST