前言

在处理大型文本数据时，Oracle 数据库的 CLOB（Character Large Object）类型非常有用。使用 Oracle Call Interface（OCI）可以高效地将超长文本插入到数据库中。本篇文章将展示如何使用 OCI 插入超长文本到 Oracle 数据库的 CLOB 字段中。

插入CLOB数据

建立表

1
2
3
4
CREATE TABLE Components (
    name VARCHAR()
    clob_column CLOB
);

插入数据代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
#include <iostream>
#include <fstream>
#include <oci.h>
#include <cstring>
#include <sstream>

using namespace std;

void checkerr(OCIError* errhp, sword status) {
    if (status != OCI_SUCCESS) {
        text errbuf[512];
        sb4 errcode = 0;
        OCIErrorGet((dvoid*)errhp, (ub4)1, (text*)NULL, &errcode,
            errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
        cerr << "Error - " << errbuf << endl;
        exit(EXIT_FAILURE);
    }
}

string readFile(const string& filename) {
    ifstream file(filename);
    if (!file.is_open()) {
        cerr << "Unable to open file: " << filename << endl;
        exit(EXIT_FAILURE);
    }

    stringstream buffer;
    buffer << file.rdbuf();
    return buffer.str();
}

int main() {
    OCIEnv* envhp = nullptr;
    OCIError* errhp = nullptr;
    OCISvcCtx* svchp = nullptr;
    OCIServer* srvhp = nullptr;
    OCISession* authp = nullptr;
    OCIBind* bind1p = nullptr, * bind2p = nullptr;
    OCIStmt* stmthp = nullptr;
    OCILobLocator* clob = nullptr;

    text* username = (text*)"name";
    text* password = (text*)"password";
    text* dbname = (text*)"dbname";

    try {
        // 初始化OCI环境
        checkerr(nullptr, OCIEnvCreate(&envhp, OCI_DEFAULT, nullptr, nullptr, nullptr, nullptr, 0, nullptr));
        checkerr(nullptr, OCIHandleAlloc(envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, 0, nullptr));

        // 连接到数据库
        checkerr(errhp, OCIHandleAlloc(envhp, (dvoid**)&srvhp, OCI_HTYPE_SERVER, 0, nullptr));
        checkerr(errhp, OCIServerAttach(srvhp, errhp, dbname, strlen((char*)dbname), 0));
        checkerr(errhp, OCIHandleAlloc(envhp, (dvoid**)&svchp, OCI_HTYPE_SVCCTX, 0, nullptr));
        checkerr(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, srvhp, 0, OCI_ATTR_SERVER, errhp));
        checkerr(errhp, OCIHandleAlloc(envhp, (dvoid**)&authp, OCI_HTYPE_SESSION, 0, nullptr));
        checkerr(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, username, strlen((char*)username), OCI_ATTR_USERNAME, errhp));
        checkerr(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, password, strlen((char*)password), OCI_ATTR_PASSWORD, errhp));
        checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT));
        checkerr(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, authp, 0, OCI_ATTR_SESSION, errhp));

        // SQL插入语句
        text* sql = (text*)"INSERT INTO Components (name, clob_column)\n"
            "VALUES (:1, :2)\n";;
        checkerr(errhp, OCIHandleAlloc(envhp, (dvoid**)&stmthp, OCI_HTYPE_STMT, 0, nullptr));
        checkerr(errhp, OCIStmtPrepare(stmthp, errhp, sql, strlen((char*)sql), OCI_NTV_SYNTAX, OCI_DEFAULT));

        // 创建临时CLOB对象
        checkerr(errhp, OCIDescriptorAlloc(envhp, (dvoid**)&clob, OCI_DTYPE_LOB, 0, nullptr));
        checkerr(errhp, OCILobCreateTemporary(svchp, errhp, clob, OCI_DEFAULT, SQLCS_IMPLICIT, OCI_TEMP_CLOB, FALSE, OCI_DURATION_SESSION));

        // 从文件读取数据
        string filename = "DR5.302.302(已装配的耦合腔).xml";
        string clobData = readFile(filename);

        ub4 len = clobData.size();
        // 写入数据到CLOB对象
        checkerr(errhp, OCILobWriteAppend(svchp, errhp, clob, &len, (dvoid*)clobData.c_str(), clobData.size(), OCI_ONE_PIECE, nullptr, nullptr, 0, SQLCS_IMPLICIT));

        // 绑定参数
        checkerr(errhp, OCIBindByPos(stmthp, &bind1p, errhp, 1, (dvoid*)&"components1", 3, SQLT_CHR, nullptr, nullptr, nullptr, 0, nullptr, OCI_DEFAULT));
        checkerr(errhp, OCIBindByPos(stmthp, &bind2p, errhp, 2, (dvoid*)&clob, -1, SQLT_CLOB, nullptr, nullptr, nullptr, 0, nullptr, OCI_DEFAULT));

        // 执行SQL插入
        checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, nullptr, nullptr, OCI_DEFAULT));

        // 提交事务
        checkerr(errhp, OCITransCommit(svchp, errhp, 0));

        // 清理
        OCILobFreeTemporary(svchp, errhp, clob);
        OCIDescriptorFree(clob, OCI_DTYPE_LOB);
        OCIHandleFree(stmthp, OCI_HTYPE_STMT);
        OCISessionEnd(svchp, errhp, authp, OCI_DEFAULT);
        OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(srvhp, OCI_HTYPE_SERVER);
        OCIHandleFree(authp, OCI_HTYPE_SESSION);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIHandleFree(envhp, OCI_HTYPE_ENV);

        cout << "Data inserted successfully into CLOB column." << endl;
    }
    catch (...) {
        cerr << "An error occurred." << endl;
        if (svchp) OCITransRollback(svchp, errhp, 0);
        if (clob) OCIDescriptorFree(clob, OCI_DTYPE_LOB);
        if (stmthp) OCIHandleFree(stmthp, OCI_HTYPE_STMT);
        if (authp) OCIHandleFree(authp, OCI_HTYPE_SESSION);
        if (srvhp) OCIHandleFree(srvhp, OCI_HTYPE_SERVER);
        if (svchp) OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        if (errhp) OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        if (envhp) OCIHandleFree(envhp, OCI_HTYPE_ENV);
    }

    return 0;
}

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
#include <iostream>
#include <fstream>
#include <oci.h>
#include <cstring>
#include <sstream>

