Re: DBMS_OUTPUT Question

Another solution to the problem would be to use the dbms_application_info package to set the module, action and client_info fields of the v$session dynamic performance view.

The first call would initialize things:

dbms_application_info.set_module('Archiving','Beginning');

The next calls would be to dbms_application_info.set_action or dbms_application_info.set_client_info to record specific values in the action or client info fields that marks the progress of your script.

It appears from the script that you have the privilege to read v$session data directly, but if you do not, the package also provides procedures that reads these values as well.

Frank Hubeny

rspeaker_at_my-deja.com wrote:

> Hi,
>
> considering the following code snippet ....
>
> create or replace procedure VRU_ARCHIVE
> as
> v_start_time varchar2(20);
> v_end_time varchar2(20);
>
> BEGIN
>
> select to_char(sysdate,'MON DD,YYYY HH24:MI:SS') into v_start_time from
> dual;
>
> DBMS_OUTPUT.PUT_LINE('Beginning ARCHIVE1 Procedure at
> ||v_start_time||'.');
>
> ARCHIVE1;
>
> select to_char(sysdate,'MON DD,YYYY HH24:MI:SS') into v_end_time from
> dual;
>
> DBMS_OUTPUT.PUT_LINE('Finished ARCHIVE1 Procedure at
> '||v_end_time||'.');
>
> select to_char(sysdate,'MON DD,YYYY HH24:MI:SS') into v_start_time from
> dual;
>
> DBMS_OUTPUT.PUT_LINE('Beginning ARCHIVE2 Procedure at
> '||v_start_time||'.');
>
> ARCHIVE2;
>
> select to_char(sysdate,'MON DD,YYYY HH24:MI:SS') into v_end_time from
> dual;
>
> DBMS_OUTPUT.PUT_LINE('Finished VRU_TRANTABLE_ARCH Procedure at
> '||v_end_time||'.');
>
> END; -- Procedure VRU_ARCHIVE
>
> my output messages do not appear until the very end of the procedure,
> and then all at one time. The information stored in the variables is
> correct, but I would like to see it output to the screen progressively,
> so I know when ARCHIVE1 started, when it finished, when ARCHIVE2
> started, when it finished, etc. How can I do this?
>
> Thanks,
> Roy
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Nov 04 1999 - 22:57:12 CST