CODEWITHSUNDEEP
oracle-databaseplsqlbulkinsert
julealgon
julealgon

Reputation: 8162

Can I use INSERT ALL with multiple recordtype variables?

Given a PL/SQL block where I have access to 2 recordtype variables, I want to insert these 2 records into the same table in a single statement, but my attempts to use INSERT ALL have failed thus far. Is it possible to use INSERT ALL with record variables at all?

Here is some code that works, using dedicated inserts:

DECLARE
    mProperty1 MyTable%ROWTYPE;
    mProperty2 MyTable%ROWTYPE;
    ...
BEGIN
    ...
    INSERT INTO MyTable VALUES mProperty1;
    INSERT INTO MyTable VALUES mProperty2;
    ...
END;

If I try to convert the statement to a INSERT ALL though, it fails with an error message:

DECLARE
    mProperty1 MyTable%ROWTYPE;
    mProperty2 MyTable%ROWTYPE;
    ...
BEGIN
    ...
    INSERT ALL
          INTO MyTable VALUES mProperty1
          INTO MyTable VALUES mProperty2
          SELECT 1 FROM DUAL;
    ...
END;

ORA-06550: line 14, column 60: PLS-00382: expression is of wrong type ORA-06550: line 13, column 60: PLS-00382: expression is of wrong type ORA-06550: line 13, column 60: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 12, column 7: PL/SQL: SQL Statement ignored

Am I missing something obvious? Is there a way to make this statement work?

Upvotes: 4

Views: 108

Answers (1)

Popeye
Popeye

Reputation: 35900

I have tried using different methods but succeeded with the only following:

Method 1:

Using particular record names from %ROWTYPE field

DECLARE
    mProperty1 MyTable%ROWTYPE;
    mProperty2 MyTable%ROWTYPE;
BEGIN
    mProperty1.COL1 := 1;
    mProperty2.COL1 := 2;
    INSERT ALL 
    INTO  MyTable VALUES (mProperty1.col1)
    INTO  MyTable VALUES (mProperty2.col1)
    SELECT 1 FROM DUAL;
END;
/
-- may be bad choice but you can use like this

Method 2:

If you are concerned about performance then you can also use this method:

DECLARE
    TYPE mProperty_TYPE IS TABLE OF MyTable%ROWTYPE;
    mProperty mProperty_TYPE;
BEGIN
    mProperty := mProperty_TYPE();
    mProperty.EXTEND(2);
    mProperty(1).COL1 := 3;
    mProperty(2).COL1 := 4;
    FORALL I IN 1..mProperty.COUNT 
    INSERT INTO MyTable VALUES mProperty(I);
END;
/

db<>fiddle demo

Upvotes: 1

Related Questions