Error_2646
Error_2646

Reputation: 3781

Execute SQL from cursor value

(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

Answers (3)

siva krishna
siva krishna

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

Lasha Kitia
Lasha Kitia

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

Littlefoot
Littlefoot

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

Related Questions