0
回答
OCI V9编程 在执行PL/SQL块时,不能同时绑定标量变量和数组变量,求大神解答
华为云4核8G,高性能云服务器,免费试用   

环境:

    1.ORACLE环境,WIN7下的32位客户端和Ubuntu下32位的服务器

    2.编译环境为WIN7的VS2010

    3.当使用WIN7下的64位客户端且编译成64位的程序,不会此问题。

/*使用的SQL语句

SQL:

  • create table test_oci(id number,val varchar2(200));

  • 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;

  • 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));

}

/*执行没有任何反应*/

OCIStmtExecute(svr,stmt,err,1,0,0,0,OCI_DEFAULT);

}


/*如果将分析的SQL改变,却可以成功*/

...

{


OCISvcCtx *svr;

OCIStmt *stmt;

OCIError *err;

char sql[]="BEGIN \

UPDATE_TEST_OCI.ADD_TEST_OCI(3,:id,:val); \

END;";

/*分析没有问题*/

OCIStmtPrepare(stmt,err,(text*)sql,(ub4)strlen(sql),OCI_NTV_SYNTAX,OCI_DEFAULT);

{

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,&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));

}

/*执行成功*/

OCIStmtExecute(svr,stmt,err,1,0,0,0,OCI_DEFAULT);

}


/*请教给位大神,什么地方出错了,小弟感谢至极*/

举报
凯子哥
发帖于5年前 0回/443阅
顶部