6 Replies Latest reply: Feb 27, 2013 12:49 AM by Billy~Verreynne RSS

    Displaying pl/sql output during run-time

    dvsoukup
      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
          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
            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
              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
                dvsoukup
                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
                  >
                  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
                    Billy~Verreynne
                    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.