Re: Returning cursor from PL/SQL function using DBMS

Boffo Jinko wrote:

> I am new to DBMS, and I need to return a cursor that is built using the
> dynamic SQL I will create inside the procedure. Here is a simplified version
> of the procedure - the final will be more complex, justifying the use of
> dynamic SQL:
>
> --------
>
> PROCEDURE FindOrgLinks
> (
> pLinkKey in number,
> pLinkType in number,
> pCursor in out genrefcursor -- defined elsewhere
> ) IS
> vSelectString varchar2(500);
> vCursorId number;
> vNumRows number;
> vOrgPk number;
> BEGIN
> vCursorId := DBMS_SQL.OPEN_CURSOR;
>
> vSelectString := 'select org_pk,name,div_type
> from organizations where org_pk < 10';
>
> DBMS_SQL.PARSE(vCursorId,vSelectString,DBMS_SQL.NATIVE);
>
> DBMS_SQL.DEFINE_COLUMN(vCursorId,1,vOrgPk);
>
> vNumRows := DBMS_SQL.EXECUTE(vCursorId);
>
> loop
> if DBMS_SQL.FETCH_ROWS(vCursorId) = 0 then
> exit;
> end if;
> DBMS_SQL.COLUMN_VALUE(vCursorId,1,vOrgPk);
> end loop;
>
> DBMS_SQL.CLOSE_CURSOR(vCursorId);
>
> return;
> END FindOrgLinks;
>
> ----------
>
> So, how do I assign the cursor variable pCursor to the cursor that was
> opened by the DBMS statements?
>
> Thank you for your help,
> Scott

Go to http://tahiti.oracle.com and look up the OPEN FOR syntax for native dynamic SQL as in the following example:

CREATE OR REPLACE PROCEDURE child (
  p_NumRecs IN PLS_INTEGER,
  p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
   OPEN p_return_cur FOR
   'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;

END child;
/

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)