This discussion is archived
6 Replies Latest reply: Feb 26, 2013 10:49 PM by BillyVerreynne RSS

Displaying pl/sql output during run-time

880597 Newbie
Currently Being Moderated
Hello,

I was wondering if there was an efficient way to display output data during run-time of a stored procedure/pkg? I use dbms_output.put_line in conjunction with set serveroutput on. While this most certainly displays output to me, it only gets displayed AFTER the code has finished running.

Right now I have a "status" table set up to where I do inserts/updates to it, followed by commits, throughout the pl/sql code. This way I can query the "status" table to see where at in the process the job is currently at. I find this helpful when doing some inserts that consist of several million records.


Anyhow, the question is more of, can I display output DURING the run-time with some special command, instead of having to go make a status table and periodically writing to that table? And, is this good/bad practice to use a table in this manner?
  • 1. Re: Displaying pl/sql output during run-time
    APC Oracle ACE
    Currently Being Moderated
    If you just want a "progress bar" then the best thing is to use the V$SESSION_LONGOPS view. We can write to this using [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_appinf.htm#i996999]DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS() procedure.

    The usual approach is to initialise the Session Longops record with Toral Work and units, and then just increment the So Far amount at whatever intervals make sense to you. OracleNerd has a littel demo of how its done. [url http://www.oraclenerd.com/2007/12/instrumentation-dbmsapplicationinfo.html]Check it out.

    This approach is more performant than writing to a table.

    Cheers, APC

    Edited by: APC on Feb 26, 2013 5:49 PM

    Added links to docs, etc
  • 2. Re: Displaying pl/sql output during run-time
    Bjoern Rost Oracle ACE Director
    Currently Being Moderated
    several people have written their own logging frameworks to extent thelogging functions. Like these:

    http://tylermuth.wordpress.com/2009/11/03/logger-a-plsql-logging-and-debugging-utility/
    http://log4plsql.sourceforge.net/

    Bjoern
  • 3. Re: Displaying pl/sql output during run-time
    APC Oracle ACE
    Currently Being Moderated
    I haven't used Tyler's package (but I will check it out somnetime)

    However, I have used log4plsql. In my opnion the implementation is incomplete and it is not very performant. It was the first PL/SQL logger in the wild (back in 2004) and as such it was a half-decent stab. It saved my bacon once, but I wouldn't recommend it to anyone.

    YMMV

    Cheers, APC
  • 4. Re: Displaying pl/sql output during run-time
    880597 Newbie
    Currently Being Moderated
    That's some good information right there! Thank you very much :)

    After looking at it, it seems like using the vsession table might be as much overhead as just creating a basic "STATUS" table and writing/updating to that. Though... this is just from an initial look. I'll try and actually go implement the proposed ideas here and see how that works.... see if it's easier/harder than writing to a table.

    Anyhow, thanks again for suggesting this. Always love learning new tricks of the trade!
  • 5. Re: Displaying pl/sql output during run-time
    rp0428 Guru
    Currently Being Moderated
    >
    Right now I have a "status" table set up to where I do inserts/updates to it, followed by commits, throughout the pl/sql code. This way I can query the "status" table to see where at in the process the job is currently at. I find this helpful when doing some inserts that consist of several million records.
    >
    That method is the preferred method in my opinion. It is the most flexible and general purpose and you can write a package that does the job properly and interfaces with your code in a way that keeps your code generic and doesn't muck it up with the 'how' of the logging process.

    Also, you can make your logging code AUTONOMOUS so that you can log things even if the original code has to rollback what it was doing.
    >
    Anyhow, the question is more of, can I display output DURING the run-time with some special command
    >
    No - Oracle has NO knowledge or concept of 'display'; only client code can 'display' something.
    >
    And, is this good/bad practice to use a table in this manner?
    >
    Any well-written process should log what it is doing. The main criteria is to prevent the logging activity from interfering with the main transaction processing.

    Using a LOG package that contains AUTONOMOUS_TRANSACTION procedures/functions is a common method of capturing process activity.
  • 6. Re: Displaying pl/sql output during run-time
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    dvsoukup wrote:

    I was wondering if there was an efficient way to display output data during run-time of a stored procedure/pkg? I use dbms_output.put_line in conjunction with set serveroutput on. While this most certainly displays output to me, it only gets displayed AFTER the code has finished running.
    Correct. As PL/SQL is running in a server process and is not connected to a keyboard and screen and unable to interact with the user. After all, this is client-server and the client process deals with user interaction. Not the server.

    DBMS_OUTPUT is an expensive (private server process memory) buffer that server code can use - which a client can then query and render afterwards.

    If you want an interactive form of display, enabling server code to output data that is immediately seen by the client, a second set of client-server processes are needed. The main server process running user code, can use a DBMS_PIPE to dynamically output data during execution. The 2nd server process provides the pipe reader/server process - it receives the piped messages. The 2nd client controls this (it starts the server process's pipe reader) and it receives the data from the pipe. Which it then can render - enabling the user to have a "real-time" display of the main server's process (debug) data output.

    This is an active method (reading debug output data from a server process). Using a log table is simpler, but passive - and requires the 2nd client to continually query the log table to obtain the server process's latest debug/message output.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points