Re: Dynamic SQL and Cursors

Originally posted by Jamie Jones

> 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

The correct code for the OPEN is:

  OPEN cur FOR SelectClause_Txt;

The USING clause is for assigning values to bind variables, not for assigning the entire SQL statement. For example:

OPEN cur FOR 'select ename from emp where empno=:x' USING 1234;

This assigns the value 1234 to the bind variable :x used in the SQL statement.

--
Posted via http://dbforums.com