Notifications
Clear all

How can I report work status (i.e. log statements) for a long-running stored proc or query?

1 Posts
1 Users
0 Likes
901 Views
0
Topic starter

I have a stored proc which runs for a couple hours and does lots of things.  Right now, it's hard for me to tell how much progress it's made.  I've tried things like DBMS_OUTPUT.PUT_LINE but I don't know where to get this output.  What's the best way for me to handle this?

1 Answer
0
Topic starter

Beginning in Neo 3.1, the Platform has a new table and function for this purpose:

 

function: WRITE_PROC_LOG(MESSAGE in VARCHAR2)

table: PROC_LOG (includes columns MESSAGE and CREATION_TIME)

 

In your proc, add statements like WRITE_PROC_LOG('Starting to do ...')

This will be inserted into PROC_LOG *in a new transaction*.  So you can query PROC_LOG while the long-running code is executing and see updates.

 

select CREATION_TIME, MESSAGE

from PROC_LOG

order by CREATION_TIME desc