上期我们介绍了Halo数据库关于Oracle兼容性方面的知识及如何启用Halo中的Oracle解析引擎，如果有疑问可再次阅读Halo数据库是目前Oracle兼容性最好的数据库之一。本期内容将在上期开启Oracle解析引擎的基础上，进一步演示Halo对于Oracle的支持，通过直接调用ORACLE调用接口（OCI）对Halo数据库进行简单基础操作，并与Oracle进行简单比较。(完整代码放文章末尾）

准备阶段

Oracle数据库中存在scott用户用于熟悉数据库一些操作，我们也可以在Halo数据库中创建一个scott用户并赋予相关权限，用于学习。

create role scott password 'tiger' superuser;    // 创建scott 赋予超级管理员权限alter role scott login;    // 赋予登录权限 可以使用 \du 查看用户相关信息

按照下述操作将我们提供的OCI驱动导入Linux系统中

1、将libhalooci.so.10.2、libiconv.so.2以及libpq.so.5.11文件拷贝至/usr/lib目录下。

2、建立相关软链接

ln -s /usr/lib/libhalooci.so.10.2 /usr/lib/libhalooci.soln -s /usr/lib/libiconv.so.2 /usr/lib/libiconv.soln -s /usr/lib/libpq.so.5.11 /usr/lib/libpq.soln -s /usr/lib/libpq.so.5.11 /usr/lib/libpq.so.5

3、准备两台虚拟机，一台启动Halo数据库服务，另一台启动Oracle数据库服务。简单修改一下halo_demo.c代码，由于先前我们创建了scott用户，此处只需要简单的变更一下数据库实例名。Halo默认存在halo0root数据库，此处就直接使用这个默认的数据库即可。Oracle可以将数据库实例名与配置文件中的一一对应即可。

text *username = (text *)"scott";  // 用户名 text *passwd = (text *)"tiger";    // 用户密码text *server = (text *)"//localhost:1921/halo0root";  // 数据库实例名

4、编译示例代码，生成可执行文件，并运行

gcc halo_demo.c -o halo_demo -I ${ORACLE_HOME} -L /usr/lib -lhalooci -lpq -liconv -std=c99

简单比较

示例程序主要执行的动作有创建t_test表、向表中插入指定数据、更新表数据、检索表数据、删除表数据以及删除表。完整的可执行文件运行结果如下所示，左边为Halo数据库，右边为Oracle数据库。

1、创建t_test表

当可执行程序成功运行，连接上scott用户后，执行下面代码将创建t_test表，表中存在三个字段分别为no、name及age等。

void create_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt *stmhp;    text *sql_statement =        (text *)"create table t_test \n (no number(10) primary key\n, name varchar2(40)\n, age number(10))";    // ...完整代码请查看文章末尾}

Oracle执行结果：

Halo执行结果：

可以看到数据库中出现了t_test表，且表的字段为no，name以及age。

2、向t_test表中插入数据

将会向t_test表中插入三条数据，分别为{1，"张三"，23}、{2，"李四"，23}、{3，"王五"，21}。

void insert_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt *stmhp;    text *sql_statement =        (text *)"insert into t_test(no,name,age) values (:no,:name,:age)";    // ···    int no[3] = {1, 2, 3};    int age[3] = {23, 23, 21};    char *s_name[3] = {"张三", "李四", "王五"};     // ...完整代码请查看文章末尾}

Oracle执行结果：

Halo执行结果：

3、检索t_test表数据并打印

void select_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt *stmhp;    text *sql_statement =        (text *)"select no,name,age from t_test";    // ...完整代码请查看文章末尾}

Oracle执行结果：

Halo执行结果：

4、更新t_test表数据

此处将更新表中数据，将id为3的那条数据中的age改成99。

void update_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt    *stmhp;      text *sql_statement =  (text *)"update t_test set age=:1 where no=:2";    int age,id;     // ...       age = 99;    id = 3;     // ...完整代码请查看文章末尾}

Oracle执行结果：

Halo执行结果：

5、删除t_test表数据

此处将上面更改过的那条数据给删除掉。

void delete_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt    *stmhp;    text *sql_statement =        (text *)"delete from t_test where no=:1";    int id;        // ...    id = 3;    // ...完整代码请查看文章末尾}

Oracle执行结果：

Halo执行结果：

6、将t_test表从数据库中删除

void drop_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt *stmhp;    text *sql_statement = (text *)"drop table t_test";    // ...完整代码请查看文章末尾}

Oracle执行结果：

Halo执行结果：

结论

通过上述的展示的图片，我们可以看到，通过调用Oracle官方提供的OCI能对Halo数据库能进行指定的数据库操作。

问题收录

1、找不到oci.h文件说明ORACLE_HOME环境变量未设置或文件不存在

解决方法：

su - rootfind / -name oci.h

如果文件存在设置环境变量地址（注意是否需要再次切换用户）

export ORACLE_HOME=指定oci.h路径

如果文件不存在，前往

https://www.oracle.com/database/technologies/instant-client/downloads.html 下载SDK包，解压之后在设置ORACLE_HOME环境变量的值。

2、error while loading shared libraries: libXXX.so.X: cannot open shared object file: No such file 代表ld找不到指定动态库文件

解决方法：

vim /etc/ld.so.conf添加动态库所处目录 如 /usr/libldconfig

示例代码

更多OCI用法请参考Oracle官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/introduction.html#GUID-27645179-6957-4004-8BB8-38775266B038

#include <stdio.h>#include <string.h>#include "oci.h"
sb4 errcode;text errbuf[512] = {0};
void create_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt *stmhp;    text *sql_statement =        (text *)"create table t_test \n (no number(10) primary key\n, name varchar2(40)\n, age number(10))";
    int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);    if (ret != OCI_SUCCESS)    {        return;    }
    OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);    ret = OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);    if (ret != OCI_SUCCESS)    {        printf("FAILURE IN CREATING TABLE(S)\n");        OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);        printf ("%s", errbuf);        OCIHandleFree(stmhp, OCI_HTYPE_STMT);        return;    }    printf("Table(s) Successfully Creat\n");    OCIHandleFree(stmhp, OCI_HTYPE_STMT);}
