CODEWITHSUNDEEP
javasqloracle-database
supertorpez
supertorpez

Reputation: 57

Error: ORA-01008: not all variables bound call SQL function

Hi have this problem when executing the calling to my SQL funcion.

This is the code to call the Function

CallableStatement cst = conn.prepareCall("{call OMV_CREAR_INTERACCION(?,?,?,?,?,?,?,?)}");          
        // Parametros del procedimiento almacenado
        cst.setInt(1, Integer.parseInt(objSite));
        cst.setString(2, titulo);
        cst.setString(3, "CRM");
        cst.setString(4, "");
        cst.setString(5, razon1);
        cst.setString(6, razon2);
        cst.setString(7, razon3);              
        // Ejecuta el procedimiento almacenado
        cst.execute();

But when execute that, appear in the console this:

Error: ORA-01008: not all variables bound
Exception in thread "RsSp" java.lang.NumberFormatException: null
    at java.lang.Integer.parseInt(Unknown Source)
    at java.lang.Integer.parseInt(Unknown Source)
    at enviaSmsPortDonante.EnviaSMSPortDonante.interaccion(EnviaSMSPortDonante.java:327)
    at enviaSmsPortDonante.EnviaSMSPortDonante.run(EnviaSMSPortDonante.java:124)
    at java.lang.Thread.run(Unknown Source)

And this is the SQL function:

CREATE OR REPLACE PROCEDURE OMV_CREAR_INTERACCION(p_site_objid IN NUMBER,
                                              p_titulo     IN VARCHAR2,
                                              p_origen     IN VARCHAR2,
                                              p_notas      IN VARCHAR2,
                                              p_reason_1   IN VARCHAR2,
                                              p_reason_2   IN VARCHAR2,
                                              p_reason_3   IN VARCHAR2,
                                              p_resultado  OUT VARCHAR2)

Can you help me. Thanks

Upvotes: 1

Views: 999

Answers (1)

Bhushan
Bhushan

Reputation: 6181

Your have 8 placeholers in your function.

{call OMV_CREAR_INTERACCION(?,?,?,?,?,?,?,?)}

And you are providing only 7 parameters via setXXX().

So you have to pass 8th parameter to cst.

And you are also getting java.lang.NumberFormatException. So you have to check whether objSite is not null and has value in following statement.

cst.setInt(1, Integer.parseInt(objSite));

Update1

CallableStatement cst = conn.prepareCall("{call OMV_CREAR_INTERACCION(?,?,?,?,?,?,?,?)}");          
cst.setInt(1, Integer.parseInt(objSite));
cst.setString(2, titulo);
cst.setString(3, "CRM");
cst.setString(4, "");
cst.setString(5, razon1);
cst.setString(6, razon2);
cst.setString(7, razon3);
cst.registerOutParameter(8, Types.VARCHAR);//out parameter for p_resultado     
cst.execute();

//to get out parameter
String str = cst.getString(8);

Upvotes: 1

Related Questions