Dynamic SQL Query

From: Jamie Jones <j.jones_at_link-hrsystems.com>
Date: 19 Aug 2003 08:34:45 -0700
Message-ID: <71521e87.0308190734.31b9daf5@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. Received on Tue Aug 19 2003 - 10:34:45 CDT