Multi-table insert using pipelined function

Thanks to Adrian Billington's work. This article is an extraction of:

http://www.oracle-developer.net/display.php?id=429

one source, two targets

Consider an example of loading customers and addresses from a single file delivery. Let's imagine that a single customer record has up to three addresses stored in his or her history. This means that as many as four records are generated for each customer. For example:

CUSTOMER_ID LAST_NAME  ADDRESS_ID STREET_ADDRESS                 PRIMARY

----------- ---------- ---------- ------------------------------ -------

       1060 Kelley          60455 7310 Breathing Street          Y

       1060 Kelley         119885 7310 Breathing Street          N

     103317 Anderson        65045 57 Aguadilla Drive             Y

     103317 Anderson        65518 117 North Union Avenue         N

     103317 Anderson        61112 27 South Las Vegas Boulevard   N

I have removed most of the detail, but this example shows that Kelley has two addresses in the system and Anderson has three. My loading scenario is that I need to add a single record per customer to the customers table, and all of the address records need to be inserted into the addresses table.

Using object-relational features

It is the most elegant solution to this requirement. I first need to create four types to describe my data:

An object “supertype” to head the type hierarchy. This will contain only the attributes that the subtypes need to inherit. In my case, this will be just the customer_id.

A collection type of this supertype. I will use this as the return type for my pipelined function.

A customer object “subtype” with the remaining attributes required for the customers table load.

An address object “subtype” with the remaining attributes required for the addresses table load.

I've picked a small number of attributes for demonstration purposes. My types look like this:

/* File on web: multitype_setup.sql */

-- Supertype...

CREATE TYPE customer_ot AS OBJECT

( customer_id NUMBER

) NOT FINAL;

-- Collection of supertype...

CREATE TYPE customer_ntt AS TABLE OF customer_ot;

-- Customer detail subtype...

CREATE TYPE customer_detail_ot UNDER customer_ot

( first_name VARCHAR2(20)

, last_name  VARCHAR2(60)

, birth_date DATE

) FINAL;

-- Address detail subtype...

CREATE TYPE address_detail_ot UNDER customer_ot

( address_id     NUMBER

, primary        VARCHAR2(1)

, street_address VARCHAR2(40)

, postal_code    VARCHAR2(10)

) FINAL;

If you have never worked with object types, I suggest that you review the contents of Chapter 26. Briefly, however, Oracle's support for substitutability means that I can create rows of either customer_detail_ot or address_detail_ot, and use them wherever the customer_ot supertype is expected. So if I create a pipelined function to pipe a collection of the supertype, this means that I can also pipe rows of either of the subtypes. This is but one example of how an object-oriented type hierarchy can offer a simple and elegant solution.

a multitype pipelined function

Let's take a look at the pipelined function body, and then I'll explain the key concepts.

/* File on web: multitype_setup.sql */

  1     FUNCTION customer_transform_multi(

  2              p_source     IN customer_staging_rct,

  3              p_limit_size IN PLS_INTEGER DEFAULT customer_pkg.c_default_limit

  4              )

  5        RETURN customer_ntt

  6        PIPELINED

  7        PARALLEL_ENABLE (PARTITION p_source BY HASH(customer_id))

  8        ORDER p_source BY (customer_id, address_id) IS

  9  

 10        aa_source     customer_staging_aat;

 11        v_customer_id customer_staging.customer_id%TYPE := -1; /* Needs a non-null default */

 12  

 13     BEGIN

 14        LOOP

 15           FETCH p_source BULK COLLECT INTO aa_source LIMIT p_limit_size;

 16           EXIT WHEN aa_source.COUNT = 0;

 17  

 18           FOR i IN 1 .. aa_source.COUNT LOOP

 19  

 20              /* Only pipe the first instance of the customer details... */

 21              IF aa_source(i).customer_id != v_customer_id THEN

 22                 PIPE ROW ( customer_detail_ot( aa_source(i).customer_id,

 23                                                aa_source(i).first_name,

 24                                                aa_source(i).last_name,

 25                                                aa_source(i).birth_date ));

 26              END IF;

 27  

 28              PIPE ROW( address_detail_ot( aa_source(i).customer_id,

 29                                           aa_source(i).address_id,

 30                                           aa_source(i).primary,

 31                                           aa_source(i).street_address,

 32                                           aa_source(i).postal_code ));

 33  

 34              /* Save customer ID for "control break" logic... */

 35              v_customer_id := aa_source(i).customer_id;

 36  

 37           END LOOP;

 38        END LOOP;

 39        CLOSE p_source;

 40        RETURN;

 41     END customer_transform_multi;

