Hi,
In today’s article I want to explain how to use Oracle’s external tables in order to access flat files in the filesystem. Since Oracle 9i it’s possible to mount files in the file system and make then accessible in the database. Those files appear as normal tables within the database and can be accessed in read-only mode. You cannot write to those files or use indexes on these tables. But you can read data from it and join it with other tables.
My example file (cities.txt) looks like this:
First your administrator (or you) has to create a new directory in the file system and make it accessible within the database. Then the user who should access the external table needs to have read + write rights to this directory. The following two lines are granting both:
| CREATE OR REPLACE DIRECTORY ext_file_data AS ‚c:\‘; GRANT read, write ON DIRECTORY ext_file_data TO test; |
With these two lines the user TEST will receive read and write access to c:\.
After you’ve created the directory you can define a table with four columns. When you create the table you have to enhance the external table definition and it looks like this:
| CREATE TABLE tbl_cities_ext ( city_name VARCHAR2(25), population NUMBER, country_name VARCHAR2(25), country_code VARCHAR2(3) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_file_data ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ‚,‘ MISSING FIELD VALUES ARE NULL ( city_name CHAR(25), population CHAR(10), country_name CHAR(25), country_code CHAR(3) ) ) LOCATION (‚cities.txt‘) ) REJECT LIMIT UNLIMITED; |
You can define a lot of things in the external table definition. Normally you use the oracle loader tool but it’s also possible to use data pump. With DEFAULT DIRECTORY you define the directory in which the files are located. Then you can define several access parameters. These parameters define how the data is stored in the files. In my case records are delimited by a new line and fields are terminated by ‘,’. If field values are missing they should be null. Then we have the column list. For each column within the file we have a column in the external table definition. You could also specify fixed column length and column positions and so on. The filenames itself are put in the LOCATION. You can also put more than one filename here separated by comma. This is just a brief introduction. If you need further information on it, just google oracle external files. You find a lot of tutorials there.
When everything was implemented like this then you can start accessing the table with normal SQL operations. This technique can be used to import data to the database.
You find the example code for this in the download section of this article.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!
