Mark -- Thanks for the question regarding "Retrieving DBMS_OUTPUT.put_line from JDBC?", version 9.2.0.4

You Asked

I'm using a Java application to process an incoming feed.  The app inserts data into some 
Oracle tables, then runs an Oracle stored procedure.  The Java application is called from 
a UNIX script, connects to Oracle using Oracle's thin client driver and sends output to 
standard out.  In the existing Oracle stored procedure, there are calls to 
DBMS_OUTPUT.put_line(...).   The output of the app does not contain results of the 
DBMS_OUTPUT.put_line calls.

I know that the UNIX script that calls the java app could be changed to make a call to 
PL/SQL with the first line being something like, "set serveroutput on size 999999", then 
call the stored procedure instead of calling the stored procedure from JDBC, and I would 
see the output in the script's log.  I'd rather avoid doing this if possible.

Is there a way to either:
1) Use some other Oracle function instead of DBMS_OUTPUT.put_line() which will send data 
to standard out
or
2) Somehow configure the JDBC driver in a way that I haven't yet discovered.
or 
3) Some other clever suggestion you have.

Thank you very much for any thoughts.
 

and we said...

Here is an excerpt from my book "Expert One on One Oracle" about dbms_output - it shows 
how to teach other environments to be dbms_outupt aware:


<quote>
Teaching other environments about DBMS_OUTPUT

By default, tools such as SQL*PLUS and SVRMGRL are DBMS_OUTPUT aware. Most other 
environments are not. For example, your Java/JDBC program is definitely not DBMS_OUTPUT 
aware. We'll see in this section how to make Java/JDBC DBMS_OUTPUT aware. The same 
principles used below apply equally to any programming environment. The methods I use 
with Java can be easily applied to Pro*C, OCI, VB or any number of programmatic 
environments.

We'll start with a small PL/SQL routine that generates some output data:

scott@TKYTE816> create or replace
  2  procedure  emp_report
  3  as
  4  begin
  5      dbms_output.put_line
  6      ( rpad( 'Empno', 7 ) ||
  7        rpad('Ename',12) ||
  8        rpad('Job',11) );
  9
 10      dbms_output.put_line
 11      ( rpad( '-', 5, '-' ) ||
 12        rpad('  -',12,'-') ||
 13        rpad('  -',11,'-') );
 14
 15      for x in ( select * from emp )
 16      loop
 17          dbms_output.put_line
 18          ( to_char( x.empno, '9999' ) || '  ' ||
 19            rpad( x.ename, 12 ) ||
 20            rpad( x.job, 11 ) );
 21      end loop;
 22  end;
 23  /

Procedure created.

scott@TKYTE816> set serveroutput on format wrapped
scott@TKYTE816> exec emp_report
Empno  Ename       Job
-----  ----------  ---------
 7369  SMITH       CLERK
 7499  ALLEN       SALESMAN
 …
 7934  MILLER      CLERK

PL/SQL procedure successfully completed.

Now, we'll set up a class to allow Java/JDBC to easily perform DBMS_OUTPUT for us.

import java.sql.*;

