Re: PL/SQL : How do you echo text to screen ?

# tssmith_at_best.com (Tim Smith) writes:
#
# >One serious flaw of the DBMS_OUTPUT package is that there is no way of
# >doing an intermediate flush of the output. ...
#
#
# That's more a design question of the Oracle tools. Something like
# sql*plus etc. does an oexec() OCI call, which blocks until the pl/sql
# has finished, then it retrieves the dbms_output output if the
# serveroutput variable is set. There's no nonblocking oexec() or oparse()
# call, and no mechanism to keep polling for output.
#
# I don't see an easy way to do this anyway, after all it's still client/
# server, the code you submit gets executed on the server, then you send
# some more sql statements to retrieve the output. There's no way to send
# text to the client while running, no out-of-band data.

That's not true. Sending data from server to client before a procedure ends is not "out-of-band". The PRINT statement in Sybase (serverside  PRINT) transmits the data to client every few lines. And you can do "set flushmessage on" to force the server flush the message line by line. Oracle may have opted to let the client to send a second request for the output (I don't know). But that's not what client/server should be. It's more like mainframe tradition.

In Oracle, the bigger problem with DBMS_OUTPUT.PUT_LINE() is not the timing. We can often afford to wait for a couple hours to see the message (just pretend I'm using a mainframe computer.) However, because all the messages are buffered, it fills up the memory quite quick. If I were to prcocess a table with 10 million rows (not a big table at all), and print a 10 character message for each row, the total amount of buffer easily exceeds the memory.

-- 

Q Vincent Yin               |   Repeat 
vyin_at_cs.ubc.ca              |       delete(next->bug);
(604) 876-9096 (H)          |   Until 0 = 1;