Re: Process a huge SQL procedure before timeout with JSP

In article <199a58b3.0107101306.42d3cfdb_at_posting.google.com>, junkmailblackhole_at_yahoo.com says...
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:<9i5f9d02taj_at_drn.newsguy.com>...
>
>> A process that takes over an hour is not a good candidate for a JSP.
>>
>> What I would do is:
>>
>>- instrument the process with calls to DBMS_APPLICATION_INFO, allowing it to set
>>values in the action, module and client_info columns in V$session and the more
>> columns in v$session_longops
>>
>> - have the jsp submit the procedure to be executed using dbms_job
>>
>>- have a screen that uses a meta tag every minute or so to query v$session and
>> v$session_longops to report the processes status and its estimated time to
>> completion.
>
>Thanks for your help (btw, I'm impressed by the number of oracle
>answers I see you write on google...keep it up). I'm not sure what
>you mean by instrumenting with DBMS_APPLICATION_INFO, but I did get to
>submitting with DBMS_JOB. It looks like:
>
>SQL> declare
> 2 l_job number;
> 3 begin
> 4 dbms_job.submit l_job,
>'LEWIS_CLARK.MergeIntoNew(''emptyset'',''emptyset2'',''emptyset11'');');
> 5 commit;
> 6 end;
> 7 /
>
>or in the JSP:
>
>String procCall = "DECLARE l_job NUMBER; BEGIN DBMS_JOB.SUBMIT(l_job,
>'LEWIS_CLARK.MergeIntoNew('' ... '');'); COMMIT; END;";
> CallableStatement cs = con.prepareCall (procCall);
>cs.executeUpdate();
>
>On executing either of these I get a "PL/SQL procedure successfully
>completed" message, but there is no feedback from the procedure (the
>procedure inserts data into a table in the database, so I could tell
>if it was working by querying that table). Are there any glaring
>errors here that I'm not seeing? Thanks --
>

dbms_job stuff runs in the background -- so you wouldn't expect to see anything. it is running exactly as expected.

As for instruementing -- what I meant was to use the dbms_application_info package all over your "mergeintoNew" procedure. This package allows you to set 3 columns in v$session (values will be immediately visible to other sessions, like yours without committing in the procedure). As well, it can be used to set values in the v$session_longops table.

Here is a snippet from my book describing how to use the v$session_longops stuff:

Using session longops

Many operations in the database may take a considerable amount of time. Parallel execution, Recovery Manager, large sorts, loads and so on fall into this category. These long running operations take advantage of their ability to set values in the dynamic performance view V$SESSION_LONGOPS to let us know how far along in their work they are – and so can your applications. This view displays the status of various database operations that run for longer than 6 seconds – that is, functions the database performs that the Oracle developers felt would normally take longer then 6 seconds have been instruemented to populate the V$SESSION_LONGOPS view. That does not mean that anything that takes longer then 6 seconds will automatically appear in this view. These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle release.

Changes made to this view are immediately visible to other sessions – without the need to commit your transaction. For any process that updates this view, you will be able to monitor their progress from another session by querying the V$SESSION_LONGOPS view. You too have the ability to populate rows in this view – typically 1 row but you may use others if you like.

The API to set the values in this view is defined as:

PROCEDURE SET_SESSION_LONGOPS

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RINDEX                         BINARY_INTEGER          IN/OUT
 SLNO                           BINARY_INTEGER          IN/OUT
 OP_NAME                        VARCHAR2                IN     DEFAULT
 TARGET                         BINARY_INTEGER          IN     DEFAULT
 CONTEXT                        BINARY_INTEGER          IN     DEFAULT
 SOFAR                          NUMBER                  IN     DEFAULT
 TOTALWORK                      NUMBER                  IN     DEFAULT
 TARGET_DESC                    VARCHAR2                IN     DEFAULT
 UNITS                          VARCHAR2                IN     DEFAULT 

See the supplied packages guide for more details.

>Dane

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp