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;
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;
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;
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.