Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » AODU

C,C++,ODPI-C Demo

Oracle数据库支持C和c++程序员选择高性能API来操作数据。OCI非常可靠,性能更高效。Oracle工具如SQL*Plus、Real Application Testing (RAT)、SQL*Loader和DataPump都使用OCI。OCI为其他特定于语言的接口提供了基础,比如Oracle JDBC-OCI、Oracle Data Provider for Net (ODP.Net)、Oracle Precompilers、Oracle ODBC和Oracle C++ Call Interface (OCCI)驱动程序。OCI也被主要的脚本语言驱动程序使用,比如 node-oracledb for Node.js, PHP OCI8, ruby-oci8, Perl DBD::Oracle, Python cx_Oracle,以及统计编程语言R的ROracle驱动程序。可以下面的链接
https://www.oracle.com/database/technologies/appdev/oci.html

ODPI-C是什么

Oracle Database Programming Interface for C (ODPI-C)是一个新的C代码开源库,它简化了对Oracle数据库驱动程序和用户应用程序的通用OCI特性的使用。ODPI-C是OCI之上的一个薄层,需要Oracle客户端库。ODPI-C的目标是以一种易于使用的方式向C或c++开发人员公开通用的OCI功能。OCI的API非常灵活和高效,为开发人员提供了非常细粒度的控制,用于各种各样的用例。ODPI-C也很灵活,但主要针对语言驱动程序的创建者。这些创建者在脚本语言的类型系统和语义的范围内进行编程。这些语言通常通过跨平台的“通用”api向用户提供简化的数据访问。因此,ODPI-C提供了易于使用的通用数据访问功能,同时还提供了Oracle数据库的功能。当然,ODPI-C不仅限于驱动程序。ODPI-C是在Apache 2.0和/或Oracle UPL许可下获得许可的,因此代码可以在您自己的项目中重用。


获取ODPI的代码

https://oracle.github.io/odpi/
wget https://github.com/oracle/odpi/archive/v3.3.0.zip 

解压并编译生成ODPI的库文件

