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，只有81MSQL*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 Package，SQL*Plus Package放在一起，就可以运行sqlplus命令了，更简单，更方便
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
其他官方链接
Oracle Instant Client ODBC Installation Notes
Oracle Instant Client downloads
Database Application Development
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
