Login   |   Signup  
Exforsys

Oracle Database Management Utilities

  1. Oracle 10g/11g Data and Database Management Utilities
  2. Oracle Utilities - External Tables
  3. Oracle Utilities - External Tables Environment Setup
  4. Oracle Utilities - A Basic External Table
  5. Oracle Utilities - Creating External Table Metadata, The Easy Way
  6. Oracle Utilities - Unloading data to External Tables
  7. Oracle Utilities - Inter-Version Compatibility
  8. Oracle Utilities - Data Transformation with External Tables
  9. Oracle Utilities - Extending the alert.log Analysis
  10. Oracle Utilities - Reading the listener.log from the database
  11. Oracle Utilities - Mapping XML files as External Tables
  12. Oracle Utilities - Dynamically Changing the External Reference
  13. Oracle Utilities - Summary

Career Training

Technical Training

Skill Test

Ads


Home arrow Reviews arrow Oracle Database Management Utilities

Oracle Utilities - Data Transformation with External Tables

Author: Packt Publishing     Published on: 4th Jan 2010

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.

 

Ads

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
  1.  
  2. SQL> desc amount_jan
  3.  Name              Null?         Type
  4.  ----------------- --------      ------------
  5.  REGION                          VARCHAR2(16)
  6.  AMOUNT                          NUMBER(3)
  7.  
Copyright exforsys.com



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

