Problems calling PL/SQL function from OCI Application (in C)

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 some of the easier ones first.  It is not working.  I get the exact same error on any of the procedures that I try.     I am pretty sure I am not invoking the call correctly, but I cannot figure out what is the exact problem.    

Here is the PL/SQL of the procedure I am trying to use as a test.  

PROCEDURE DELSTL(comp IN 3DCOMPS.COMPID%TYPE, -- VARCHAR2 20 chars long
          rowcount OUT integer,
          errorcode OUT number)
AS
        tempcount   INTEGER;
BEGIN
        errorcode := 0;
        DELETE FROM 3DCOMPS WHERE COMPID = comp;
        tempcount := sql%rowcount;
        IF (sql%rowcount < 1) THEN
          errorcode := 330;
        END IF;
        DELETE FROM IDINF WHERE COMPID = comp;
        IF (sql%rowcount < 1) THEN
            errorcode := 332;
        ELSIF (tempcount < sql%rowcount) THEN
           tempcount := sql%rowcount;
        END IF;
        rowcount := tempcount;
        DELETE FROM ATTLOC WHERE COMPID1 = comp OR COMPID2 = comp;
END;



Here is my OCI code to invoke the stored procedure

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,&bind1,err,(text*)":P_ERRNUM",-1,&errnum, sizeof(long),SQLT_INT,NULL,&alenp1,NULL,1,&curelep1,OCI_DEFAULT); //bind errnum
OCIBindByName(statement,&bind2,err,(text*)":P_ROWCNT",-1,&rowcnt, sizeof(long),SQLT_INT,NULL,&alenp2,NULL,1,&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*)&errcode,(OraText*)errbuf,512, OCI_HTYPE_ERROR); //check to see what the error was
    printf("ERROR %d - %s\n",errcode,errbuf);
    return FAIL;
}


I used 'FAKEIDNUM' since I obviously don't want to actually delete anything during this test. Since that ID doesn't exist in the tables, rowcnt should be 0 and errnum should be 332 when the procedure ends.

The error I am getting is

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

Any idea what I am doing wrong?
jdwpv4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Commented:
BEGIN DELSTL('FAKEIDNUM',:P_ROWCNT,:P_ERRNUM);

