11

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
GameFreak
  • 2,881
  • 7
  • 34
  • 38
  • 1
    I hope that proc isn't being run in the normal course of events by your application... dynamic DDL is only really appropriate for admin scripts (e.g. setting up a new environment). – Jeffrey Kemp May 20 '09 at 02:34

1 Answers1

33

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

EXECUTE IMMEDIATE  'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ')';
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Petros
  • 8,862
  • 3
  • 39
  • 38