Execute SQL from cursor value

Question

(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.

Littlefoot · Accepted Answer · 2018-08-13 16:07:20Z

3

Remove the terminating semi-colon; dynamic SQL doesn't like it.

SELECT 'INSERT INTO GT_DUMMY VALU... <snip> ... || CHR(39) || ');' 
                                                                ^
                                                                |
                                                               here

answered Aug 13, 2018 at 16:07

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lasha Kitia · Accepted Answer · 2018-08-13 16:13:24Z

1

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

answered Aug 13, 2018 at 16:13

Lasha Kitia

1417 bronze badges

Comments

siva krishna · Accepted Answer · 2018-08-13 16:34:04Z

1

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

answered Aug 13, 2018 at 16:34

siva krishna

291 silver badge9 bronze badges

Collectives™ on Stack Overflow

Execute SQL from cursor value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related