DevOps, CI/CD and Automation

Hello Everyone,

After initializing OCI to use AL32UTF8 on the client side, connecting to an AL32UTF8 database, I can successfully insert a single ASCII character using SQLT_AFC, but when I try to insert the Cent Sign Unicode character, which http://en.wikipedia.org/wiki/UTF-8 tells me is encoded in UTF-8 as the two bytes 0xC2 0xA2, it fails ORA-12899 as shown below.

On page 2.30, the OCI doc states:
OCIBindByName() and OCIBindByPos() bind variables with default character set
in the OCIEnvNlsCreate() call, including OCI_UTF16ID. The actual length and the
returned length are always in bytes if OCIEnvNlsCreate() is used.

thus the length argument of OCIBindByPos should be strlen(utf8_char) I think.

Can somehow tell me what I am missing? Thanks, --DD

PS: Before using

const oratext*

as arg to insert_one_char, I had

cont char*

, but got the same error.

PPS: If I switch from SQLT_AFC to SQLT_STR in the bind, I get this error instead:

Error: ORA-01480: trailing null missing from STR bind value

Here's the output of running the program:

C:\C++ test progs>oci_bind_char_utf8.exe
charset id of AL32UTF8: 873
Connecting to Oracle...
Connected to 'OCI101' as oci101

Client:
utf16=false; charset=AL32UTF8(873); Ncharset=AL32UTF8(873)
NLS_CHARACTER_SET=AL32UTF8

Server:
NLS_CHARACTER_SET=AL32UTF8

inserting 'A' ASCII char

Server:
NLS_CHARACTER_SET=AL32UTF8

inserting 'cents' UNICODE char, in UTF-8
Error: ORA-12899: value too large for column "OCI101"."STRING_TAB"."ONE_CHAR" (actual: 2, maximum: 1)


OCI call failed: oci_bind_char_utf8.cpp: 140
Aborting!

Here's the C++ / OCI program itself:

#include <oci.h>
#include <iostream>
using namespace std;

