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';