oracle 收到信息,java程序接收oracle的dbms_output信息

The following is an excerpt from Tom Kyte's expert one on one Oracle. Is it what you want?

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

racle:thin:@dbserver:1521

ra8i",

"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.[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18921899/viewspace-1017083/，如需转载，请注明出处，否则将追究法律责任。