This discussion is archived
4 Replies Latest reply: Nov 19, 2012 4:04 AM by 792937 RSS

Problem when retrieving information from sys_refcursor

792937 Newbie
Currently Being Moderated
Hi all,

When I get cursor from an sys_refcursor out parameter when calling a stored procedure, I got an "ORA-24338 statement handle not executed". The same code work well when retrieving ref cursor as return value of a stored function.

The code sample is as follows:
// prepare the statement
retStatus = OCIStmtPrepare(pCmdHandlers->m_pOCIStmt, pCmdHandlers->m_pOCIError, (text *)pSQL, (ub4)strlen(pSQL), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
CheckOCIStatus(pCmdHandlers, retStatus);

// prepare for cursor storage
retStatus = OCIHandleAlloc(pConnHandlers->m_pOCIEnv, &param.b_pvValue, OCI_HTYPE_STMT, 0, NULL);
CheckOCIStatus(pCmdHandlers, retStatus);

// Bind input parameter
retStatus = OCIBindByName(pCmdHandlers->m_pOCIStmt, (OCIBind **)&param.b_pvBind, pCmdHandlers->m_pOCIError, (text *)szCol, (sb4)-1, &param.b_pvValue, (sb4)0, (ub2)SQLT_RSET, (dvoid*) 0, (ub2 *) &param.b_pvIndicator, (ub2 *) 0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);
CheckOCIStatus(pCmdHandlers, retStatus);

// Execute the command
retStatus = OCIStmtExecute(pConnHandlers->m_pOCISvcCtx, pCmdHandlers->m_pOCIStmt, pCmdHandlers->m_pOCIError, (ub4) 1, (ub4) 0, (OCISnapshot *)NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
CheckOCIStatus(pCmdHandlers, retStatus);

// get the sys_refcursor column number and info But I got ORA-24338: statement handle not executed error
OCIStmt pStmt = (OCIStmt )param.b_pvValue;
ub4 nCol = 0;
ub4 size = 0;
retStatus = OCIAttrGet(pStmt, OCI_HTYPE_STMT, &nCol, &size, OCI_ATTR_PARAM_COUNT, pCmdHandlers->m_pOCIError);
CheckOCIStatus(pCmdHandlers, retStatus);


// information about ORA-24338 is as follows
ORA-24338: statement handle not executed
Cause:      A fetch or describe was attempted before executing a statement handle.
Action:      Execute a statement and then fetch or describe the data.

I tried to execute the procedure by pl/sql, it works fine.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points