Unexpected error running stored oracle PL/SQL proceedure <p>The database I am accessing has a whole API of stored procedures (several hundred) in several packages. I working on new client software to interact with this database and I need to invoke these stored procedures using OCI. Since I am new at this, I decided to start with the easiest one first. It is not working. </p> <p>It would be helpful if I could get the actual PL/SQL code of the stored procedure. The package does not reside in my user schema, but I do have execute privileges on it. When I query the ALL_SOURCE view I get the package declarations, but not the package body. I also tried the dbms_metadata.get_ddl view, but it just says "Package PTAPI not found in schema for ". Are there any other ways to get the actual PL/SQL code from the package body?</p> <p>Here is the PL/SQL package declaration of the example procedure I am trying to use. This particular one creates a textual error message given an error code and some optional parameters that depend on the specific error code. </p> <pre><code>-- format an error message from ERRMSGS table PROCEDURE formatmessage( p_error IN errmsgs.error%TYPE -- index into ERRMSGS , p_errnum OUT errmsgs.error%TYPE -- adjusted error number , p_errmsg OUT errmsgs.MESSAGE%TYPE -- formatted message , p_a IN VARCHAR2 DEFAULT NULL , p_b IN VARCHAR2 DEFAULT NULL , p_c IN VARCHAR2 DEFAULT NULL , p_d IN VARCHAR2 DEFAULT NULL , p_e IN VARCHAR2 DEFAULT NULL ); -- formatmessage </code></pre> <p>My code to invoke the procedure is below [statement and err are OCI handles passed into this function from higher up]</p> <pre><code>char errmsg[256]; //buffer to receive the error message long errnum; //buffer to receive the adjusted error number memset(errmsg,0,256); errnum = 0; //start the message buffer empty OCIBind* bind1 = NULL, *bind2 = NULL; //to receive the bind handles ub4 curelep1 = 1; //1 errnum element ub4 curelep2 = 1; //1 errmsg element ub2 alenp1 = sizeof(long); //errnum element size ub2 alenp2 = 256; //errmsg element size char sql[] = "BEGIN PTAPI.FORMATMESSAGE(193,:P_ERRNUM,:P_ERRMSG, '','','','',''); END;\0" OCIStmtPrepare(statement,err,(text*)sql,strlen(sql),OCI_NTV_SYNTAX, OCI_DEFAULT); //parse the SQL statement OCIBindByName(statement,&amp;bind1,err,(text*)":P_ERRNUM",-1,&amp;errnum, sizeof(long),SQLT_INT,NULL,&amp;alenp1,NULL,1,&amp;curelep1,OCI_DEFAULT); //bind errnum OCIBindByName(statement,&amp;bind2,err,(text*)":P_ERRMSG",-1,errmsg,256, SQLT_STR,NULL,&amp;alenp2,NULL,1,&amp;curelep2,OCI_DEFAULT); //bind errmsg if(OCIStmtExecute(svcctx,statement,err,1,0,NULL,NULL,OCI_DEFAULT) != OCI_SUCCESS) //execute the statement { long errcode; char errbuf[512]; OCIErrorGet (err,1,NULL,(sb4*)&amp;errcode,(OraText*)errbuf,512, OCI_HTYPE_ERROR); //check to see what the error was printf("ERROR %d - %s\n",errcode,errbuf); return FAIL; } </code></pre> <p>The statement parses properly and the two binds succeed, but I get an error on execute. The error I get is</p> <pre><code>ERROR 6550 - ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'FORMATMESSAGE' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'FORMATMESSAGE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored </code></pre> <p>I am confused by this error because I imitated a call to this same function from the old client software that works properly. I definitely have the right number and types of arguments. Any ideas why I would be getting this error?</p> <p><strong>---- UPDATE ----</strong> </p> <p>I found a better example procedure to test. This one is not stored in a package, it is a standalone procedure, and so I have the full PL/SQL code. I get the exact same error. It has to be something to do with how I am invoking them. Thanks for any ideas.</p> <p>This one deletes an item of a certain type from the database (entries on 3 different tables). </p> <p>Here is the PL/SQL</p> <pre><code>PROCEDURE DELSTL(comp IN 3DCOMPS.COMPID%TYPE, rowcount OUT integer, errorcode OUT number) AS tempcount INTEGER; BEGIN errorcode := 0; DELETE FROM 3DCOMPS WHERE COMPID = comp; tempcount := sql%rowcount; IF (sql%rowcount &lt; 1) THEN errorcode := 330; END IF; DELETE FROM IDINF WHERE COMPID = comp; IF (sql%rowcount &lt; 1) THEN errorcode := 332; ELSIF (tempcount &lt; sql%rowcount) THEN tempcount := sql%rowcount; END IF; rowcount := tempcount; DELETE FROM ATTLOC WHERE COMPID1 = comp OR COMPID2 = comp; END; </code></pre> <p>Here is my slightly modified code for this new test procedure</p> <pre><code>long errnum; //buffer to receive the error number long rowcnt; //buffer to receive the row count OCIBind* bind1 = NULL, *bind2 = NULL; //to receive the bind handles ub4 curelep1 = 1; //1 errnum element ub4 curelep2 = 1; //1 rowcnt element ub2 alenp1 = sizeof(long); //errnum element size ub2 alenp2 = sizeof(long); //rowcnt element size char sql[] = "BEGIN DELSTL('FAKEIDNUM',:P_ROWCNT,:P_ERRNUM); END;\0" OCIStmtPrepare(statement,err,(text*)sql,strlen(sql),OCI_NTV_SYNTAX, OCI_DEFAULT); //parse the SQL statement OCIBindByName(statement,&amp;bind1,err,(text*)":P_ERRNUM",-1,&amp;errnum, sizeof(long),SQLT_INT,NULL,&amp;alenp1,NULL,1,&amp;curelep1,OCI_DEFAULT); //bind errnum OCIBindByName(statement,&amp;bind2,err,(text*)":P_ROWCNT",-1,&amp;rowcnt, sizeof(long),SQLT_INT,NULL,&amp;alenp2,NULL,1,&amp;curelep2,OCI_DEFAULT); //bind rowcnt if(OCIStmtExecute(svcctx,statement,err,1,0,NULL,NULL,OCI_DEFAULT) != OCI_SUCCESS) //execute the statement { long errcode; char errbuf[512]; OCIErrorGet (err,1,NULL,(sb4*)&amp;errcode,(OraText*)errbuf,512, OCI_HTYPE_ERROR); //check to see what the error was printf("ERROR %d - %s\n",errcode,errbuf); return FAIL; } </code></pre> <p>I use 'FAKEIDNUM' since I obviously dont want to actually delete anything during this test. Since that doesnt exist in those tables, rowcnt should be 0 and errnum should be 332 when the procedure ends.</p> <p>I get exactly the same error as with the other procedure. </p> <pre><code>ERROR 6550 - ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DELSTL' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DELSTL' ORA-06550: line 1, column 7: PL/SQL: Statement ignored </code></pre> <p>Does this give anyone any ideas? Thanks!</p>