Oracle: any replacements for DBMS_OUTPUT package?

Question

especially one that doesn't have the 256 max chars/line and 1000000 max chars/buffer limitation.

Answer 1

Maybe one of these options will suit your needs (depending whether you write something on server side or client side):

• UTL_FILE
• TEXT_IO
• AskTom post

(update by Mark Harrison) I went with the my-dbms-output package in the AskTom post. One really nice feature is that you can access the results via a view, so that it's easy to show the output in a client program. I renamed it to a shorter name.

Answer 2

You can use the TCP package to write output to a terminal or to a remote data logger. Quite handing for debugging package code running in scheduled tasks.

Edit: Here is an example procedure:

procedure pDebug( str in varchar2 )
-- output debugging message to display or tcp console
   is
x number;
l number;
nPort number;
sAddress varchar2(5000);
  begin
if c_bDebug = 1 then
 if c_tcpbDebug = 1 then
  if cSocket.remote_host is NULL then
   nPort := strMetaDataValue( 'TCP-DEBUG-PORT' );
   sAddress := strMetaDataValue( 'TCP-DEBUG-ADDRESS' );
   dbms_output.put_line( 'tcp:port ' || nPort );
   dbms_output.put_line( 'tcp:address ' || sAddress );
   if length( sAddress ) > 1 and nvl( nPort, 0 ) > 0 then
    begin
    dbms_output.put_line( 'tcp:open start ' ||to_char( SYSDATE, 'DD-MON-YYYY HH24:MI:SS' ) );
    cSocket := utl_tcp.open_connection( sAddress, nPort ); -- open connection
    dbms_output.put_line( 'tcp:open ' || to_char( SYSDATE, 'DD-MON-YYYY HH24:MI:SS' ) );
    c_tcpbDebug := 1;
    exception
     when others then
      dbms_output.put_line( SQLERRM );
      dbms_output.put_line( 'Cant open debug tcp session ' || SYSTIMESTAMp );
      c_tcpbDebug := 0;
    end;
   else
    c_tcpbDebug := 0;
   end if;
  end if;   

  if cSocket.remote_host is not NULL then
   dbms_output.put_line( 'tcp:write' );
   x := utl_tcp.write_line( cSocket, to_char( SYSDATE, 'DD-MON-YYYY HH24:MI:SS' ) || ' ' || str );
   x := utl_tcp.write_line( cSocket, utl_tcp.crlf );
  end if;
 end if;
-- this bit prints out the debug statement in 254 char bits
 l := length( str );
 x := 1;
 while x <= l loop
  dbms_output.put_line( substr( str,x,254 ) );
  x := x + 254;
 end loop;
end if;
end pDebug;

Answer 3

What version of Oracle? Both of those limitations have been relaxed in recent versions. 10.2 supports lines longer than 255 characters (the new limit is 32k) and eliminates the maximum buffer size limitation. Oracle 9.2 had the 255 characters per line/ 1 MB total limit, but Oracle has desupported that version.

Answer 4

Another option, although probably not a great one, is to write to the alert log.

sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ' -- The message ');

Answer 5

INSERT is a fantastic alternative. Not only do you get the information out of your process, it's persisted for future reference or analysis. And the results can be retrieved and filtered and processed with a very common language called SQL. You could have a column with a default of sysdate to check time and order. It can be placed inside an autonomous transaction to avoid losing the logging due to a rollback.

Answer 6

One limitiation of dbms_output is that the output becomes available only after the statement has finished. To keep track of long running processes I use dbms_pipe to sent out status messages. On the other end of the pipe you can then see what the process is up to.