Have PL/SQL Outputs in Real Time

Question

Is it possible to have Outputs from PL/SQL in real time? I have a pretty huge package that runs for more than an hour and I'd like to see where the package is at a particular time.

Anyways, I currently do this with a log table, which gets filled up with hundreds of log descriptions per run, I'm just curious if this is possible.

Thanks!

Answer 1

This is the kind of thing I use (output can be seen in v$session and v$session_longops)...

DECLARE
   lv_module_name   VARCHAR2(48);
   lv_action_name   VARCHAR2(32);

   gc_MODULE   CONSTANT   VARCHAR2(48) := 'MY_PROC';

   -- For LONGOPS
   lv_rindex BINARY_INTEGER;
   lv_slno   BINARY_INTEGER;

   lc_OP_NAME   CONSTANT   VARCHAR2(64)   :=   '['||gc_MODULE||']';
   lv_sofar   NUMBER;

   -- This is a guess as to the amount of work we will do
   lv_totalwork   NUMBER;
   lc_TARGET_DESC   CONSTANT VARCHAR2(64) := 'Tables';
   lc_UNITS   CONSTANT VARCHAR2(64) := 'Rows';

   CURSOR tab_cur
   IS
      SELECT owner, table_name
        FROM all_tables;

BEGIN
   <<initialisation>>
   BEGIN
      -- To preserve the calling stack, read the current module and action
      DBMS_APPLICATION_INFO.READ_MODULE( module_name => lv_module_name
                                       , action_name => lv_action_name );

      -- Set our current module and action
      DBMS_APPLICATION_INFO.SET_MODULE( module_name => gc_MODULE
                                      , action_name => NULL );
   END initialisation;

   <<main>>
   BEGIN
      DBMS_APPLICATION_INFO.SET_ACTION( action_name => 'Part 01' );
      NULL;

      DBMS_APPLICATION_INFO.SET_ACTION( action_name => 'Part 02' );
      FOR tab_rec IN tab_cur
      LOOP
         DBMS_APPLICATION_INFO.SET_CLIENT_INFO( client_info => 'Rows = ['||TO_CHAR( tab_cur%ROWCOUNT, '999,999,999' )||']' );
         NULL;
      END LOOP;

      DBMS_APPLICATION_INFO.SET_ACTION( action_name => 'Part 03' );

      --Initialising longops
      lv_rindex := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;
      lv_sofar := 0;
      lv_totalwork := 5000; -- This is a guess, but could be actual if the query is quick

      FOR tab_rec IN tab_cur
      LOOP
         DBMS_APPLICATION_INFO.SET_CLIENT_INFO( client_info => 'Rows = ['||TO_CHAR( tab_cur%ROWCOUNT, '999,999,999' )||']' );

         lv_sofar := lv_sofar + 1;

         -- Update our totalwork guess
         IF lv_sofar > lv_totalwork
         THEN
            lv_totalwork := lv_totalwork + 500;
         END IF;

         DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS( rindex      => lv_rindex
                                                  , slno        => lv_slno
                                                  , op_name     => lc_OP_NAME
                                                  , sofar       => lv_sofar
                                                  , totalwork   => lv_totalwork
                                                  , target_desc => lc_TARGET_DESC
                                                  , units       => lc_UNITS
                                                  );
      END LOOP;

      -- Clean up longops
      DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS( rindex      => lv_rindex
                                               , slno        => lv_slno
                                               , op_name     => lc_OP_NAME
                                               , sofar       => lv_sofar
                                               , totalwork   => lv_sofar
                                               , target_desc => lc_TARGET_DESC
                                               , units       => lc_UNITS
                                               );
   END main;

   <<finalisation>>
   BEGIN
      -- Reset the module and action to the values that may have called us
      DBMS_APPLICATION_INFO.SET_MODULE( module_name => lv_module_name
                                      , action_name => lv_action_name );

      -- Clear the client info, preventing any inter process confusion for anyone looking at it
      DBMS_APPLICATION_INFO.SET_CLIENT_INFO( client_info => NULL );
   END finalisation;
END;
/