Sample Code
  1.  
  2.    INSERT ALL
  3.       INTO AMOUNT_JAN (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  4.       INTO AMOUNT_FEB (REGION,       AMOUNT)      VALUES(COUNTRY,   FEB)
  5.       INTO AMOUNT_MAR (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  6.       INTO AMOUNT_APR (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  7.       INTO AMOUNT_MAY (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  8.       INTO AMOUNT_JUN (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  9.       INTO AMOUNT_JUL (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  10.       INTO AMOUNT_AUG (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  11.       INTO AMOUNT_SEP (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  12.       INTO AMOUNT_OCT (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  13.       INTO AMOUNT_NOV (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  14.       INTO AMOUNT_DEC (REGION,       AMOUNT)      VALUES(COUNTRY,   JAN)
  15.    SELECT     COUNTRY,
  16.       JAN,
  17.       FEB,
  18.       MAR,
  19.       APR,
  20.       MAY,
  21.       JUN,
  22.       JUL,
  23.       AUG,
  24.       SEP,
  25.       OCT,
  26.       NOV,
  27.       DEC
  28.    FROM       REGION_REVENUE
  29.  
Copyright exforsys.com


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
  1.  
  2. SQL> create table low_amount(
  3.   2 region      varchar2(16),
  4.   3 month       number(2),
  5.   4 amount      number(3))
  6. Table created.
  7. SQL> create table high_amount as select * from low_amount
  8. Table created.
  9.  
Copyright exforsys.com


Ads

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

Sample Code
  1.  
  2.     INSERT ALL
  3.             WHEN ( JAN <= 500 ) THEN
  4.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  5.                     VALUES ( COUNTRY, '01', JAN )
  6.             WHEN ( FEB <= 500 ) THEN
  7.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  8.                     VALUES ( COUNTRY, '02', FEB )
  9.             WHEN ( MAR <= 500 ) THEN
  10.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  11.                     VALUES ( COUNTRY, '03', MAR )
  12.             WHEN ( APR <= 500 ) THEN
  13.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  14.                     VALUES ( COUNTRY, '04', APR )
  15.             WHEN ( MAY <= 500 ) THEN
  16.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  17.                     VALUES ( COUNTRY, '05', MAY )
  18.             WHEN ( JUN <= 500 ) THEN
  19.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  20.                     VALUES ( COUNTRY, '06', JUN )
  21.             WHEN ( JUL <= 500 ) THEN
  22.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  23.                     VALUES ( COUNTRY, '07', JUL )
  24.             WHEN ( AUG <= 500 ) THEN
  25.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  26.                     VALUES ( COUNTRY, '08', AUG )
  27.             WHEN ( SEP <= 500 ) THEN
  28.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  29.                     VALUES ( COUNTRY, '09', SEP )
  30.             WHEN ( OCT <= 500 ) THEN
  31.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  32.                     VALUES ( COUNTRY, '10', OCT )
  33.             WHEN ( NOV <= 500 ) THEN
  34.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  35.                     VALUES ( COUNTRY, '11', NOV )
  36.             WHEN ( DEC <= 500 ) THEN
  37.                     INTO LOW_AMOUNT( REGION, MONTH, AMOUNT)
  38.                     VALUES ( COUNTRY, '12', DEC )
  39.             WHEN ( JAN > 500 ) THEN
  40.                     INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
  41.                     VALUES ( COUNTRY, '01', JAN )
  42.             WHEN ( FEB > 500 ) THEN
  43.                     INTO HIGH_AMOUNT( REGION, MONTH, AMOUNT)
  44.                     VALUES ( COUNTRY, '02', FEB )
  45.             WHEN ( MAR > 500 ) THEN
  46.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  47.                   VALUES ( COUNTRY,   '03', MAR )
  48.          WHEN ( APR > 500 ) THEN
  49.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  50.                   VALUES ( COUNTRY,   '04', APR )
  51.          WHEN ( MAY > 500 ) THEN
  52.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  53.                   VALUES ( COUNTRY,   '05', MAY )
  54.          WHEN ( JUN > 500 ) THEN
  55.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  56.                   VALUES ( COUNTRY,   '06', JUN )
  57.          WHEN ( JUL > 500 ) THEN
  58.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  59.                   VALUES ( COUNTRY,   '07', JUL )
  60.          WHEN ( AUG > 500 ) THEN
  61.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  62.                   VALUES ( COUNTRY,   '08', AUG )
  63.          WHEN ( SEP > 500 ) THEN
  64.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  65.                   VALUES ( COUNTRY,   '09', SEP )
  66.          WHEN ( OCT > 500 ) THEN
  67.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  68.                   VALUES ( COUNTRY,   '10', OCT )
  69.          WHEN ( NOV > 500 ) THEN
  70.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  71.                   VALUES ( COUNTRY,   '11', NOV )
  72.          WHEN ( DEC > 500 ) THEN
  73.                   INTO HIGH_AMOUNT(   REGION, MONTH, AMOUNT)
  74.                   VALUES ( COUNTRY,   '12', DEC )
  75. SELECT   COUNTRY,
  76.          JAN,
  77.          FEB,
  78.          MAR,
  79.          APR,
  80.          MAY,
  81.          JUN,
  82.          JUL,
  83.          AUG,
  84.          SEP,
  85.          OCT,
  86.          NOV,
  87.          DEC
  88. FROM     REGION_REVENUE
  89.  
Copyright exforsys.com




 
This tutorial is part of a Oracle Database Management Utilities tutorial series. Read it from the beginning and learn yourself.

Oracle Database Management Utilities

  1. Oracle 10g/11g Data and Database Management Utilities
  2. Oracle Utilities - External Tables
  3. Oracle Utilities - External Tables Environment Setup
  4. Oracle Utilities - A Basic External Table
  5. Oracle Utilities - Creating External Table Metadata, The Easy Way
  6. Oracle Utilities - Unloading data to External Tables
  7. Oracle Utilities - Inter-Version Compatibility
  8. Oracle Utilities - Data Transformation with External Tables
  9. Oracle Utilities - Extending the alert.log Analysis
  10. Oracle Utilities - Reading the listener.log from the database
  11. Oracle Utilities - Mapping XML files as External Tables
  12. Oracle Utilities - Dynamically Changing the External Reference
  13. Oracle Utilities - Summary
 

Comments

 

Share


Connect

Related Articles

  1. Oracle Coherence: Creating Indexes
  2. Oracle Coherence: Query Limitations
  3. Oracle Coherence: Built-in Aggregators
  4. Oracle Coherence: Implementing LookupValuesAggregator
  5. Oracle Application Express 3.2 – The Essentials and More Review
  6. Oracle Siebel CRM 8 Installation and Management Review
  7. Oracle Forms - Report Files
  8. Oracle Forms Modules
  9. Oracle Forms - PL/SQL Libraries
  10. Oracle Forms - Understanding XML
  11. Oracle Coherence: Obtaining Query Results
  12. Oracle Coherence: Controlling Query Scope Using Data Affinity
  13. Oracle Coherence: Other built-in value extractors
  14. Oracle Coherence: Querying The Data Grid
  15. Oracle Coherence: Aggregators

Exforsys e-Newsletter

Enhance Technical and Soft Skills every week, get our Job Interview Questions and Answers eBook free when you subscribe.

Subscribe and Get Free Bonus PDF Book

eBook

Subscribe

Privacy and Legal | Contact Us | DMCA | Sitemap

Copyright © 2000 - 2012 exforsys.com. All Rights Reserved