• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

problem calling stored procedure

 
Scott Lynch
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm getting the following error when trying to call a stored procedure:
java.sql.SQLException: ORA-01009: missing mandatory parameter
It's a stored procedure that has 9 "OUT" parameters, and one "IN" parameter. My prepare statement looks like this:
CallableStatement csStatement = m_cConnection.prepareCall("call PROC_FOO(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
I'm registering all 9 "OUT" parameters as either java.sql.Types.VARCHAR or java.sql.Types.NUMERIC depending on whether or not they're declared as VARCHAR2 or NUMBER in PL/SQL. I've tried quite a few things with different (but bad) results.
1) Adding an extra parameter to the end results in a "ORA-06553: PLS-306: wrong number or types of arguments in call to 'PROC_FOO'" error.
2) Trying to make the procedure a function (? = call...) resulted in a "PLS-00222: no function with name 'PROC_FOO' exists in this scope" error.
3) Surrounding the procedure call with {}'s resulted in a "ORA-00600: internal error code, arguments: [12259], [], [], [], [], [], [], []" error (considering forwarding this one to Oracle support).
This is using JRE 1.4 and Oracle 9i (8.2.0.32).
Anyone have any other crazy ideas I can try?
[ June 22, 2002: Message edited by: Scott Lynch ]
 
Kevin Mukhar
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you sure you are calling it correctly?
You claim there are ten parameters:
> It's a stored procedure that has 9 "OUT"
> parameters, and one "IN" parameter.
But the code snippet you posted has 11 parameters:

Are you sure you are registering the parameters correctly? Each parameter is numbered as shown above REGARDLESS of whether it is IN or OUT.
I'm going to guess that the IN parameter is the first in the list, and all the OUT parameters follow it. Thus, when you register the OUT parameters, you start counting at 2, because the IN parameter is 1. That is, you should NOT have the following:
registerOutParameter(1, java.sql.Types.VARCHAR);
[ June 24, 2002: Message edited by: Kevin Mukhar ]
 
Scott Lynch
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Kevin Mukhar:
Are you sure you are calling it correctly?
You claim there are ten parameters:
> It's a stored procedure that has 9 "OUT"
> parameters, and one "IN" parameter.
But the code snippet you posted has 11 parameters:

Are you sure you are registering the parameters correctly? Each parameter is numbered as shown above REGARDLESS of whether it is IN or OUT.
I'm going to guess that the IN parameter is the first in the list, and all the OUT parameters follow it. Thus, when you register the OUT parameters, you start counting at 2, because the IN parameter is 1. That is, you should NOT have the following:
registerOutParameter(1, java.sql.Types.VARCHAR);
[ June 24, 2002: Message edited by: Kevin Mukhar ]

Looks like I pasted the code that I had set up when I was testing to see what would happen with extra parameters. The IN parameter is actually the last one (I thought that odd myself.. but I didn't write the procedure). And yeah, I've got 9 calls to registerOutParameter() numbering 1-9, and then one call to setString() for the 10th parameter (OUT).
More new information: I seem to get this error *only* when preparing procedure/function calls using the braces around it. i.e. -
Works:
csStatement = m_cConnection.prepareCall("? = call bar(?, ?, ?)");
Doesn't work:
csStatement = m_cConnection.prepareCall("{ ? = call bar(?, ?, ?) }");
Odd, eh?
 
Kevin Mukhar
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Scott Lynch:
More new information: I seem to get this error *only* when preparing procedure/function calls using the braces around it. i.e. -
Works:
csStatement = m_cConnection.prepareCall("? = call bar(?, ?, ?)");
Doesn't work:
csStatement = m_cConnection.prepareCall("{ ? = call bar(?, ?, ?) }");
Odd, eh?[/QB]

Most definitely odd. According to both Sun and Oracle, the correct format for creating a callable statement for a stored procedure call is
csStatement = m_cConnection.prepareCall("{ call PROC_FOO(?, ?, ?) }");
So, when you use code like this exactly which error message do you get? Could you also post some of your code?
 
Scott Lynch
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can post some of it.. but there's really too much code to post it all.. I wrote an abstract servlet class with an abstract inner "data retrieval" class so all I have to write for new data retrieval servlets is a prepare function, a bind function, and a fetchrow function. Here's the class file that tries to call the package:

As far as the error I get when using the braces..
java.sql.SQLException: ORA-00600: internal error code, arguments: [12259], [], [], [], [], [], [], []
FWIW, I believe this is using Oracle 9.0.1.0 (I think I had said 8.2.0.32 previously)
EDIT: I get the same error message on the procedure call as I do on the function call.
[ June 25, 2002: Message edited by: Scott Lynch ]
[ June 25, 2002: Message edited by: Scott Lynch ]
 
Scott Lynch
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay.. well.. I found the solution to the issue:

I think the main problem was that my code was trying to loop through the ResultSet that was returned by a procedure/function call.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic