File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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 Java Interview Guide this week in the Jobs Discussion 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:
subject: return value in stored procedures...
It's not a secret anymore!