[SOLVED] Insert array / nested table into table

Insert array / nested table into table

I really apologise if answer already was given, but I could only find answers for php.

My problem is that I got nested table array "test_nested_table" that got values ('a','b','c'). I also got table "test_table" in the DB that got three columns col1, col2, col3.

All I want to do is something like

insert into test_table values (test_nested_table);

I understand I can do that:

insert into test_table values (test_nested_table(1), test_nested_table(2), test_nested_table(3));

However, my actual real life table might be very big and I would be very surprised if I really need to type all 100 elements to insert.

Solution

I have come up with the following solution:

declare
    type test_array is varray(3) of varchar2(10);
    ta test_array := test_array ('a','b','c');
    sql_txt varchar2(1000) := 'insert into test_table_1 values (''';
begin
    for n in 1 .. ta.count loop
    sql_txt := sql_txt || ta(n)||''',''';
    end loop;
    sql_txt := rtrim(sql_txt,',''')||''')';
    execute immediate sql_txt;
end;
/

So, if you have an array or any other collection type, you can use dynamic approach to insert a lot of values without writing them all in the insert statement; however, I believe there still should be a more usual way to do that.