Returning cursor from PL/SQL function using DBMS

From: Boffo Jinko <srxyz2003_at_go.com>
Date: Thu, 20 Nov 2003 14:42:28 -0500
Message-ID: <bpj5f6$1lk20v$1@ID-147295.news.uni-berlin.de>

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 Received on Thu Nov 20 2003 - 13:42:28 CST