Call an Oracle stored procedure via OCI and return

问题:

I would like to call an Oracle stored procedure from C++ using the OCI interface and iterate over the results using an out SYS_REF_CURSOR as a parameter to the procedure. I'm new to OCI so might be missing something simple. Most of this code is taken from here: https://community.oracle.com/thread/507765?start=0&tstart=0

My stored procedure is:

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;

And my c++ code snippet is:

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++;
}

The stored procedure will be difficult to change so prefer to amend my code. Oracle version is: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Thanks in advance.

回答1:

You've set:

sqlCharArray = "BEGIN FXT_TEST_CALL; END;"

Which has no bind parameters; you're trying to set one, hence the error, and the procedure definition requires one. You need to call it as:

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