使用ORACLE OCI V8版本编程，在执行PL/SQL块时，不能同时绑定标量变量和数组变量......

2019独角兽企业重金招聘Python工程师标准>>>

使用的SQL如下：

1.   create table test_oci(id number,val varchar2(200));
2.   CREATE OR REPLACE PACKAGE UPDATE_TEST_OCI AS
TYPE ID_T IS TABLE OF TEST_OCI.ID%TYPE INDEX BY BINARY_INTEGER;
TYPE VAL_T IS TABLE OF TEST_OCI.VAL%TYPE INDEX BY BINARY_INTEGER;
PROCEDURE ADD_TEST_OCI(N IN NUMBER, V_ID IN ID_T, V_VAL IN VAL_T); 
END;
3.   CREATE OR REPLACE PACKAGE BODY UPDATE_TEST_OCI AS
  PROCEDURE ADD_TEST_OCI(N IN NUMBER, V_ID IN ID_T, V_VAL IN VAL_T) AS
  BEGIN
    FOR I IN 1 .. N LOOP
      INSERT INTO TEST_OCI VALUES (V_ID(I), V_VAL(I));
    END LOOP;
    COMMIT;
  END;
END;

假设环境句柄，错误句柄，服务器句柄，上下文句柄，语句句柄已分配就绪，且已连接数据库并开始会话。

代码如下：
{
OCISvcCtx *svr;
OCIStmt *stmt;
OCIError *err;
char sql[]="BEGIN \
UPDATE_TEST_OCI.ADD_TEST_OCI(:n,:id,:val); \
END;";
/*分析没有问题*/
OCIStmtPrepare(stmt,err,(text*)sql,(ub4)strlen(sql),OCI_NTV_SYNTAX,OCI_DEFAULT);
{
OCIBind *nbd;
int n;
ub2 n_rlen=sizeof(int);
sb2 n_ind=0;
ub2 n_rcode=0;


OCIBind *idbd;
int id[3]={1,2,3};
ub2 id_rlen[3]={sizeof(int),sizeof(int),sizeof(int)};
sb2        id_ind[3]={0,0,0};
ub2 id_rcode[3]={0,0,0};
ub4 id_cur=3;


OCIBind *valbd;
char val[3][20]={"test1","test2","test3"};
ub2 val_rlen[3]={sizeof(val[0]),sizeof(val[1]),sizeof(val[2])};
sb2        val_ind[3]={0,0,0};
ub2 val_rcode[3]={0,0,0};
ub4 val_cur=3;


/*绑定也没问题*/
OCIBindByName(stmt,&nbd,err,":n",strlen(":n"),&n,sizeof(int),SQLT_INT,&n_ind,&n_rlen,&n_rcode,0,0,OCI_DEFAULT);
OCIBindByName(stmt,&idbd,err,":id",strlen(":id"),&id,sizeof(int),SQLT_INT,id_ind,id_rlen,id_rcode,id_cur,&id_cur,OCI_DEFAULT);
OCIBindArrayOfStruct(idbd,err,sizeof(int),sizeof(sb2),sizeof(ub2),sizeof(ub2));
OCIBindByName(stmt,&valbd,err,":val",strlen(":val"),&val,sizeof(val[0]),SQLT_STR,val_ind,val_rlen,val_rcode,id_cur,&id_cur,OCI_DEFAULT);
OCIBindArrayOfStruct(valbd,err,sizeof(val[0]),sizeof(sb2),sizeof(ub2),sizeof(ub2));
}
/*执行没有任何反应，编程成64位程序，使用64位客户端可以执行成功*/
OCIStmtExecute(svr,stmt,err,1,0,0,0,OCI_DEFAULT);
}

但如果修改分析的语句成为：

...

char sql[]="BEGIN \
    UPDATE_TEST_OCI.ADD_TEST_OCI(3,:id,:val); \
END;"

...

OCIBindByName(stmt,&idbd,err,":id",strlen(":id"),&id,sizeof(int),SQLT_INT,id_ind,id_rlen,id_rcode,id_cur,&id_cur,OCI_DEFAULT);
OCIBindArrayOfStruct(idbd,err,sizeof(int),sizeof(sb2),sizeof(ub2),sizeof(ub2));
OCIBindByName(stmt,&valbd,err,":val",strlen(":val"),&val,sizeof(val[0]),SQLT_STR,val_ind,val_rlen,val_rcode,id_cur,&id_cur,OCI_DEFAULT);
OCIBindArrayOfStruct(valbd,err,sizeof(val[0]),sizeof(sb2),sizeof(ub2),sizeof(ub2));

绑定:id,:val占位符，32位客户端则执行成功。


如果使用v7版本的OCI操作，无论那种情况都会成功执行。

转载于:https://my.oschina.net/u/1160717/blog/134303