jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases 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 REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "return value in stored procedures..." Watch "return value in stored procedures..." New topic

return value in stored procedures...

Vin Man

Joined: Jun 13, 2001
Posts: 26
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;
con = getDBConnection();
cs = con.prepareCall(
"{? = call HasAccess('Vinman')}");

cs.registerOutParameter(1, java.sql.Types.OTHER);
System.out.println("registered out param");
System.out.println("executed the stored procs");
catch(Exception e){
System.out.println("problem getting access privs of user "
+ userID + ". and the problem is " +
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.
I agree. Here's the link: http://aspose.com/file-tools
subject: return value in stored procedures...
It's not a secret anymore!