EXECUTE IMMEDIATE using column names from dual

Question

In my package, I have a procedure like this:

PROCEDURE sp_directUpdate(COL C%ROWTYPE) IS
BEGIN

   EXECUTE IMMEDIATE 'INSERT INTO T1(SELECT COL.F0, COL.F1 FROM DUAL)';

END IF;

END;

for table T1, I need only two columns from dual: COL.F0 & COL.F1.

When I execute this statement, I get "COL"."F1" is an invalid identifier.

In the same procedure, for inserting values into table T2, my statement might look like this:

 EXECUTE IMMEDIATE 'INSERT INTO T2(SELECT COL.F0, COL.F1, COL.F4 FROM 
  DUAL)';

I will run into a similar problem again. Can you suggest me a way to solve this problem without using INTO clause?

score 0 · Answer 1 · answered Apr 28 '17 at 07:55

0

Firstly, the INSERT AS SELECT syntax does not have parentheses () around the query.

If you use EXECUTE IMMEDIATE, the statement is a string executed outside the context of the procedure so it cannot refer to the parameters. You would need to supply them as bind variables, e.g.:

PROCEDURE sp_directUpdate(COL C%ROWTYPE) IS
BEGIN

   EXECUTE IMMEDIATE 'INSERT INTO T1 AS SELECT :1, :2 FROM DUAL'
     USING COL.F0, COL.F1;

END;

However, I would question whether you need to use dynamic SQL at all - you can run the insert directly:

PROCEDURE sp_directUpdate(COL C%ROWTYPE) IS
BEGIN

   INSERT INTO T1 AS SELECT COL.F0, COL.F1 FROM DUAL;

END;

In addition, in this case you could use a single row insert statement instead of running an "insert as select":

PROCEDURE sp_directUpdate(COL C%ROWTYPE) IS
BEGIN

   INSERT INTO T1 VALUES (COL.F0, COL.F1);

END;

P.S. if this is supposed to do an insert, why is the procedure called "directUpdate"? :)

answered Apr 28 '17 at 07:55

Jeffrey Kemp

59,135
14
106
158

I am actually trying to write a generic stored procedure for inserting records into 500 different tables. So, my bind variables would also need to be dynamic. For instance, I have a table T5. For T5, I need 50 different column values - INSERT INTO T5 VALUES(COL.F0,COL.F1...COL.F50). For T2, I need only 2 values. INSERT INTO T4 VALUES(COL.F0,COL.F1). Is it possible in SQL? – Neptune_Runner May 01 '17 at 21:03
It's not easy, and I would question why you want a procedure if all it's doing is being a wrapper for the built-in INSERT statement which supports arbitrary binds already. You run the risk of hitting the "inner platform effect". – Jeffrey Kemp May 01 '17 at 23:13
That's right. I am actually looking for an insert statement that is capable of inserting into any table I like. – Neptune_Runner May 02 '17 at 00:31
Yes.... that's the SQL **INSERT** statement. That's exactly what it's for. – Jeffrey Kemp May 02 '17 at 04:26

EXECUTE IMMEDIATE using column names from dual

1 Answers1