I have a java stored procedure to insert data into oracle tables. It works fine when I test in test instance but when running the same procedure in production gives me an error
"Error while calling SXPA_UPDATE_PAID_REQUEST java.sql.SQLException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'SXPA_UPDATE_PAID_REQUEST'
I checked the java types in both test and production evironments and both are same.
can someone please help me how to fix this error.
I have attached the stored procedure and jave code that calls this procedure.
CREATE OR REPLACE PROCEDURE SXPA_UPDATE_PAID_REQUEST (
    -- OUTPUT
    RETURN_CODE OUT NUMBER,
    RETURN_MESSAGE OUT VARCHAR,
    
    -- INPUT
    P_REQUEST_NUMBER IN NUMBER,
    P_REQUEST_SEQUENCE IN NUMBER,
    P_PA_CODE IN VARCHAR2,
    P_PA_SUB_CODE IN VARCHAR2,
    P_COUNTRY_CODE IN VARCHAR2,
    P_REPAIR_ORDER IN VARCHAR2,
    P_REPAIR_LINE IN VARCHAR2,
    P_VIN IN VARCHAR2,
    P_GACES_TIMESTAMP IN DATE,
    
    P_STATUS_CODE IN VARCHAR2,
    P_PAYMENT_DATE IN DATE,
    
    P_USER_ID IN VARCHAR2,
    
    P_PAID_AMOUNT IN NUMBER
)
IS
   V_RESULT NUMBER := 0;
   EX_REQUEST_NOT_FOUND EXCEPTION;
   EX_DETAIL_NOT_FOUND EXCEPTION;
   EX_INSERT_FAILED EXCEPTION;
   EX_REQUEST_IGNORED EXCEPTION;
   EX_INVALID_KEY EXCEPTION;
   EX_INVALID_KEY_P_A_C EXCEPTION;
   EX_INVALID_KEY_DEALER_SUB_C EXCEPTION;
   EX_INVALID_KEY_GEO_SALES_C EXCEPTION;
   EX_INVALID_KEY_REPAIR_ORDER_D EXCEPTION;
   EX_INVALID_KEY_REPAIR_LINE_D EXCEPTION;
   REQUEST_ROW XPA.SXPAA01_REQUEST%ROWTYPE;
   DETAIL_ROW SXPAA02_REQUEST_DETAIL%ROWTYPE;
   COMMENT_ROW SXPAA03_COMMENT%ROWTYPE;
BEGIN

     -- Get Data From Request Table
     -- Check if there is corresponding Xpa Data
     SELECT * INTO REQUEST_ROW FROM XPA.SXPAA01_REQUEST WHERE XPAA01_REQUEST_R = P_REQUEST_NUMBER;
     IF SQL%NOTFOUND THEN
          RAISE EX_REQUEST_NOT_FOUND;
     END IF;

     -- Check to see if we get a match on Key Data
     IF ( REQUEST_ROW.XPAA01_P_A_C <> P_PA_CODE ) THEN
          RAISE EX_INVALID_KEY_P_A_C;
     ELSIF ( REQUEST_ROW.XPAA01_DEALER_SUB_C != P_PA_SUB_CODE ) THEN
          RAISE EX_INVALID_KEY_DEALER_SUB_C;
     ELSIF ( REQUEST_ROW.XPAA01_GEO_SALES_C != P_COUNTRY_CODE ) THEN
          RAISE EX_INVALID_KEY_GEO_SALES_C;
     ELSIF ( REQUEST_ROW.XPAA01_REPAIR_ORDER_D != P_REPAIR_ORDER ) THEN
          RAISE EX_INVALID_KEY_REPAIR_ORDER_D;
     ELSIF ( REQUEST_ROW.XPAA01_REPAIR_LINE_D != P_REPAIR_LINE ) THEN
          RAISE EX_INVALID_KEY_REPAIR_LINE_D;
     END IF;
     
     
     -- Get Data From RequestDetail Table with highest Sequence Number
     -- Check if there is no data, reports a problem
     SELECT * INTO DETAIL_ROW FROM SXPAA02_REQUEST_DETAIL A WHERE
            A.XPAA01_REQUEST_R = P_REQUEST_NUMBER AND
            A.XPAA02_REQUEST_SEQUENCE_R IN ( SELECT MAX(B.XPAA02_REQUEST_SEQUENCE_R) FROM
                                           SXPAA02_REQUEST_DETAIL B WHERE
                                           B.XPAA01_REQUEST_R = P_REQUEST_NUMBER );
     IF SQL%NOTFOUND THEN
          RAISE EX_DETAIL_NOT_FOUND;
     END IF;
     
     -- If Xpa RequestSequence is less than Gaces RequestSequence, if it is; report problem
     IF( DETAIL_ROW.XPAA02_REQUEST_SEQUENCE_R = P_REQUEST_SEQUENCE ) THEN
         SXPA_CREATE_PAID_DETAIL( V_RESULT, P_REQUEST_NUMBER, P_REQUEST_SEQUENCE, P_STATUS_CODE, P_PAYMENT_DATE, P_PAID_AMOUNT );
         IF( V_RESULT != 0 ) THEN RAISE EX_INSERT_FAILED; END IF;
     ELSIF( DETAIL_ROW.XPAA02_REQUEST_SEQUENCE_R < P_REQUEST_SEQUENCE ) THEN
         -- This should never happen, but just in case
         SXPA_CREATE_PAID_DETAIL( V_RESULT, P_REQUEST_NUMBER, P_REQUEST_SEQUENCE, P_STATUS_CODE, P_PAYMENT_DATE, P_PAID_AMOUNT );
         IF( V_RESULT != 0 ) THEN RAISE EX_INSERT_FAILED; END IF;
     ELSIF( DETAIL_ROW.XPAA02_REQUEST_SEQUENCE_R > P_REQUEST_SEQUENCE ) THEN
         -- Check Status Code, if it is 'P' then ignore else insert a new Xpa record
         IF( DETAIL_ROW.XPAA04_ENHANCED_STATUS_C != 'P' ) THEN
             SXPA_CREATE_PAID_DETAIL( V_RESULT, P_REQUEST_NUMBER, DETAIL_ROW.XPAA02_REQUEST_SEQUENCE_R, P_STATUS_CODE, P_PAYMENT_DATE, P_PAID_AMOUNT );
             IF( V_RESULT != 0 ) THEN RAISE EX_INSERT_FAILED; END IF;
         ELSE
             RAISE EX_REQUEST_IGNORED;
         END IF;
     ELSE
         RAISE EX_INVALID_KEY;
     END IF;

     RETURN;
