Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

17003 Invalid Column Index

843854Mar 31 2005 — edited Apr 1 2005
Hello,
I am calling a pl/sql function from java. The function returns a varchar2 value. I'm getting a 17003 Invalid column index: error. at this line:
String status = myResultSet.getString(1); Any ideas or help would be greatly appreciated. Thanks in advance.


JavaCode:
  public static void main (String args []) {

    // declare Connection and Statement objects
    Connection myConnection = null;
    CallableStatement myStatement = null;

    try {

      // register the Oracle JDBC drivers
      DriverManager.registerDriver(
        new oracle.jdbc.OracleDriver()
      );

      // create a Connection object, and connect to the database
      // using the Oracle JDBC Thin driver
      myConnection = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "uname",
        "pword"
      );
     //String sql = "{call ioboard.GetStatus(?)}";
     String sql = "{?=call ioboard.GetStatus(?)}";

      // create a Statement object
      myStatement = myConnection.prepareCall( sql );
      
      myStatement.registerOutParameter(1, java.sql.Types.LONGVARCHAR );
      myStatement.setString( 2, "Kelly.Brace" );
      // create a ResultSet object, and populate it with the
      // result of a SELECT statement 
      ResultSet myResultSet = myStatement.executeQuery();
  
      // retrieve the row from the ResultSet using the
      // next() method
      myResultSet.next();

      // retrieve the user from the row in the ResultSet using the
      // getString() method
      String status = myResultSet.getString(1);
      
      System.out.println("Hello Kelly, your status is: " + status);
   
      // close this ResultSet object using the close() method
      myResultSet.close();

    } catch (SQLException e) {

      System.out.println("Error code = " + e.getErrorCode());
      System.out.println("Error message = " + e.getMessage());
      System.out.println("Stack message = \n"); 
       e.printStackTrace();
      
    } finally {

      try {

        // close the Statement object using the close() method
        if (myStatement != null) {
          myStatement.close();
        }

        // close the Connection object using the close() method
        if (myConnection != null) {
          myConnection.close();
        }

      } catch (SQLException e) {

        System.out.println("Error code = " + e.getErrorCode());
        System.out.println("Error message = " + e.getMessage());

      }

    }

  } // end of main()
Here's what the function looks like:
CREATE OR REPLACE FUNCTION GetStatus( user_name in varchar2)
RETURN VARCHAR2
is
v_status varchar2(10);
BEGIN
select iob_location into v_status 
from ioboard.iob_user 
where iob_username = user_name;
 
  RETURN( v_status);
END;
This works perfectly in the SQL Window:
select iob_location
from ioboard.iob_user 
where iob_username = 'Kelly.Brace';
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 29 2005
Added on Mar 31 2005
4 comments
1,583 views