Skip to Main Content

SQL & PL/SQL

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!

Calling PL/SQL procedure that returns boolean in java

741415Aug 13 2010 — edited Aug 13 2010
Hi All,
Was not sure weather to post this on Java forum threads or PL/SQL forum threads. So posting at both locations.

I have to call a PL/SQL procedure from java. This PL/SQL has IN/OUT parameters as well as return a Boolean value.
---------------------------------------------------------
The procedure definition is as follows:

FUNCTION GET_NEXT(O_error_message IN OUT VARCHAR2,
IO_item_no IN OUT ITEM_MASTER.ITEM%TYPE,
I_item_type IN ITEM_MASTER.ITEM_NUMBER_TYPE%TYPE)
return BOOLEAN;
END ITEM_NUMBER_TYPE_SQL;
--------------------------------------------------------

And the java function I am using is as follows:
This is in the ADF Application module impl code

---------------------------------------------------------
public String callNextItem(){
CallableStatement callableStmt = null;
String rmsUser = getDBTransaction().getConnectionMetadata().getUserName();
String callableStatement = "begin ? := ITEM_NUMBER_TYPE_SQL.VALIDATE_FORMAT(?,?,?); end;";
System.out.println("callableStatement "+callableStatement);
try{
callableStmt = getDBTransaction().createCallableStatement(callableStatement,0);
callableStmt.registerOutParameter(1, Types.*BIT*);
callableStmt.registerOutParameter(2, Types.VARCHAR);
callableStmt.registerOutParameter(3, Types.VARCHAR);
callableStmt.registerOutParameter(4, Types.VARCHAR);
callableStmt.setBoolean(1, false);
callableStmt.setString(2, "");
callableStmt.setString(3, "");
callableStmt.setString(4, "UPC-A");
callableStmt.executeUpdate();
System.out.println("STATUS : " + callableStmt.getString(3));
System.out.println("ERROR : " + callableStmt.getString(2));
String status = "";
getDBTransaction().commit();
System.out.println("commited ");
callableStmt.close();
return status;
}catch(SQLException e){
System.out.println("Error:" +e);
throw new JboException(e);
}
}
------------------------------------------------------
But this function never works. Throws "not valid expression type" error.

I have called several PL/SQL procedures before, only difference being
they never used to return any value. Particularly I feel the cause of the
error is the Boolean type that is returned from the procedure.

If you have any idea, please help.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 10 2010
Added on Aug 13 2010
1 comment
969 views