aspose file tools*
The moose likes JDBC and the fly likes return value in stored procedures... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "return value in stored procedures..." Watch "return value in stored procedures..." New topic
Author

return value in stored procedures...

Vin Man
Greenhorn

Joined: Jun 13, 2001
Posts: 26
hi,
I have a stored function named HasAccess(UserID_in IN CHAR) return boolean, which is compiled and tested properly in SQLPlus. I am attempting to call the stored function in a java program as follows:
class xyz {
public boolean isUpdatable(String userID){
Connection con;
CallableStatement cs;
boolean zzz;
try{
con = getDBConnection();
try{
cs = con.prepareCall(
"{? = call HasAccess('Vinman')}");

cs.registerOutParameter(1, java.sql.Types.OTHER);
System.out.println("registered out param");
cs.execute();
System.out.println("executed the stored procs");
}
finally{}
}
catch(Exception e){
System.out.println("problem getting access privs of user "
+ userID + ". and the problem is " +
e.getMessage());
}
return zzz;

}
During run-time, my program failed on the line where I am trying to registered the return varaiable (cs.registerOutParameter(1, java.sql.Types.OTHER);
), and e.getMessage() is "ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

What type should I use in the registered method?
Thanks in advance...
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

from what I have seen, there is no such thing as java.sql.Types.BOOLEAN. Your method of returning a java.sql.Types.OTHER requires that you return an object, and a boolean value is not an object( at least I assume that this is true in PL/SQL ). Try returning and int value where 1 is true and 0 is false. Then you can safely use java.sql.Types.INTEGER. I'm not aware of a way to safely return a BOOLEAN data type from a PL/SQL procedure.
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: return value in stored procedures...
 
Similar Threads
trying to get rows to ResultSet with CallableStatement and stored function
help:java.sql.SQLException: ORA-06550
Problem calling IN parameters Stored Procedure from Java
Oracle wrong number or types of arguments...
Calling a function vs a procedure on Oracle with CallableStatement