Saturday, May 26, 2012

How to Query a Text File In Oracle

With Oracle external tables, we can run SQL queries against text files without physically loading them into the database. To create a external table, simply specify the "ORGANIZATION EXTERNAL" parameters after "Create table". Once it is created, you can query the text file like a regular table.

 CREATE TABLE "BDM"."TBL_TRADE_EXT" (
 "SYMBOL" VARCHAR2(32),
 "DT" VARCHAR2(32),
 "OPEN" NUMBER, "HIGH" NUMBER,
 "LOW" NUMBER, "CLOSE" NUMBER,
 "ADJ_CLOSE" NUMBER )
 ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER DEFAULT DIRECTORY "DIR_MKT_DATA"
 ACCESS PARAMETERS ( records delimited by '\n' SKIP 1 fields terminated by "," )
 LOCATION ( 'all.csv' ) )

1 comment:

Ananth Natarajan (Anand) said...

Critical point to note: The file has to be present in the Oracle server machine, in one of the UTL_FILE directories, or any "Directory" object explicitly created and named in the external table definition.