Re: DBMS_OUTPUT.PUT_LINE

I only use dbms_output for informational and debugging messages (limited).

For a production job, I will actually use the package utl_file to create a log file.

To summize what needs to be done in psuedo code:

open log file
write to log file
flush write buffer when needed
close log file

In a unix environment, I can then tail the log file to monitor the job.

>DECLARE
> CURSOR my_cur IS
> SELECT col1, col2, col3, col4
> FROM table1;
>BEGIN
> FOR my_rec IN my_cur
> LOOP
> --Display progress line.
> DBMS_OUTPUT.PUT_LINE('Processing record number '||TO_CHAR(my_cur%
>ROWCOUNT));
>
> --Insert statements
> INSERT INTO table2 VALUES col1, col2, etc....
> INSERT INTO table3 VALUES col3, col4, etc....
> COMMIT;
> END LOOP;
>END;
>
>The desired behavior is for DBMS_OUTPUT.PUT_LINE to print one line at a
>time, as the cursor FOR loop processes each record. The script takes a
>while to run, and I want to give the user a visual indication of the
>progress of the script. However, it does not print one line at a
>time. Instead, it prints all the lines at the termination of the
>script, which is not helpful.
>
>I understand that the DBMS_OUTPUT buffer does not get flushed to the
>screen until the PL/SQL block terminates. How can I achieve the
>desired behavior of printing one line at a time to the screen? I do
>not want to enclose each INSERT statement in its own PL/SQL block,
>because if an exception is raised, I do not want the subsequent
>statements to execute.
Received on Sun Apr 30 2000 - 00:00:00 CDT