How does Oracle Insert Into work when order of values is not defined?

Question

I came across some code that looks like this. I understand that it will return the auto-generated id, but what I don't understand is when I pass cursor data when I call this function, how does it identify what values are to be inserted in which columns when the column order is not defined?

FUNCTION INSERT_ROW(DATA IN OWNER.TABLE%ROWTYPE)
    RETURN OWNER.TABLE.ID%TYPE
IS 
    l_ID OWNER.MY_TABLE.ID%TYPE;
    l_Data OWNER.MY_TABLE%ROWTYPE := DATA;
BEGIN   
    INSERT INTO OWNER.MY_TABLE
    VALUES l_Data
    RETURNING ID INTO l_ID;

I tried to look up many examples and I only come across ones where the values are defined in order like this INSERT INTO my_table (val2, val3, val4) VALUES (2, 3, 4) RETURNING val1 INTO val1;

The column list for a table is ordered as defined in the dictionary. See [all_tab_columns](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/ALL_TAB_COLUMNS.html).column_id. — William Robertson, Jun 15 '18 at 22:06
Hi @JeffreyKemp I wanted to know how Insert Into Returning syntax works when a cursor is passed instead of index versus values are passed like I mentioned in the example that I mostly came across. I think BobbyDurrett and Bob answered it that the %ROWTYPE defines a record in order of column id. What do you suggest the title should be? — PKT, Jun 20 '18 at 03:12
Your RETURNING clause only has one column, so to ask how the "order of values" works makes little sense in this context. Your question appears to be about the INTO and VALUES clauses, not the RETURNING clause. — Jeffrey Kemp, Jun 20 '18 at 06:45
Makes sense made that edit, Thanks @JeffreyKemp given that this was first stack question I will do more research on creating titles. — PKT, Jun 20 '18 at 14:27

score 2 · Answer 1 · answered Jun 15 '18 at 22:56

The insert values statement in your code is a PL/SQL extension to the standard insert values clause that has parentheses. This is a page from the 12.2 manual about this topic:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/INSERT-statement-extension.html#GUID-D81224C4-06DE-4635-A850-41D29D4A8E1B

The OWNER.TABLE%ROWTYPE data type defines a record with the same columns as the table and in the same order. You are just passing the data into the function in that format and passing it into a variable and then into the insert statement.

score 2 · Accepted Answer · answered Jun 16 '18 at 04:22

The order of columns in a table in Oracle IS defined. Take a look at the ALL_TAB_COLUMNS view - there's a COLUMN_ID column which defines the order of columns within the table. If a field list is not given in a SELECT (i.e. SELECT * FROM MY_TABLE) the columns from MY_TABLE will be returned in ALL_TAB_COLUMNS.COLUMN_ID order. This is also the same way columns are ordered in a %ROWTYPE variable, and it's the way that an INSERT which doesn't have a field list specified expects fields to be ordered.

score 0 · Answer 3 · answered Jun 16 '18 at 23:45

The main purpose of the RETURNING clause is to obtain the value of a derived column, a value which is generated during the insert process. Usually this is a technical primary key derived from a sequence, or since 12c an IDENTITY column.

So for instance:

create table my_table (
     val1 number generated as identity primary key
     , val2 varchar2(16)
     , val3 varchar2(16)
     , val4 date)
/

declare
    id number;
begin
    INSERT INTO my_table (val2, val3, val4) 
    VALUES ('one', 'test', sysdate) 
    RETURNING val1 INTO id;

    dbms_output.put_line('new id = ' || id);
end;
/

This is why the examples you found specify columns in the INSERT projection: the value of the primary key is generated automatically, so there's no point in us assigning it a value in our code.

Now your function uses a record type in its insert statement. We can't do that with IDENTITY columns. This variant ...

declare
    lrec my_table%rowtype;
    id number;
begin
    lrec.val2 := 'two';
    lrec.val3 := 'test again';
    lrec.val4 :=  sysdate;

    INSERT INTO my_table
    VALUES lrec
    RETURNING val1 INTO id;

    dbms_output.put_line('new id = ' || id);
end;
/

... will hurl

ORA-32795: cannot insert into a generated always identity column

But we can use a %rowtype with the old-fashioned sequence and trigger combo:

create table my_table (
     val1 number primary key
     , val2 varchar2(16)
     , val3 varchar2(16)
     , val4 date)
/

create sequence my_seq start with 42;

create or replace trigger my_trg
    before insert on my_table for each row
begin
    :new.val1 := my_seq.nextval;
end;
/

declare
    lrec my_table%rowtype;
    id number;
begin
    lrec.val1 := 1;
    lrec.val2 := 'three';
    lrec.val3 := 'test again';
    lrec.val4 :=  sysdate;

    INSERT INTO my_table
    VALUES lrec
    RETURNING val1 INTO id;

    dbms_output.put_line('new id = ' || id);
end;
/

Here is a LiveSQL demo (free Oracle OTN account required, alas). If you run it you will see that the trigger overrides the assigned value and the val1 column has the value from the sequence.

How does Oracle Insert Into work when order of values is not defined?

3 Answers3

Linked