If you are currently working with SQL*Loader and want to convert to using external tables, you can use the SQL*Loader to generate the SQL required to create the external table, using the EXTERNAL_TABLE option. A small example will help demonstrate this process. Suppose you have the following table DDL:
SQL> create table books (book_id number, book_desc varchar2(30));
In this situation, you want to load the following data from a CSV file into the BOOKS table. The data is in a file named books.dat and is as follows:
1|RMAN Recipes 2|Linux for DBAs 3|SQL Recipes
You also have a books.ctl SQL*Loader control file that contains the following data:
load data
INFILE ‘books.dat’ INTO TABLE books APPEND
FIELDS TERMINATED BY ‘|’ (book_id,book_desc)
You can use SQL*Loader with the EXTERNAL_TABLE=GENERATE_ONLY clause to generate the SQL required to create an external table; for example,
$ sqlldr dk/f00 control=books.ctl log=books.log external_ table=generate_only
The prior line of code does not load any data. Rather, it creates a file, named books. log, that contains the SQL required to create an external table. Here is a partial listing of the code generated:
CREATE TABLE “SYS_SQLLDR_X_EXT_BOOKS” (“BOOK_ID” NUMBER, “BOOK_DESC” VARCHAR2(30) )ORGANIZATION external (TYPE oracle_loaderDEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE ‘SYS_SQLLDR_XT_TMPDIR_00000′:’books.bad’ LOGFILE ‘books.log_xt’READSIZE 1048576FIELDS TERMINATED BY “|” LDRTRIM REJECT ROWS WITH ALL NULL FIELDS (“BOOK_ID” CHAR(255) TERMINATED BY “|”, “BOOK_DESC” CHAR(255) TERMINATED BY “|”) )location (‘books.dat’ ))REJECT LIMIT UNLIMITED;
Before you run the prior code, create a directory that points to the location of the books.dat file; for example,
SQL> create or replace directory SYS_SQLLDR_XT_TMPDIR_00000 as ‘/u01/sqlldr’;
Now, if you run the SQL code generated by SQL*Loader, you should be able to view the data in the SYS_SQLLDR_X_EXT_BOOKS table:
SQL> select * from SYS_SQLLDR_X_EXT_BOOKS;
Here is the expected output:
BOOK_ID BOOK_DESC
1 RMAN Recipes
2 Linux for DBAs 3 SQL Recipes
This is a powerful technique, especially if you already have existing SQL*Loader control files and want to ensure that you have the correct syntax when converting to external tables.
Viewing External Table Metadata
At this point, you can also view metadata regarding the external table. Query the DBA_ EXTERNAL_TABLES view for details:
SQL> select owner ,table_name,default_directory_name ,access_parametersfrom dba_external_tables;
Here is a partial listing of the output:
OWNER TABLE_NAME DEFAULT_DIRECTORY_NA ACCESS_PARAMETERS SYS EXADATA_ET EXA_DIR records delimited …
Additionally, you can select from the DBA_EXTERNAL_LOCATIONS table for information regarding any flat files referenced in an external table:
SQL> select owner ,table_name ,location from dba_external_locations;
Here is some sample output:
OWNER TABLE_NAME LOCATION
SYS EXADATA_ET ex.csv
Leave a Reply