static void checkerr(
    sword status, const char* file, int line, OCIError* errhp = 0
) {
    if (status == OCI_SUCCESS) {
        return;
    }
    cerr << "Error: ";
    switch (status) {
    case OCI_ERROR:
        if (!errhp) {
            cerr << "OCI_ERROR";
        } else {
            text errbuf[512];
            char errmsg[600];
            sb4 errcode = 0;
            (void)OCIErrorGet(
                (dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR
            );
            sprintf(errmsg, "%.*s\n", 512, errbuf);
            cerr << errmsg;
        }
        break;
    case OCI_SUCCESS_WITH_INFO: cerr << "OCI_SUCCESS_WITH_INFO"; break;
    case OCI_NEED_DATA:         cerr << "OCI_NEED_DATA"; break;
    case OCI_NO_DATA:           cerr << "OCI_NO_DATA"; break;
    case OCI_INVALID_HANDLE:    cerr << "OCI_INVALID_HANDLE"; break;
    case OCI_STILL_EXECUTING:   cerr << "OCI_STILL_EXECUTING"; break;
    case OCI_CONTINUE:          cerr << "OCI_CONTINUE"; break;
    default:                    cerr << "UNKNOWN"; break;
    };
    cerr << endl;
    cerr << "OCI call failed: " << file << ": " << line << endl;
    cerr << "Aborting!" << endl;
    exit(1);
}
#define CHECKERR(call) checkerr(call, __FILE__, __LINE__)
#define CHECKERR2(errhp, call) checkerr(call, __FILE__, __LINE__, errhp)

static ostream& print_env_charsets(
    ostream& os, OCIEnv* envhp, OCIError* errhp
) {
    // Find out if we are in UTF16 mode (because then all statements and
    // string meta-data is passed/returned in wide chars), and the client
    // charsets.
    ub1 utf16_attr = 0;
    CHECKERR(OCIAttrGet(
        envhp, OCI_HTYPE_ENV, &utf16_attr, 0, OCI_ATTR_ENV_UTF16, errhp
    ));

    ub2 charset_id_attr = 0;
    CHECKERR(OCIAttrGet(
        envhp, OCI_HTYPE_ENV, &charset_id_attr, 0,
        OCI_ATTR_ENV_CHARSET_ID, errhp
    ));
    oratext charset_id[OCI_NLS_MAXBUFSZ];
    CHECKERR(OCINlsCharSetIdToName(
        envhp, charset_id, OCI_NLS_MAXBUFSZ, charset_id_attr
    ));

    ub2 ncharset_id_attr = 0;
    CHECKERR(OCIAttrGet(
        envhp, OCI_HTYPE_ENV, &ncharset_id_attr, 0,
        OCI_ATTR_ENV_NCHARSET_ID, errhp
    ));
    oratext ncharset_id[OCI_NLS_MAXBUFSZ];
    CHECKERR(OCINlsCharSetIdToName(
        envhp, ncharset_id, OCI_NLS_MAXBUFSZ, ncharset_id_attr
    ));

    return cout
        << "utf16=" << ((utf16_attr == 1)? "true": "false")
        << "; charset=" << charset_id << '(' << charset_id_attr << ')'
        << "; Ncharset=" << ncharset_id << '(' << ncharset_id_attr << ')';
}

static ostream& print_nls_character_set(
    ostream& os, void* handle /*env or session*/, OCIError* errhp
) {
    OraText charset[OCI_NLS_MAXBUFSZ] = {0};
    CHECKERR(OCINlsGetInfo(
        handle, errhp, charset, OCI_NLS_MAXBUFSZ, OCI_NLS_CHARACTER_SET
    ));
    return os << "NLS_CHARACTER_SET=" << charset;
}

static ub2 get_al32utf8_id() {
    OCIEnv* envhp = 0;
    CHECKERR(OCIEnvCreate(&envhp, OCI_DEFAULT, 0, 0, 0, 0, 0, 0));

    const oratext al32utf8[] = "AL32UTF8";
    ub2 charset_id = OCINlsCharSetNameToId(envhp, al32utf8);
    if (charset_id == 0) {
        cerr << "OCINlsCharSetNameToId failed" << endl;
        exit(1);
    }
    CHECKERR(OCIHandleFree(envhp, OCI_HTYPE_ENV));
    cout << "charset id of AL32UTF8: " << charset_id << endl;
    return charset_id;
}

static void insert_one_char(
    OCIEnv* envhp, OCIError* errhp, OCISvcCtx* svchp,
    const oratext* utf8_char
) {
    const char *const sql =
        "INSERT INTO string_tab ("
            "one_char"
        ") VALUES ("
            ":1"
        ")";

    OCIStmt* inserthp = 0;
    CHECKERR(OCIHandleAlloc(
        (dvoid *) envhp, (dvoid **) &inserthp, OCI_HTYPE_STMT, 0, 0
    ));

    CHECKERR(OCIStmtPrepare(
        inserthp, errhp,
        (const OraText*)sql, (ub4) strlen(sql),
        (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT
    ));

    OCIBind* bind_one_char = 0;
    const size_t utf8_len = strlen((const char*)utf8_char);
    CHECKERR(OCIBindByPos(
        inserthp, &bind_one_char, errhp, 1,
        const_cast<oratext*>(utf8_char), (sb4)utf8_len, SQLT_AFC,//SQLT_STR
        (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT
    ));

    CHECKERR2(errhp, OCIStmtExecute(
        svchp, inserthp, errhp, (ub4) 1, (ub4) 0,
        (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
        OCI_DEFAULT
    ));

    // Commit transaction
    CHECKERR(OCITransCommit(svchp, errhp, 0));
    CHECKERR(OCIHandleFree(inserthp, OCI_HTYPE_STMT));
}

int main(int argc, char** argv) {
    // create table string_tab(one_char CHAR(1))
    const char*const username = "oci101";
    const char*const password = "oci101";
    const char*const database = "OCI101";

    const ub4 mode = OCI_DEFAULT;
    //const ub4 mode = OCI_DEFAULT | OCI_OBJECT;
    //ub2 charset = 0;
    ub2 charset = get_al32utf8_id();

    OCIEnv* envhp = 0;
    CHECKERR(OCIEnvNlsCreate(&envhp, mode, 0, 0, 0, 0, 0, 0, charset, charset));

    OCIError* errhp = 0;
    CHECKERR(OCIHandleAlloc(envhp, (void**)&errhp, OCI_HTYPE_ERROR, 0, 0));

    cout << "Connecting to Oracle..." << endl;
    OCISvcCtx* svchp = 0;
    CHECKERR(OCILogon2(
        envhp, errhp, &svchp,
        (const OraText*)username, (ub4)strlen(username),
        (const OraText*)password, (ub4)strlen(password),
        (const OraText*)database, (ub4)strlen(database),
        OCI_DEFAULT
    ));
    cout << "Connected to '" << database << "' as " << username << endl;

    OCISession* session = 0; // get session handle
    CHECKERR(OCIAttrGet(
        svchp, OCI_HTYPE_SVCCTX, &session, 0, OCI_ATTR_SESSION, errhp
    ));

    cout << "\nClient: " << endl;
    print_env_charsets(cout, envhp, errhp) << endl;
    print_nls_character_set(cout, envhp, errhp) << endl;

    cout << "\nServer: " << endl;
    print_nls_character_set(cout, session, errhp) << endl;

    cout << "\ninserting 'A' ASCII char" << endl;
    const oratext a[] = "A";
    insert_one_char(envhp, errhp, svchp, a);

    cout << "\nServer: " << endl;
    print_nls_character_set(cout, session, errhp) << endl;

    cout << "\ninserting 'cents' UNICODE char, in UTF-8" << endl;
    const oratext cents[] = "\xC2" "\xA2";
    insert_one_char(envhp, errhp, svchp, cents);

    CHECKERR(OCILogoff(svchp, errhp));
    cout << "Disconnected from Oracle. Bye." << endl;
    CHECKERR(OCIHandleFree(envhp, OCI_HTYPE_ENV));
    return 0;
}