CODEWITHSUNDEEP
javasqldatabaseoracle-database
Kordh Rufan
Kordh Rufan

Reputation: 31

SQL Exception executeQuery: 17041, Missing IN or OUT parameter at index:: 3

Hi I'm having an specific problem with my code in java comunicating with an oracle server.

I'm doing a call in oracle in order to retrieve a data using an Stored procedure. This is my call.

String procedure = "{call " + instnace+ "." + package+ "." + "GetRequestData(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" + "}";

This Stored Proecudre receives 2 values, COD_AREA and TELEFONO, and then return 18 Values.

try{            
    pstmt.setString(1, RQ.getCOD_AREA());
    pstmt.setString(2, RQ.getTELEFONO());//TELEFONO()               
    pstmt.executeQuery();               
        int o_requestid= (pstmt.getInt(3));
        RP.setO_requestid(String.valueOf(o_requestid));
        RP.setO_requestdate(pstmt.getString(4));
        RP.setO_requesterid(pstmt.getString(5));
        RP.setO_requesterfirstname(pstmt.getString(6));
        RP.setO_requesterlastname(pstmt.getString(7));
        RP.setO_requestercompanyname(pstmt.getString(8));
        RP.setO_requesterclassname(pstmt.getString(9));
        RP.setO_requesteremail(pstmt.getString(10));
        RP.setO_address1(pstmt.getString(11));
        RP.setO_address2(pstmt.getString(12));
        RP.setO_city(pstmt.getString(13));
        RP.setO_state(pstmt.getString(14));
        int o_autoinstall= (pstmt.getInt(15));
        RP.setO_autoinstall(String.valueOf(o_autoinstall));
        RP.setO_plandesired(pstmt.getString(16));
        RP.setO_riskanalisis(pstmt.getString(17));
        RP.setO_status(pstmt.getString(18));
        int n_error= (pstmt.getInt(19));
        RP.setN_error(String.valueOf(n_error));     
        RP.setStr_error(pstmt.getString(20));           
    CerrarConexion(conn);
}

The error I'm having is: SQL Exception executeQuery: 17041, Missing IN or OUT parameter at index:: 3 Why I'm having this? I searched on stackoverflow and other sites but its related with inserts and updates, but not with Stored Procedures. I appreciate if anyone can help me out with this.

This is my beginConnection method:

private OracleCallableStatement beginConnection(String firmaSP) throws SystemException, Exception {

    OracleCallableStatement pstmt = null;
    // Se obtiene la conexion a la BD
    conn = Conexion.getConnection(infoGenerico, 0);
    pstmt = (OracleCallableStatement) conn.prepareCall(firmaSP);
    return pstmt;
}

Upvotes: 0

Views: 814

Answers (1)

rgettman
rgettman

Reputation: 178243

It looks like you have parameters that are IN, and IN OUT at least. With a stored procedure that you need to call, you should use a CallableStatement, not a PreparedStatement, which I'm assuming you're using based on the variable name pstmt. A CallableStatement allows you to return values back.

Then for every parameter in which you expect values back (IN OUT and OUT), you must register the out parameter with the registerOutParameter method before you execute it with execute().

Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType. All OUT parameters must be registered before a stored procedure is executed.

E.g.:

cstmt.registerOutParameter(3, Types.Integer);

With Java 8 there is an overload that that takes a SQLType instead of an int for the type parameter.

Upvotes: 2

Related Questions