2

Is there a way to retrieve output from PL/SQL continuously rather than wait until the SP completes its execution. Continuously mean as when it executes the execute immediate. Any other mechanism to retrieve pl/sql output?

As per Oracle docs

Output that you create using PUT or PUT_LINE is buffered in the SGA. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller. So, for example, Enterprise Manager or SQL*Plus do not display DBMS_OUTPUT messages until the PL/SQL program completes.

R-K
  • 119
  • 1
  • 1
  • 8

3 Answers3

3

As far as I know, there is a way, but not with DBMS_OUTPUT.PUT_LINE. Technique I use is:

  • create a log table which will accept values you'd normally display using DBMS_OUTPUT.PUT_LINE. Columns I use are
    • ID (a sequence, to be able to sort data)
    • Date (to know what happened when; might not be enough for sorting purposes because operations that take very short time to finish might have the same timestamp)
    • Message (a VARCHAR2 column, large enough to accept the whole information)
  • create a logging procedure which will be inserting values into that table. It should be an autonomous transaction so that you could COMMIT within (and be able to access data from other sessions), without affecting the main transaction

Doing so, you'd

  • start your PL/SQL procedure
  • call the logging procedure whenever appropriate (basically, where you'd put the DBMS_OUTPUT.PUT_LINE call)
  • in another session, periodically query the log table as select * from log_table order by ID desc

Additionally, you could write a simple Apex application with one report page which selects from the logging table and refreshes periodically (for example, every 10 seconds or so) and view the main PL/SQL procedure's execution.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
3

The approach that Littlefoot has provided is what I normally use as well.

However, there is another approach that you can try for a specific use case. Let's say you have a long-running batch job (like a payroll process for example). You do not wish to be tied down in front of the screen monitoring the progress. But you want to know as soon as the processing of any of the rows of data hits an error so that you can take action or inform a relevant team. In this case, you could add code to send out emails with all the information from the database as soon as the processing of a row hits an error (or meets any condition you specify).

You can do this using the functions and procedures provided in the 'UTL_MAIL' package. UTL_MAIL Documentation from Oracle

Santhosh Jose
  • 517
  • 1
  • 6
  • 18
3

For monitoring progress without the overhead of logging to tables and autonomous transactions. I use:

DBMS_APPLICATION.SET_CLIENT_INFO( TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' On step A' );

and then monitor in v$session.client_infofor your session. It's all in memory and won't persist of course but it is a quick and easy ~zero cost way of posting progress.

Another option (Linux/UNIX) for centralised logging that is persistent and again avoids logging in the database more generally viewable that I like is interfacing to syslog and having Splunk or similar pick these up. If you have Splunk or similar then this makes the monitoring viewable without having to connect to the database query directly. See this post here for how to do this.

https://community.oracle.com/thread/2343125

TenG
  • 3,843
  • 2
  • 25
  • 42