This function is parallel-enabled, and it processes the source data in arrays for maximum performance. The main concepts specific to multityping are:

Line(s) Description

5 My function's return is a collection of the customer supertype. This allows me to pipe subtypes instead.

7-8 I have data dependencies so have used hash partitioning with ordered streaming. I need to process each customer's records together, because I will need to pick off the customer attributes from the first record only, and then allow all addresses through.

21-26 If this is the first source record for a particular customer, pipe out a row of CUSTOMER_DETAIL_OT. Only one customer details record will be piped per customer.

28-32 For every source record, pick out the address information and pipe out a row of ADDRESS_DETAIL_OT.

querying a multitype pipelined function

I now have a single function generating rows of two different types and structures. Using SQL*Plus, let's query a few rows from this function.

/* File on web: multitype_query.sql */

SQL> SELECT *

  2  FROM   TABLE(

  3            customer_pkg.customer_transform_multi(

  4               CURSOR( SELECT * FROM customer_staging ) ) ) nt

  5  WHERE  ROWNUM <= 5;

CUSTOMER_ID

-----------

          1

          1

          1

          1

          2

That's a surprise - where's my data? Even though I used SELECT *, I have only the CUSTOMER_ID column in my results. The reason for this is simple: my function is defined to return a collection of the customer_ot supertype, which has only one attribute. So unless I code explicitly for the range of subtypes being returned from my function, the database will not expose any of their attributes. In fact, if I reference any of the subtypes’ attributes using the above query format, the database will raise an ORA-00904: invalid identifier exception.

Fortunately, Oracle supplies two ways to access instances of object types: the VALUE function and the OBJECT_VALUE pseudo-column. Let's see what they do (they are interchangeable):

/* File on web: multitype_query.sql */

SQL> SELECT VALUE(nt) AS object_instance --could use “nt.OBJECT_VALUE” instead

  2  FROM   TABLE(

  3            customer_pkg.customer_transform_multi(

  4               CURSOR( SELECT * FROM customer_staging ) ) ) nt

  5  WHERE  ROWNUM <= 5;

OBJECT_INSTANCE(CUSTOMER_ID)

---------------------------------------------------------------------------

CUSTOMER_DETAIL_OT(1, 'Abigail', 'Kessel', '31/03/1949')

ADDRESS_DETAIL_OT(1, 12135, 'N', '37 North Coshocton Street', '78247')

ADDRESS_DETAIL_OT(1, 12136, 'N', '47 East Sagadahoc Road', '90285')

ADDRESS_DETAIL_OT(1, 12156, 'Y', '7 South 3rd Circle', '30828')

CUSTOMER_DETAIL_OT(2, 'Anne', 'KOCH', '23/09/1949')

This is more promising. I now have the data as it is returned from the pipelined function, so I'm going to do two things with it. First I will determine the type of each record using the IS OF condition; this will be useful to me later on. Second, I will use the TREAT function to downcast each record to its underlying subtype (until I do this, the database thinks that my data is of the supertype and so will not allow me access to any of the attributes). The query now looks something like this:

/* File on web: multitype_query.sql */

