CODEWITHSUNDEEP
sqloracle-databaseplsqlbulkinsert
Luke
Luke

Reputation: 1673

PL/SQL bulk INSERT into a table with an unknown structure

Can you issue a FORALL bulk INSERT into a table with an unknown structure? That means, can you build dynamically the INSERT command in the FORALL construct without knowing the number of fields at compile time?

Number and name of fields is retrieved at runtime and stored in a collection:

TYPE RowType is TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
TYPE TableType is TABLE OF RowType;
my_table TableType;

So at runtime my_table could be filled this way for example:

my_table(1)('FIELD1') = 'VALUE1A';
my_table(1)('FIELD2') = 'VALUE2A';
my_table(1)('FIELD3') = 'VALUE3A';
my_table(2)('FIELD1') = 'VALUE1B';
my_table(2)('FIELD2') = 'VALUE2B';
my_table(2)('FIELD3') = 'VALUE3B';
my_table(3)('FIELD1') = 'VALUE1C';
my_table(3)('FIELD2') = 'VALUE2C';
my_table(3)('FIELD3') = 'VALUE3C';

The insert statements that should be bulk executed therefore are:

INSERT INTO TABLENAME (FIELD1,FIELD2,FIELD3) VALUES (VALUE1A,VALUE2A,VALUE3A);
INSERT INTO TABLENAME (FIELD1,FIELD2,FIELD3) VALUES (VALUE1B,VALUE2B,VALUE3B);
INSERT INTO TABLENAME (FIELD1,FIELD2,FIELD3) VALUES (VALUE1C,VALUE2C,VALUE3C);

EDIT: Do you even read the questions or you just read a couple of words in the title? The linked question asks how to bind a variable, this question asks how to bulk issue dynamic statements. Yes, there are the words 'insert' and 'table' in both questions.

Upvotes: 1

Views: 297

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

No, you can't dynamically build and execute a FORALL...INSERT... statement dynamically. You can, however, build up an INSERT statement dynamically of the form:

INSERT ALL
  INTO TABLENAME (FIELD1,FIELD2,FIELD3) VALUES (VALUE1A,VALUE2A,VALUE3A)
  INTO TABLENAME (FIELD1,FIELD2,FIELD3) VALUES (VALUE1B,VALUE2B,VALUE3B)
  INTO TABLENAME (FIELD1,FIELD2,FIELD3) VALUES (VALUE1C,VALUE2C,VALUE3C)

Or if the data you want to insert into your table resides in another table you might find an INSERT...(subquery) statement like

INSERT INTO TABLENAME
  SELECT FIELD1, FIELD2, FIELD3
    FROM OTHER_TABLE
    WHERE something <> something_else

or you might be able to use a MERGE statement similar to

MERGE INTO TABLENAME t
  USING (SELECT FIELD1, FIELD2, FIELD3 FROM OTHER_TABLE) o
    ON (t.FIELD1 = o.FIELD1)
  WHEN NOT FOUND THEN
    INSERT (FIELD1, FIELD2, FIELD3) VALUES (o.FIELD1, o.FIELD2, o.FIELD3)

which will do a mass insert based on the data specified in the USING clause and the match criteria in the ON predicate.

So there may be ways to do what you want but without knowing the specifics of the source of your data and how you're manipulating that data prior to inserting it into your database it's tough to say whether or not any of them would apply.

Best of luck.

Upvotes: 2

Related Questions