Re: Native SQL statements (and/or PL/SQL)

From: Joseph Weinstein <joe_at_weblogic.com>
Date: 1998/11/25
Message-ID: <365C5E95.40287208_at_weblogic.com>#1/1

Atish Kumar wrote:

> I cannot seem to see anything in the JDBC spec that allows me to
> talk natively to the database, and get the result perhaps as
> probably as say an AsciiStream.

Hi Atish! Here's some code that will retrieve Oracle put_line stuff. It works with our Oracle JDBC driver, and may well work with theirs. Let me know if it helps,

Joe Weinstein at BEA, the home of the WebLogic Java Application Server.

---

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

public class t2oraproc
{
  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",   "myserver");

      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) {;}}   }
}

--
Joseph Weinstein       Staff Engineer            http://www.weblogic.com
See our newsgroups:     http://www.weblogic.com/services/newsgroups.html
try weblogic free *with support*: http://www4.weblogic.com/howtoregister.html

Received on Wed Nov 25 1998 - 00:00:00 CET