0

I have the following C code which uses a stored procedure to insert 2 strings into the database:

char query[1024];

memset(query, 0, sizeof(query));
sprintf(query, "BEGIN bns_saa_message_insert (:1, :2); END;");


/* prepare statement */
if( checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) query,
        (ub4) strlen((char *) query),
        (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) == OCI_ERROR)
    return -1;


/* bind input params */
if( checkerr(errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) hostNumber,
        (sword) sizeof(hostNumber) - 1, SQLT_CHR, (dvoid *) 0,
        (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) == OCI_ERROR)
    return -1;

if( checkerr(errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) saaMessage,
        (sword) sizeof(saaMessage) - 1, SQLT_CHR, (dvoid *) 0,
        (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) == OCI_ERROR)
    return -1;
//end of param binding

printf("query: %s",query);//this shows param1 and param2 have not been replaced when I did the binding above

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

The error I get is :

ORA-01008: not all variables bound

and the printf outlined in the code above outputs:

query: BEGIN bns_saa_message_insert (:1, :2); END;

Question
How do I fix this error?

EDIT
I have seen similar questions answered here in C# or Java, but not C
"ORA-01008: not all variables bound" error
ORA-01008: not all variables bound. They are bound

Community
  • 1
  • 1
Adrian
  • 5,603
  • 8
  • 53
  • 85
  • 2
    The output of the `printf` statement isn't relevant. The query and the parameters are transmitted to the server separately. And the query and it's execution plan are cached on the server side without the specific parameters. That's part of the overall concept. – Codo Feb 08 '12 at 18:36

1 Answers1

4

It looks like a minor mistake. Your second call to OCIBindByPos should use 2 instead of 1 for the fourth parameter:

if( checkerr(errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 2, (dvoid *) saaMessage,
Codo
  • 75,595
  • 17
  • 168
  • 206