class DbmsOutput 
{
/*
 * our instance variables. It is always best to 
 * use callable or prepared statements and prepare (parse)
 * them once per program execution, rather then one per 
 * execution in the program.  The cost of reparsing is 
 * very high.  Also -- make sure to use BIND VARIABLES!
 *
 * we use three statments in this class. One to enable 
 * dbms_output - equivalent to SET SERVEROUTPUT on in SQL*PLUS.
 * another to disable it -- like SET SERVEROUTPUT OFF.
 * the last is to "dump" or display the results from dbms_output
 * using system.out
 *
 */
private CallableStatement enable_stmt;
private CallableStatement disable_stmt;
private CallableStatement show_stmt;


/* 
 * our constructor simply prepares the three
 * statements we plan on executing. 
 *
 * the statement we prepare for SHOW is a block of 
 * code to return a String of dbms_output output.  Normally, 
 * you might bind to a PLSQL table type but the jdbc drivers
 * don't support PLSQL table types -- hence we get the output
 * and concatenate it into a string.  We will retrieve at least
 * one line of output -- so we may exceed your MAXBYTES parameter
 * below. If you set MAXBYTES to 10 and the first line is 100 
 * bytes long, you will get the 100 bytes.  MAXBYTES will stop us
 * from getting yet another line but it will not chunk up a line.
 *
 */
public DbmsOutput( Connection conn ) throws SQLException
{
    enable_stmt  = conn.prepareCall( "begin dbms_output.enable(:1); end;" );
    disable_stmt = conn.prepareCall( "begin dbms_output.disable; end;" );

    show_stmt = conn.prepareCall( 
          "declare " +
          "    l_line varchar2(255); " +
          "    l_done number; " +
          "    l_buffer long; " +
          "begin " +
          "  loop " +
          "    exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; " +
          "    dbms_output.get_line( l_line, l_done ); " +
          "    l_buffer := l_buffer || l_line || chr(10); " +
          "  end loop; " +
          " :done := l_done; " +
          " :buffer := l_buffer; " +
          "end;" );
}

/*
 * enable simply sets your size and executes
 * the dbms_output.enable call
 *
 */
public void enable( int size ) throws SQLException
{
    enable_stmt.setInt( 1, size );
    enable_stmt.executeUpdate();
}

/*
 * disable only has to execute the dbms_output.disable call
 */
public void disable() throws SQLException
{
    disable_stmt.executeUpdate();
}

/*
 * show does most of the work.  It loops over
 * all of the dbms_output data, fetching it in this
 * case 32,000 bytes at a time (give or take 255 bytes).
 * It will print this output on stdout by default (just
 * reset what System.out is to change or redirect this 
 * output).
 */

public void show() throws SQLException
{
int               done = 0;

    show_stmt.registerOutParameter( 2, java.sql.Types.INTEGER );
    show_stmt.registerOutParameter( 3, java.sql.Types.VARCHAR );

    for(;;)
    {    
        show_stmt.setInt( 1, 32000 );
        show_stmt.executeUpdate();
        System.out.print( show_stmt.getString(3) );
        if ( (done = show_stmt.getInt(2)) == 1 ) break;
    }
}

/* 
 * close closes the callable statements associated with
 * the DbmsOutput class. Call this if you allocate a DbmsOutput
 * statement on the stack and it is going to go out of scope -- 
 * just as you would with any callable statement, result set
 * and so on.
 */
public void close() throws SQLException
{
    enable_stmt.close();
    disable_stmt.close();
    show_stmt.close();
}
}

In order to demonstrate its use, I've set up the following small Java/JDBC test program. 
Here dbserver is the name of the database server and ora8i is the service name of the 
instance:

import java.sql.*;
 
class test {

public static void main (String args []) 
   throws SQLException 
{
    DriverManager.registerDriver 
      (new oracle.jdbc.driver.OracleDriver());
 
    Connection conn = DriverManager.getConnection
         ("jdbc:oracle:thin:@dbserver:1521:ora8i", 
          "scott", "tiger");
    conn.setAutoCommit (false); 

    Statement stmt = conn.createStatement();

    DbmsOutput dbmsOutput = new DbmsOutput( conn );

    dbmsOutput.enable( 1000000 );

    stmt.execute
    ( "begin emp_report; end;" );
    stmt.close();

    dbmsOutput.show();

    dbmsOutput.close();
    conn.close();
}
 
}


Now we will test it, by first compiling it and then running it:

$ javac test.java

$ java test
Empno  Ename       Job        
-----  ----------  ---------
 7369  SMITH       CLERK      
 7499  ALLEN       SALESMAN   
 7521  WARD        SALESMAN
……

So, that shows how to teach Java to do DBMS_OUTPUT for us. Just as SQL*PLUS does, you'll 
have to call DbmsOutput.show() after executing any statement that might procedure some 
output to be displayed. After we execute an insert, update, delete or stored procedure 
call – SQL*PLUS is calling DBMS_OUTPUT.GET_LINES to get the output. Your Java (or C, or 
VB) application would call 'show' to display the results.
 

