Home » Developer & Programmer » Precompilers, OCI & OCCI » PLS-00306: wrong number or types of arguments in call to existing stored procedure (Merged) (Oracle)
PLS-00306: wrong number or types of arguments in call to existing stored procedure (Merged) [message #441455] Mon, 01 February 2010 04:38 Go to next message
lavanyabl
Messages: 4
Registered: February 2010
Junior Member
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?

[Updated on: Mon, 01 February 2010 04:41] by Moderator

Report message to a moderator

PLS-00306: wrong number or types of arguments in call to existing stored procedure [message #441468 is a reply to message #441455] Mon, 01 February 2010 04:57 Go to previous messageGo to next message
lavanyabl
Messages: 4
Registered: February 2010
Junior Member
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 lbl3081;
char vlu30256;
char typ30;
ub8 seq_no30;
char vlu_hint30;

}PARAM;

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


The way I invoke the stored procedure:

char command250 = "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?
Re: PLS-00306: wrong number or types of arguments in call to existing stored procedure [message #441476 is a reply to message #441468] Mon, 01 February 2010 05:07 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
lavanyabl wrote on Mon, 01 February 2010 04:57
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.



then This is not Oracle problem , its 'C' programming issue.
Re: PLS-00306: wrong number or types of arguments in call to existing stored procedure [message #441477 is a reply to message #441476] Mon, 01 February 2010 05:10 Go to previous message
lavanyabl
Messages: 4
Registered: February 2010
Junior Member
Yes, it is something to do with the compatibility of the datatypes used in Oracle/PLSQL and those used in C. Can you please throw some light on where I might have gone wrong?
Previous Topic: Pro*C Precompiler Hell (merged)
Next Topic: [Oracle][ODBC]Invalid attribute/option identifier. SQL State :HY092
Goto Forum:
  


Current Time: Mon Sep 26 07:50:46 CDT 2016

Total time taken to generate the page: 0.22066 seconds