[oracle@xd08mdb01 odpi-3.3.0]$ make -f Makefile
mkdir -p build
mkdir -p lib
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiConn.c -o build/dpiConn.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiContext.c -o build/dpiContext.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiData.c -o build/dpiData.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiEnv.c -o build/dpiEnv.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiError.c -o build/dpiError.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiGen.c -o build/dpiGen.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiGlobal.c -o build/dpiGlobal.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiLob.c -o build/dpiLob.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiObject.c -o build/dpiObject.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiObjectAttr.c -o build/dpiObjectAttr.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiObjectType.c -o build/dpiObjectType.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiPool.c -o build/dpiPool.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiStmt.c -o build/dpiStmt.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiUtils.c -o build/dpiUtils.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiVar.c -o build/dpiVar.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiOracleType.c -o build/dpiOracleType.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiSubscr.c -o build/dpiSubscr.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiDeqOptions.c -o build/dpiDeqOptions.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiEnqOptions.c -o build/dpiEnqOptions.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiMsgProps.c -o build/dpiMsgProps.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiRowid.c -o build/dpiRowid.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiOci.c -o build/dpiOci.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiDebug.c -o build/dpiDebug.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiHandlePool.c -o build/dpiHandlePool.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiHandleList.c -o build/dpiHandleList.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiSodaColl.c -o build/dpiSodaColl.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiSodaCollCursor.c -o build/dpiSodaCollCursor.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiSodaDb.c -o build/dpiSodaDb.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiSodaDoc.c -o build/dpiSodaDoc.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiSodaDocCursor.c -o build/dpiSodaDocCursor.o
gcc -c -Iinclude -O2 -g -Wall -Wextra -fPIC src/dpiQueue.c -o build/dpiQueue.o
gcc -shared -Wl,-soname,libodpic.so.3 -o lib/libodpic.so.3.3.0 build/dpiConn.o build/dpiContext.o build/dpiData.o build/dpiEnv.o build/dpiError.o build/dpiGen.o bu     ild/dpiGlobal.o build/dpiLob.o build/dpiObject.o build/dpiObjectAttr.o build/dpiObjectType.o build/dpiPool.o build/dpiStmt.o build/dpiUtils.o build/dpiVar.o build/     dpiOracleType.o build/dpiSubscr.o build/dpiDeqOptions.o build/dpiEnqOptions.o build/dpiMsgProps.o build/dpiRowid.o build/dpiOci.o build/dpiDebug.o build/dpiHandleP     ool.o build/dpiHandleList.o build/dpiSodaColl.o build/dpiSodaCollCursor.o build/dpiSodaDb.o build/dpiSodaDoc.o build/dpiSodaDocCursor.o build/dpiQueue.o -ldl -lpth     read
ln -sf libodpic.so.3.3.0 lib/libodpic.so.3
ln -sf libodpic.so.3 lib/libodpic.so
[oracle@xd08mdb01 odpi-3.3.0]$ [oracle@xd08mdb01 odpi-3.3.0]$ pwd /home/oracle/odpi-3.3.0 [oracle@xd08mdb01 odpi-3.3.0]$ ls -l total 48 drwxr-xr-x 2 oracle oinstall  4096 Mar  9 21:20 build -rw-r--r-- 1 oracle oinstall   150 Dec  3 07:12 CONTRIBUTING.md drwxr-xr-x 3 oracle oinstall    50 Dec  3 07:12 doc drwxr-xr-x 2 oracle oinstall    36 Dec  3 07:12 embed drwxr-xr-x 2 oracle oinstall    19 Dec  3 07:12 include drwxr-xr-x 2 oracle oinstall    71 Mar  9 21:20 lib -rw-r--r-- 1 oracle oinstall 12028 Dec  3 07:12 LICENSE.md -rw-r--r-- 1 oracle oinstall  4785 Dec  3 07:12 Makefile -rw-r--r-- 1 oracle oinstall  2621 Dec  3 07:12 Makefile.win32 -rw-r--r-- 1 oracle oinstall  2245 Dec  3 07:12 README.md drwxr-xr-x 3 oracle oinstall  4096 Dec  3 07:12 samples drwxr-xr-x 2 oracle oinstall  4096 Dec  3 07:12 src drwxr-xr-x 4 oracle oinstall  4096 Dec  3 07:12 test [oracle@xd08mdb01 odpi-3.3.0]$ 
运行测试程序

https://blogs.oracle.com/opal/odpi-c-a-light-weight-driver-for-oracle-database



#include <dpi.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#define USER           "ohsdba"
#define PASSWORD       "oracle"
#define CONNECT_STRING "192.168.10.55/ohs_ac"
static dpiContext *gContext = NULL;
static dpiErrorInfo gErrorInfo;

//-----------------------------------------------------------------------------
// printError()
//   Prints the error message. The error is first fetched
// from the global DPI context.
//-----------------------------------------------------------------------------
int printError(void)
{
    if (gContext)
        dpiContext_getError(gContext, &gErrorInfo);
    fprintf(stderr, " [FAILED]\n");
    fprintf(stderr, "    FN: %s\n", gErrorInfo.fnName);
    fprintf(stderr, "    ACTION: %s\n", gErrorInfo.action);
    fprintf(stderr, "    MSG: %.*s\n", gErrorInfo.messageLength,
            gErrorInfo.message);
    fflush(stderr);
    return DPI_FAILURE;
}

