Can I use INSERT ALL with multiple recordtype variables?

Question

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?

@KedarLimaye I'm not entirely sure how that thread helps in this case. It doesn't appear to be using INSERT ALL and is dealing with cursors, which is not what I'm working with here. — julealgon
@KedarLimaye In this particular case, I just wanted to make my script as short and performant as possible so I went straight for it assuming the API would be orthogonal with the normal INSERT. — julealgon

Popeye Popeye · Accepted Answer · 2019-07-11T07:57:13

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

Can I use INSERT ALL with multiple recordtype variables?

1 Answers