DBMS_OUTPUT.PUT_LINE

I have a SQL script that inserts rows into several tables. I have a loop set up as follows:

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.

Thanks in advance for your help.

Todd Owers

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 28 2000 - 00:00:00 CDT