Oracle External Table

Lets take a look at Oracle External Table. This is a very useful concept for Oracle / Oracle Apps Developers.

External Tables
Oracle External Table enables you to access data from external sources as if the data were in a table. To simplify, you can have a data file in the Unix directory and you can have a select statement to select data from the data file. Oh yeah, you heard it right!!! This is very useful when you have to process data files as part of interfaces.

I, myself, have used it extensively to read AP, AR and GL Daily Interface files. It is similar to SQL Loader functionality.

Defining an External Table

In my note, we will looking at creating an external table to read GL Journal Data. I will also include the sample data file so that you can try it out. Before you can start defining the external table, you need to know the data file format. In our example the data fields are separated by “|”. We don’t have any header or trailer records as well.

CREATE TABLE xxraj_gl_interface_load_tbl
(

Batch_Name                            VARCHAR2(240),
Header_Name                         VARCHAR2(240),
JE_Category                            VARCHAR2(30),
JE_Source                                VARCHAR2(30),
Period_Name                          VARCHAR2(10),
Accounting_Date                   DATE,
Concatenated_Segments      VARCHAR2(120),
Segment1                                 VARCHAR2(30),
Segment2                                VARCHAR2(30),
Segment3                                VARCHAR2(30),
Segment4                                VARCHAR2(30),
segment5                                VARCHAR2(30),
Segment6                                VARCHAR2(30),
Accounted_DR                      NUMBER,
Accounted_CR                      NUMBER,
Batch_Description               VARCHAR2(240),
Header_Description            VARCHAR2(240),
Line_Description                 VARCHAR2(240),
Currency_Code                     VARCHAR2(3),
Conversion_Rate                 NUMBER,
Conversion_Type                 VARCHAR2(30),
Actual_Flag                           VARCHAR2(1),
Creation_Date                      DATE,
Line_Number                       NUMBER

)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY gl_in_files
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS
TERMINATED BY ‘|’
OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL

(
Batch_Name ,
Header_Name ,
JE_Category ,
JE_Source ,
Period_Name ,
Accounting_Date ,
Concatenated_Segments ,
Segment1 ,
Segment2 ,
Segment3 ,
Segment4 ,
Segment5 ,
Segment6 ,
Accounted_DR ,
Accounted_CR ,
Batch_Description ,
Header_Description ,
Line_Description ,
Currency_Code ,
Conversion_Rate ,
Conversion_Type ,
Actual_Flag ,
Creation_Date ,
Line_Number
)

)
LOCATION (‘GL_INTERFACE.txt’)
);

ORGANIZATION EXTERNAL –> This identifies the table as an Oracle External Table

DEFAULT DIRECTORY  gl_in_files –> You need to define the directory in Oracle. In our example, we will be placing the file @ /data/prod/interfaces/in/GL directory.
And in Oracle Database, we will be creating  a directory called “gl_in_files” as follows:
CREATE OR REPLACE DIRECTORY gl_in_files AS ‘/data/prod/interfaces/in/GL’

FIELDS TERMINATED BY ‘|’ –> the different data fields in the data file are separated by “|”.

LOCATION (‘GL_INTERFACE.txt’) –> this specifies the file name is “GL_INTERFACE.txt”. The location is identified by the Directory name given in the script. In our case it is “gl_in_files”.

Access the Data File using External Table

Once the External Table is successfully created, please follow the below steps:

• Place the data file in the directory that you have defined, In our example, the data file will be placed @ /data/prod/interfaces/in/GL
• Make sure the file has read privileges. I usually give “775” privileges on the data file.
• Run the select query from Toad / Oracle SQL Developer / SQL Plus. You may use the below sample query:

SELECT * FROM xxraj_gl_interface_load_tbl

Errors:

There are chances that you run into errors while running the SQL Query to select the data using External Table. You will have to debug the issues yourself.
The good news, you will see a log file and a bad file in the directory where the data file is placed. You can check the files to see the errors and make changes to the Oracle External Table definition or data file, based on the errors.

Additional Options
You can include lot of different options in the External Table Definition. Few of them include:

BADFILE <file_name>  –> Provide the name of the Bad File that will be created
DISCARDFILE <file_name>–> Provide the name of the Discard File that will be created
LOGFILE <file_name> –> Provide the name of the Log File that will be created
SKIP <number_of_rows> –> This options allows you to SKIP rows

Download the sample Data file and Oracle External Table Definition using below links. I hope this helps you guys. Feel free to post your questions / comments /feedback.

External Table Definition

GL Data File –> Please change the extension to “.txt”. Word press does not allow me to upload files with “.txt” extension.