Cannot call ANY stored functions from Java

From: jacob nikom <jacob_nikom_at_ieee.org>
Date: 20 Feb 2002 09:50:34 -0800
Message-ID: <6eebd424.0202200950.37a5f0cd@posting.google.com>

Hi,

I cannot call ANY stored procedure from my Java program. All stored functions run very well in PL/SQL, but when I call them from my Java program they crush with error codes like ORA-00600.

Here is the code for one of my stored procedures which runs very well in PL/SQL:

---

PL/SQL code:

CREATE OR REPLACE PACKAGE types AS
TYPE cursorType IS REF CURSOR;
END;
/

CREATE OR REPLACE FUNCTION list_recs (id IN NUMBER) RETURN types.cursorType IS tracks_cursor types.cursorType; BEGIN
OPEN tracks_cursor FOR
SELECT * FROM accounts1
WHERE id = row_number;
RETURN tracks_cursor;
END;
/

variable c refcursor
exec :c := list_recs(11)

SQL> print c

COLUMN1 A1 ROW_NUMBER
---------- ---------- ----------

rec_11     jacob              11
rec_12     jacob              11
rec_13     jacob              11
rec_14     jacob              11
rec_15     jacob              11

--------------------------------------------------------

Here is my Java code:

import java.sql.*;
import java.io.*;

import oracle.jdbc.driver.*;

class list_recs
{
  public static void main(String args[]) throws SQLException, IOException
  {
    String query;
    CallableStatement cstmt = null;
    ResultSet cursor;

    // input parameters for the stored function     String user_name = "jacob";

    // user name and password
    String user = "jnikom";
    String pass = "jnikom";

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    try { Class.forName ("oracle.jdbc.driver.OracleDriver"); }     catch (ClassNotFoundException e)
      { System.out.println("Could not load driver"); }

    Connection conn =

      DriverManager.getConnection (
          "jdbc:oracle:thin:@oracle_url:1521:bosdev",user,pass);

    try
    {

      String sql = "{ ? = call list_recs(?) }";
      cstmt = conn.prepareCall(sql);

      // Use OracleTypes.CURSOR as the OUT parameter type
      cstmt.registerOutParameter(1, OracleTypes.CURSOR);
      String id = "11";
      cstmt.setInt(2, Integer.parseInt(id));

      // Execute the function and get the return object from the call
      cstmt.executeQuery();
      ResultSet rset = (ResultSet) cstmt.getObject(1);

      while (rset.next())
      {
        System.out.print(rset.getString(1) + " ");
        System.out.print(rset.getString(2) + " ");
        System.out.println(rset.getString(3) + " ");
      }

    }
    catch (SQLException e)
    {

      System.out.println("Could not call stored function");
      e.printStackTrace();
      return;

    }
    finally
    {

      cstmt.close();
      conn.close();

    }
    System.out.println("Stored function was called");   }
}

Here is how I run it on Win2K and using Oracle9 on Solaris:

C:\Jacob\Work\Java\Test\Vaultus\Oracle9i\FunctionReturnsResultset>java list_recs
Could not call stored function
java.sql.SQLException: ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [], [], [], []

        at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)

        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)

        at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:889)
        at
oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:490)
        at

oracle.jdbc.driver.OracleStatement.getCursorValue(OracleStatement.java:2661)

        at
oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:4189)

        at
oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:4123)

        at
oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:541)

        at list_recs.main(list_recs.java:42)

C:\Jacob\Work\Java\Test\Vaultus\Oracle9i\FunctionReturnsResultset>

I would appreciate any help,

Jacob Nikom Received on Wed Feb 20 2002 - 11:50:34 CST