通过OCI调用Oracle存储过程，并在C++中使用out-ref游标返回结果

Question

我想使用OCI接口从C++调用Oracle存储过程，并使用out SYS_REF_CURSOR作为过程的参数来迭代结果。我是OCI的新手，所以可能会错过一些简单的东西。此代码的大部分内容取自此处：https://community.oracle.com/thread/507765?start=0&tstart=0

我的存储过程是：

CREATE OR REPLACE PROCEDURE FXT_TEST_CALL(CRESULTS OUT SYS_REFCURSOR) IS
sTESTQUERY VARCHAR2(4000);
BEGIN
sTESTQUERY := ' SELECT set_nam, cc_type from  fxt_con_rules';
OPEN CRESULTS FOR sTESTQUERY;
END 
FXT_TEST_CALL;

我的c++代码片段是：

OCIError* pOciError;
int answer;
OCIStmt* pOciStatement;
char* sqlCharArray = "BEGIN FXT_TEST_CALL; END;";
char set_nam[40];
char cc_type[40];
OCIEnv* g_pOciEnvironment = NULL;
OCIServer* g_pOciServer = NULL;
OCISession* g_pOciSession = NULL;
OCISvcCtx* g_pOciServiceContext = NULL;
sb2* pIndicator=0;
sb2* pIndicator2=0;
sb2* pIndicator3=0;
OCIDefine* pOciDefine;
OCIDefine* pOciDefine2;
OCIBind* pBind;
OCIStmt* cursor;
answer = OCIHandleAlloc(g_pOciEnvironment, (void **)(&pOciStatement), OCI_HTYPE_STMT, 0, NULL);
answer = OCIStmtPrepare(pOciStatement, pOciError, (unsigned char *)sqlCharArray, strlen(sqlCharArray),OCI_NTV_SYNTAX, OCI_DEFAULT);
answer = OCIHandleAlloc(g_pOciEnvironment, (void **)(&cursor), OCI_HTYPE_STMT, 0, NULL);
// I get an error "ORA-01036: illegal variable name/number" after this line
answer = OCIBindByPos(pOciStatement,&pBind, pOciError, 1, &cursor, 0,SQLT_RSET, pIndicator2, 0,NULL, 0,0,OCI_DEFAULT);
answer = OCIStmtExecute(g_pOciServiceContext, pOciStatement, pOciError, 1, 0, NULL, NULL, OCI_COMMIT_ON_SUCCESS);
answer = OCIDefineByPos(cursor,&pOciDefine, pOciError,1,set_nam,40, SQLT_STR,pIndicator, 0, 0,OCI_DEFAULT);
answer = OCIDefineByPos(cursor,&pOciDefine2, pOciError,2,cc_type,40, SQLT_STR,pIndicator3, 0, 0,OCI_DEFAULT);
// loop for debug to see if set_nam and cc_type are being populated
int blah = 0;
while ((answer = OCIStmtFetch(cursor,pOciError, 1,OCI_FETCH_NEXT,OCI_DEFAULT)) == 0)
{
    blah++;
}

存储过程将很难更改，所以更愿意修改我的代码。Oracle版本为：Oracle Database 11g Enterprise Edition 11.2.0.4.0版提前谢谢。

Answer 1

您已设置：

sqlCharArray = "BEGIN FXT_TEST_CALL; END;"

它没有绑定参数；您正试图设置一个，因此出现错误，而过程定义需要一个。您需要将其称为：

sqlCharArray = "BEGIN FXT_TEST_CALL(:1); END;"