Dynamic SQL and Cursors

From: Jamie Jones <j.jones_at_link-hrsystems.com>
Date: Thu, 21 Aug 2003 12:13:29 +0100
Message-ID: <vk9ahd47a1gl69@corp.supernews.com>

Hi,

I am having a few problems trying to execute dynamically generated SQL in an Oracle (version 9i if it is relevant) function. The simplified version of the function is as follows:

---

CREATE OR REPLACE FUNCTION GetFilterPopulation ( PRM_FilterID_Num IN NUMBER DEFAULT -1,   PRM_UserID_Num IN NUMBER DEFAULT -1)
  RETURN NULL
AS
  TYPE GenericCurTyp IS REF CURSOR;
  cur GenericCurTyp;
  SelectClause_Txt varchar2(1000);
BEGIN    SelectClause_Txt := 'SELECT UserID FROM Users'

• Execute the select and return appropriate data OPEN cur FOR ':s' USING SelectClause_Txt; LOOP DBMS_OUTPUT.PUT_LINE (cur.UserID); END LOOP; END; /

  ---

The error message that I am recieveing is "PLS-00487: Invalid reference to variable 'CUR'" which relates to this line:   DBMS_OUTPUT.PUT_LINE (cur.UserID);

Any help with where I am going wrong here would be greatly appreciated.

Thanks in advance.
Jamie Received on Thu Aug 21 2003 - 06:13:29 CDT