Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 456
GameFreak Avatar asked May 19 '09 22:05

GameFreak


1 Answers

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

EXECUTE IMMEDIATE  'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ')';
like image 135
Petros Avatar answered Nov 14 '22 13:11

Petros