Another use for INSERT ALL syntax

Over on the Pythian blog Babette Turner-Underwood discusses Oracle's multitable insert syntax. She's been using in on a data conversion project. The advantage of INSERT ALL is that it allows us to insert several different VALUES clauses in a single statement. The documentation shows two uses for multitable inserts:

• transforming a single row of data from one table into several rows of data in a another table;
  
• putting data from a single row into one or more tables, depending upon the values of certain columns (a poor man's partitioining).
  

And, as Babette notes, in data migration we sometimes need to split columns from one old table into more than one new table.

None of these uses are what we might call everyday. So Babette is correct to describe the multitable insert as "little known". However, there is one use for the syntax, which is slightly arcane but is a much more common situation: creating referenced records for foreign keys on the fly. Usually we would expect to have our primary key records before we create child records because they exist as entities in their own right: Customers, Products, etc. But there are others which are only brought into existence by the creation of child records. For instance, the canonical implementation of an order process requires two tables - Order_Headers and Order_Lines. Order_Headers really just acts as an intersection table between Customers and Order_Lines: we only need an Order Header record when a customer places an order for a specific item.

One way of handling this is to have foreign keys which are DEFERRABLE INITIALLY DEFERRED. This means that the foreign key is not enforced until the end of the transaction. So we can insert a Order_Lines record without having a Order_Headers record. This is only a temporary suspension: eventually we will need to create that parent record. Besides, this violation of relational theory will disappoint Hugh Darwen. It would be nice to be able to create the parent record at the moment when we create the child record, so we can enforce the foreign key immediately. Which is where the multitable insert comes in.

Here is a simple orders implementation, with an IMMEDIATE foreign key:

SQL> desc order_headers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 STATUS                                    NOT NULL VARCHAR2(3)

SQL> desc order_lines
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER
 LINE_ID                                   NOT NULL NUMBER
 PROD_ID                                   NOT NULL NUMBER
 QTY                                       NOT NULL NUMBER
 CREATED                                   NOT NULL DATE
 STATUS                                    NOT NULL VARCHAR2(3)
 SHIPPED                                            DATE

SQL> insert into order_lines (order_id, line_id, prod_id, qty)
  2      values (63, ordl_id.nextval, 5000, 2)
  3  /
insert into order_lines (order_id, line_id, prod_id, qty)
*
ERROR at line 1:
ORA-02291: integrity constraint (A.ORDL_ORD_FK) violated - parent key not found


SQL> 

.
What we want to be able to do is use the creation of the first order line to create the order header record too. The multitable syntax has several limitations. The two which are relevant here are: it must be driven off a subquery and we cannot use the RETURNING clause to retrieve the values of any sequence used in the insertion.

SQL> create or replace procedure new_order_line  (
  2      p_cust_id in customers.cust_id%type
  3        , p_prod_id in products.prod_id%type
  4        , p_qty in order_lines.qty%type
  5        , p_ord_id in out order_headers.order_id%type
  6        )
  7  is
  8      l_ord_id order_headers.order_id%type;
  9  begin
 10      if p_ord_id is null then
 11          select ord_id.nextval into l_ord_id from dual;
 12      else
 13          l_ord_id := p_ord_id;
 14      end if;
 15      
 16      insert all
 17        when p_ord_id is null then
 18           into order_headers (order_id, cust_id)
 19           values (new_ord_id, new_cust_id)
 20        when 1=1 then
 21           into order_lines (order_id, line_id, prod_id, qty)
 22           values (new_ord_id, ordl_id.nextval, new_prod_id, new_qty)
 23      select p_cust_id as new_cust_id
 24             , p_prod_id as new_prod_id
 25             , p_qty as new_qty
 26             , l_ord_id as new_ord_id
 27      from dual;
 28      
 29      if p_ord_id is null then
 30          p_ord_id := l_ord_id;
 31      end if;
 32  end new_order_line ;
 33  /

Procedure created.

SQL> 

I admit this is a slighty clunky procedure. My orginal implementation required only one IF statement, but had a single table INSERT statement for Order_Lines as well as the multitable statement. I'm sure if I had more time I could polish this code more. Anyway, let's call this procedure twice:

SQL> declare
  2    n number;
  3  begin
  4    new_order_line(1000, 5000, 2, n);
  5    new_order_line(1000, 5002, 1, n);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from order_headers
  2  /
  ORDER_ID    CUST_ID STA
---------- ---------- ---
     80002       1000 NEW

SQL> select * from order_lines
  2  /
  ORDER_ID    LINE_ID    PROD_ID        QTY CREATED   STA SHIPPED
---------- ---------- ---------- ---------- --------- --- ---------
     80002     250510       5000          2 30-APR-07 NEW
     80002     250511       5002          1 30-APR-07 NEW

SQL>

Of course I could have impelmented this as two separate single table inserts, which would have avoided the some of duplication I mentioned. However there is another advantage to the multitable version. It is a single statement, so if any part of it fails, the whole thing fails:

SQL> declare
  2    n number;
  3  begin
  4    new_order_line(1000, 5001, null, n);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("A"."ORDER_LINES"."QTY")
ORA-06512: at "A.NEW_ORDER_LINE", line 13
ORA-06512: at line 4


SQL> 
SQL> select * from order_headers
  2  /

no rows selected

SQL> 

This is neater because the logic of the business model is that we do not want an order header if there are no order lines for it.

We still don't have a constraint syntax which allows us to enforce parentage. And there is still no sign of a constraint which will allow us to enforce arcs. But there is always hope.