Re: HELP HELP HELP! Simple PL/SQL Problem !!!!?????

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/17
Message-ID: <34981aba.3869433@inet16>#1/1

On 17 Dec 1997 15:22:02 GMT, "Markus" <zm86_at_dial.pipex.com> wrote:

>Can anyone HELP??
>
>I am an experienced systems programmer but have never used Oracle or SQL
>before, and I am trying to teach myself PL/SQL.
>
>I have written a simple multiple file update procedure, which took some
>doing, but it works. As this procedure will process some 500,000 records I
>want to display it's progress to the terminal (say every 1000 records), and
>control totals at the end of the run. Can anyone tell me how to display a
>message on the screen?? I do not seem to be able to find any type of
>display command!!
>
>I am currently running the procedure using Enterprise Manager's SQL
>Worksheet Module.
>
>
>
>Many thanks in advance,
>

Your pl/sql routine can use the dbms_application_info package available with O7.2 and up. Using this package, you can write immediate messages into the v$session table. You control 3 fields in this system table --

Module
Action
Client_info

Your pl/sql routine can do something like:

begin

   dbms_application_info.set_module( 'MyProgram', 'Beginning' );    ....
   dbms_application_info.set_action( 'MainLoop' );    for i in 1 .. 50000 loop

       if ( mod(i,1000) = 0 ) then
           dbms_application_info.set_client_info( 'Processing Record ' || i );
       end if;
       ....

   end loop;
   dbms_application_info.set_action( 'Done Main Loop, performing ...' );

   ....
end;

The, in enterprise manager or sqlplus or any query environment, you could:

select username, module, action, client_info from v$session where module = 'MyProgram';

to check on the status of your application in real time....

See the file $ORACLE_HOME/rdbms/admin/dbmsutil.sql Or

select text
from all_source
where name = 'DBMS_APPLICATION_INFO'
and type = 'PACKAGE'
order by line
/

to read about this package.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 17 1997 - 00:00:00 CST