Usefulness of DBMS_OUTPUT.PUT_LINE?
Ramon F Herrera
having 2 different purposes:
- interactive debugging
- returning some value(s) to the calling program.
What about the DBMS_OUTPUT.PUT_LINE utility?
Is is used only to display stuff from SQL*PLUS,
and therefore limited to interactive sessions?
OR
DBMS_OUTPUT.PUT_LINE is really flexible (like 'printf')
and it can be use, for instance to return some value
from a stored procedure to a remote Java client?
TIA,
-Ramon F. Herrera
Sybrand Bakker
wrote:
The answer is a).
What do I get know as reward,for answering such an obvious question?
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Connor McDonald
Check out the utl_file package, or dbms_application_info
hth
connor
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Thomas Kyte
says...
I'm a C programmer at heart.
I'm really confused by your post here -- how is printf used to return something
to the calling program?????? I mean, other then displaying stuff on stdout --
sure I could use pipes with fork/exec/dup to capture stdout but that isn't
really "returning" something in the conventional sense.
Anyway, you can use dbms_output to display stuff on screen in sqlplus or svrmgrl
-- because they call dbms_output.get_lines to display it.
Your programs can do the same. Here is a very small excerpt from my book that
describes this (lots of other stuff in there as well)
.....
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.
>
>TIA,
>
>-Ramon F. Herrera
--
Thomas Kyte (tk...@oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp