Boolean as argument to a stored procedure not working with JDBC

[g***@alum.mit.edu]

Hi all,

I have a stored (PL/SQL) procedure in Oracle as follows:

SQL> describe approvedb;
PROCEDURE approvedb

Argument Name Type In/Out Default?
----------------------- ----------------------- ------ --------
DBRID NUMBER(10) IN
SWITCHTOPENDAPPROVAL BOOLEAN IN

So I can do

SQL> execute approvedb(33,true);

fine. But when I try to execute PreparedStatement via JDBC,
it gives me "wrong number or types of arguments in call" --
it doesn't like the Boolean object. What's going on?

[Joe Weinstein]

Post by g***@alum.mit.edu
Hi all,
SQL> describe approvedb;
PROCEDURE approvedb
Argument Name Type In/Out Default?
----------------------- ----------------------- ------ --------
DBRID NUMBER(10) IN
SWITCHTOPENDAPPROVAL BOOLEAN IN
So I can do
SQL> execute approvedb(33,true);
fine. But when I try to execute PreparedStatement via JDBC,
it gives me "wrong number or types of arguments in call" --
it doesn't like the Boolean object. What's going on?

Hi. Try this:

PreparedStatement p = c.prepareStatement("{call approvedb( ?, ? )}");
p.setInt(1, 33 );
p.setBoolean(2, true );
p.executeUpdate();

Joe Weinstein at BEA

[g***@alum.mit.edu]

Post by Joe Weinstein
PreparedStatement p = c.prepareStatement("{call approvedb( ?, ? )}");
p.setInt(1, 33 );
p.setBoolean(2, true );
p.executeUpdate();

That's exactly the problem -- that's the code I started with and it
wasn't working.
Well, actually I started with

p.setObject(1,new Integer(33));
p.setObject(2,Boolean.TRUE);

but then I tried the way you describe it, same result.

[Joe Weinstein]

Post by g***@alum.mit.edu
Hi all,
SQL> describe approvedb;
PROCEDURE approvedb
Argument Name Type In/Out Default?
----------------------- ----------------------- ------ --------
DBRID NUMBER(10) IN
SWITCHTOPENDAPPROVAL BOOLEAN IN
So I can do
SQL> execute approvedb(33,true);
fine. But when I try to execute PreparedStatement via JDBC,
it gives me "wrong number or types of arguments in call" --
it doesn't like the Boolean object. What's going on?

Seems like a DBMS/driver bug. I ran this code:

Statement s = c.createStatement();
try{s.executeUpdate("drop table joe");} catch (Exception ignore){}
s.executeUpdate("create table joe( bar int )");
s.executeUpdate("CREATE OR REPLACE procedure joeproc (bar IN BOOLEAN) "
+ " AS BEGIN INSERT INTO JOE VALUES(1); END; ");
System.out.println("We created the proc");

PreparedStatement p = c.prepareStatement("{call joeproc(?)}");
p.setBoolean(1, true );
p.execute();
System.out.println("We executed the proc");

I got:
We created the proc
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'JOEPROC'
ORA-06550: line 1, column 7

I did that with oracle's latest thin driver and a third-party oracle
driver, and got the same!
Joe

[g***@alum.mit.edu]

Post by Joe Weinstein
I did that with oracle's latest thin driver and a third-party oracle
driver, and got the same!

Yup, that's exactly the same error. Thanks, I feel kinda vindicated.
Time for a bug report, I suppose :)

[Angus]

Post by g***@alum.mit.edu

Post by Joe Weinstein
I did that with oracle's latest thin driver and a third-party oracle
driver, and got the same!

Yup, that's exactly the same error. Thanks, I feel kinda vindicated.
Time for a bug report, I suppose :)

Actually, the Oracle documentation indicates that BOOLEAN is a PL/SQL
type only, and there is no mapping between the "java.lang.Boolean"
class and the PL/SQL BOOLEAN data type. There are some tricky
workarounds available. I recall an newsletter by Scott Stephens (via
the "Builder.com" Web site), but I think you'll probably need to
settle for a different type of flag (other than BOOLEAN).

By the way, Joe, don't you have access to "SQL*Plus"? Wouldn't that be
easier to use for creating stored procedures (than using JDBC)?

Good Luck,
Avi.

[g***@alum.mit.edu]

Thanks, Avi.

Post by Angus
class and the PL/SQL BOOLEAN data type. There are some tricky
workarounds available. I recall an newsletter by Scott Stephens (via

Well, for now I suppose the fastest workaround would be to just call
SQL
explicitly, without PreparedStatement and the "?" bind variables
(just passing the literal statement with values filled in).

Post by Angus
By the way, Joe, don't you have access to "SQL*Plus"? Wouldn't that be
easier to use for creating stored procedures (than using JDBC)?

Doesn't really matter for this example, though.

[HansF]

On Fri, 06 May 2005 13:50:13 -0700, grisha interested us by writing:

Funny thing about documentation - it sometimes is useful. Oracle has a
document entitled "Oracle9i JDBC Developer's Guide and Reference" (at
http://docs.oracle.com). In Chapter 19, we find the following statement
AND solution:

------------
Boolean Parameters in PL/SQL Stored Procedures

Due to a restriction in the OCI layer, the JDBC drivers do not support the
passing of BOOLEAN parameters to PL/SQL stored procedures. If a PL/SQL
procedure contains BOOLEAN values, you can work around the restriction by
wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts
the argument as an INT and passes it to the first stored procedure. When
the second procedure is called, the server performs the conversion from
INT to BOOLEAN.

The following is an example of a stored procedure, BOOLPROC, that attempts
to pass a BOOLEAN parameter, and a second procedure, BOOLWRAP, that
performs the substitution of an INT value for the BOOLEAN.
------------

Strong suggestion when working with Oracle stuff .... if at first you
don't succeed, check the docco before yelling for help. <g>

/Hans

[g***@alum.mit.edu]

Post by HansF
Strong suggestion when working with Oracle stuff .... if at first you
don't succeed, check the docco before yelling for help.

Thanks, Hans. I thought I googled enough for this before asking the
question,
but I guess not :)