Since your procedure has only the id as an input parameter, it's not expecting a rowcound or error number.
BEGIN DELSTL('FAKEIDNUM');
Also, you indicate these procedures are in packages, so the call should be qualified with the package name( e.g. begin pkg1.proc1(param1) end;.
0
jdwpv4Author Commented:
Thanks for your answer,
Note: Although many of them are in packages, this particular one is not.  It is stand alone.  I chose it for the example because of that - the ones that are in packages I cannot get the PACKAGE BODY for because they are wrapped.  I thought it would be more helpful to use one that I had full code for.  I get the same error on all of them that I have tried.

PROCEDURE DELSTL(comp IN 3DCOMPS.COMPID%TYPE, -- VARCHAR2 20 chars long
         rowcount OUT integer,     <-------------------- ROWCNT
          errorcode OUT number)   <-------------------- ERRCODE

AS

These are the parameters for row count and error count.  They are supposed to be bound to variables in my OCI program so that their output goes into those variables.   How would they be bound to the OCI program if I used BEGIN DELSTL('FAKEIDNUM'); ?
0
Mark GeerlingsDatabase AdministratorCommented:
I have a lot of experience with PL\SQL, but no experience with OCI.  This error looks to me like the database thinks you are not passing the correct variables or datatypes to this procedure.  This procedure will need three variables: one input value, and two numeric variables to accept the two "out" parameters.  Maybe the problem is the datatype of these two variables:  :P_ROWCNT and :P_ERRNUM.  I don't see where you define them, and assign them a data type that Oracle recognizes as "number".

It looks like you define variables named: "errnum" and " rowcnt" but then try to use variables named: :P_ERRNUM and :P_ROWCNT.  As I said, I have no OCI experience so I don't expect that to add an implied ":P_" prefix to a variable that you declare, but maybe it does?  And, I don't know if OCI (or your O/S?) is case-sensitive for variable names, or not.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
I've also not use OCI.

Not sure if this matters but one of the out parameters is a number but the bind variable is SQLT_INT.  Try SQLT_NUM:
https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci03typ.htm#LNOCI031


In the docs check out the example:  PL/SQL Block in an OCI Program
https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci05bnd.htm#LNOCI050

Also pay attention to this small tidbit:
Note that the PL/SQL procedure argument, new_salary, although a PL/SQL OUT variable, must be bound, not defined. This is explained in Defining PL/SQL Output Variables and in Information for Named Data Type and REF Defines, and PL/SQL OUT Binds.
0
johnsoneSenior Oracle DBACommented:
I used to do quite a bit of OCI code, but it has been a while.  I think where you are getting issues is with your variable types.

In your bind calls, you are using sizeof(long), I would change that to sizeof(curelep1), then the size of the variable is determined and not the size a data type.  Then you can change the datatype and not have to worry about changing the bind call.  I'm also not sure about using the ub4 datatype, it doesn't seem to be specifically called out in the documentation, try using int and see if that makes a different.

Do you have a specific reason that you are using OCI?  I always question that.  I realize that it is fast, but when you do an upgrade, OCI changes a lot.  We try to use Pro*C as much as possible and only use OCI when we need to do things that Pro*C cannot.  Since Pro*C is a precompiler, changes for upgrades are minimal (if any).
0
johnsoneSenior Oracle DBACommented:
Also, if you are using long type variables, then you should be using SQLT_LNG and not SQLT_INT.
0
jdwpv4Author Commented:
Hi, I got the solution.  None of the posted answers is correct although I am going to say that slightwv is the closest because the correct answer can be found in the links that he provided in his answer.  

A summary of the solution and lessons I learned follows:
There is a massive amount of confusion regarding datatypes in Oracle-OCI programs.   Lots of people not only on this site but also on Stack Exchange and Oracle PL/SQL developer community were confused by my definition of the destination C variables as longs.   OCI is post-compiler, so it has no idea what data types your C variables are.  In the function calls you merely provide a pointer and a size in bytes of the buffer pointed to by the pointer.   OCI will then store the results in that buffer in whatever format you specify in the OCI call (unless the buffer isn't large enough, in which case an
error will be thrown).  The oracle function call will still succeed even if the datatypes are not compatible because OCI has no way of knowing what your C data type is.  Although obviously if the C data type is not compatible with the Oracle datatype you will get weird results in your C program.  In this case, I was using a C data type of long which is a 32 bit signed integer.  This is compatible with
the oracle datatype SQLT_INT which is defined as an 8, 16, or 32 bit signed integer.   See the definition of Integer in the first linke to Oracle datatypes provided by slightvw.    

Some people suggested I use the types SQLT_NUM and SQLT_LNG.  Both of those are wrong.   Technically SQLT_NUM would work, although I would have to do additional conversion after the OCI call to get the final result.   SQLT_NUM is oracles internal NUMBER datatype.  This is a 21 byte floating point number system.  It is similar to IEEE floating point except that it uses 21 bytes instead of 8 to store the exponent and mantissa.  Oracle states that you should never have to use that as an external data type in your OCI programs because the statement will automatically convert the internal NUMBER type to an external INTEGER or FLOAT type based upon what you request.   However if you want to do the conversion yourself for some reason you are able to request it in the NUMBER format.    SQLT_LNG is not a number at all.  It is a long character string, defined as more than 4000 characters.    This is not compatible with numbers at all.


Ok, so C datatype wasn't the problem.   The problem was that I was sending the PL/SQL program a TABLE of values, rather than a single value.   As you can see in the PL/SQL procedure definition
the procedure does not take a TABLE of integers for rowcount or a TABLE of numbers for errorcode, it only takes a single value for each.    Now as a life long C programmer, to me there is no
difference between a single value and an array with only one element in it.  But I guess in Oracle they are NOT the same.   Prior to Oracle8, there were separate OCI Bind functions for binding
single values and binding arrays (obndrv and obndra, respectively).  But with Oracle8 onward, they were replaced with the single call OCIBindByName.    The parameters curelep, alenp, and maxarr_size
should only be passed to the function if binding an ARRAY.  If binding a single value, NULL or 0 should be passed.   Since this was not clearly explained in the documentation, I created and passed
those values according to an array size of 1 element.   Thus the bind call created a TABLE of values (with 1 element in each table) and tried to use that for the PL/SQL procedure hence the incorrect datatype error.

Changing my bind calls to the following fixed the problem;
OCIBindByName(statement,&bind1,err,(text*)":P_ERRNUM",-1,&errnum, sizeof(long),SQLT_INT,NULL,NULL,NULL,0,NULL,OCI_DEFAULT); //bind errnum
OCIBindByName(statement,&bind2,err,(text*)":P_ROWCNT",-1,&rowcnt, sizeof(long),SQLT_INT,NULL,NULL,NULL,0,NULL,OCI_DEFAULT); //bind rowcnt

Thanks for everyone's time!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
I apologize for the suggest of SQLT_LNG.  I just went back to the documentation page that I got that from, and I totally misread it.  There is an 8 in one of the columns in the table of datatypes that I was reading and I mistook that for a 8-byte/64-bit integer.
0
jdwpv4Author Commented:
@johnsone, no worries.  Its real easy to misread their docs.  Oracle documentation reads like the ramblings of a severe Alzheimer's patient.   I think they do that on purpose so that they can sell books.   Thanks for the effort though!
0
slightwv (䄆 Netminder) Commented:
I also have to apologize.  My straight C coding paychecks ended back in the early 90's.

ARRAY BINDING, of course!  Makes perfect sense not that you've explained it.

Not that you care or that it is related but I use them in the .Net world today:
http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

Just never put 2 and 2 together on this question...

Glad I was able to have some small part of the solution.
0
jdwpv4Author Commented:
None of the expert posts was correct.   Gave some points to the one post that did lead to the solution by means of the reference documents he linked.   Posted the correct solution in my own final answer for the benefit of anyone else who may come across this thread in the future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.