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

本期内容将在 Oracle 解析引擎的基础上，进一步演示 Halo 对于 Oracle 的支持，通过直接调用 Oracle 调用接口（OCI）对 Halo 数据库进行简单基础操作，并与 Oracle 进行简单比较。（完整代码见文末）

## 准备阶段

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

sql

create role scott password 'tiger' superuser;
alter role scott login;

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

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

2. 建立相关软链接：

bash

ln -s /usr/lib/libhalooci.so.10.2 /usr/lib/libhalooci.so
ln -s /usr/lib/libiconv.so.2 /usr/lib/libiconv.so
ln -s /usr/lib/libpq.so.5.11 /usr/lib/libpq.so
ln -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. 编译示例代码，生成可执行文件，并运行：

bash

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 表

连接成功后，执行以下代码创建 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 表，字段结构一致。

### 2. 向 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 表数据

将 no=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 = 99;
    int id = 3;
    // ...
}

Oracle 执行结果：

Halo 执行结果：

### 5. 删除 t_test 表数据

删除 no=3 的记录：

void delete_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
    OCIStmt *stmhp;
    text *sql_statement =
        (text *)"delete from t_test where no=:1";
    int 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 执行结果：

## 结论

通过上述对比可见，Halo 数据库在 OCI 接口层面具备良好的 Oracle 兼容性，能够支持常见的 DDL 与 DML 操作，且行为与 Oracle 高度一致。

## 问题收录

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

解决方法：

bash

su - root
find / -name oci.h
# 若存在，设置环境变量
export ORACLE_HOME=/path/to/oracle/sdk
# 若不存在，请从 https://www.oracle.com/database/technologies/instant-client/downloads.html 下载 Instant Client SDK 并解压后设置 ORACLE_HOME

2. **error while loading shared libraries: libXXX.so.X: cannot open shared object file**：表示动态链接器找不到指定库。

解决方法：

bash

echo '/usr/lib' >> /etc/ld.so.conf
ldconfig

## 示例代码

更多 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 *)"//localhost:1921/halo0root";
    /* Environment */
    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;
}

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

最新文章

羲和（Halo）数据库关键补丁更新公告 - 2025年4月

羲和（Halo）数据库关键补丁更新公告 - 2024年6月

羲和（Halo）数据库关键补丁更新公告 - 2023年12月