Archive for the ‘Oracle External Tables’ Category
External Table Access
I left to chance where students would attempt to place their external files in a Linux or Unix implementation. As frequently occurs, they choose a location in their student
user’s home directory. Any attempt to read an external table based on a file in this type of directory fails because it’s not accessible by the Oracle user because the directory within the student
user’s account isn’t reachable. You can’t simply chown
a directory and the files in a directory.
The failure returns the following result:
SELECT COUNT(*) FROM transaction_upload * ERROR AT line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening FILE /home/student/upload/transaction_upload.LOG |
The reason isn’t readily visible to all, but a virtual directory must reference a physical directory owned by the oracle
user and dba
group. That also means the oracle
user must own all directories from the logical mount point to the physical directory name.
Assuming you’re working in an Oracle Database 11g XE instance, you can create a valid upload
directory by navigating to this directory:
/u01/app/oracle |
Then, issue this command as the root
user to create a new upload
directory:
mkdir upload |
Now you have the following directory:
/u01/app/oracle/upload |
Assuming you’ve created the upload
directory as the root
user, the root
user should issue the following two commands from the /u01/app/oracle
directory:
chown -R oracle:dba upload chmod -R 755 upload |
Having made that change you should now be able to query the external file source, like a *.csv (comma-separated values) file. Hope this helps those trying to use external tables, which I subsequently wrote about for Toad World – External Tables.