GeoRaster lets you monitor and report the execution progress of many operations (listed in Reporting Operation Progress in GeoRaster). The following are the basic steps for reporting the progress of an operation:

1. Use the SDO_GEOR_UTL.createReportTable procedure to create the report table under the appropriate user's schema. (This must be called once before you can monitor any operations.)

   EXECUTE SDO_GEOR_UTL.createReportTable;
   

2. In the user session where the operations are to be executed and monitored, perform the following actions:

   1. Use SDO_GEOR_UTL.enableReport to enable the monitoring. (You must call this procedure in order to be able to get the status report later.)

      EXECUTE SDO_GEOR_UTL.enableReport;
      

   2. Optionally, use SDO_GEOR_UTL.setClientID to set the client ID. The client ID is used to identify the user session that executes the operation. If this procedure is not called, the client ID defaults to the SQL session ID. For example:

      EXECUTE SDO_GEOR_UTL.setClientID(100);
      

   3. Optionally, use SDO_GEOR_UTL.setSeqID to set the sequence ID. The sequence ID is used to identify the repeated operations in the same SQL session. If this procedure is not called, the sequence ID defaults to 0. For example:

      EXECUTE SDO_GEOR_UTL.setSeqID(1);
      

   4. Execute the operation to be monitored. For example:

      -- Generate pyramid for georid=6. The progress of this generatePyramid call 
      -- can be monitored by step 3.
      DECLARE
        gr sdo_georaster;
      BEGIN
         SELECT georaster INTO gr 
          FROM georaster_table WHERE georid = 6 FOR UPDATE;
        sdo_geor.generatePyramid(gr, 'rLevel=5, resampling=NN');
        UPDATE georaster_table SET georaster = gr WHERE georid = 6;
         COMMIT;
      END;
      /
      

   5. Optionally, repeat steps c and d for each additional operation to be monitored. For example:

      EXECUTE SDO_GEOR_UTL.setSeqID(2);
      -- Generate pyramid for georid=7. The progress of this generatePyramid call
      -- can be monitored by step 3.
      DECLARE
        gr sdo_georaster;
      BEGIN
         SELECT georaster INTO gr 
          FROM georaster_table WHERE georid = 7 FOR UPDATE;
        sdo_geor.generatePyramid(gr, 'rLevel=5, resampling=NN');
        UPDATE georaster_table SET georaster = gr WHERE georid = 7;
         COMMIT;
      END;
      /
      

   6. Optionally, use SDO_GEOR_UTL.disableReport to disable the monitoring. If this procedure is not called, the monitoring is automatically stopped when the user session ends.

      EXECUTE SDO_GEOR_UTL.disableReport;
      

3. From another session under the same user, retrieve the execution status report.

   To get the progress of a specific operation identified by client ID and sequence ID, use the SDO_GEOR_UTL.getProgress function. This function returns the progress as a number between 0 and 1 reflecting the percentage of completion. For example, the following query shows that the operation if 55% complete:

   SELECT sdo_geor_utl.getProgress(100, 2) progress FROM DUAL;
    
   PROGRESS
   --------
       0.55
    
   1 row selected.
   

   To get the status report of a specific operation identified by client ID and sequence ID, use the SDO_GEOR_UTL.getStatusReport function. This function returns an array of strings describing the progress and other information about the operation. For example:

   -- Check the status of the generatePyramid on georid=6
   SELECT sdo_geor_utl.getStatusReport(100, 1) FROM DUAL;
   SDO_GEOR_UTL.GETSTATUSREPORT(100,1)
   -----------------------------------------------------------------------------
   SDO_STRING2_ARRAY('31-OCT-11 02.20.04.854558 PM', 'GeneratePyramid', 'RDT:RDT_1', 'RID:6', '100% complete', 'operation completed')
    
   1 row selected.
   
   -- Check the status of the generatePyramid on georid=7
   SELECT sdo_geor_utl.getStatusReport(100, 2) FROM DUAL;
    
   SDO_GEOR_UTL.GETSTATUSREPORT(100,2)
   -----------------------------------------------------------------------------
   SDO_STRING2_ARRAY('31-OCT-11 02.20.08.854558 PM', 'GeneratePyramid', 'RDT:RDT_1', 'RID:7', '55% complete', 'operation completed')
    
   1 row selected.
   

   To get the status of all the monitored operations, enter the following statement:

   SELECT * from the (select sdo_geor_utl.getAllStatusReport() FROM DUAL); 
   COLUMN_VALUE
   ------------------------------------------------------------------------------
   SDO_STRING2_ARRAY('Client:100', 'Sequence:1', '31-OCT-11 02.20.04.854558 PM', 'GeneratePyramid', 'RDT:RDT_1', 'RID:6', '100% complete', 'operation completed')
   SDO_STRING2_ARRAY('Client:100', 'Sequence:2', '31-OCT-11 02.20.08.854558 PM', 'GeneratePyramid', 'RDT:RDT_1', 'RID:7', '55% complete', NULL)
    
   2 rows selected.
   

If you need to clear or drop the report table, use the SDO_GEOR_UTL.clearReportTable or SDO_GEOR_UTL.dropReportTable procedure, respectively:

EXECUTE SDO_GEOR_UTL.clearReportTable;
-- or:
EXECUTE SDO_GEOR_UTL.dropReportTable;