Discussions

Web tier: servlets, JSP, Web frameworks: JDBC Stored procedure call errror

  1. JDBC Stored procedure call errror (2 messages)

    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 ) {}
    }
    }
  2. 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
  3. JDBC Stored procedure call errror[ Go to top ]

    This has been solved