Starting in Oracle Database 18c, initialization parameter UTL_FILE_DIR is deprecated and no longer supported. It can not be altered at DB level "alter system set utl_file_dir='/path/to/utl_dir;". Instead, specify the name of a directory object.
UTL_FILE_DIR is no longer listed in V$SYSTEM_PARAMETER and related views:
SQL> SELECT value FROM v$system_parameter WHERE name='utl_file_dir' ;
no rows selected
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir unknown
But, view v$parameter has it:
SQL> column name format a15
SQL> column value format a55
SQL> SELECT value FROM v$parameter WHERE name='utl_file_dir' ;
VALUE
--------------------------------------------------------------------------------
/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV
SQL> SELECT value FROM apps.v$parameter WHERE name='utl_file_dir' ;
VALUE
--------------------------------------------------------------------------------
/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV
SQL> select name, value from apps.v$parameter2 where name = 'utl_file_dir';
NAME VALUE
--------------- --------------------------------------------------
utl_file_dir /path/to/utl_dir
utl_file_dir /home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV
Doc ID 2525754.1 (Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2) states:
With Oracle Database 19c, you can specify the directories that you want Oracle E-Business Suite to use for PL/SQL file I/O in the supplemental UTL_FILE_DIR parameter within the apps.v$parameter and apps.v$parameter2 views. Each directory specified in that parameter must also have a corresponding directory object defined within the database. When you upgrade your Oracle E-Business Suite database from Oracle Database 11g or Oracle Database 12c to Oracle Database 19c, you use a script called txkCfgUtlfileDir.pl to migrate the directory paths formerly specified in the UTL_FILE_DIR database initialization parameter to the new supplemental UTL_FILE_DIR parameter and to create the corresponding directory objects. After the upgrade, you also use the same script to add, modify, or delete directory paths in the supplemental UTL_FILE_DIR parameter.
SQL> show parameter event
NAME TYPE VALUE
--------------------- ----------- ------------------------------
event string 10946 trace name context forever, level 8454144
xml_db_events string enable
SQL> show user
apps
SQL> FND_FILE.PUT_LINE(fnd_file.output, 'Hello output!');
SQL> FND_FILE.close;
This should dump a file on APPLPTMP.
FND_FILE uses the UTL_FILE package, which can only write to directories specified in init.ora (before 19c database)). APPLPTMP is a special directory used only for PLSQL temporary files. When a concurrent program uses the FND_FILE package, the concurrent manager uses APPLPTMP as the directory to which it writes temporary files. Thus, APPLPTMP must be one of the directories listed in init.ora in order for FND_FILE to work
But a concurrent program calling a PL/SQL procedure owned by a custom schema xxdoc keeps failing with errors:
ORACLE error 20100 in FDPSTP
Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.
Directory &FILE_DIR is invalid, contact your system administrator.
Action: Check that the directory is a valid directory with wri
The procedure in custom schema called by a concurrent program is similar to:
CREATE OR REPLACE PROCEDURE xxdoc.proc_test1(errbuf out varchar2, retcode out number) as
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Test CP from custom procedure');
END;
CREATE OR REPLACE SYNONYM proc_test1 for xxdoc.proc_test1; (by APPS)
The fix: Grant permission on directory EBS_DB_DIR_UTIL to XXDOC
Note "select * from dba_directories where directory_name = 'EBS_DB_DIR_UTIL';" returns 0 rows in R12c database of EBS.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ------------------- ----------
2 PDB$SEED READ ONLY NO
3 EBSDEV READ WRITE NO
SQL> alter session set container=IFSDEV;
Session altered.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ------------------- ----------
3 EBSDEV READ WRITE NO
SQL> set lines 200 pages 1234
SQL> col directory_name for a35
SQL> col directory_path for a60
SQL> col owner for a20
SQL> select * from dba_directories where directory_name like '%UTIL%';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
----------- --------------------------- -------------------------- -----------------------
SYS EBS_DB_DIR_UTIL /path/to/utl_dir 3
SQL> GRANT READ,WRITE on directory EBS_DB_DIR_UTIL to XXDOC;
Grant succeeded.
SQL> col grantee for a12
SQL> col owner for a15
SQL> col table_name for a35
SQL> col grantor for a20
SQL> col privilege for a20
SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE
from dba_tab_privs
where table_name like '%UTIL%' and TYPE in ('DIRECTORY')
order by owner,table_name,GRANTEE,privilege;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
-------------- ----------- ------------------- --------------- ----------------
APPS SYS EBS_DB_DIR_UTIL SYSTEM READ
APPS SYS EBS_DB_DIR_UTIL SYSTEM WRITE
SYSTEM SYS EBS_DB_DIR_UTIL SYS EXECUTE
SYSTEM SYS EBS_DB_DIR_UTIL SYS READ
SYSTEM SYS EBS_DB_DIR_UTIL SYS WRITE
XXDOC SYS EBS_DB_DIR_UTIL SYS READ
XXDOC SYS EBS_DB_DIR_UTIL SYS WRITE
NOTES: 19c database has UTL directories. Seems all of them point to the folder on db host.
SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from dba_tab_privs
where table_name like '%UTL%' and TYPE in ('DIRECTORY')
order by owner,table_name,GRANTEE,privilege;
REFERENCES:
- Concurrent Requests Fail With Error ORA-20100 (Doc ID 2723960.1)
- Creating a Custom Application in Oracle EBS Release 12.1.3 and above (Doc ID 1577707.1)
- Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)
[ Section 2.2.1: Granting Privileges to Editioned Objects
Section 1.1.4: Tools and Scripts for Edition-Based Development ]
