How to create UTL_FILE Directory in 19C Database

By -

UTL_FILE is the package PLSQL program can read and write the OS text files

It is used to write the custom log files

Examples:

General debug and temporary files

Log and output files generated by concurrent processing 

11g R2 and 12c Release 1, We must the UTL_FILE_DIR database initialization parameter to define the directory or directories where these files are located.

Oracle Database 18c, the UTL_FILE_DIR database initialization parameter is desupported. Instead of database directory objects are used to specify the locations used for PL/SQL file I/O.

The main advantage is no need to DB bounce and directory change will reflect immediately

Steps to create the UTL_FILE

Step 1: Query to find the UTL Direcory

select name,value from apps.v$parameter where name like 'utl%';

Step 2: Create new directory

Example: mkdir /utlfile/common/sanjeev

Step 3: Export the PERL and PERL5LIB

[oracle@vtebs db]$ export PERL5LIB=/oracle/product/19.0.0/db/perl/lib/site_perl/5.28.1:/oracle/product/19.0.0/db/perl/lib/site_perl/5.28.1:/oracle/product/19.0.0/db/appsutil/perl

Step 4: execute the below command to create the directory

[oracle@ vtebs appsutil]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=addUtlFileDir

 

Enter the full path of Context File: /oracle/product/19.0.0/db/appsutil/TEST_vtebs01.xml

Enter the APPS Password:

Enter the SYSTEM Password:

Enter the new OS path to be added to UTL_FILE_DIR: /utlfile/common/ercebs9d/Sanjeev

 

Script Name    : txkCfgUtlfileDir.pl

Script Version : 120.0.12020000.11

Started        : Fri Jan 14 12:26:08 EST 2022

Log File       : /oracle/product/19.0.0/db/appsutil/log/TXK_UTIL_DIR_Fri_Jan_14_12_25_45_2022/txkCfgUtlfileDir.log

Context file: /oracle/product/19.0.0/db/appsutil/TEST_vtebs01.xml exists.

Completed        : Fri Jan 14 12:26:20 EST 2022

Successfully Completed the script

ERRORCODE = 0 ERRORCODE_END

[oracle@vtebs appsutil]$

 

Will see few important ant parameters below

Parameter

Description

txkCfgUtlfileDir.pl

This file using to migrate our previous UTL_FILE_DIR database initialization parameter settings to the new parameter when you upgrade your database, and to update the new supplemental UTL_FILE_DIR parameter whenever necessary after the upgrade. You can also use the txkCfgUtlfileDir.pl script to create directory objects.

getUtlFileDir mode

Retrieve the directory path values from the source UTL_FILE_DIR database initialization parameter

setUtlFileDir

the script stores the paths for the supplemental UTL_FILE_DIR parameter in the new Oracle E-Business Suite tables underlying the apps.v$parameter and apps.v$parameter2 views.

skipdirvalidation=Yes

cannot validate the 19c Oracle home directory, then you should pass the -skipdirvalidation=Yes parameter in the txkCfgUtlfileDir.pl script command

addUtlFileDir

Add a new directory path to the supplemental UTL_FILE_DIR parameter


Reference:-

Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)    

Comments

Post a Comment

Popular posts from this blog

How to troubleshoot long running concurrent request in R12.2

By -
Normally we are getting concurrent program running longer and concurrent request not picking jobs, etc. those are very frequent issue we expected from development team. Collect below basis level of information to development team 1 Oracle seeded program/Custom program? 2 How much time it used earlier? 3 Is there any recent code change done in concurrent program? 4 Is this program fetching higher data compare to last run? 5 Does this job running any specific time/ It can be run any time? 6 Does this job fetching data using DB link? 7 Does the problem happen on both the test and production instance? Once asked above questions to development team in meanwhile we need to start basic sanity check in our environment. 1 Verify the status of the concurrent program select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from apps.fnd_concurrent_requests where request_id=’1234567’; 2 Find which concurrent manger ran a specific concurrent request select b.USER_CONCURR
Read more

How to run Gather Schema Statistics in R12.2

By -
Image
What is Gather Schema Statistics? Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. The cost-based optimization (CBO)  uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan. Why Run the Gather Schema Statistics? When Data is update, insert, delete in table of user end Like (Technical User ,Finance User ,Ect) It become necessary to run the Gather Schema Statistics It’s recommended to Run GSS in Weekly Once or twice. How to run Gather Schema Statistics? Login application with sysadmin user Submit Request Window Navigate to: Concurrent > Requests Enter the parameters This can be run for specific schemas by specifying the schema name or enter ‘ALL’ to gather statistics for every schema in the database Submit the Gather Schema Statistics program Parameters: Schem
Read more

How to compile forms in R12.2

By -
STEP 1--> Login to app server STEP 2--> Go to  directory $AU_TOP/forms?US STEP 3--> Move to ".fmb" file STEP 4--> Execute the below command to generate ".fmx" frmcmp_batch userid=apps/<apps_pwd> module=<form_name>.fmb output_file=<form_name>.fmx module_type=form batch=no compile_all=special (EX):-   frmcmp_batch userid=apps/apps module=JAINTAXT.fmb output_file=$JA_TOP/forms/US/JAINTAXT.fmx module_type=form batch=no compile_all= special (O/p):   Created form file /oracle/CLONE/apps/fs2/EBSapps/appl/ja/12.0.0/forms/US/ JAINTAXT.fmx Refer: R12: How to Compile a Form in Release 12 (Doc ID 1085928.1)
Read more