SQL*Loader external tables
External files are read-only when you use SQL*Loader. You must use Oracle Data Pump when you want to make them read and write files. This other page shows you how to implement both read and write external files.
You must first create a virtual directory and then grant a schema privileges to read or to read and write to the virtual directory. If you don’t plan on having any log, bad record failure, or discard files, you can grant read only permissions on the virtual directory. You must grant read and write privileges when you also want to generate log and exception files.
1. Create the virtual directory as a privileged user (and don’t forget to create the physical directory on the file system):
SQL> CREATE DIRECTORY download AS 'C:\Download'; |
2. As the privileged user grant the read only privilege to the schema that will read the data:
SQL> GRANT READ ON DIRECTORY download TO importer; |
Alternatively, if you want to write log or exception files, grant read and write like:
SQL> GRANT READ, WRITE ON DIRECTORY download TO importer; |
You may benefit from downloading and installing the VERTICAL_QUERY function. It lets you display information for directory permissions easily without formatting at the SQL*Plus command line. You’ll find all you need in this post with the source code as open text.
3. Create Comma Separated Value (CSV) file in the target directory:
1,'Indiana','Jones' 2,'Ravenwood','Marion' 3,'Marcus','Brody' 4,'Rene','Belloq' |
4. Create an External Table in the Database as the IMPORTER user (or whichever one you choose):
Create an external table that uses the oracle_loader method to read a file. The following doesn’t require write privileges because it suppresses logging:
CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII NOBADFILE NODISCARDFILE NOLOGFILE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT 0; |
You should note that the rejection limit is set to zero because logging is disabled. This is a good practice because you’re should be assuming an all or nothing on the load without the log information.
Create an external table that uses the SQL*Loader method to read a file. The following requires write privileges because it log activity and errors:
CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE CHARACTER DISCARDFILE CHARACTER LOGFILE CHARACTER FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT UNLIMITED; |
The rejection limit is unlimited because they’ll be captured in the enabled log files.
Unlike the ".csv" in the LOCATION argument, you can’t append a ".log", ".bad", or ".dis" extensions for log, bad, or discard files unless you enclose them in single quotes. You’ll raise an ugly exception if you include those exceptions when you query the table. Unfortunately, the syntax error doesn’t throw an exception when you create the table. Here’s an example of the runtime exception stack:
SELECT * FROM character * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "dot": expecting one of: "badfile, byteordermark, characterset, colon, column, data, delimited, discardfile, disable_directory_link_check, fields, fixed, load, logfile, language, nodiscardfile, nobadfile, nologfile, date_cache, processing, readsize, string, skip, territory, vari" KUP-01007: at line 2 column 20 |
You can override the ".log", ".bad", or ".dis" extensions with the following syntax, which also states the virtual directory where they should be written. Please note that the virtual directory MUST BE IN UPPERCASE only!
CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'character.bad' DISCARDFILE 'DOWNLOAD':'character.dis' LOGFILE 'DOWNLOAD':'character.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT UNLIMITED; |
If you forget to make the virtual directory an uppercase string, you’ll raise the following error stack:
ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: DATA cartridge error KUP-04080: directory object download NOT found |
You can also use position specific files. There are two separate syntax patterns but only one is mentioned in the Oracle 11g Database Utilities manual (the first that uses CHAR(n) to designate length).
1 Apple 1.49 2 Orange 2 |
You could create a position specific file for the same data but it’s probably easier with a different file example. This example fruits and their price per pound, like this one:
This uses length of fields:
CREATE TABLE grocery ( grocery_id NUMBER , item_name VARCHAR2(20) , item_amount NUMBER(4,2)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'grocery.bad' LOGFILE 'DOWNLOAD':'grocery.log' FIELDS MISSING FIELD VALUES ARE NULL ( grocery_id CHAR(3) , item_name CHAR(20) , item_amount CHAR(4))) LOCATION ('grocery.csv')) REJECT LIMIT UNLIMITED; |
The other approach is to label the starting and ending character positions. It’s more tedious because you need to keep track of both values. Also, don’t forget character positions are 1-based numbers.
This uses absolute start and end positions:
CREATE TABLE grocery ( grocery_id NUMBER , item_name VARCHAR2(20) , item_amount NUMBER(4,2)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'grocery.bad' LOGFILE 'DOWNLOAD':'grocery.log' FIELDS MISSING FIELD VALUES ARE NULL ( grocery_id POSITION(1:3) , item_name POSITION(4:23) , item_amount POSITION(24:27))) LOCATION ('grocery.csv')) REJECT LIMIT UNLIMITED; |
You may also want to review this article. It shows you how to check if the external file is there before you query the data.
Subscribe via RSS
As a side note, keep your CSV files in the directory you create with the CREATE DIRECTORY … command. The CREATE TABLE … statement will likely be in an SQL file, but you put that into the BIN dir (or another place).
Karsten
10 Feb 09 at 5:18 am
I need your help. When i created a external table all is ok. But when I run a select return the next error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file TEMP.LST in EXT_DIR not found
ORA-06512: at “SYS.ORACLE_LOADER”, line 14
ORA-06512: at line 1
Can you help me?
Thanks.
Luis Isunza
11 May 09 at 4:17 pm
I can replicate your problem if I change the ownership of the directory to something other than the Oracle database OS user’s account and restrict both group and user read access. Here’s the error I got and another post about discovering the file.
I’d suggest you change the directory permissions to 740 if you want to allow only read or 760 if you want to allow read and write. The other way I replicated it was to have no physical file present. That’s a different issue. I’ve added to the blog post to solve for it.
Hope this helps.
maclochlainn
11 May 09 at 7:10 pm
Luis, I finally got back to this and did it justice. I apologize for the delay. You can find it here.
maclochlainn
11 Jul 09 at 7:49 pm
Thanks for your article. I need your advice inusing the function ‘replace’ in the access parameters of the External Table. I am replacing all the ‘\’ with a null value in column straatnaam_nen( “replace(:STRAATNAAM_NEN, ‘\’, ”)” )
Appreciate your help,
Kind Regards
Donald
Donald
17 Jul 09 at 8:04 am
Step 1 needs a ; at the end
Ben
3 Nov 09 at 2:05 pm
Thanks, it’s fixed.
maclochlainn
15 Jun 10 at 11:23 pm
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04027: file name check failed: OMIJID10000019375.log
29913. 00000 – “error in executing %s callout”
I’m getting the above issue when selecting data from external table. its observed that I am getting this error when I upgraded to oracle 18c and moved database to cloud server. It used to work fine before upgrade.
Can you help me understanding the root cause and fix for this?
Thanks a ton in advance.
Dams
15 Mar 19 at 4:15 am
SQL*Loader expects to work on the local file system. If you’ve implemented in the Cloud there are a number of configuration issues that may cause this type of error.
maclochlainn
27 Jan 21 at 12:03 pm