OCI小例

环境：

WinXPSP3 + ORACLE 10G + VC6

步骤：

1）新建demo.c，内容如下：

#include <stdio.h> #include <oratypes.h> #include <ocidfn.h> #include <ocidem.h> //声明cda和lda struct cda_def cda; struct cda_def lda; //删表和建表DDL text *dt=(text *)"DROP TABLE part_nos"; text *ct=(text *)"create table part_nos(partno number, description varchar2(20))"; //建包语句 text *cp=(text *)"/ create or replace package update_parts as/ type part_number is table of part_nos.partno%type/ index by binary_integer;/ type part_description is table of part_nos.description%type/ index by binary_integer;/ procedure add_parts(n in number,/ descrip in part_description,/ partno in part_number);/ end update_parts;"; text *cb=(text *)"/ create or replace package body update_parts as/ procedure add_parts(n in number,/ descrip in part_description,/ partno in part_number) is/ begin/ for i in 1..n loop/ insert into part_nos/ values(partno(i),descrip(i));/ end loop;/ end add_parts;/ end update_parts;"; #define DESC_LEN 20 #define MAX_TABLE_SIZE 1200 //PLSQ匿名块 text *pl_sql_block=(text *)"/ BEGIN/ update_parts.add_parts(3,:description,:partno);/ end;"; text descrip[3][20]={"Frammis","Widget","Thingie"}; sword numbers[]={12125,23169,12126}; ub2 descrip_alen[3]={DESC_LEN,DESC_LEN,DESC_LEN}; ub2 descrip_rc[3]; ub4 descrip_cs=(ub4)3; ub2 descrip_indp[3]; ub2 num_alen[3]={(ub2)sizeof(sword),(ub2)sizeof(sword),(ub2)sizeof(sword)}; ub2 num_rc[3]; ub4 num_cs=(ub4)3; ub2 num_indp[3]; dvoid oci_error(void); main() { //连接oracle printf("connecting to oracle..."); if(olon(&lda,"scott/x",-1,NULL,-1,-1)) {//连接失败 printf("Cannot logon as scott. Exiting.../n"); exit(1); } //打开光标 if(oopen(&cda,&lda,NULL,-1,-1,NULL,-1)) {//打开光标失败 printf("Cannot open cursor, exiting.../n"); exit(1); } //删除表 printf("/nDropping table..."); //分析和执行SQL语句(drop table) if(oparse(&cda,dt,-1,0,2)) if(cda.rc!=942) oci_error(); //创建表 printf("/ncreating table..."); //分析和执行sql语句(create table) if(oparse(&cda,ct,-1,0,2)) oci_error(); //分析和执行建包语句(create or replace package) printf("/ncreating package..."); if(oparse(&cda,cp,-1,0,2)) oci_error(); if(oexec(&cda)) oci_error(); printf("/ncreating package body..."); if(oparse(&cda,cb,-1,0,2)) oci_error(); if(oexec(&cda)) oci_error(); //分析调用存储过程的PLSQL匿名块 printf("/nparsing PLSQL block..."); if(oparse(&cda,pl_sql_block,-1,0,2)) oci_error(); //把C数组绑定到PLSQ匿名块上 printf("/nBinding arrays..."); if(obndra(&cda, (text *)":description", -1, (ub1 *)descrip, DESC_LEN, VARCHAR2_TYPE, -1, descrip_indp, descrip_alen, descrip_rc, (ub4)MAX_TABLE_SIZE, &descrip_cs, (text *)0, -1, -1)) oci_error(); if(obndra(&cda, (text *)":partno", -1, (ub1 *)numbers, (sword)sizeof(sword), INT_TYPE, -1, num_indp, num_alen, num_rc, (ub4)MAX_TABLE_SIZE, &num_cs, (text *)0, -1, -1)) oci_error(); //执行该块 printf("/nExecuting block..."); if(oexec(&cda)) oci_error(); printf("/n"); //关闭光标 if(oclose(&cda)) { printf("error closing cursor!/n"); return -1; } //结束事务，退出oracle if(ologof(&lda)) { printf("error logging off!/n"); return -1; } exit(1); } //oci 错误处理 dvoid oci_error(void) { text msg[600]; sword rv; //取错误信息 rv=oerhms(&lda,cda.rc,msg,600); //显示错误码和错误信息 printf("/n/n%.*s",rv,msg); //显示发生错误的oci函数 printf("processing oci function %s/n",oci_func_tab[cda.fc]); //关闭光标 if(oclose(&cda)) printf("error closing cursor!/n"); if(ologof(&lda)) printf("error logging off!/n"); exit(1); }

2）编译：

cl demo.c /nologo /c /I E:/oracle/product/10.2.0/db_1/OCI/include

3）链接：

link demo.obj /nologo /LIBPATH:E:/oracle/product/10.2.0/db_1/OCI/lib/MSVC oci.lib

4）执行demo.exe进行测试。