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

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 3

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 4

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 5

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 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.