Re: Dynamic SQL and Cursors

Jamie Jones wrote:
>
> 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

You open the cursor, then you need to *fetch* from it into a variable - which can then be outputted

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"