Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Usefulness of DBMS_OUTPUT.PUT_LINE?
In article <c9bc36ff.0207052048.10fcc707_at_posting.google.com>, ramon_at_conexus.net
says...
> >Being a C programmer, I am accustomed to the 'printf' statement >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?
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_at_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;
Procedure created.
scott_at_TKYTE816> set serveroutput on format wrapped
scott_at_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
{
/*
/*
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;" );
/*
/*
/*
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;}
/*
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");
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 (tkyte@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 Received on Sat Jul 06 2002 - 08:09:45 CDT