Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Claudio.

Asked: December 15, 2016 - 10:39 am UTC

Last updated: December 16, 2016 - 2:19 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi,

it has been a while since I've tried to obtain the SQL code currently running in a Procedure, or just right after it has run (exactly like SQL%ROWCOUNT).

How can I retrieve the SQL source? I'd LOVE to be able to save the source to a Log table I'm currently using and tracing all infos.

Thank you so much!

Claudio de Biasio

and Connor said...

In V$SESSION, the status will be ACTIVE for a running session, and the SQL_ID column will be populated with the currently running SQL.

You can use that to query V$SQL. Also in V$SQL is the columns

PROGRAM_ID
PROGRAM_LINE#

which point to the object_id of the procedure and the line number from which the SQL originated from.

Hope this helps.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library