Archive for the ‘external tables’ tag
Update to Oracle External File Basics
While an earlier entry discusses CSV uploads to Oracle external tables, I neglected to mention some things. You can also upload position specific files, override the directory for log files, and override the file extensions.
There are actually two syntax methods for uploading position specific file. Only one is covered in the Oracle 11g Database Utilities manual. It makes you wonder if Oracle supports one or both.
You can override default log, bad, or discard file extensions when you enclose the relative file name in single quotes. You can also specify a virtual directory that differs from your upload (data import) directory.
You may get a surprise if you think virtual directories are case insensitive while defining external tables. They’re not. You must enter overriding virtual directories in uppercase only! You’ll raise this exception stack if you attempt anything else:
ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: DATA cartridge error KUP-04080: directory object download NOT found |
The full syntax for virtual directories and extensions is in this note.
How to relate table, virtual directory, and external file names
I was trying to automate cleaning up external files when I discovered that there isn’t an administrative view in Oracle Database 11g to link table, virtual directory, and external file. Reflecting on that discovery in Oracle 11g, I realized that limits the concept of a push paradigm with an external file. So, I wrote one.
Oracle External Table Basics
External files are great tools for reading data into and writing data out of an Oracle database. You have two options for reading data into the database. One uses SQL*Loader and the other uses Oracle Data Pump. You have only one option to write data from the database into an external table file. That’s Oracle Data Pump.
I thought this was pretty straightforward when recommending it as a solution. Given the questions that I got back, it appears that it isn’t. Actually, I couldn’t find an example for how you import data through an external table by using Oracle Data Pump. I only checked the Oracle Database Utilities 11g documentation, but maybe its somewhere else.
The “Creating an external table that uses SQL*Loader” page demonstrates how you can create an input or read-only external table. The “Creating an external table that uses Oracle Data Pump” shows you how to create read-write external tables.
Exceptions covered in the Articles
The two referenced pages should help you understand the basics and resolve these error messages (at least on an Oracle Database 11g where I tested them):
Exception stack raised by Oracle SQL*Loader when you provide file extensions for log, bad, or discarded file names without enclosing them in single quotes:
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 also enclose a different Oracle virtual directory by using ‘virtual_directory’:’name.extension’ syntax.
Exception stack raised by Oracle Data Pump when you fail to enumerate columns in the source query:
ERROR at line 6: ORA-30656: COLUMN TYPE NOT supported ON external organized TABLE |
Exception stack raised by Oracle Data Pump when you try to rebuild the external table without previously dropping the external file:
CREATE TABLE item_export * ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: DATA cartridge error KUP-11012: file item_export.dmp IN C:\DATA\Download already EXISTS |