Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Update to Oracle External File Basics

leave a comment »


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.

Advertisements

Written by maclochlainn

June 28, 2008 at 11:55 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: