problem calling stored procedure

[Scott Lynch]

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]

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:

> CallableStatement csStatement = m_cConnection.prepareCall("call PROC_FOO(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
                                                                           1  2  3  4  5  6  7  8  9  10 11

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]

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:

> CallableStatement csStatement = m_cConnection.prepareCall("call PROC_FOO(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
                                                                           1  2  3  4  5  6  7  8  9  10 11

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]

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]

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:

    private class AccumDataRetriever extends DataRetriever
    {
        // Stored procedure parameters
        final static int RETURN_VAL = 1;
        final static int P_CONS_ID = 2;
        final static int P_WINDOW_TYPE = 3;
        final static int P_AMOUNT = 4;
        final static int P_COUNT = 5;
        final static int P_ERR_MSG = 6;
        final static String WINDOW_TYPE = "7D";
        public AccumDataRetriever(Connection cConnection) throws java.sql.SQLException { super(cConnection); }
        protected PreparedStatement getSQLStatement() throws java.sql.SQLException
        {
            StringBuffer sBuffer = new StringBuffer();
            CallableStatement csStatement;
            sBuffer.append("{ ? = call PKG_ACCUM.GetAccumFromId(?, ?, ?, ?, ?) }");
            csStatement = m_cConnection.prepareCall(sBuffer.toString());
            csStatement.registerOutParameter(RETURN_VAL,java.sql.Types.INTEGER);
            csStatement.registerOutParameter(P_AMOUNT,java.sql.Types.DOUBLE);
            csStatement.registerOutParameter(P_COUNT,java.sql.Types.INTEGER);
            csStatement.registerOutParameter(P_ERR_MSG,java.sql.Types.VARCHAR);
            return (PreparedStatement)csStatement;
        }
        protected void setBindVariables(AuthPromptHashtable hInput) throws java.sql.SQLException
        {
            CallableStatement csStatement = (CallableStatement)m_pstStatement;
            csStatement.setString(P_WINDOW_TYPE,WINDOW_TYPE);
            csStatement.setLong(P_CONS_ID,Long.parseLong((String)hInput.get("CONS_ID")));
        }
        protected void addToOutput(ResultSet rsResults, AuthPromptHashtable hOutput) throws java.sql.SQLException
        {
            CallableStatement csStatement = (CallableStatement)m_pstStatement;
            if(csStatement.getInt(RETURN_VAL) == 0)
            {
                addNextValue(hOutput,"ID_ACCUM_AMT",new Integer(0));
                addNextValue(hOutput,"ID_ACCUM_CNT",new Integer(0));
                addValueOnce(hOutput,"AccumError",csStatement.getString(P_ERR_MSG));
            }
            else
            {
                addNextValue(hOutput,"ID_ACCUM_AMT",new Integer( (int) ((csStatement.getDouble(P_AMOUNT) + .005) * 100)));  // Prevent rerounding errors by adding half a cent
                addNextValue(hOutput,"ID_ACCUM_CNT",new Integer(csStatement.getInt(P_COUNT)));
            }
        }
   }

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]

Okay.. well.. I found the solution to the issue:

Solution Description
--------------------
This is an invalid use of JDBC methods. You shouldn't use executeQuery() to
call a stored procedure, even if the procedure performs a query. To call a
stored procedure with JDBC, use the executeUpdate() method.
See the section titled, Stored procedure Calls in JDBC Programs, 
in Chapter 3 of the JDBC Developer's Guide for additional information on
calling PL/SQL stored procedures from JDBC.

I think the main problem was that my code was trying to loop through the ResultSet that was returned by a procedure/function call.