Why does running this query with EXECUTE IMMEDIATE cause it to fail?
16,635
Try to lose the ";" from inside the string that you Execute Immediate.
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ')';
Author by
GameFreak
Updated on June 21, 2022Comments
-
GameFreak almost 2 years
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.