Why does running this query with EXECUTE IMMEDIATE cause it to fail?

sql oracle plsql dynamic-sql ora-00911
GameFreak picture GameFreak · May 20, 2009 · Viewed 16.3k times · Source

I am writing a PL/SQL procedure that needs to to dynamically generate some queries, one of which involves creating a temporary table using results from a query taken as a parameter.

CREATE OR REPLACE PROCEDURE sqlout(query IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE  'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ');';
END;

It compiles correctly, but even with very simple queries such as with:

BEGIN
    sqlout('SELECT * FROM DUAL');
END;

IT throws ORA-00911: invalid character. If I run the created query manually it runs correctly. At this point I am able to determine what is causing the problem.

Answer

Petros picture Petros · May 20, 2009

Try to lose the ";" from inside the string that you Execute Immediate.

EXECUTE IMMEDIATE  'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ')';

Related questions

pl/sql - to_date not working with execute immediate parameter

i wanna be able to execute my below proc like so: exec procname('29-JAN-2011'); proc code is: PROCEDURE procname(pardate VARCHAR2) IS vardate DATE := to_date(pardate, 'DD-MON-YYYY'); SQLS VARCHAR2(4000); BEGIN SQLS := 'SELECT cola, colb FROM tablea WHERE …

Read More
SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More