I want a PL/SQL procedure to give me progress updates as it runs. However, DBMS_OUTPUT seems only to give me the output when the whole procedure is done.

Is there a way to make it give me updates during runtime?



Not to the current session, no.

You can use the dbms_application_info package to instrument the code so that tools running in other sessions can query v$session_longops and see how far along the process is. This is the same tool that Oracle internally writes to during long-running operations so lots of front-ends like Enterprise Manager and Toad will already read from it.

Alternately, you could use autonomous transactions to write to a status table as your code runs and read from that status table in a different session. I can't see why you'd want to, but you could theoretically also use the dbms_alert package to send alerts to other sessions.

  • I'm trying the alternative thing, but it isn't working. if (counter MOD 100) = 0 then INSERT INTO PROGRESS(COUNT) VALUES(counter); END IF; But a different user querying the progress table can't see anything. – Daniel Paczuski Bak Jun 24 '15 at 19:33
  • 1
    The insert would need to be done by a function defined as an autonomous transaction. Otherwise, it wouldn't be visible outside the scope of your transaction. Of course, that means that the changes wouldn't be rolled back if the parent transaction rolls back. – Justin Cave Jun 24 '15 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.