For more details:
External Table:
An External Table is basically a file that resides on the server side, as a regular flat file or as a data pump formatted file. The External Table is not a table itself; it is an external file with an Oracle format and its physical location. This feature first appeared back in Oracle 9i Release 1 and it was intended as a way of enhancing the ETL process by reading an external flat file as if it was a regular Oracle table. On its initial release it was only possible to create read-only External Tables, but, starting with 10g—it is possible to unload data to External Tables too.
Simple Example of Data loading using External Table:
ext_tab_dir --> Name of the Directory
xx_reports_access_rmg --> Name of the custom table
xx_access_external_tab --> Name of the external table
xx_reports_access.csv --> Name of the data file
CREATE OR REPLACE DIRECTORY ext_tab_dir AS '/u02/DEV/apps/apps_st/appl/custom_top/12.0.0/bin'
CREATE TABLE son_access_external_tab
(user_name VARCHAR2(100 BYTE),
poo_org_id NUMBER,
project_owning_org VARCHAR2(240 BYTE),
hcc_org_id NUMBER,
home_cost_center_org VARCHAR2(240 BYTE),
attribute1 VARCHAR2(100 BYTE),
attribute2 VARCHAR2(100 BYTE),
attribute3 VARCHAR2(100 BYTE),
attribute4 VARCHAR2(100 BYTE),
attribute5 VARCHAR2(100 BYTE),
attribute6 VARCHAR2(100 BYTE),
attribute7 VARCHAR2(300 BYTE),
attribute8 VARCHAR2(500 BYTE))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL)
LOCATION ('xx_reports_access.csv'))
REJECT LIMIT UNLIMITED;
SELECT * FROM xx_access_external_tab
SELECT * FROM xx_reports_access_rmg
-- Simple Insert
INSERT INTO xx_reports_access_rmg
(user_name, poo_org_id, project_owning_org, hcc_org_id,
home_cost_center_org)
(SELECT user_name, poo_org_id, project_owning_org, hcc_org_id,
home_cost_center_org
FROM xx_access_external_tab)
-- Insert with your custom logic
INSERT INTO xx_reports_access_rmg
(user_name, poo_org_id, project_owning_org, hcc_org_id,
home_cost_center_org)
(SELECT user_name, LTRIM (RTRIM (poo_org_id)),
DECODE
(ASCII (SUBSTR (project_owning_org,
(LENGTH (project_owning_org)
),
LENGTH (project_owning_org)
)
),
13, SUBSTR (project_owning_org,
1,
LENGTH (project_owning_org) - 1
),
project_owning_org
) project_owning_org,
LTRIM (RTRIM (hcc_org_id)),
DECODE
(ASCII (SUBSTR (home_cost_center_org,
(LENGTH (home_cost_center_org)
),
LENGTH (home_cost_center_org)
)
),
13, SUBSTR (home_cost_center_org,
1,
LENGTH (home_cost_center_org) - 1
),
home_cost_center_org
) home_cost_center_org
FROM xx_access_external_tab)
SQL*Loader
SQL*Loader is a high-speed data loading utility that loads data from external files into tables in an Oracle database. SQL*Loader accepts input data in a variety of formats, can perform filtering, and can load data into multiple Oracle database tables during the same load session.SQL*Loader provides three methods for loading data: Conventional Path Load, Direct Path Load, and External Table Load.
External Tables
A feature has been added to external tables that allows users to preprocess input data before it is sent to the access driver. The ability to manipulate input data with a preprocessor program results in additional loadable data formats, which greatly enhances the flexibility and processing power of external tables. The types of preprocessor programs that can be used are versatile, ranging from system commands, user-generated binaries (for example, a C program), or user-supplied shell scripts. Because the user supplies the program to preprocess the data, it can be tailored to meet the user’s specific needs. This means that the number of loadable formats is restricted only by the ability to manipulate the original data set.
Refer the other post "External Tables in Oracle Database 11g"
to be contd....
Thanks,
Rajesh
No comments:
Post a Comment