Oracle Utilities - Data Transformation with External Tables

Category: Oracle Utilities
Comments (0)

One of the main uses of the External Tables is their support of the ETL process, allowing the user to perform a data load that is transformed to the target format without an intermediate stage table.

Let's read an External Table whose contents are:

This data can be loaded in a single command to multiple tables. Let's create several tables with the same structure:

Sample Code

SQL> desc amount_jan
Name Null? Type
----------------- -------- ------------
REGION VARCHAR2(16)
AMOUNT NUMBER(3)

Now we can issue a command to send the data from the External Table to the different tables.

Sample Code

INSERT ALL
INTO AMOUNT_JAN (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_FEB (REGION, AMOUNT) VALUES(COUNTRY, FEB)
INTO AMOUNT_MAR (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_APR (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_MAY (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_JUN (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_JUL (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_AUG (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_SEP (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_OCT (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_NOV (REGION, AMOUNT) VALUES(COUNTRY, JAN)
INTO AMOUNT_DEC (REGION, AMOUNT) VALUES(COUNTRY, JAN)
SELECT COUNTRY,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
FROM REGION_REVENUE

In this example, we will perform a conditional insert to different tables depending on the value of the amount column. We will first create three tables, one for low, another for average, and a third for high amounts:

Sample Code

SQL> create table low_amount(
2 region varchar2(16),
3 month number(2),
4 amount number(3))
Table created.
SQL> create table high_amount as select * from low_amount
Table created.

Now we can read the External Table and have the data inserted conditionally to one of three mutually exclusive targets.

Sample Code

INSERT ALL
WHEN ( JAN <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '01', JAN )
WHEN ( FEB <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '02', FEB )
WHEN ( MAR <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '03', MAR )
WHEN ( APR <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '04', APR )
WHEN ( MAY <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '05', MAY )
WHEN ( JUN <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '06', JUN )
WHEN ( JUL <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '07', JUL )
WHEN ( AUG <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '08', AUG )
WHEN ( SEP <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '09', SEP )
WHEN ( OCT <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '10', OCT )
WHEN ( NOV <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '11', NOV )
WHEN ( DEC <= 500 ) THEN
INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '12', DEC )
WHEN ( JAN > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '01', JAN )
WHEN ( FEB > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '02', FEB )
WHEN ( MAR > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '03', MAR )
WHEN ( APR > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '04', APR )
WHEN ( MAY > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '05', MAY )
WHEN ( JUN > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '06', JUN )
WHEN ( JUL > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '07', JUL )
WHEN ( AUG > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '08', AUG )
WHEN ( SEP > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '09', SEP )
WHEN ( OCT > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '10', OCT )
WHEN ( NOV > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '11', NOV )
WHEN ( DEC > 500 ) THEN
INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
VALUES ( COUNTRY, '12', DEC )
SELECT COUNTRY,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
FROM REGION_REVENUE

Comments

Subscribe via RSS

Get Daily Updates via

Sponsored Links

Oracle Utilities Tutorials

Oracle Utilities - Data Transformation with External Tables

Comments

Post Your Comment:

Sponsored Links

Subscribe via RSS