Is it possible to use 'INSERT INTO SELECT' with dynamic query stored in variable?

Question

please advise or give a hint:

secenario:

language: PL/SQL ORACLE
part of procedure
perform "INSERT INTO ( SELECT ... )'
SELECT query is in variable with additional bindings in WHERE clause
the code is part of procedure

Origial working code with cursor:

 OPEN ret_cursor FOR v_query using v_1, v_2, v_3;

LOOP
    FETCH ret_cursor  INTO   v_text1, v_number1;
    EXIT WHEN ret_cursor %NOTFOUND;
    --DBMS_OUTPUT.PUT_LINE('Name' || v_text1  );
    --DBMS_OUTPUT.PUT_LINE('Value' || to_char( round ( v_number1, 2 ), 'FM99990.00') );
END LOOP;

Now I would like to achieve the same but insert the data to table in kind of this

INSERT INTO TBL_NAME
(
    text1, val1
)
v_query using using v_1, v_2, v_3;

v_query: holds select query with biding in where clause to v_1, v_2, v_3

my problem is how to exexute the binding + query that the command INSERT INTO works .....

Thank You very much for help. Appreciated.

Alex Poole · Accepted Answer · 2022-06-06 15:53:02Z

2

You can make the whole insert dynamic, by concatenating your variable onto your fixed insert into ... part, and running the combined string; something like:

execute immediate 'INSERT INTO TBL_NAME (text1, val1) ' || v_query
  using v_1, v_2, v_3;

Of course, you can put the fixed part and/or the combined string into their own variables, which might make it clearer and make debugging easier.

answered Jun 6, 2022 at 15:53

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

Collectives™ on Stack Overflow

Is it possible to use 'INSERT INTO SELECT' with dynamic query stored in variable?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related