Reputation: 3781
(Just to preempt - I know how hacky and flawed this approach is. It's just going to be a personal use thing to make a certain task easier.)
For this example, I want to create two dynamic SQL insert statements which insert the first two results from ALL_TABLES into GT_DUMMY, padded with an input value.
CREATE GLOBAL TEMPORARY TABLE GT_DUMMY
( Test_Field VARCHAR(100)
);
CREATE OR REPLACE PROCEDURE LOAD_GT_DUMMY
( Insert_String VARCHAR
)
IS
BEGIN
FOR col IN
( SELECT 'INSERT INTO GT_DUMMY VALUES(' || CHR(39) || Insert_String || Table_Name || CHR(39) || ');' AS insertStatement
FROM ALL_TABLES
WHERE ROWNUM <= 2
) LOOP
DBMS_OUTPUT.put_line(col.insertStatement);
-- Result of EXEC LOAD_GT_DUMMY('SOMETHING'); :
-- INSERT INTO GT_DUMMY VALUES('SOMETHINGDUAL');
-- INSERT INTO GT_DUMMY VALUES('SOMETHINGSYSTEM_PRIVILEGE_MAP');
-- This command fails when
EXECUTE IMMEDIATE col.insertStatement;
END LOOP;
END;
The inserts are well formed and will execute if I just run them standalone, but the EXECUTE IMMEDIATE col.insertStatement; command is not working. The procedure compiles, but when I try to run
EXEC LOAD_GT_DUMMY('SOMETHING');
I get an error
ORA-00933: SQL command not properly ended
ORA-06512: at "MY_SCHEMA.LOAD_GT_DUMMY", line 14
ORA-06512: at line 1
Any thoughts? Is my syntax off?
Best.
Upvotes: 3
Views: 178
Reputation: 29
Remove the colon in sql statement and execute it will work
FOR col IN
( SELECT 'INSERT INTO GT_DUMMY VALUES(' || CHR(39) || Insert_String || Table_Name || CHR(39) || ')' AS insertStatement
FROM ALL_TABLES
WHERE ROWNUM <= 2
) LOOP
Upvotes: 1
Reputation: 141
Try with INTO v_result
in the end of EXECUTE
statement
EXECUTE IMMEDIATE col.insertStatement INTO v_result;
P.S. don't forget to declare v_result
variable
You can check similar example down here
Upvotes: 1
Reputation: 142720
Remove the terminating semi-colon; dynamic SQL doesn't like it.
SELECT 'INSERT INTO GT_DUMMY VALU... <snip> ... || CHR(39) || ');'
^
|
here
Upvotes: 3