Skip to Main Content
  • Questions
  • Retrieving DBMS_OUTPUT.put_line from JDBC?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mark.

Asked: July 27, 2005 - 5:13 pm UTC

Last updated: August 03, 2017 - 1:04 am UTC

Version: 9.2.0.4

Viewed 10K+ times! This question is

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.


Rating

  (26 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

This worked great!

Mark, July 28, 2005 - 12:18 pm UTC

Thank you, Tom for your quick and comprehensive response. This really helps me!

Dubugging how java program printing console out from package.

Anupam v Rahangdale, April 24, 2006 - 9:05 am UTC

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

Ganti Raghunath, August 18, 2006 - 5:55 am UTC

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?

From an Oracle guy who doesn't know Java, September 05, 2006 - 11:01 am UTC

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?

Tom Kyte
September 05, 2006 - 5:15 pm UTC

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

A reader, September 27, 2006 - 10:44 am UTC

Thanks for this solution that saved me a lot of effort:)
sheers

AlcoNaft, July 05, 2008 - 3:45 pm UTC

Thanks a lot! Your post saves a lot of time for me :)

Usage of this information

A reader, October 10, 2008 - 1:04 pm UTC

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 ?

A reader, February 20, 2009 - 10:35 am UTC

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();
Tom Kyte
February 21, 2009 - 9:08 pm UTC

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();
<b>
    DbmsOutput dbmsOutput = new DbmsOutput( conn );
    dbmsOutput.enable( 1000000 );
</b>
    stmt.execute
    ( "begin emp_report; end;" );
    stmt.close();
<b>
    dbmsOutput.show();
    dbmsOutput.close();
</b>
    conn.close();
}

Very Good

User, April 28, 2009 - 5:05 am UTC

It was very useful one. Thanks a ton

Mixed logs from different procedures

Raúl, April 05, 2010 - 5:15 am UTC

Hello Tom,

First of all, thanks for the usefull code.

I have a question with regard to the class DbmsOutput. In the lines below you first call the procedure emp_report:

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

and then you read the logs that it wrote during its execution.

dbmsOutput.show();

If a different session is calling another procedure at the same time, the logs that the class DbmsOutput will show, would be a mix of all the procedures executed at this time, wouldn't it?. In that case isn't there any way to get only those logs asociated at a particular Oracle session?.

Thanks in advance
Tom Kyte
April 05, 2010 - 10:09 pm UTC

that is the way it works - packages and stored procedures would be utterly useless if they mixed data from different sessions.


dbms_output.put_line - writes to an array stored as a package global variable. package variables are specific to a SESSION.

dbms_output.get_line(s) - reads that private array (private to the session) and returns the values found therein.

alternative

RC, April 07, 2010 - 12:22 am UTC

Some peeople use pipelined functions as an alternative for dbms_output: http://berxblog.blogspot.com/2009/01/pipelined-function-vs-dbmsoutput.html

helped see into stored procedure

A reader, September 14, 2010 - 4:31 pm UTC

I was executing a stored procedure from JDBC and it wasn't giving me any feedback so I could not verify if it was connecting at all b/c the stored procedure wasn't returning anything. I am now able to see what dbms_output lines to see the interaction. Thanks a whole lot!!

Proc Call using Spring StoredProcedure

Sreenath, September 23, 2010 - 1:30 am UTC

Printing DBMS output on java console works fine when i run the proc with a simple statement Object but when i using the spring's StoredProcedure object and make a call it does not print logs.

When i tried to analyse, the only difference b/w the both the approaches was that, the code snippet mentioned above was executing a anonymous block of code, whereas i am trying to directly make a proc call without anonymous block.

I would be grateful if you can suggest a solution for this. In case you need any clarifications on this please let me know.
Tom Kyte
September 23, 2010 - 10:52 am UTC

this doesn't make any sense to me. we (plsql) don't care how we are called.


the dbms_output stuff really doesn't know if it was called or executed - as long as dbms_output.enable was invoked, all dbms_output.put_line does it write to an array.

something else is going wrong here - you'll need to debug this a little more.

@A reader

Oleksandr Alesinskyy, September 24, 2010 - 8:30 am UTC

It is not possible to say anything useful without seeing (at least a snippet of) your code.


Proc Call using Spring StoredProcedure

Sreenath, September 27, 2010 - 1:06 am UTC