void drop_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt *stmhp;    text *sql_statement = (text *)"drop table t_test";    int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);    if (ret != OCI_SUCCESS)    {        return;    }
    OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);    ret = OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);    if (ret != OCI_SUCCESS)    {        printf("FAILURE IN DROPING TABLE(S)\n");        OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);        printf ("%s", errbuf);        OCIHandleFree(stmhp, OCI_HTYPE_STMT);        return;    }    printf("Table(s) Successfully Drop\n");    OCIHandleFree(stmhp, OCI_HTYPE_STMT);}
void insert_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt *stmhp;    text *sql_statement =        (text *)"insert into t_test(no,name,age) values (:no,:name,:age)";    OCIBind *bind_no = (OCIBind *)0;    OCIBind *bind_name = (OCIBind *)0;    OCIBind *bind_age = (OCIBind *)0;
    int no[3] = {1, 2, 3};    int age[3] = {23, 23, 21};    char *s_name[3] = {"张三", "李四", "王五"};
    int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp,OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);    if (ret != OCI_SUCCESS)    {        return;    }
    OCIStmtPrepare(stmhp, errhp, sql_statement,(ub4)strlen((char *)sql_statement),(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);    for (int i = 0; i < 3; i++)    {        OCIBindByName(stmhp, &bind_no, errhp, (text *)":no",                      -1, (dvoid *)&no[i],                      sizeof(no[i]), SQLT_INT,                      (dvoid *)0, 0, (ub2 *)0, (ub4)0,                      (ub4 *)0, OCI_DEFAULT);
        OCIBindByName(stmhp, &bind_name, errhp, (text *)":name",                      -1, (dvoid *)s_name[i],                      strlen(s_name[i]) + 1, SQLT_STR, (dvoid *)0, 0,                      (ub2 *)0, (ub4)0, (ub4 *)0,                      OCI_DEFAULT);
        OCIBindByName(stmhp, &bind_age, errhp, (text *)":age",                      -1, (dvoid *)&age[i], sizeof(age[i]),                      SQLT_INT, (dvoid *)0, 0, (ub2 *)0,                      (ub4)0, (ub4 *)0, OCI_DEFAULT);
        ret = OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);        if (ret != OCI_SUCCESS)        {            printf("FAILURE IN INSERTING DATA\n");            OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);            printf ("%s", errbuf);            OCIHandleFree(stmhp, OCI_HTYPE_STMT);            return;        }    }    OCITransCommit(svchp, errhp, (ub4)0);    printf("Table(s) Successfully Insert \n");    OCIHandleFree(stmhp, OCI_HTYPE_STMT);}
