CODEWITHSUNDEEP
oracle-databaseplsqldynamicquery
Luqpa
Luqpa

Reputation: 47

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

please advise or give a hint:

secenario:

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.

Upvotes: 0

Views: 608

Answers (1)

Alex Poole
Alex Poole

Reputation: 191425

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.

Upvotes: 2

Related Questions