CODEWITHSUNDEEP
sqloracle-databaseloopsdbms-output
Giuseppe
Giuseppe

Reputation: 507

Loop in SQL oracle, show DBMS_OUTPUT.PUT_LINE before completing the execution

I have a for loop in a dynamic oracle sql statement:

begin
for i in (select x from index_list) loop

-- do stuff with i.x

dmbs_output.put_line('I completed item '||i.x||' of the loop');

end loop;
end;
/

However the messages in dmbs_output.put_line will only be displayed after all the loop has been completed, all together. Is there a way to show them when each round of the loop is executed?

Upvotes: 1

Views: 2271

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Check your development tool (PL/SQL Developer, SQL Developer, Toad etc) to learn how to use the PL/SQL debugger.

The other options are

  1. Use dbms_application_info to set values for the session's current module, action and client info. These will then be visible from other sessions by querying v$session.
  2. Log messages to a table or a file.
    • If writing to a table, you will need to commit each entry, using an autonomous transaction if there are other transactions that you don't want to commit at each loop iteration.
    • If writing to a file, you might consider defining an external table over the file to make the contents queryable.
  3. Write it as a pipelined function and query it, so that the output appears one row at a time as the query results.
  4. In theory you could use the HTTP interface and track results on a web page. I haven't tried this so not sure how practical it is.

Upvotes: 1

EdStevens
EdStevens

Reputation: 3872

dbms_output does not write the the output device (screen) at all. Being a server-side process, it has no means to access the client's display device. Instead, it writes to an internal buffer. That buffer is returned to (made available to) the client only upon completion of the process that was called by the client, when processing control is passed back to the calling client. So, no, you will not see the results while process control still belongs to the pl/sql procedure - the whole procedure, not just the loop.

And even then, it is up to the client on what to do with that buffer. In sqlplus, we direct it to display the buffer contents with use of the 'set serveroutput on' command. Other clients may have other means to direct it to process the message buffer. Still other clients have no means at all and simply ignore it.

Upvotes: 3

Related Questions