linux下用C编写的OCI连接Oracle数据库程序代码2009-11-28 15:57206人阅读评论(0)收藏举报在Oracle 建立数据库create table employees(employee_id number(3), name varchar2(20), sex varchar2(5), birthday date);alter session set nls_date_format = 'yyyy-mm-dd';insert into employees values('101', '耳温枪', 'na', '1999-05-03');insert into employees values('102', 'xiaoli ', 'na', '1999-02-03');insert into employees values('103', 'xiaozhang ', '男', '1999-02-03');insert into employees values('104', '五千万', '男', '1999-02-03');insert into employees values('105', '问问', '女', '1999-02-03');insert into employees values('106', '阿散酸', '女', '1999-02-03');insert into employees values('107', '阿斯顿', '男', '1999-02-03');insert into employees values('108', '撒旦', 'na', '2001-5-05');insert into employees values ('001','张三','三','2001-07-05');insert into employees values('111','张三','一','2003-02-08');OCI代码：/*gcc -D_LARGEFILE64_SOURCE -D_FILE_OFFSET_BITS=64-I${ORACLE_HOME}/rdbms/public -I${ORACLE_HOME}/rdbms/demo-L${ORACLE_HOME}/lib -lclntsh -o oracle_test oracle_test.c*/#include <stdio.h>#include <stdlib.h>#include <string.h>#include <oci.h>static OCIEnv *p_env;static OCIError *p_err;static OCISvcCtx *p_svc;static OCIStmt *p_sql;static OCIDefine *p_dfn = (OCIDefine *) 0;static OCIBind *p_bnd = (OCIBind *) 0;int main(){int p_bvi;char *p_sli;char *p_sli1;int rc;char errbuf[100];int errcode;char mysql[20];p_sli=(char *)malloc(20);memset(p_sli,0,20);p_sli1=(char *)malloc(20);memset(p_sli1,0,20);/* Initialize OCI evironment*/rc = OCIEnvCreate((OCIEnv **) &p_env,OCI_DEFAULT,(dvoid *)0,(dvoid * (*)(dvoid *, size_t)) 0,(dvoid * (*)(dvoid *, dvoid *, size_t))0,(void (*)(dvoid *, dvoid *)) 0,(size_t) 0, (dvoid **) 0);/* Initialize handles */rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,(size_t) 0, (dvoid **) 0);rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,(size_t) 0, (dvoid **) 0);/* Connect to database server */rc = OCILogon(p_env, p_err, &p_svc, "scott", 5, "tiger", 5, "orcl", 4);//rc = OCILogon(p_env, p_err, &p_svc, "hr/hr@orcl", 10, NULL, -1, NULL, -1);if (rc != 0) {OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);printf("Error - %.*s/n", 512, errbuf);exit(8);}else{printf("Connect to orcl successful! 已连接！/n");}/* Allocate and prepare SQL statement */rc = OCIHandleAlloc((dvoid *) p_env, (dvoid **) &p_sql,OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);/* set my sql statement */// strcpy(mysql,"select name from employees where emploee_id=:x");strcpy(mysql, "select * from employees ");// strcpy(mysql, "insert into employees values('109', 'x12er', 'na', '1888-06-08')");//strcpy(mysql,"delete from employees where emploee_id='108'");// strcpy(mysql,"desc employees;");rc = OCIStmtPrepare(p_sql, p_err, mysql,(ub4) strlen(mysql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);/* Bind the values for the bind variables */// p_bvi = 101; /* Use employee_id=102 */// rc = OCIBindByName(p_sql, &p_bnd, p_err, (text *) ":x",// -1, (dvoid *) &p_bvi, sizeof(int), SQLT_INT, (dvoid *) 0,// (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);/* Define the select list items */rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) p_sli,(sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0,(ub2 *)0, OCI_DEFAULT);rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 2, (dvoid *) p_sli1,(sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0,(ub2 *)0, OCI_DEFAULT);/* Execute the SQL statment */rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);while (rc != OCI_NO_DATA) { /* Fetch the remaining data */printf("%s %s/n",p_sli,p_sli1);rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);}/* if(rc != OCI_SUCCESS && rc != OCI_SUCCESS_WITH_INFO){printf("insert error!/n");OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);}elseprintf("insert success!/n");*/rc = OCILogoff(p_svc, p_err); /* Disconnect */rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT); /* Free handles */rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);return;}。