Oracle10g External Tables

Oracle10g External FilesAn introduction to what is External Tables?3/4/2010For a Better Future1Introduction To have a better understanding of how External Table working in database. By Kai Liu, Sr. Oracle Developer Kaitech, Inc.3/4/2010For a Better Future2Topics of Discussion History of External File usage? Reading External Tables? Writing External Tables?3/4/2010For a Better Future3History of External File usage?Previously the main method of getting external data into the databaseUsually a disjointed process– File is put into a directory– Loader is run to load into temporary table– Validation is done on loaded rowsProblems?– Data File not found– Bad Format – rows not loaded– Have to load into character fields then validate to avoid bad formats– Separate steps mean coordination if first step fails or does not load all3/4/2010For a Better Future4Control Fileoptions (errors=200,direct=true)load datareplaceinto table TEMP_DAILY_ORDER_HEADERSWHEN REC_TYPE = ‘OH’(CUST_ID POSITION(001:008) INTEGER EXTERNAL,ORDER_ID POSITION(009:016) INTEGER EXTERNAL,ORDER_DATE POSITION(017:024) DATE FORMAT ‘YYYYMMDD’,NUM_DETAILS POSITION(025:030) INTEGER EXTERNAL,REC_TYPE POSITION(079:080) CHAR)into table TEMP_DAILY_ORDER_DETAILSWHEN REC_TYPE = ‘OD’(CUST_ID POSITION(001:008) INTEGER EXTERNAL,ORDER_ID POSITION(009:016) INTEGER EXTERNAL,LINE_NUM POSITION(017:020) INTEGER EXTERNAL,PRODUCT_ID POSITION(021:028) CHAR,QUANTITY POSITION(029:033) INTEGER EXTERNAL,PRICE POSITION(034:041) DECIMAL EXTERNAL,AMOUNT POSITION(042:050) DECIMAL EXTERNAL,DISCOUNT POSITION(051:057) DECIMAL EXTERNAL,NET_AMOUNT POSITION(058:066) DECIMAL EXTERNAL,REC_TYPE POSITION(079:080) CHAR)3/4/2010For a Better Future5External TableReading External Tables?Started with Oracle 9i• External Files referenced directly from the database, as if they were tables• Uses ORACLE_LOADER driver• Define the file Directories• Tables are created with basic file format• SELECT used as if file is another table• Cannot INSERT, UPDATE or DELETELet’s Demo3/4/2010For a Better Future7Define DirectoryCreate directory where external data files are located– files must be in this areacreate directory external_dir as 'c:\data\oracle\extapp';create directory external_bad_dir as 'c:\data\oracle\extapp';Example in Unix: DROP DIRECTORY KMSDATA;CREATE OR REPLACE DIRECTORY KMSDATA AS '/home/oracle/data';3/4/2010For a Better Future8Create the Table DefinitionsCreate table TEMP_PUBLICITY_NAME (ACCT_NBR VARCHAR2(8),PUBLICITY_NAME1 VARCHAR2(30),PUBLICITY_NAME2 VARCHAR2(30))ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY dmsdataACCESS PARAMETERS(RECORDS DELIMITED BY newlineBADFILE 'publname.bad'DISCARDFILE 'publname.dis'LOGFILE 'publname.log'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '~'(ACCT_NBR CHAR,PUBLICITY_NAME1 CHAR,PUBLICITY_NAME2 CHAR))LOCATION ('PUBLNAME.txt'))REJECT LIMIT UNLIMITED;/3/4/2010For a Better Future9Sample Data File~00002136~,~JANE DOE~,~AND PAPER INCORPORATED~~00017012~,~JOHN AND MARY~,~~~00017013~,~JOHN AND MARY~,~~~00043376~,~MRS MILLS~,~DO NOT ASK FOR PLEDGE-COVERED~~00043833~,~MRS NANCY~,~DO NOT ASK FOR PLEDGE-COVERED~~00051155~,~MRS JAMES~,~DO NOT ASK FOR PLEDGE-COVERED~~00051812~,~MRS WENDY~,~~~00057145~,~MRS CHARLES~,~DO NOT ASK FOR PLEDGE-COVERED~~00081234~,~HARRY~,~(MR STUART L PINKERT)~~06724967~,~MRS CAROL ~,~DO NOT SOLICIT EVER~~14660280~,~BRADEY FAMILY~,~(MR & MRS SMITH)~3/4/2010For a Better Future10