Using Oracle Directories names as same of path

Question

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?

• vegatripy: I assume your export DIRECTORY parameter is somehow influenced by your old legacy scripts. (If not, you can just create another Oracle directory with the same path and a former name. Export/Import data pump only use this parameter to locate where to read/write the dump and log files). However, as Balaz suggested, you can use escaped double quotes to avoid the uppercase parsing.
• EdStevens: I would add that the best solution would be to start migrating away from the hack. Start fixing the code to not rely on a directory name object that requires special handling. It doesn't have to be done all at once.

Answer 1

Just because you are able to do something, it does not necessarily mean it is a good idea.

[oracle@o73 ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 Production on Thu Apr 5 16:20:15 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> create directory "/home/oracle" as '/home/oracle';
Directory created.
SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
[oracle@o73 ~]$ expdp \'/ as sysdba\' directory=\\\"/home/oracle\\\" schemas=bp
Export: Release 18.0.0.0.0 - Production on Thu Apr 5 16:20:33 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_04": "/******** AS SYSDBA" directory=\"/home/oracle\" schemas=bp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
 /home/oracle/expdat.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Thu Apr 5 16:20:53 2018 elapsed 0 00:00:18