int main()
{
    const char *insertSql = "insert into TestTable values (:1, :2)";
    dpiData intColValue, stringColValue;
    dpiConn *conn;
    dpiStmt *stmt;

    // create context
    if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext,
            &gErrorInfo) < 0)
        return printError();

    // create connection
    if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD),
            CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0)
        return printError();

    // prepare insert statement for execution
    if (dpiConn_prepareStmt(conn, 0, insertSql, strlen(insertSql), NULL, 0,
            &stmt) < 0)
        return printError();

    // create first row
    dpiData_setInt64(&intColValue, 1);
    if (dpiStmt_bindValueByPos(stmt, 1, DPI_NATIVE_TYPE_INT64,
            &intColValue) < 0)
        return printError();
    dpiData_setBytes(&stringColValue, "Test data 1", strlen("Test data 1"));
    if (dpiStmt_bindValueByPos(stmt, 2, DPI_NATIVE_TYPE_BYTES,
            &stringColValue) < 0)
        return printError();
    if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, NULL) < 0)
        return printError();

    // create second row
    dpiData_setInt64(&intColValue, 2);
    if (dpiStmt_bindValueByPos(stmt, 1, DPI_NATIVE_TYPE_INT64,
            &intColValue) < 0)
        return printError();
    dpiData_setBytes(&stringColValue, "Test data 2", strlen("Test data 2"));
    if (dpiStmt_bindValueByPos(stmt, 2, DPI_NATIVE_TYPE_BYTES,
            &stringColValue) < 0)
        return printError();
    if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, NULL) < 0)
        return printError();

    // commit changes
    if (dpiConn_commit(conn) < 0)
        return printError();

    // cleanup
    dpiStmt_release(stmt);
    dpiConn_release(conn);

    return DPI_SUCCESS;
}
ODPI Sample代码

上面是简单的测试代码,在odpi的目录里面还有更多的sample代码,要运行可以按照下面的步骤执行

修改数据库链接字符串

