Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

return value in stored procedures...

 
Vin Man
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic