MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘position specific files’ tag

Update to Oracle External File Basics

without comments

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.

Written by maclochlainn

June 28th, 2008 at 11:55 pm