using namespace std;

void checkerr(OCIError* errhp, sword status) {
    if (status != OCI_SUCCESS) {
        text errbuf[512];
        sb4 errcode = 0;
        OCIErrorGet((dvoid*)errhp, (ub4)1, (text*)NULL, &errcode,
            errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
        cerr << "Error - " << errbuf << endl;
        exit(EXIT_FAILURE);
    }
}


int main() {
    OCIEnv* envhp = nullptr;
    OCIError* errhp = nullptr;
    OCISvcCtx* svchp = nullptr;
    OCIServer* srvhp = nullptr;
    OCISession* authp = nullptr;
    OCIBind* bind1p = nullptr, * bind2p = nullptr;
    OCIBind* bind3p = nullptr, * bind4p = nullptr;
    OCIStmt* stmthp = nullptr;
    OCILobLocator* clob = nullptr;

    text* username = (text*)"name";
    text* password = (text*)"password";
    text* dbname = (text*)"dbname";

    try {
        // 初始化OCI环境
        checkerr(nullptr, OCIEnvCreate(&envhp, OCI_DEFAULT, nullptr, nullptr, nullptr, nullptr, 0, nullptr));
        checkerr(nullptr, OCIHandleAlloc(envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, 0, nullptr));

        // 连接到数据库
        checkerr(errhp, OCIHandleAlloc(envhp, (dvoid**)&srvhp, OCI_HTYPE_SERVER, 0, nullptr));
        checkerr(errhp, OCIServerAttach(srvhp, errhp, dbname, strlen((char*)dbname), 0));
        checkerr(errhp, OCIHandleAlloc(envhp, (dvoid**)&svchp, OCI_HTYPE_SVCCTX, 0, nullptr));
        checkerr(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, srvhp, 0, OCI_ATTR_SERVER, errhp));
        checkerr(errhp, OCIHandleAlloc(envhp, (dvoid**)&authp, OCI_HTYPE_SESSION, 0, nullptr));
        checkerr(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, username, strlen((char*)username), OCI_ATTR_USERNAME, errhp));
        checkerr(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, password, strlen((char*)password), OCI_ATTR_PASSWORD, errhp));
        checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT));
        checkerr(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, authp, 0, OCI_ATTR_SESSION, errhp));

        // SQL插入语句
        text* sql = (text*)"SELECT id, clob_column FROM clob_table";
        checkerr(errhp, OCIHandleAlloc(envhp, (dvoid**)&stmthp, OCI_HTYPE_STMT, 0, nullptr));
        checkerr(errhp, OCIStmtPrepare(stmthp, errhp, sql, strlen((char*)sql), OCI_NTV_SYNTAX, OCI_DEFAULT));

        OCIDefine* defn1p = nullptr;
        OCIDefine* defn2p = nullptr;
        OCILobLocator* clob = nullptr;
        int id;
        char* buffer = new char [1024000];
        ub4 amt;


        // 绑定输出变量
        checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, 1, (dvoid*)&id, sizeof(id), SQLT_INT, nullptr, nullptr, nullptr, OCI_DEFAULT));
        checkerr(errhp, OCIDescriptorAlloc((dvoid*)envhp, (dvoid**)&clob, OCI_DTYPE_LOB, (size_t)0, (dvoid**)0));
        checkerr(errhp, OCIDefineByPos(stmthp, &defn2p, errhp, 2, (dvoid*)&clob, -1, SQLT_CLOB, nullptr, nullptr, nullptr, OCI_DEFAULT));

        // 执行查询
        checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)0, (ub4)0, (CONST OCISnapshot*)nullptr, (OCISnapshot*)nullptr, OCI_DEFAULT));


        // 获取并打印结果
        string clob_data;
        while (OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT) == OCI_SUCCESS) {
            cout << "ID: " << id << endl;
            clob_data.clear();  // 清空之前的数据
            ub4 offset = 1;  // 从第一个字节开始读取
            sb4 read_status;
            do {
                amt = 1024000;  // 设置amt为缓冲区大小
                memset(buffer, 0, 1024000);  // 清空缓冲区
                read_status = OCILobRead(svchp, errhp, clob, &amt, offset, (dvoid*)buffer, 1024000, nullptr, nullptr, 0, SQLCS_IMPLICIT);
                if (read_status == OCI_NEED_DATA) {
                    clob_data.append(buffer, amt);  // 将读取到的数据追加到string中
                    offset += amt;;  // 更新offset
                }
                else if (read_status != OCI_SUCCESS) {
                    cout << "OCILobRead error: " << read_status << endl;
                    break; // 或者采取其他处理方法
                }
                else
                {
                    clob_data.append(buffer, amt);  // 将读取到的数据追加到string中
                    offset += amt;;  // 更新offset
                }
            } while (read_status == OCI_NEED_DATA);
            delete []buffer;
            cout << "CLOB Data: " << clob_data << endl;
        }

        // 提交事务
        checkerr(errhp, OCITransCommit(svchp, errhp, 0));

        // 清理
        OCILobFreeTemporary(svchp, errhp, clob);
        OCIDescriptorFree(clob, OCI_DTYPE_LOB);
        OCIHandleFree(stmthp, OCI_HTYPE_STMT);
        OCISessionEnd(svchp, errhp, authp, OCI_DEFAULT);
        OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(srvhp, OCI_HTYPE_SERVER);
        OCIHandleFree(authp, OCI_HTYPE_SESSION);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIHandleFree(envhp, OCI_HTYPE_ENV);

        cout << "Data inserted successfully into CLOB column." << endl;
    }
    catch (...) {
        cerr << "An error occurred." << endl;
        if (svchp) OCITransRollback(svchp, errhp, 0);
        if (clob) OCIDescriptorFree(clob, OCI_DTYPE_LOB);
        if (stmthp) OCIHandleFree(stmthp, OCI_HTYPE_STMT);
        if (authp) OCIHandleFree(authp, OCI_HTYPE_SESSION);
        if (srvhp) OCIHandleFree(srvhp, OCI_HTYPE_SERVER);
        if (svchp) OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        if (errhp) OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        if (envhp) OCIHandleFree(envhp, OCI_HTYPE_ENV);
    }

    return 0;
}

该封面图片由riyan hidayat在Pixabay上发布