Reviews

This worked great!   July 28, 2005 - 12pm Central time zone Bookmark | Bottom | Top Reviewer: Mark  Thank you, Tom for your quick and comprehensive response. This really helps me! Dubugging how java program printing console out from package.   April 24, 2006 - 9am Central time zone Bookmark | Bottom | Top Reviewer: Anupam v Rahangdale from India (pune) Hi, At very first i would like to say thank you very much for this valueable topic. I am working developer in Manas Solutions pvt. ltd. Pune India . i am searching how the java class printing dbms output in java console . and i got the answer form this topic this help me to solve my problem. Thanks & Regards Anupam v Rahangdale DBMS_OUTPUT from JDBC   August 18, 2006 - 5am Central time zone Bookmark | Bottom | Top Reviewer: Ganti Raghunath from India This was an excellent help and we might have to something similar to this in our application which is still in the design phase. I would again say a BIG Thank You for the question and the explanation. Great Solution, but what about when an exception is raised?   September 5, 2006 - 11am Central time zone Bookmark | Bottom | Top Reviewer: From an Oracle guy who doesn't know Java from Minneapolis, MN This example is what I was looking for, but it doesn't seem to work if an exception is raised from Oracle. How do you get it to print the dbms_output if an exception is raised? Followup   September 5, 2006 - 5pm Central time zone: not sure what you mean ?? sure it does, anything you wrote to the dbms_output buffer will be there, you're application just needs to retrieve and display it. saved me a lot of work   September 27, 2006 - 10am Central time zone Bookmark | Bottom | Top Reviewer: A reader  Thanks for this solution that saved me a lot of effort:) sheers   July 5, 2008 - 3pm Central time zone Bookmark | Bottom | Top Reviewer: AlcoNaft from Norway Thanks a lot! Your post saves a lot of time for me :) Usage of this information   October 10, 2008 - 1pm Central time zone Bookmark | Bottom | Top Reviewer: A reader from Chicago, IL, USA Very Useful. We used the code within our architecture so now the developers automatically see all the PUT_LINE statements when executing the batch scripts in the DOS window. Simple to understand ?   February 20, 2009 - 10am Central time zone Bookmark | Bottom | Top Reviewer: A reader  System.out.println("Executing stored proc."); clStmt = con.prepareCall("begin dbms_output.enable(?); " + "dbms_output.put_line('----hello there---'); " + "dbms_output.put_line('----hello there2---'); " + "end;"); clStmt.setInt(1, 100000); clStmt.execute(); CallableStatement showOutput = con.prepareCall("" + " begin " + " dbms_output.get_line(:1,:status); " + "end;" ); showOutput.registerOutParameter(1, java.sql.Types.VARCHAR); // line showOutput.registerOutParameter(2, java.sql.Types.INTEGER); // done (no more) ? for (int i=0;;i++){ showOutput.executeUpdate(); if(showOutput.getInt(2)== 1){ break; } System.out.println(showOutput.getString(1)); } showOutput.close(); Followup   February 21, 2009 - 9pm Central time zone: I don't know what you mean???? I did however, provide a package (a class, a module, a set of subroutines, whatever you want to call it) to make using dbms_output from a java program "simple" yes. It makes it this simple, code in bold is what you would have to 'code' and it is not hard: public static void main (String args []) throws SQLException { DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@dbserver:1521:ora8i", "scott", "tiger"); conn.setAutoCommit (false); Statement stmt = conn.createStatement(); DbmsOutput dbmsOutput = new DbmsOutput( conn ); dbmsOutput.enable( 1000000 ); stmt.execute ( "begin emp_report; end;" ); stmt.close(); dbmsOutput.show(); dbmsOutput.close(); conn.close(); } Very Good   April 28, 2009 - 5am Central time zone Bookmark | Bottom | Top Reviewer: User  It was very useful one. Thanks a ton

Write a Review