SQL> SELECT CASE

  2            WHEN VALUE(nt) IS OF TYPE (customer_detail_ot)

  3            THEN 'C'

  4            ELSE 'A'

  5         END                                    AS record_type

  6  ,      TREAT(VALUE(nt) AS customer_detail_ot) AS cust_rec

  7  ,      TREAT(VALUE(nt) AS address_detail_ot)  AS addr_rec

  8  FROM   TABLE(

  9            customer_pkg.customer_transform_multi(

 10               CURSOR( SELECT * FROM customer_staging ) ) ) nt

 11  WHERE  ROWNUM <= 5;

RECORD_TYPE CUST_REC                       ADDR_REC

----------- ------------------------------ ------------------------------

C           CUSTOMER_DETAIL_OT(1, 'Abigail

            ', 'Kessel', '31/03/1949')

A                                          ADDRESS_DETAIL_OT(1, 12135, 'N

                                           ', '37 North Coshocton Street'

                                           , '78247')

A                                          ADDRESS_DETAIL_OT(1, 12136, 'N

                                           ', '47 East Sagadahoc Road', '

                                           90285')

A                                          ADDRESS_DETAIL_OT(1, 12156, 'Y

                                           ', '7 South 3rd Circle', '3082

                                           8')

C           CUSTOMER_DETAIL_OT(2, 'Anne',

            'KOCH', '23/09/1949')

I now have my data in the correct subtype format, which means that I can access the underlying attributes. I do this by wrapping the previous query in an in-line view and accessing the attributes using dot notation, as follows.

/* File on web: multitype_query.sql */

SELECT ilv.record_type

,      NVL(ilv.cust_rec.customer_id,

           ilv.addr_rec.customer_id) AS customer_id

,      ilv.cust_rec.first_name       AS first_name

,      ilv.cust_rec.last_name        AS last_name

       

,      ilv.addr_rec.postal_code      AS postal_code

FROM  (

       SELECT CASE...

             

       FROM   TABLE(

                 customer_pkg.customer_transform_multi(

                     CURSOR( SELECT * FROM customer_staging ) ) ) nt

       ) ilv;

loading multiple tables from a multi-type pipelined function

I've removed some lines from the example above, but you should recognize the pattern. I now have all the elements needed for a multitable insert into my customers and addresses tables. Here's the loading code:

/* File on web: multitype_setup.sql */

      INSERT FIRST

         WHEN record_type = 'C'

         THEN

            INTO customers

            VALUES (customer_id, first_name, last_name, birth_date)

         WHEN record_type = 'A'

         THEN

            INTO addresses

            VALUES (address_id, customer_id, primary, street_address, postal_code)

      SELECT ilv.record_type

      ,      NVL(ilv.cust_rec.customer_id,

                 ilv.addr_rec.customer_id) AS customer_id

      ,      ilv.cust_rec.first_name       AS first_name

      ,      ilv.cust_rec.last_name        AS last_name

      ,      ilv.cust_rec.birth_date       AS birth_date

      ,      ilv.addr_rec.address_id       AS address_id

      ,      ilv.addr_rec.primary          AS primary

      ,      ilv.addr_rec.street_address   AS street_address

      ,      ilv.addr_rec.postal_code      AS postal_code

      FROM (

            SELECT CASE

                      WHEN VALUE(nt) IS OF TYPE (customer_detail_ot)

                      THEN 'C'

                      ELSE 'A'

                   END                                    AS record_type

            ,      TREAT(VALUE(nt) AS customer_detail_ot) AS cust_rec

            ,      TREAT(VALUE(nt) AS address_detail_ot)  AS addr_rec

            FROM   TABLE(

                      customer_pkg.customer_transform_multi(

                         CURSOR( SELECT * FROM customer_staging ))) nt

           ) ilv;

With this INSERT FIRST statement, I have a complex load that uses a range of object-relational features in a way that enables me to retain set-based principles. This approach might also work for you.