PLS-00306: wrong number or types of arguments in call to existing stored procedure

Question

hi , I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error.

The Store procedure definition looks like :

CREATE OR REPLACE FUNCTION SP( 
    srq_id          integer ,
    unid            IN SPkg.arr_parmid,
    parm_typ        IN SPkg.arr_parm_typ,
    parm_lbl        IN SPkg.arr_parm_lbl,
    parm_vlu        IN SPkg.arr_parm_vlu,
    commit_flag     INTEGER DEFAULT 1,
    vlu_hint        IN SPkg.arr_vlu_hint,
    create_flag     INTEGER DEFAULT 0)
RETURN INTEGER

Type definitions

TYPE arr_parm_typ IS TABLE OF char INDEX BY BINARY_INTEGER;
TYPE arr_parmid IS TABLE OF tbl_parm.UNID%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_lbl IS TABLE OF tbl_parm.PARM_LBL%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_vlu IS TABLE OF tbl_parm.PARM_VLU%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_vlu_hint IS TABLE OF tbl_parm.VLU_HINT%TYPE INDEX BY BINARY_INTEGER;

My C code looks like :

typedef struct param
{

     char lbl[30][81];
     char vlu[30][256];
     char typ[30];
     ub8 seq_no[30];
     char  vlu_hint[30];

}PARAM;

ub8 srqid;
int commit_flag;
int create_flag;
PARAM p_array;

The way I invoke the stored procedure:

    char command[250] = "begin :retval := SSP_srq_parm_all(:srq_id,:unid,:parm_typ,:parm_lbl,:parm_vlu,:commit_flag,:vlu_hint,:create_flag); end;";
   OCIStmtPrepare2((OCISvcCtx *)svchp, (OCIStmt **)&(stmthp),
                (OCIError *)errhp, (OraText *)command, 
                (ub4)strlen((char*)command), (OraText *)NULL, (ub4)0,
                OCI_NTV_SYNTAX, OCI_DEFAULT);

  //..... calls to OCIBindByName & OCIBindArrayOfStruct here..........

  status= OCIStmtExecute(svchp, stmthp,errhp, (ub4)1, (ub4) 0,(CONST OCISnapshot *)   NULL,(OCISnapshot *) NULL, OCI_DEFAULT);

OCIStmtExecute() fails with the above error. Please let me know if you need to know how the 'bind' calls look like. I dint paste them right away because the code chunk is pretty big. Can someone please help?

Answer 1

Firstly, a function is not a procedure - but that isn't the problem. Since you're using the correct number of parameters, the only thing that it could be is that the implementation of the custom types you're using don't match between Oracle and C. How are they defined in PL/SQL?