Re: Stored Proc with Cursor Param

From: Amogh <amogh.r_at_nospam.com>
Date: Mon, 29 May 2006 14:47:34 +0530
Message-ID: <whzeg.18$Ku2.104@news.oracle.com>

Geoff wrote:
> Well, I think I will use the obsolete calls. I say obsolete because the
> oracle docs say they are obsolete. However, I am not sure Oracle is all
> that confident with the 'new' way of handling a ref cursor. I say this
> because:
>
> 1. The documentation provided is not enough to get any example working
> using a ref cursor.
> 2. None of the example files on their companion cd use the 'new' way of
> handling cursors.
>
> If Oracle were confident of this new way of handling cursors then I think
> they would of done a better job of telling people about it.
>
> Thanks anyway.
>
> -g
>
>

Check this, works for me:

CREATE OR REPLACE PACKAGE ref_test AS

   TYPE p_cursor IS REF CURSOR;
   PROCEDURE getEmp (empc IN OUT p_cursor); END ref_test;
/
CREATE OR REPLACE PACKAGE BODY ref_test AS procedure getEmp( EMPC IN OUT p_cursor ) IS
BEGIN

     open empc for
        select * from EMP;

END;
END;
/

You will have to initialize and pass the arguments.

sword exec_refcursor(OCIEnv *envhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIError *errhp)
{

   sword status = 0;
   OCIBind *bndhp1;
   OCIStmt *stmthbnd;

   text *ref = (text *)"begin ref_test.getEmp(:1); end;";

   checkerr(errhp, OCIStmtPrepare(stmthp, errhp, ref,
                                 (ub4) strlen((const char *) ref),
                                 (ub4) OCI_NTV_SYNTAX, (ub4)  OCI_DEFAULT));

   checkerr(errhp,OCIHandleAlloc((void *)envhp, (void **)&stmthbnd,
                    (ub4)OCI_HTYPE_STMT, (const size_t) 0, (void **)0));

   checkerr(errhp, OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1,
                           (void *) &stmthbnd,
                           (sb4) 0, SQLT_RSET, (void *) 0, (ub2 *)0,
                           (ub2 *)0,(ub4)0,(ub4)0, (ub4) OCI_DEFAULT));

   checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                           (OCISnapshot *) NULL, (OCISnapshot *) NULL,
                           (ub4) OCI_DEFAULT));

   checkerr(errhp,display_column(stmthbnd,errhp));

   return success... ;
}

display_column(stmthp, errhp)

   OCIStmt *stmthp;
   OCIError *errhp;
{

   text *pcoln[20];
   ub4 namelen[20];

   eword i, pos;
   ub4 parmcnt = 0;
   sword retval = 0;
   OCIParam *parmdp;

   checkerr(errhp,OCIAttrGet((void *) stmthp, (ub4)OCI_HTYPE_STMT, (void *) &parmcnt,

                (ub4 *) 0, (ub4)OCI_ATTR_PARAM_COUNT, errhp));

   fprintf(stdout,"Parameter Count = %d\n", parmcnt);

   for (pos = 1; pos <= parmcnt; pos++)
   {

     retval = OCIParamGet((void *)stmthp, (ub4)OCI_HTYPE_STMT, errhp,
                       (void **)&parmdp, (ub4) pos );

     checkerr(errhp,OCIAttrGet((void*) parmdp, (ub4) OCI_DTYPE_PARAM,
                    (void*) &pcoln[pos-1],
                    (ub4 *) &namelen[pos-1], (ub4) OCI_ATTR_NAME,
                    (OCIError *) errhp));

   }

   for (i = 1; i <= parmcnt; i++)

        fprintf(stdout, "NAME = %.*s, LENGTH = %d\n ",
                              namelen[i-1],pcoln[i-1]);

   return success...
}

Rgds.
Amogh Received on Mon May 29 2006 - 04:17:34 CDT