EXCEPTION
    WHEN EX_REQUEST_NOT_FOUND THEN
         RETURN_CODE := 1;
         RETURN_MESSAGE := 'No Corresponding Request Found';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN EX_DETAIL_NOT_FOUND THEN
         RETURN_CODE := 2;
         RETURN_MESSAGE := 'No Corresponding Request Detail Found';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN EX_INVALID_KEY_P_A_C THEN
         RETURN_CODE := 2;
         RETURN_MESSAGE := 'PA Code From Gaces = *'||REQUEST_ROW.XPAA01_P_A_C||'* From XPA = *'||P_PA_CODE||'*';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN EX_INVALID_KEY_DEALER_SUB_C THEN
         RETURN_CODE := 2;
         RETURN_MESSAGE := 'Dealer Subcode From Gaces = *'||REQUEST_ROW.XPAA01_DEALER_SUB_C||'* From XPA = *'||P_PA_SUB_CODE||'*';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN EX_INVALID_KEY_GEO_SALES_C THEN
         RETURN_CODE := 2;
         RETURN_MESSAGE := 'Geo Sales Code From Gaces = *'||REQUEST_ROW.XPAA01_GEO_SALES_C||'* From XPA = *'||P_COUNTRY_CODE||'*';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN EX_INVALID_KEY_REPAIR_ORDER_D THEN
         RETURN_CODE := 2;
         RETURN_MESSAGE := 'Repair Order From Gaces = *'||REQUEST_ROW.XPAA01_REPAIR_ORDER_D||'* From XPA = *'||P_REPAIR_ORDER||'*';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN EX_INVALID_KEY_REPAIR_LINE_D THEN
         RETURN_CODE := 2;
         RETURN_MESSAGE := 'Repair Line From Gaces = *'||REQUEST_ROW.XPAA01_REPAIR_LINE_D||'* From XPA = *'||P_REPAIR_LINE||'*';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN EX_INSERT_FAILED THEN
         RETURN_CODE := 40 + V_RESULT;
         RETURN_MESSAGE := 'Insert Failed';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN EX_REQUEST_IGNORED THEN
         RETURN_CODE := 5;
         RETURN_MESSAGE := 'Request Ignored as xpa already has paid record';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
    WHEN OTHERS THEN
         RETURN_CODE := 99;
         RETURN_MESSAGE := 'Unknown Exception Encountered';
         DBMS_OUTPUT.PUT_LINE( RETURN_MESSAGE );
END SXPA_UPDATE_PAID_REQUEST;
/

java code is
CallableStatement st = null;
try {
st = cnX.prepareCall("CALL SXPA_UPDATE_PAID_REQUEST( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
st.registerOutParameter( 1, Types.INTEGER );
st.registerOutParameter( 2, Types.VARCHAR );
st.setInt( 3, requestNumber.intValue() );
st.setInt( 4, requestSequence.intValue() );
st.setString( 5, paCode );
st.setString( 6, paSubCode );
st.setString( 7, countryCode );
st.setString( 8, repairOrder );
st.setString( 9, repairLine.trim() );
st.setString( 10, vin );
st.setDate( 11, XpaUtil.utilToSqlDate(gacesTimestamp) );
st.setString( 12, statusCode );
st.setDate( 13, XpaUtil.utilToSqlDate(paidDate) );
st.setString( 14, currentUser );
st.setDouble( 15, paidAmount );

st.execute();
returnCode = st.getInt(1);
returnMessage = st.getString(2);

if( returnCode == 0 ) {
result = REQUEST_INSERTED;
} else {
result = REQUEST_PROCESSED;
batchStatus.addError(requestNumber, requestSequence, paCode, countryCode, repairOrder, repairLine, vin, returnCode, returnMessage);
}

} catch( SQLException ex ) {
log.log(Level.SEVERE,"Error while calling SXPA_UPDATE_PAID_REQUEST",ex);
throw ex;
} finally {
if( st != null ) {
try { st.close(); } catch( SQLException ignore ) {}
}
}

Hi,

Afaik, resultsets expect objects. I have not investigated this, however, my JDBC code uses Integers instead of primitive int.

Try passing Integer objects?

Roger

This has been solved