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

Question

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?

Greg Merrill · Answer

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