Very Computer

Hello,

I am connecting to an Oracle database via the WebLogic JDBC driver.  When I
invoke a stored procedure from my Java/JDBC program, I would like to capture
the output generated by dbms_output.put_line() calls within the stored
procedure.

Does anybody know how to do this?

Thanks,
.sasha

Hi Sasha. What Oracle is doing with put_line()  is putting your output into a
buffer somewhere, so it can be available after your procdure is done. When
you SET SERVEROUTPUT ON is SQL-PLUS,  SQLPLUS  executes
an extra procedure (probably DBMS_OUTPUT.get_line()  to get the
contents of that buffer after the normal returns from your call have been
handled. Therefore, if you want this output from a JDBC driver, you should
first executeUpdate("set serveroutput on"), then after executing your procedure,

call the DBMS_OUTPUT.get_line() procedure (or whatever it is called) and
the buffer contents should be returned. I'll look into this some more, but
please
let me know what you try.

--

Joseph Weinstein        Staff Engineer       http://www.weblogic.com
See our newsgroups: http://www.weblogic.com/services/newsgroups.html
try weblogic stuff for free: http://www.weblogic.com/register.html

Hi Sasha, I did some more work on this, and here's an example or the way to do
it.
First call DBMS_OUTPUT.enable(), then after a procedure is run, call get_line()
with an output varchar, and an output int for status. The varchar will be a line
until
the status int is non-zero, meaning no more lines available.

import java.sql.*;
import weblogic.common.*;

public class test_put_line
{
  public static void main(String argv[]) throws Exception
  {
    java.sql.Connection conn = null;
    try
    {
      java.util.Properties props = new java.util.Properties();
      props.put("user",     "scott");
      props.put("password", "tiger");
      props.put("server",   "goldengate");

      Class.forName("weblogic.jdbc.oci.Driver");
      conn = DriverManager.getConnection("jdbc:weblogic:oracle",
                                         props);

      Statement stmt = conn.createStatement();
      String proc =
        "CREATE OR REPLACE PROCEDURE TEST_PUTLINE "
        + " IS BEGIN "
        + "   DBMS_OUTPUT.PUT_LINE('here is a line from put_line');"
        + "   DBMS_OUTPUT.PUT_LINE('another line from put_line');"
        + " END; ";

      stmt = conn.createStatement();
      stmt.executeUpdate( proc );
      stmt.executeUpdate("BEGIN dbms_output.enable; END;");
      stmt.executeUpdate("BEGIN TEST_PUTLINE; END;");

      CallableStatement cstmt = conn.prepareCall(
                                 "{call dbms_output.get_line(?,?)}");

      cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
      cstmt.registerOutParameter(2, java.sql.Types.INTEGER);

      while (true)
      {
        cstmt.execute();

        if (cstmt.getInt(2) == 0)
        {
          String line = cstmt.getString(1);
          System.out.println( line );
        }
        else
        {
          System.out.println("\nall lines received");
          break;
        }
      }
      cstmt.close();
      stmt.close();
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
    finally
    {
      try {conn.close();}    catch (Exception e) {;}
    }
  }

Quote:}

--

Joseph Weinstein        Staff Engineer       http://www.weblogic.com
See our newsgroups: http://www.weblogic.com/services/newsgroups.html
try weblogic stuff for free: http://www.weblogic.com/register.html