Re: How to set a bind variable in Java?

From: jacob nikom <jacob_nikom_at_ieee.org>
Date: 20 Feb 2002 06:42:06 -0800
Message-ID: <6eebd424.0202200642.59b64252_at_posting.google.com>

jacob_nikom_at_ieee.org (jacob nikom) wrote in message news:<6eebd424.0202191659.c309ffd_at_posting.google.com>...
> Hi,
>
> I have a problem with stored function in PL/SQL returning
> refcursor to Java in Oracle9 on Solaris. When I run the
> Java program which calls the stored function it complains:
> "java.sql.SQLException: ORA-01006: bind variable does not exist"
>
> My stored function runs very well in PL/SQL, but when I call
> it I have to set up a bind variable:
>
> VARIABLE rc REFCURSOR
> EXECUTE :rc := rs_sp_getmodrecs_accounts1('jacob',TO_DATE('12-FEB-02','DD-MON-YY'),TO_DATE('14-FEB-02','DD-MON-YY'))
> print rc
>
> I think the reason for the complain is that I did not set up the
> bind variable rc in Java. I don't know how to do it. Do you know
> how to solve this problem?
>
> Thank you,
>
> Jacob Nikom

Hi,

I decided to add to my mesage the code of my function in PL/SQL and the
code of my Java program. Here is the code of my stored function:

CREATE OR REPLACE PACKAGE VMP_Types
AS
  TYPE VMP_CursorType IS REF CURSOR;
END;
/

CREATE OR REPLACE FUNCTION rs_sp_getmodrecs_accounts1(

                                            user_name  IN VARCHAR,
                                            start_date IN DATE,
                                            stop_date  IN DATE)

RETURN VMP_Types.VMP_CursorType
AS
  l_cursor VMP_Types.VMP_CursorType;
BEGIN
  OPEN l_cursor FOR
    SELECT accounts1.*, rs_accounts1.last_modified     FROM accounts1, rs_accounts1 WHERE
    rs_accounts1.last_modified BETWEEN
    start_date AND stop_date
    AND
     accounts1.a1 = user_name
    AND
     rs_accounts1.last_action = 'modify'     AND
     rs_accounts1.column1 = accounts1.column1;

  RETURN l_cursor;
END;
/

• Execution of the stored procedure in SQL*PLUS VARIABLE rc REFCURSOR EXECUTE :rc := rs_sp_getmodrecs_accounts1('jacob',TO_DATE('12-FEB-02','DD-MON-YY'),TO_DATE('14-FEB-02','DD-MON-YY')) PRINT rc

Here is my Java program:

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

import oracle.jdbc.driver.*;

class GetResultSet
{
  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:_at_11.42.9.45:1521:bosdev",user,pass);
    

    conn.setAutoCommit(true);

    try
    {
      query = "begin :1 := rs_sp_getmodrecs_accounts1('?', ?, ?); end;";

      cstmt = conn.prepareCall(query);

      // Use OracleTypes.CURSOR as the OUT parameter type
      cstmt.registerOutParameter(1, OracleTypes.CURSOR);
      cstmt.setString(2, user_name);
      cstmt.setDate(3, new java.sql.Date(2002,2,12));
      cstmt.setDate(4, new java.sql.Date(2002,2,14));

      // Execute the function and get the return object from the call
      cstmt.execute();
      cursor = ((OracleCallableStatement)cstmt).getCursor(1);

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

    }
    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");   }
} Received on Wed Feb 20 2002 - 15:42:06 CET