void select_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt *stmhp;    OCIDefine *define;    int no = 0;    int age = 0;    char name[40] = {0};    ub4 fetched = 1;    text *sql_statement =        (text *)"select no,name,age from t_test";
    int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);    if (ret != OCI_SUCCESS)    {        return;    }
    OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);    OCIDefineByPos(stmhp, &define, errhp, (ub4)1, &no, sizeof(no), (ub2)SQLT_INT, NULL, 0, 0, OCI_DEFAULT);    OCIDefineByPos(stmhp, &define, errhp, (ub4)2, name, 40, (ub2)SQLT_STR, NULL, 0, 0, OCI_DEFAULT);    OCIDefineByPos(stmhp, &define, errhp, (ub4)3, &age, sizeof(age), (ub2)SQLT_INT, NULL, 0, 0, OCI_DEFAULT);    ret = OCIStmtExecute(svchp, stmhp, errhp,(ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);    if (ret == OCI_NO_DATA)    {        fetched = 0;    }else if(ret != OCI_SUCCESS)    {        OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);        printf ("%s", errbuf);        OCIHandleFree(stmhp, OCI_HTYPE_STMT);        return;    }
    if (fetched)    {        printf("no\t name\t age\t\r\n");        printf("%d\t %s\t  %d\t\r\n", no, name, age);        while (1)        {            ret = OCIStmtFetch(stmhp, errhp,1, OCI_FETCH_NEXT, OCI_DEFAULT);            if (ret == OCI_NO_DATA)            {                break;            }            printf("%d\t %s\t  %d\t\r\n", no, name, age);        }    }    printf("Table(s) Successfully Select \n");    OCIHandleFree(stmhp, OCI_HTYPE_STMT);}
void update_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt    *stmhp;    OCIBind    *bindage;    OCIBind    *bindid;    text *sql_statement =  (text *)"update t_test set age=:1 where no=:2";
    int age,id;    int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);    if (ret != OCI_SUCCESS)    {        return;    }
    OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);    OCIBindByPos(stmhp, &bindage, errhp, (ub4)1, &age, sizeof(age),                        SQLT_INT, NULL, NULL,NULL,0, NULL, OCI_DEFAULT);    OCIBindByPos(stmhp, &bindid, errhp, (ub4)2, &id, sizeof(id),                        SQLT_INT, NULL, NULL,NULL,0, NULL, OCI_DEFAULT);    age = 99;    id = 3;
    ret = OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);    if (ret != OCI_SUCCESS)    {        OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);        printf("FAILURE IN UPDATE TABLE(S)\n");        printf ("%s", errbuf);        OCIHandleFree(stmhp, OCI_HTYPE_STMT);        return;    }    OCITransCommit(svchp, errhp, (ub4)0);    printf("Table(s) Successfully Update\n");    OCIHandleFree(stmhp, OCI_HTYPE_STMT);}
void delete_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp){    OCIStmt    *stmhp;    text *sql_statement =        (text *)"delete from t_test where no=:1";    OCIBind    *bindid;    int id;    int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);    if (ret != OCI_SUCCESS)    {        return;    }
    OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);    OCIBindByPos(stmhp, &bindid, errhp, (ub4)1, &id, sizeof(id),                        SQLT_INT, NULL, NULL,NULL,0, NULL, OCI_DEFAULT);    id = 3;    if (OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT) != OCI_SUCCESS)    {        OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);        printf("FAILURE IN DELETE TABLE(S)\n");        printf ("%s", errbuf);        OCIHandleFree(stmhp, OCI_HTYPE_STMT);        return;    }    OCITransCommit(svchp, errhp, (ub4)0);    printf("Table(s) Successfully Delete\n");    OCIHandleFree(stmhp, OCI_HTYPE_STMT);}
int main(){    text *username = (text *)"scott";    text *passwd = (text *)"tiger";    text *server = (text *)"snorcl11g";
    /* Enviroment */    OCIEnv *envhp;    /* Error */    OCIError *errhp;    /* Service Context */    OCISvcCtx *svchp;
    int ret = OCIEnvCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);    if (ret != OCI_SUCCESS && ret != OCI_SUCCESS_WITH_INFO)    {        return -1;    }
    ret = OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, (size_t)0, NULL);    if (ret != OCI_SUCCESS && ret != OCI_SUCCESS_WITH_INFO)    {        OCIHandleFree(envhp, OCI_HTYPE_ENV);        return -1;    }
    ret = OCILogon(envhp, errhp, &svchp, username, strlen((const char *)username),                   passwd, strlen((const char *)passwd), server, strlen((const char *)server));
    if (ret != OCI_SUCCESS && ret != OCI_SUCCESS_WITH_INFO)    {        OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);        printf ("%s", errbuf);        OCIHandleFree(errhp, OCI_HTYPE_ERROR);        OCIHandleFree(envhp, OCI_HTYPE_ENV);        return -1;    }    printf("Logged on as --> '%s'\n", username);
    create_table(svchp, errhp, envhp);    insert_table(svchp, errhp, envhp);    select_table(svchp, errhp, envhp);    update_table(svchp, errhp, envhp);    select_table(svchp, errhp, envhp);    delete_table(svchp, errhp, envhp);    select_table(svchp, errhp, envhp);    drop_table(svchp, errhp, envhp);
    OCILogoff(svchp,errhp);    OCIHandleFree(errhp, OCI_HTYPE_ERROR);    OCIHandleFree(envhp, OCI_HTYPE_ENV);    return 0;}

ITPub博客

Halo数据库对ORACLE调用接口（OCI）的支持

最新文章