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:
Then, issue this command as the
root user to create a new
Now you have the following directory:
Assuming you’ve created the
upload directory as the
root user, the
root user should issue the following two commands from the
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.