We, at our company, have been using a small hack on Oracle Directories names since some time ago.
Before Oracle Directories we have to explicitly set the directory on pfile, by the utl_file_dir parameter. To add a new directory we had to reboot the instance.
After Oracle Directories, everything become easier!
But some legacy scripts still have been using the old style, using utl_File.Fopen('/path',...)
instead of utl_File.Fopen('DIRECTORY',...)
The small hack is create a directory in which its name is the exactly path of the desired directory. In a simple example:
create or replace directory "/tmp" as '/tmp';
select * from dba_directories ;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ---------------------
SYS /tmp /tmp
So, the users can normally use a command like that:
l_file_handle := UTL_FILE.FOPEN('/tmp','file.txt','W');
That's really nice.
But I'm facing a problem when using such a directory as the output of a EXPDP dump. EXPDP parser seems to ignore any quotations marks, and is parsing the directory name in uppercase.
$expdp \"/ as sysdba\" parfile= parfile.par
Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 05 April, 2018 10:50:19
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name /TMP is invalid
[oracle@brux0009 P00OMS /EXPORT/spool]$ cat parfile.par
DIRECTORY='/tmp'
DUMPFILE=dual.dmp
LOGFILE=dual.log
TABLES=SYS.dual
Any tips?