Linux下C语言访问Oracle数据库Demo

前提条件 1. Linux环境已经存在，安装好了Oracle 本demo 运行环境本地环境 REDHAT LINUX AS 4 ,ORACLE 10G本地数据库sid orc

前提条件

 1. Linux环境已经存在，，安装好了Oracle

 本demo 运行环境

本地环境     RedHat LINUX AS 4 ,ORACLE 10G
本地数据库sid orcl,ip:127.0.0.1,用户名:kingbi，密码：kingbi,表dsd_test. 显示表dsd_test 的所有记录.

步骤：

(1) 创建表 create table dsd_test(aa char(3)); insert into dsd_test  values('123');insert into dsd_test  values('456');

      被配置本地命名服务（$ORACLE_HOME/network/admin/tnsnames.ora

     ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

(2) 修改配置文件 /etc/profile (用root用户修改，增加 2句代码

      ...

      export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC
      export ORACLE_HOME=........                                                          #以下2行为新加

      export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib          #这句很关键
       .....

(3) 更改$ORACLE_HOME/precomp/admin/pcscfg.cfg （include= 开头的语句为新加的，,/usr/lib/gcc/i386-redhat-linux/3.4.3/include 也是新加的）

sys_include=(/ade/aime_rdbms_9819/oracle/precomp/public,/usr/include,/usr/lib/gcc-lib/i486-SUSE-linux/2.95.3/include,/usr/lib/gcc-lib/i386-redhat-linux/3.2.3/include,/usr/lib/gcc-lib/i386-redhat-linux7/2.96/include,/usr/lib/gcc/i386-redhat-linux/3.4.3/include)
include=$ORACLE_HOME/lib
include=$ORACLE_HOME/precomp/precomp/include
include=$ORACLE_HOME/precomp/oracore/include
include=$ORACLE_HOME/precomp/oracore/public
include=$ORACLE_HOME/precomp/rdbms/include
include=$ORACLE_HOME/precomp/rdbms/public
include=$ORACLE_HOME/precomp/rdbms/demo
include=$ORACLE_HOME/precomp/nlsrtl/include
include=$ORACLE_HOME/precomp/nlsrtl/public
include=$ORACLE_HOME/precomp/network_src/include
include=$ORACLE_HOME/precomp/network_src/public
include=$ORACLE_HOME/precomp/network/include
include=$ORACLE_HOME/precomp/network/public
include=$ORACLE_HOME/precomp/plsql/public
ltype=short
(4) 编写c 程序 test.pc文件

#include

EXEC SQL INCLUDE SQLCA;

int main()
{
     //声明SQL变量
     EXEC SQL BEGIN DECLARE SECTION;
     VARCHAR user[20],pass[20],tnsname[20];

     //VARCHAR预编译后为struct { unsigned short len; unsigned char arr[20];}
     char ename[20];
     EXEC SQL END DECLARE SECTION;

     //声明结束
     int i=0;
     strcpy(user.arr,"kingbi");
     user.len=(unsigned short)strlen((char *)user.arr);
     strcpy(pass.arr,"kingbi");
     pass.len=(unsigned short)strlen((char *)pass.arr);
     strcpy(tnsname.arr,"orcl");
     tnsname.len=(unsigned short)strlen((char *)tnsname.arr);
     //连接数据库
     EXEC SQL CONNECT :user IDENTIFIED BY :pass USING :tnsname;

    //执行查询

    EXEC SQL declare emp_cursor cursor for
     select aa from kingbi.test;          
     EXEC SQL open emp_cursor;
     EXEC SQL WHENEVER NOT FOUND DO break;

     while(1)
     {
           EXEC SQL fetch emp_cursor into :empno,:ename;
           printf("the name is %s\n",ename);
           i=i+1;
     }
    
     printf("Yeah!We get %d records\n",i);

     EXEC SQL close emp_cursor;
     EXEC SQL commit work release;
}

(5) 预编译 ，编译后会生成 .c文件

proc test.pc

(6)编译

gcc -o test test.c $ORACLE_HOME/lib/libclntsh.so

(6) 运行生成的可执行文件

./test

输出：

the name is 123               
the name is 456               
Yeah!We get 2 records