OCIStmtPrepare returns OCI_INVALID_HANDLE

Question

I am using below code to INSERT into a oracle table. The insert query contains the ÜÜ chars.

I am getting OCI_INVALID_HANDLE error when I am calling the OCIStmtPrepare. What might be the issue here?

int executeINSERTQuery()
{
    OCIEnv *envhp;
    OCIError *errhp = NULL;
    OCISvcCtx *svchp = NULL;
    OCIExtProcContext *context=NULL;
    int status = OCIEnvNlsCreate((OCIEnv **)&envhp,
                                (ub4)0, NULL, NULL, NULL, NULL, (size_t) 0, NULL,
                                (ub2)OCI_UTF16ID, (ub2)OCI_UTF16ID);

    printf("Status: %d\n", status);
    const char sqlstr [300] = "INSERT INTO ABCD_TABLE VALUES('966','31-AUG-15','19-JAN-17','901','31-DEC-12','1',\'\',\'\',\'\',\'\',\'\',\'\',\'ÜÜcreatectare,ää\')";
    OCIStmt *stmthp;

    printf("Statement: %s\n", sqlstr);

    status = OCIHandleAlloc((dvoid *)envhp, 
                 (dvoid **) &stmthp,
                 (ub4) OCI_HTYPE_STMT,  
                 (size_t) 0, 
                 (dvoid **) 0);


    printf("OCIHandleAlloc: %d\n", status);

    status = OCIStmtPrepare (stmthp, errhp, (const text *) sqlstr, strlen(sqlstr), OCI_NTV_SYNTAX, OCI_DEFAULT);

    if ( status != OCI_SUCCESS )
    {
        text errbuf[512];
        sb4 errcode = 0;
        switch (status)
        { 
            case OCI_SUCCESS:
                break;
            case OCI_SUCCESS_WITH_INFO:
                printf("ErrorOCI_SUCCESS_WITH_INFO\n");
                break;
            case OCI_NEED_DATA:
                printf("ErrorOCI_NEED_DATA\n");
                break;
            case OCI_NO_DATA:
                printf("ErrorOCI_NO_DATA\n");
                break;
            case OCI_ERROR:
                OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
                wprintf(L"Error : %S, Error Code: %d\n", errbuf, errcode);
                break;
            case OCI_INVALID_HANDLE:
                printf("Error OCI_INVALID_HANDLE\n");
                break;
            default:
                break;
        }
    }

    printf("OCIStmtPrepare: %d\n", status);
    status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)1, NULL, NULL, OCI_COMMIT_ON_SUCCESS) ;
    printf("OCIExecute: %d\n", status);

    return status;
}

Answer 1

You are initialising the statement handler pointer, but you don't seem to be initialising the error handler pointer:

status = OCIHandleAlloc((dvoid *) envhp,
             (dvoid **) &errhp,
             (ub4) OCI_HTYPE_ERROR,
             (size_t) 0,
             (dvoid **) 0);

or the service context pointer:

status = OCIHandleAlloc((dvoid *) svchp,
             (dvoid **) &errhp,
             (ub4) OCI_HTYPE_SVCCTX,
             (size_t) 0,
             (dvoid **) 0);

Not directly related, but it's usually a good idea to list the target columns in the inser statement:

INSERT INTO ABCD_TABLE(col1, col2, ...) VALUES(...)

Your insert statement would also be slightly easier to read if you used null instead of escaped empty strings (though the \'\' pattern looks odd anyway - don't think you need to escape those? - so maybe that isn't the intent); and you are relying on your session NLS settings to implicitly convert the date values, which also isn't a good idea. You should really be binding the variable values anyway, but that might be your next step.