Re: Q./Help !: how retrieve the text of the query I'm executing from a v$ view ??

In article <7rqoce$7vb$1_at_nnrp1.deja.com>,   monsri_at_my-deja.com wrote:
> Hello here,
> I have a pretty difficult -???- question: I'd like to be able
> to retrieve the whole text of a query I'm running AS I'm just
> executing it. Let me explain (suppose we're in a SQL*Plus
> session):
> If I do:
> SQL> select field1, userDefFunct(field2)
> 2 from myTable
> 3 group by field1;
>
> I'd like to be able to find the entire text of this same
> query because I NEED TO QUERY IT IN MY FUNCTION userDefFunct() !
> How can I know that in some V$SQL... views the field SQL_TEXT
> corresponds to the very current and active query I'm running ?
>
> Or maybe it's impossible ???
>
> (this is to avoid passing 4 or 5 parameters to the userDefFunct()
> function, and depending on this my statement "userDefFunct(field2)"
> will return something particular)
>
> Thanks a lot !
> Regards,
> Seb
>

Based on the description above you can not do what you want to do by querying the v$ views. It is relatively easy to see the SQL you are running in one session using a second session, but if you query v$sqlarea for the same session you are in, you will retrieve the query SQL against v$sqlarea as it is now the current SQL statement.

It is possible to see all the open cursors for a session, but I do not know how you would identify the last executed cursor.

Maybe if you explained what you were trying to accomplish someone could provide you a solution.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 16 1999 - 08:02:31 CDT