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

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Mark.

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

Answered by: Tom Kyte - Last updated: August 03, 2017 - 1:04 am UTC

Category: Developer - Version: 9.2.0.4

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Haversine PL/SQL

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.


and you rated our response

  (25 ratings)

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

Reviews

This worked great!

July 28, 2005 - 12:18 pm UTC

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 - 9:05 am UTC

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 - 5:55 am UTC

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 05, 2006 - 11:01 am UTC

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?

Tom Kyte

Followup  

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

September 27, 2006 - 10:44 am UTC

Reviewer: A reader

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

July 05, 2008 - 3:45 pm UTC

Reviewer: AlcoNaft from Norway

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

Usage of this information

October 10, 2008 - 1:04 pm UTC

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 - 10:35 am UTC

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();
Tom Kyte

Followup  

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

April 28, 2009 - 5:05 am UTC

Reviewer: User

It was very useful one. Thanks a ton

Mixed logs from different procedures

April 05, 2010 - 5:15 am UTC

Reviewer: Raúl from Spain

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

Followup  

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

April 07, 2010 - 12:22 am UTC

Reviewer: RC from The Netherlands

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

September 14, 2010 - 4:31 pm UTC

Reviewer: A reader

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

September 23, 2010 - 1:30 am UTC

Reviewer: Sreenath from India

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

Followup  

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

September 24, 2010 - 8:30 am UTC

Reviewer: Oleksandr Alesinskyy

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


Proc Call using Spring StoredProcedure

September 27, 2010 - 1:06 am UTC

Reviewer: Sreenath from India

To the asktom follow up team,

please delete my above post which contains the code snippet.
Tom Kyte

Followup  

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

June 28, 2012 - 2:41 pm UTC

Reviewer: Rodolfo from Brazil

We are in 2012 and this topic still saving lives!

Thanks again

September 26, 2012 - 2:06 pm UTC

Reviewer: Richard from CA

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

Yeah

November 19, 2012 - 5:01 am UTC

Reviewer: it050115

Still very useful. thx

Output not coming on terminal

January 02, 2013 - 12:32 am UTC

Reviewer: Dinesh Salve from Hyderabad, India

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

Followup  

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

March 04, 2014 - 4:53 pm UTC

Reviewer: DeeJay from New Jersey

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

November 13, 2014 - 2:16 pm UTC

Reviewer: A reader

Hi Darling,

This is what we have exactly implemented in our code.

Thanks

Very Useful

May 13, 2015 - 9:31 am UTC

Reviewer: A reader from Philippines

Thanks to this code

dbms_output (ormatted) in Java logger file

June 19, 2015 - 7:18 pm UTC

Reviewer: Bob from UK

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

October 19, 2016 - 8:52 pm UTC

Reviewer: Pedro Reta from MX

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

Followup  

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

August 02, 2017 - 11:09 am UTC

Reviewer: Sumant from India

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

Followup  

August 03, 2017 - 1:04 am UTC

No.

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

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here