Re: Dynamic SQL Query

j.jones_at_link-hrsystems.com (Jamie Jones) wrote in news:71521e87.0308190734.31b9daf5_at_posting.google.com:

> Hi,
>
> I am having a few problems trying to execute dynamic SQL queries.
>
> The function that I have written builds up a dynamic SELECT statement.
> The function then executes the sql statement, loops through the
> returned data and in this case outputs the value to the screen.
>
> This example mocks up the dynamic creation of the sql statement for
> simplicity:
>
> -----------------------------------------
> SET SERVEROUTPUT ON;
> DECLARE SelectClause_Txt varchar2(1000);
> BEGIN
> SelectClause_Txt := 'SELECT GB_GradeID_Num FROM GradeBoundaries';
> FOR cur IN (EXECUTE IMMEDIATE SelectClause_Txt)
> LOOP
> DBMS_OUTPUT.PUT_LINE(cur.GB_GradeID_Num);
> END LOOP;
> END;
> /
> -----------------------------------------
>
> I get the following error message:
> -----------------------------------------
> ERROR at line 4:
> ORA-06550: line 4, column 21:
> PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of
> the
> following:
> . ( ) , * @ % & | = - + < / > at in is mod not range rem =>
> .. <an exponent (**)> <> or != or ~= >= <= <> and or like
> between ||
> -----------------------------------------
>
> In an attempt to prove the the fault is with the "EXECUTE IMMEDIATE"
> clause I have tried running the query as if the SELECT was static:
>
> -----------------------------------------
> SET SERVEROUTPUT ON;
> DECLARE SelectClause_Txt varchar2(1000);
> BEGIN
> FOR cur IN (SELECT GB_GradeID_Num FROM GradeBoundaries)
> LOOP
> DBMS_OUTPUT.PUT_LINE(cur.GB_GradeID_Num);
> END LOOP;
> END;
> /
> -----------------------------------------
>
> This, rather unsurprisingly, works fine. Does anybody have any idea
> how I can get this query working using a SELECT statement that is
> built up dynamically?
>
> Many Thanks,
> Jamie Jones.
>

You are trying to execute a SQl statement where a cursor variable is required. You can't do that. Received on Tue Aug 19 2003 - 12:41:00 CDT