Oracle OCI查询数据库

例子代码:

/*
 * create table testtab(a int, col1 char(5) not null, colch char(10), vchar varchar2(20));
 * insert into testtab values(1, 'a', 'bb', 'ccc');
 * insert into testtab(a, col1) values(2, 'aaa');
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

#define MAX_COLUMNS 100
#define MAX_COL_SIZE 1024

OCIEnv     *envhp;
OCIError   *errhp;
OCISvcCtx  *svchp;
OCIStmt    *stmthp;
OCIDefine  *defnp[MAX_COLUMNS];
void       *buffers[MAX_COLUMNS];
sb2        indicators[MAX_COLUMNS];
ub4        col_count;

#define CHECK(status, msg) \
    if ((status) != OCI_SUCCESS) { \
        text errbuf[512]; \
        sb4 errcode = 0; \
        OCIErrorGet(errhp, 1, NULL, &errcode, errbuf, sizeof(errbuf), OCI_HTYPE_ERROR); \
        fprintf(stderr, "%s - OCI Error: %s\n", msg, errbuf); \
        exit(1); \
    }

const char * data_type_name(ub2 data_type) {
    switch (data_type) {
    case SQLT_CHR           : return "CHR";
    case SQLT_DATE          : return "DATE";
    case SQLT_TIMESTAMP     : return "TIMESTAMP";
    case SQLT_TIMESTAMP_TZ  : return "TIMESTAMP_TZ";
    case SQLT_TIMESTAMP_LTZ : return "TIMESTAMP_LTZ";
    case SQLT_INTERVAL_YM   : return "INTERVAL_YM";
    case SQLT_INTERVAL_DS   : return "INTERVAL_DS";
    case SQLT_CLOB          : return "CLOB";
    case SQLT_BLOB          : return "BLOB";
    case SQLT_INT           : return "INT";
    case SQLT_UIN           : return "UIN";
    case SQLT_FLT           : return "FLT";
    case SQLT_PDN           : return "PDN";
    case SQLT_BIN           : return "BIN";
    case SQLT_NUM           : return "NUM";
    case SQLT_NTY           : return "NTY";
    case SQLT_REF           : return "REF";
    case SQLT_VST           : return "VST";
    case SQLT_VNU           : return "VNU";
    case SQLT_AFC           : return "AFC";
    default                 : printf("Unknown type: %d", data_type); return "UNKNOWN";
    }
}

int main() {
    int i;
    const char *username = "<username>";
    const char *password = "<password>";
    const char *db = "<dbname>";
    char sql[1024] = "SELECT * from testtab";

    OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL);
    OCIEnvInit(&envhp, OCI_DEFAULT, 0, NULL);
    OCIHandleAlloc(envhp, (void **)&errhp, OCI_HTYPE_ERROR, 0, NULL);
    OCIHandleAlloc(envhp, (void **)&svchp, OCI_HTYPE_SVCCTX, 0, NULL);
    CHECK(OCILogon(envhp, errhp, &svchp, (OraText *)username, strlen(username), (OraText *)password, strlen(password), (OraText *)db, strlen(db)), "Logon");

    OCIHandleAlloc(envhp, (void **)&stmthp, OCI_HTYPE_STMT, 0, NULL);

    CHECK(OCIStmtPrepare(stmthp, errhp, (OraText *)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT), "Prepare");
    CHECK(OCIStmtExecute(svchp, stmthp, errhp, 0, 0, NULL, NULL, OCI_DEFAULT), "Execute");

    // Describe columns info
    printf("Columns:\n");
    CHECK(OCIAttrGet(stmthp, OCI_HTYPE_STMT, &col_count, 0, OCI_ATTR_PARAM_COUNT, errhp), "AttrGet param count");
    for (i = 1; i <= col_count; ++i) {
        OCIParam *param = NULL;
        CHECK(OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, (void **)&param, i), "ParamGet");

        OraText *col_name;
        ub4 col_name_len;
        ub2 data_type;
        ub4 data_size;
        ub1 data_nullable;

        CHECK(OCIAttrGet(param, OCI_DTYPE_PARAM, &col_name,         &col_name_len,  OCI_ATTR_NAME, errhp),      "Get column name");
        CHECK(OCIAttrGet(param, OCI_DTYPE_PARAM, &data_type,        0,              OCI_ATTR_DATA_TYPE, errhp), "Get column type");
        CHECK(OCIAttrGet(param, OCI_DTYPE_PARAM, &data_size,        0,              OCI_ATTR_DATA_SIZE, errhp), "Get column size");
        CHECK(OCIAttrGet(param, OCI_DTYPE_PARAM, &data_nullable,    0,              OCI_ATTR_IS_NULL, errhp),   "Get column nullable");

        printf("%d: name=%.*s, type=%s, size=%d, nullable=%d\n", i, col_name_len, col_name, data_type_name(data_type), data_size, data_nullable);

        // Allocate buffer
        buffers[i - 1] = malloc(MAX_COL_SIZE);
        memset(buffers[i - 1], 0, MAX_COL_SIZE);

        ub2 oci_type = SQLT_STR;  // default
        switch (data_type) {
            case SQLT_NUM:
            case SQLT_INT:
                oci_type = SQLT_STR; // we'll fetch numbers as strings for simplicity
                break;
            case SQLT_CHR:
            case SQLT_VCS:
            case SQLT_AFC:
                oci_type = SQLT_STR;
                break;
            default:
                printf("Unsupported column type: %d (column %.*s)\n", data_type, col_name_len, col_name);
                exit(1);
        }

        CHECK(OCIDefineByPos(stmthp, &defnp[i - 1], errhp, i,
                                buffers[i - 1], MAX_COL_SIZE,
                                oci_type, &indicators[i - 1], NULL, NULL, OCI_DEFAULT), "Define");
    }

    printf("Results:\n");
    while (OCIStmtFetch2(stmthp, errhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT) == OCI_SUCCESS) {
        for (i = 0; i < col_count; ++i) {
            if (indicators[i] == -1)
                printf("NULL\t");
            else
                printf("%s\t", (char *)buffers[i]);
        }
        printf("\n");
    }

    // Cleanup
    for (i = 0; i < col_count; ++i)
        free(buffers[i]);

    OCIHandleFree(stmthp, OCI_HTYPE_STMT);
    OCILogoff(svchp, errhp);
    OCIHandleFree(errhp, OCI_HTYPE_ERROR);
    OCIHandleFree(envhp, OCI_HTYPE_ENV);

    return 0;
}

编译运行

$ gcc -I${ORACLE_HOME}/rdbms/public -L${ORACLE_HOME}/lib -lclntsh query.c

$ ./a.out
Columns:
1: name=A, type=NUM, size=22, nullable=1
2: name=COL1, type=AFC, size=5, nullable=0
3: name=COLCH, type=AFC, size=10, nullable=1
4: name=VCHAR, type=CHR, size=20, nullable=1
Results:
1   a       bb          ccc
2   aaa     NULL    NULL