0

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?

2
  • 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.
    – vegatripy
    Commented Apr 5, 2018 at 15:51
  • 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.
    – EdStevens
    Commented Apr 5, 2018 at 21:11

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.