To the asktom follow up team,

please delete my above post which contains the code snippet.
Tom Kyte
September 27, 2010 - 12:07 pm UTC

there is no I in team.

and it is just "I" here - no team.

Thank you so much

Rodolfo, June 28, 2012 - 2:41 pm UTC

We are in 2012 and this topic still saving lives!

Thanks again

Richard, September 26, 2012 - 2:06 pm UTC

I second Rodolfo's comment - 2012 and 110% useful. Thanks much!!!

Yeah

it050115, November 19, 2012 - 5:01 am UTC

Still very useful. thx

Output not coming on terminal

Dinesh Salve, January 02, 2013 - 12:32 am UTC

Dear Tom,
Thanks a lot for this article.
Below is what I implemented in pro*c but i am unable to get output on terminal as for(;;) is never coming out I guess.

int enable_DbmsOutput ()
{
EXEC SQL CALL dbms_output.enable(100000);

if (sql_error_chk(g_ora_err_msg_buffer) != SUCCESS)
{
traceprintf(ERRDEF,"Unable to enable dbms_output logging. \n Error message is %s",g_ora_err_msg_buffer);
return FAILURE;
}
else
{
traceprintf(INFODEF,"dbms_output logging is enabled.");
}
return SUCCESS;

}


int disable_DbmsOutput ()
{
EXEC SQL CALL dbms_output.disable;

if (sql_error_chk(g_ora_err_msg_buffer) != SUCCESS)
{
traceprintf(ERRDEF,"Unable to disable dbms_output logging. \n Error message is %s",g_ora_err_msg_buffer);
return FAILURE;
}
else
{
traceprintf(INFODEF,"dbms_output logging is disabled.");
}
return SUCCESS;

}



int show_DbmsOutput ()
{
int done;
char l_output[1000];
char get_output_str[1000]="declare l_line varchar2(255); l_done number; l_buffer long; begin loop exit when length(l_buffer)+255 > 1000 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; :l_output := l_buffer; end;";

traceprintf(TRCDEF,"dbms_output starts here.");

for(;;)
{
EXEC SQL EXECUTE get_output_str;
traceprintf(TRCDEF,"%s",l_output);
if (done == 1) break;
}

if (sql_error_chk(g_ora_err_msg_buffer) != SUCCESS)
{
traceprintf(ERRDEF,"Unable to disable dbms_output logging. \n Error message is %s",g_ora_err_msg_buffer);
return FAILURE;
}
else
{
traceprintf(TRCDEF,"dbms_output logging is disabled.");
}

traceprintf(TRCDEF,"dbms_output ends here.");
return SUCCESS;

}


I am calling function enable_DbmsOutput(); after connecting to database and show_DbmsOutput(); before "commit work release;" but I see no output and cursor just waiting in loop.

Please help to get this resolved.

Tom Kyte
January 04, 2013 - 1:43 pm UTC

use static sql if you want the host variables to be automagically populated for you:

http://docs.oracle.com/cd/B14117_01/appdev.101/a97269/pc_afemb.htm#i9680


otherwise - we don't "see" the :done, :l_output, etc stuff in there (the precompiler doesn't) so you'd have to explicitly tell us about them.


http://docs.oracle.com/cd/B14117_01/appdev.101/a97269/pc_afemb.htm#sthref3076

suggest you use static sql (first link)

Very nice solution

DeeJay, March 04, 2014 - 4:53 pm UTC

I was looking for this question, trying to find out on web but couldnt find the solution anywhere apart from this place.

My procedure is too big and makes call to other sub procedures, which I want to test while calling that procedure from web.

I did modifiactions to my code as suggested, but somehow I am not able to get the result on my console. (I am making a simple JDBC call through websphere application server). Can you please help me what else do I need to do.


CallableStatement enableOut = null;
CallableStatement disableOut = null;


enableOut = conn.prepareCall("begin dbms_output.enable(?); end;" );
disableOut = conn.prepareCall("begin dbms_output.disable; end;");


enableOut.setInt(1, 1000000);
enableOut.executeUpdate();

stmt.execute(); // This is where I am calling my procedure

disableOut.executeUpdate();

enableOut.close();
disableOut.close();
stmt.close();

Great Answer

A reader, November 13, 2014 - 2:16 pm UTC

Hi Darling,

This is what we have exactly implemented in our code.

Thanks

Very Useful

