Monday, March 15, 2010

Use PREPROCESSOR in External Table

External tables can be preprocessed by user-supplied preprocessor programs. By using a preprocessing program, users can use data from a file that is not in a format supported by the driver. ...

For more : http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/tables013.htm#ADMIN12898

CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER, CUST_ID NUMBER, TIME_ID DATE, CHANNEL_ID NUMBER, PROMO_ID NUMBER, QUANTITY_SOLD NUMBER,
AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2), UNIT_PRICE NUMBER(10,2))
ORGANIZATION external
(TYPE oracle_loader DEFAULT DIRECTORY data_file_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
PREPROCESSOR exec_file_dir:'gunzip' OPTIONS '-c'
BADFILE log_file_dir:'sh_sales.bad_xt'
LOGFILE log_file_dir:'sh_sales.log_xt'
FIELDS TERMINATED BY "|"
LDRTRIM ( PROD_ID, CUST_ID, TIME_ID DATE(10) "YYYY-MM-DD", CHANNEL_ID, PROMO_ID, QUANTITY_SOLD,
AMOUNT_SOLD, UNIT_COST, UNIT_PRICE))
location ('sh_sales.dat.gz') )
REJECT LIMIT UNLIMITED;