Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Usefulness of DBMS_OUTPUT.PUT_LINE?

Re: Usefulness of DBMS_OUTPUT.PUT_LINE?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 6 Jul 2002 06:09:45 -0700
Message-ID: <ag6q6p02eqk@drn.newsguy.com>


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;

 22 end;
 23 /

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");

    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 (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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US