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 Using CallableStatement to access an Oracle Proc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Using CallableStatement to access an Oracle Proc" Watch "Using CallableStatement to access an Oracle Proc" New topic

Using CallableStatement to access an Oracle Proc

Mike Firkser
Ranch Hand

Joined: Oct 21, 2003
Posts: 248

Any help on this would be greatly appreciated. I'm trying to access a proc in Oracle using JDBC, and all I get is frustration. The proc works with Crustal Reports, so I know that is no the problem. The vitals:

The proc input is:

In my java class file I have the following in a try/catch

Right now the error I'm getting is "PLS-00306: wrong number or types of arguments in call to 'XYZ_USER_LISTING'
ORA-06550: line 1, column 9:" but I'm passing four Strings and the Proc wants 4 VARCHARs. The "before execute" line is printing out, but not the "after execute" so I assume it is the execute() line that is kicking out the Exception.

Any help will be greatly appreciated. Thanks!
[ September 27, 2005: Message edited by: Mike Rutgers ]

Mike Firkser
Rutgers '84
Annie Smith
Ranch Hand

Joined: Mar 05, 2005
Posts: 172
I am not very sure but I think you might find something here

Cheers!<br /><b>Annie</b>
Natasha Veselova

Joined: Oct 03, 2005
Posts: 1
You are declaring
5 IN parameters in the procedure spec:

PROCEDURE xyz_user_listing( xyz_user_cur IN OUT xyz_user_refcur, org_id IN VARCHAR2, user_status IN VARCHAR2, email_ind IN VARCHAR, userId IN VARCHAR2 )IS . . . .

But passing only 4 (no matter that xyz_user_cur is declared as OUT since it's also declared as IN parameter you should pass some value to the procedure)
That's why you are getting wrong number of arguments exception.

Instead of
callableStatement = conn.prepareCall("{? = CALL XYZ_REPORTS.xyz_user_listing(?, ?, ?, ?)}");

you should have

callableStatement = conn.prepareCall("{CALL XYZ_REPORTS.xyz_user_listing(?, ?, ?, ?, ?)}");

and register the first argument as out parameter.
callableStatement.registerOutParameter(1, Types.INTEGER);
[ October 04, 2005: Message edited by: Natalia Veselova ]
I agree. Here's the link:
subject: Using CallableStatement to access an Oracle Proc
It's not a secret anymore!