CODEWITHSUNDEEP
oracle-databaseplsqlinsertbulkinsert
Fulley
Fulley

Reputation: 73

Oracle: insert from type table

i want to insert from a type of table into a table.

Is there a way to do this with bulk? And can I change the type table content a little?

Just like here, but the other way around: How to insert data into a PL/SQL table type rather than PL/SQL table?

Upvotes: 0

Views: 710

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Assuming that you have something like

CREATE TYPE my_nested_table_type
    AS TABLE OF <<something>>;

DECLARE
  l_nt my_nested_table_type;
BEGIN
  <<something that populates l_nt>>

then the way to do a bulk insert of the data from the collection into a heap-organized table would be to use a FORALL

FORALL i in 1..l_nt.count
  INSERT INTO some_table( <<list of columns>> )
    VALUES( l_nt(i).col1, l_nt(i).col2, ... , l_nt(i).colN );

Upvotes: 1

Related Questions