Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using CallableStatement to access an Oracle Proc

 
Mike Firkser
Ranch Hand
Posts: 249
Java Oracle PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Annie Smith
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not very sure but I think you might find something here
 
Natasha Veselova
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are declaring
5 IN parameters in the procedure spec:
code:

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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic