Geek Answers Handbook

Oracle: any replacements for DBMS_OUTPUT package?

especially one that does not have 256 max characters / lines and 1,000,000 max characters / buffer limit.

+5
source share
6 answers

Perhaps one of these options will suit your needs (depending on whether you are writing something on the server side or on the client side):

(update from Mark Harrison) I went with the my-dbms-output package in an AskTom post. One really nice feature is that you can access the results through a presentation so that it is easy to show the result in the client program. I renamed it to a shorter name.

+7
source

Oracle? . 10.2 255 ( 32 .) . Oracle 9.2 255 /1 , Oracle .

+6

TCP . .

: :

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;
+2

INSERT - . , . , SQL. sysdate, . , - .

+2

Another option, although probably not the largest, is to write warnings to the log.

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

One limitation of dbms_output is that the output becomes available only after the statement completes. To track long running processes, I use dbms_pipe to send status messages. At the other end of the pipe, you can see what happens in the process.

+1
source

More articles:


All Articles