Re: DBMS_OUTPUT.PUT_LINE and JDBC

From: Joseph Weinstein <joe_at_bea.com>
Date: Thu, 18 Jul 2002 13:57:11 -0700
Message-ID: <3D372BA7.3875F72E_at_bea.com>

Ken Koellner wrote:

> I have some Oracle procedures that use DBMS_OUTPUT.PUT_LINE to print
> textual output for the purposes of logging progress on some long
> multi-step updates. I'd like to fire these off from Java/JDBC
> programs. Does anyone know of any way in JDBC to get this messages
> back? I like the Java program to print them to a log file. I need them
> in the Java program as these messages should be interspersed with other
> messages from non-Oracle related tasks the program performs. I spend
> sometime looking at the API Doc for many of the classes/methods in
> java.sql.* and I haven't found anything. Any idea?
>
> -Ken

Hi! Here is a small JDBC program that obtains that DBMS_OUTPUT stuff. Note that this program has only been tested with *our* Oracle driver, but it does work with our driver. Let me know if it works with Oracle's own drivers.
Joe Weinstein at B.E.A.

I get:

here is a line from put_line
another line from put_line

all lines received

---

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

public class ora_getline
{
  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) {;}     }
  }
} Received on Thu Jul 18 2002 - 22:57:11 CEST