A reader, May 13, 2015 - 9:31 am UTC

Thanks to this code

dbms_output (ormatted) in Java logger file

Bob, June 19, 2015 - 7:18 pm UTC

Hi Tom,

I am using the log4j utility in Java to write out my logs. I am able to get the dbms_output to come into my logfiles but it comes back as a single string.

Is it possibble to have each dbms_output message to come separately in my log file?

Thanks
Bob

Open Cursors after DBMS_OUTPUT.DISABLE

Pedro Reta, October 19, 2016 - 8:52 pm UTC

Hi Tom,

I have just one question. I noticed that you do not call dbms_output.disable in your example. Is there any reason for this?
In my case, I'm sending SQL strings to the Java connection to get evaluated. However, if I call dbms_output.enable, then submit a few dbms_output.put_line(...) calls and then I cast dbms_output.disable, it seems this process leaves an open cursor in the connection for each additional statement submitted for evaluation.

For example,

DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE(...); ............
DBMS_OUTPUT.PUT_LINE(...); ............
DBMS_OUTPUT.PUT_LINE(...); ............
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.PUT_LINE(...); ............ opens cursor
SELECT 1 FROM DUAL ................... opens cursor

I'm using this query to check the count of open cursors:

SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;

Is this normal? And if it is, what does it mean? Is the cursor intended to be there for further reading?

Thanks so much for your contributions!
Connor McDonald
December 07, 2016 - 2:41 pm UTC

(Sorry for the delay)

Can you elaborate more on what you are seeing - here is my test case


SQL>
SQL> exec DBMS_OUTPUT.ENABLE;

PL/SQL procedure successfully completed.

SQL> exec DBMS_OUTPUT.PUT_LINE('...');

PL/SQL procedure successfully completed.

SQL> exec DBMS_OUTPUT.PUT_LINE('...');

PL/SQL procedure successfully completed.

SQL> exec DBMS_OUTPUT.PUT_LINE('...');

PL/SQL procedure successfully completed.

SQL> exec DBMS_OUTPUT.DISABLE;

PL/SQL procedure successfully completed.

SQL> exec DBMS_OUTPUT.PUT_LINE('...');

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT a.value as highest_open_cur
  2  FROM v$mystat a, v$statname b
  3  WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' ;

HIGHEST_OPEN_CUR
----------------
               2

1 row selected.

SQL>
SQL> SELECT 1 FROM DUAL;

         1
----------
         1

1 row selected.

SQL>
SQL> SELECT a.value as highest_open_cur
  2  FROM v$mystat a, v$statname b
  3  WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' ;

HIGHEST_OPEN_CUR
----------------
               2

1 row selected.

SQL>
SQL> SELECT 2 FROM DUAL;

         2
----------
         2

1 row selected.

SQL>
SQL> SELECT a.value as highest_open_cur
  2  FROM v$mystat a, v$statname b
  3  WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' ;

HIGHEST_OPEN_CUR
----------------
               2

1 row selected.

SQL>
SQL> SELECT 3 FROM DUAL;

         3
----------
         3

1 row selected.

SQL>
SQL> SELECT a.value as highest_open_cur
  2  FROM v$mystat a, v$statname b
  3  WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' ;

HIGHEST_OPEN_CUR
----------------
               2

1 row selected.

SQL>
SQL> SELECT 4 FROM DUAL;

         4
----------
         4

1 row selected.

SQL>
SQL> SELECT a.value as highest_open_cur
  2  FROM v$mystat a, v$statname b
  3  WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' ;

HIGHEST_OPEN_CUR
----------------
               2

1 row selected.

SQL>
SQL>


Very useful code

Sumant, August 02, 2017 - 11:09 am UTC

There is no problem in code, however when i retrieve data from sql script file and run it, gives an error : Invalid SQL statement.

My script file has code to display sysdate.

I tried to fetch data from script file line by line and character by character, that was not useful.
Do you have any idea what might be the problem?
Thank you.
Connor McDonald
August 03, 2017 - 1:04 am UTC

No.

You gave us no clues...so we dont have a clue about the answer.

DBMS_OUT on JAVA Console

Ali, December 17, 2020 - 1:18 pm UTC

This is an excellent solution, so much more helpful in tracing the code logic that spreads across JAVA and Oracle and moves back and forth. Extremely useful. Thank you Tom. Always relevant !.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here