External Tables Dec 6th, 2011
External tables have been around for quite some time and we have used them at REALSOFT many times. I though of adding an example to help new comers
What is an External TAble.
It is a definition of an Oracle table whose actual data source is an external flat file (residing on the file system).
The beauty of it is that you can issue Queries against this file as if it is a normal Oracle table. The following are some limitations
1) No DML is possible for External Tables
2) No index creation (because the actual data is in an external segment of the file system).
3) If the external flat file is removed, then (of course) the query will generate errors
Example: I have a comma delimited flat file located on a unix file system.
The filename is bank_list.cvs and the directory name is /home/oracle
Create an Oracle Directory to define the location of the filesystem
SQL>Create or Replace DIRECOTORY ext_dir as '/home/oracle';
The admin has to Grant you READ permission on this directory
SQL>Grant READ on ext_dir to SCOTT;
CREATE TABLE ext_table
(
CPR VARCHAR2(20 BYTE),
APPNO NUMBER(10),
BNK_CODE NUMBER(4),
BANK_ACCOUNT VARCHAR2(20 BYTE),
ACC_ANAME VARCHAR2(240 BYTE),
ACC_ENAME VARCHAR2(240 BYTE),
STATUS NUMBER(1)
)
organization external (type oracle_loader -- Note that internal SQL Loader is used
default directory ext_dir
access parameters (
records delimited by newline badfile 'bank.bad' -- if you do not specify badfile a default ad file is generate
fields terminated by ','
)
location ('bank_list.csv') -- this is the name of the flat file
)
reject limit unlimited
Note that the badfile clause should come with the RECORDS keyword (if written after fields terminated, it will generated run time error.
Once this is done you can issue a statement like
SQL> SELECT * FROM EXT_TABLE;
In case there are record that failed to be fetched (normally due to length, data type violations or other reasons) then you can examine the BADFILE (bank.bad) to take a look at these rejected row. (later on, we will show an example, to treat the bad file as an external flat file and load it as an external table)
NOTE: Each time you run the EXT_TABLE and rejected rows exist, the bank.bad is over written. If there are no rejected rows, then no file is generated (the old file would still exist)
Try to issue UPDATE, DELETE , INSERT statement and you should receive errors
rename the actual physical file issue a SELECT and note that an error is generated
Many people would load the external file information into an actual Oracle Table
SQL> CREATE table BANK_LIST as SELECT * from EXT_TABLE;
*Note : Dropping the Table does not delete the information from the flat file, but the definition is of course removed.
In the following, we shall also create an external table for the badfile called bank.bad to simplify the process of examining the result of the loading of the EXT_TABLE external Table.
SQL>create table ext_log
(logrow varchar2(4000))
organization external (type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields
missing field values are null
(logrow position (1:4000))
)
location ('bank.bad')
)
reject limit unlimited
HERE, we assume that each line of the bank.bad does not exceed 4000 Lines. If it does, they you need to add more field in the ext_log (not to exceed 4000 character per column because this is the max allowed for VARCHAR2).
If you want to check if EXT_TABLE the number of rejections then you can simply issue
SQL> SELECT COUNT(*) FROM EXT_LOG;
IMPORTANT: If there are no rejected rows, then the bank.bad will not be created. This potentially has two problems
1) The old bad file still exists and your SELECT COUNT(*) would give you the BAD file on an older attempt
2) The file does not exist at all , and the SELECT COUNT(*) would generate an error
Therefore, we would like to clear the bad file if it already or create it if it does not exit
It is just two operations, Open the bad file for Write and close it
DECLARE
V1 VARCHAR2(32767);
F1 UTL_FILE.FILE_TYPE;
BEGIN
F1 := UTL_FILE.FOPEN('EXT_DIR','bank.bad','W');
UTL_FILE.FCLOSE(F1);
end;
Ammar