[oracle@xd08mdb01 odpi-3.3.0]$ cd samples/
[oracle@xd08mdb01 samples]$ grep CONNECT_STRING *.c
SampleLib.c:    dpiSamples__getEnvValue("ODPIC_SAMPLES_CONNECT_STRING", "localhost/orclpdb",
[oracle@xd08mdb01 samples]$

[oracle@xd08mdb01 samples]$ vi SampleLib.c
[oracle@xd08mdb01 samples]$ grep CONNECT_STRING SampleLib.c
    dpiSamples__getEnvValue("ODPIC_SAMPLES_CONNECT_STRING", "192.168.10.55/ohs_ac",
编译ODIP Sample代码
[oracle@xd08mdb01 samples]$ make -f Makefile
运行SQL脚本
[oracle@xd08mdb01 sql]$ pwd
/home/oracle/odpi-3.3.0/samples/sql
[oracle@xd08mdb01 sql]$ ls
DropSamples.sql  SampleEnv.sql  SetupSamples.sql
[oracle@xd08mdb01 sql]$ sqlplus system/oracle12@192.168.10.55/ohs_ac
运行Sample程序
编译后的代码位于samples/build目录,运行即可
19.5Instance Client下载地址

https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

Basic Package包,具体运行OCI, OCCI, and JDBC:OCI的所有库文件,instantclient-basic-windows.x64-19.5.0.0.0dbru.zip,只有81M
SQL*Plus Package包,是sqlplus命令的包,instantclient-sqlplus-windows.x64-19.5.0.0.0dbru.zip,只有973k

Tools Package包,包含Data Pump, SQL*Loader and Workload Replay Client,instantclient-tools-windows.x64-19.5.0.0.0dbru.zip,只有1M

Basic PackageSQL*Plus Package放在一起,就可以运行sqlplus命令了,更简单,更方便

编译OCI
How to Build Oracle Precompiler, OCI and OCCI Programs (Doc ID 1011991.102)
Master Note for Precompilers, OCI and OCCI (Doc ID 1129588.1)
make -f demo_rdbms.mk EXE=<exe name> OBJS="<file1>.o <file2>.o ... <filen>.o" build
比如编译文件test1.c:
make -f demo_rdbms.mk EXE=test1 OBJS=test1.o build
比如编译文件test2a.c and test2b.c:
make -f demo_rdbms.mk EXE=test2 OBJS="test2a.o test2b.o" build
OCI Demo
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/loe.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/oci-demo-programs.html#GUID-75E18629-0C54-4495-A747-AFB346034F26
How To Use OCI To Insert Unicode Characters into NVARCHAR Column (Doc ID 743878.1)
SQL/Describe: Describing the Columns of a Select Statement (Doc ID 124599.1)
Sample Code to Insert into a Timestamp Column from OCI (Doc ID 1335817.1)
Basic OCI Program to Bind and Fetch Data when the Env is Created in Unicode (OCI_UTF16ID) Mode (Doc ID 1302061.1)
How To Mix OCI8 Calls Within Pro*C Application (Doc ID 99398.1)
编译OCCI
make -f demo_rdbms.mk EXE=<exe name> OBJS="<file1>.o <file2>.o ... <filen>.o" buildocci
比如编译文件test1.cpp:
make -f demo_rdbms.mk EXE=test1 OBJS=test1.o buildocci
比如编译文件test2a.cpp and test2b.cpp:
make -f demo_rdbms.mk EXE=test2 OBJS="test2a.o test2b.o" buildocci
https://docs.oracle.com/en/database/oracle/oracle-database/19/lncpp/loe.html
Oracle Database示例代码HR、OE、SH、PM、IX、BI,可参考下面的链接
https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/installing-sample-schemas.html#GUID-1E645D09-F91F-4BA6-A286-57C5EC66321D
https://github.com/oracle/db-sample-schemas/releases/tag/v19c
https://github.com/oracle/db-sample-schemas
其他开发语言
https://www.oracle.com/database/technologies/application-development.html

Instant-client下载地址

Instant Client是对Oracle数据库库、工具和头文件的重新打包,用于创建和运行连接到远程(或本地)Oracle数据库的应用程序。即时客户端可以用来运行OCI、OCCI、Pro*C、JDBC和ODBC应用程序,还可以用来运行脚本语言驱动程序,包括PHP的OCI8、Python的cx_Oracle、Node.js的node-oracledb和Ruby的Ruby-oci8 API。SDK包提供了头文件,用于编译使用OCI和OCCI API的C和c++程序。一些实用程序,如SQL*Plus、工作负载重放客户端、数据泵和SQL*Loader也可以在即时客户端包中使用。OCI库默认包含在Oracle数据库18c中。OCI也是轻量级的Oracle即时客户端,允许ISV和客户快速安装和部署OCI应用程序和工具。
Instant Client客户端下载
https://www.oracle.com/database/technologies/instant-client/downloads.html

https://www.oracle.com/database/technologies/faq-instant-client.html

有兴趣可以下载曾经的sqluldr,尝试学习并编译
https://raw.githubusercontent.com/jaiminpan/practice/master/ociuldr/ociuldr.c
https://raw.githubusercontent.com/jaiminpan/practice/master/ociuldr/tbuldr.c
https://raw.githubusercontent.com/wxd237/sqluldr4/master/sqluldr2.c
https://raw.githubusercontent.com/wxd237/sqluldr4/master/sqluldr3.c
gcc -c -I$ORACLE_HOME/rdbms/demo     \
       -I$ORACLE_HOME/rdbms/public   \
       -I$ORACLE_HOME/plsql/public   \
       -I$ORACLE_HOME/network/public \
       -I$ORACLE_HOME/precomp/public ociuldr.c
gcc -L$ORACLE_HOME/lib -L$ORACLE_HOME/rdbms/lib -o ociuldr ociuldr.o -lclntsh  `cat $ORACLE_HOME/lib/sysliblist`

数据模型设计

使用Oracle的SQL Developer Data Modeler,无需连接到数据库即可创建设计,下面是HR,SH,OE,PM的样例。链接为https://www.oracle.com/database/technologies/appdev/datamodeler-samples.html

其他官方链接

OCI Programmer's Guide

OCCI Programmer's Guide

ODPI-C Documentation

ODBC Documentation

Oracle Instant Client ODBC Installation Notes

ODBC Driver Release Notes

Oracle Instant Client downloads

ODPI-C homepage

Scripting Languages

Oracle Database

Database XE Express Edition

Database Application Development

Database App Development VM

Database Cloud Service


Reference
https://www.oracle.com/database/technologies/instant-client/downloads.html
https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

https://blogs.oracle.com/opal/odpi-c-31-is-available

http://www.adp-gmbh.ch/ora/misc/oci/adpoci/index.html

http://vrogier.github.io/ocilib/

https://static.rainfocus.com/oracle/oow19/sess/155356052830600180DE/PF/DEV4625-Scalability-and-High-Availability-for-Oracle-Database-Python-Applications_1568931704636001Qx1H.pdf


关键词:Instant Client odpi demo oci 

相关文章

C,C++,ODPI-C Demo
Open Source Languages and Oracle Database
Top