linuxC_OCI方式ORACLE编程

本代码记录了通过oci方式访问oracle的代码:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

// cols for query result:
#define N 8

static OCIEnv    *p_env;
static OCIError  *p_err;
static OCISvcCtx  *p_svc;
static OCIStmt    *p_sql;
static OCIDefine  *p_dfn = (OCIDefine *) 0;
static OCIBind    *p_bnd = (OCIBind *) 0;

int main()
{
    int    p_bvi;
    char   *p_cols[N];
    int    rc;
    char   errbuf[100];
    int    errcode;
    char   mysql[20];
    int    i;

    for (i = 0; i < N; i++)
    {
        p_cols[i] = (char *)malloc(20);
    }

    /* Initialize OCI evironment*/
    rc = OCIEnvCreate(
        (OCIEnv **) &p_env,OCI_DEFAULT,(dvoid *)0,
        (dvoid * (*)(dvoid *, size_t)) 0,
        (dvoid * (*)(dvoid *, dvoid *, size_t))0,
        (void (*)(dvoid *, dvoid *)) 0,
        (size_t) 0,
        (dvoid **) 0);

/* Initialize handles */
    rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,  (size_t) 0, (dvoid **) 0);
    rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0);

    /* Connect to database server 
       c##scott : username
       8        : username_len
       Orcl666  : userpasswd
       7        : userpasswd_len
       jesse    : db_name
       5        : db_name_len
    */
    rc = OCILogon(p_env, p_err, &p_svc, "c##scott", 8, "Orcl666",  7, "jesse", 5);
    if (rc != 0)
    {
        OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("Error - %.*s/n", 512, errbuf);
        exit(8);
    }
    else
    {
        printf("Connect to orcl successful! 已连接！\n");
    }

    /* Allocate and prepare SQL statement */
    rc = OCIHandleAlloc((dvoid *) p_env, (dvoid **) &p_sql,  OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);

    /* set my sql statement */
    strcpy(mysql, "select * from emp");
    rc = OCIStmtPrepare(p_sql, p_err, mysql, (ub4) strlen(mysql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

    /* Define the select list items */
    for(i = 0; i < N; i++)
    {
        rc = OCIDefineByPos(p_sql, &p_dfn, p_err, i+1, (dvoid *) p_cols[i], (sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0,  (ub2 *)0, OCI_DEFAULT);
    }    

    /* Execute the SQL statment */
    rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
    while (rc != OCI_NO_DATA)
    {
        /* Fetch the remaining data */
        for (i = 0; i < N; i++)
        {
            printf("%10s  ", p_cols[i]);
        }
        printf("\n");

        rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);
    }

    /* Destroy heap memory */
    for(i = 0; i < N; i++)
    {
        if(p_cols[i] != NULL)
        {
            free (p_cols[i]);
            p_cols[i] = NULL;
        }
    }

    /* Disconnect */
    rc = OCILogoff(p_svc, p_err);

    /* Free handles */
    rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT);
    rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
    rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);

    return 0;
}

编译： makefile

Demo:testSQL.c
        gcc testSQL.c -I/u01/app/oracle/product/19.3.0/db_1/rdbms/public -L/u01/app/oracle/product/19.3.0/db_1/lib -lclntsh -o Demo