4 Replies Latest reply on Nov 19, 2012 12:04 PM by 792937

    Problem when retrieving information from sys_refcursor

      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.