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

NewRacGuy (nospam) wrote:
>
> I wrote a function like this
> ------------------------------------
> create or replace function test_function
> (l_data_input in varchar2)
> return varchar2 as
> result varchar2(2000);
> begin
> select sql_text into result from v$session a,v$sql b
> where a.sql_address = b.address
> and a.sql_hash_value = b.hashvalue
> and a.audsid = sys_context('USERENV','SESSIONID');
> return result;
> end;
> ----------------------------------------
> Then I do "select test_function(ssn) from test", what I got is the sql
> "select sql_text into ....", this is not what I wanted. I would like to
> catch the sql "select test_function(ssn).....".
>
> Is there any way of doing it?

First, make your helpers' life easier by making sure the SQL is correct and telling us the Oracle version. The change I have to make is "b.hashvalue" to "b.hash_value", "select test_function(ssn) from test" to "select test_function('') from dual" (any string as argument works). To run the code in a user not as SYS, I grant select v_$session and v_$sql to that user in SYS first. (Running as SYS returns more than 1 row from v$session unless Oracle version is 10g.)

To answer your question, there's probably no easy way to achieve that. V$session.sql_address as well as prev_sql_addr and their hash values point to the immediate past SQL the session executed. As far as I know, Oracle doesn't have a concept of cursor parent and child in the sense of operating system process parent - child. (Oracle cursor "parent" - "child" may as well be called cursor head - tail, as in J. Morle, "Scaling Oracle8i".) I looked at all columns of v$sql(area) and x$kglob and can't find such cursor-to-cursor relationship.

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 Received on Fri Nov 11 2005 - 13:09:39 CST