Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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)Received on Thu Nov 20 2003 - 14:32:34 CST