UTL_FILE Utility Basic Setups

DB and Server Setups

1.       The directory path on the application node will need to be mounted/shared on the database node.

2.      The directory path is readable/writable from the database node.

3.       The directory path should have required permissions.

DB Level - Verification SQL

SELECT directory_name,

       directory_path

  FROM sys.dba_directories

WHERE directory_name = 'CUSTOM_DIR';

SELECT grantee,

       privilege,

       table_name

  FROM sys.dba_tab_privs

WHERE table_name = 'CUSTOM_DIR';

Server Level - Verification Command

ls -altr CUSTOM_DIR_PATH

Basic Code with Important Exceptions

DECLARE

   lf_utl_file_id      UTL_FILE.file_type;

   lv_dba_dir_name     VARCHAR2 (30) := 'CUSTOM_DIR';

   lv_data_file_name   VARCHAR2 (80) := 'outbound_file.txt';

   lv_utl_file_mode    VARCHAR2 (1) := 'w';    -- 'w', 'r'

   lv_data_line        VARCHAR2 (2000);

BEGIN

   IF NOT UTL_FILE.is_open (lf_utl_file_id)

   THEN

      lf_utl_file_id      :=

         UTL_FILE.fopen (lv_dba_dir_name,

                         lv_data_file_name,

                         lv_utl_file_mode,

                         32767);

   END IF;

   lv_data_line   := '';             -- Extract Data

   UTL_FILE.put_line ( lf_utl_file_id, lv_data_line);

   UTL_FILE.fclose (lf_utl_file_id);

EXCEPTION

   WHEN UTL_FILE.invalid_mode

   THEN

      raise_application_error ( -20051, 'Invalid Mode Parameter');

   WHEN UTL_FILE.invalid_path

   THEN

      raise_application_error ( -20052, 'Invalid File Location');

   WHEN UTL_FILE.invalid_filehandle

   THEN

      raise_application_error ( -20053, 'Invalid Filehandle');

   WHEN UTL_FILE.invalid_operation

   THEN

      raise_application_error ( -20054, 'Invalid Operation');

   WHEN UTL_FILE.read_error

   THEN

      raise_application_error ( -20055, 'Read Error');

   WHEN UTL_FILE.internal_error

   THEN

      raise_application_error ( -20057, 'Internal Error');

   WHEN UTL_FILE.charsetmismatch

   THEN

      raise_application_error ( -20058, 'Opened With FOPEN_NCHAR, But Later I/O Inconsistent');

   WHEN UTL_FILE.file_open

   THEN

      raise_application_error ( -20059, 'File Already Opened');

   WHEN UTL_FILE.invalid_maxlinesize

   THEN

      raise_application_error ( -20060, 'Line Size Exceeds 32K');

   WHEN UTL_FILE.invalid_filename

   THEN

      raise_application_error ( -20061, 'Invalid File Name');

   WHEN UTL_FILE.access_denied

   THEN

      raise_application_error ( -20062, 'File Access Denied');

   WHEN UTL_FILE.invalid_offset

   THEN

      raise_application_error ( -20063, 'FSEEK Param Less Than 0');

   WHEN OTHERS

   THEN

      raise_application_error ( -20099, 'Unknown UTL_FILE Error');

END;

How to find the backend SQL query from Self Service / OAF pages in Oracle Applications?

Update the following Profile Option Values to Yes at User level.

FND: Diagnostics
Personalize Self-Service Defn
 
(For an Example) Supplier Page
Click > About this Page

[PAGE] Tab Page / Business Component Reference Details

View Objects

Query

Error: Element Not Found

In R12 applications, few BUTTONs maynot be performed well. (on self service pages.)

Line XXXX
Char X
Error: Element not found.
Code: 0

Oracle Applications: R12
Internet Explorer: 7

Solution

START > RUN > regedit

HKEY_CLASSES_ROOT\TypeLib\{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}\1.1\0\win32

If it's value is "C:\WINDOWS\system32\shdocvw.dll"

Replace that value with "C:\WINDOWS\system32\ieframe.dll"

How to restrict a user to one inventory organization?

Navigation

Inventory > Setup > Organization > Organization Access

Defining Organization Access

You can specify which organizations a responsibility can access by mapping responsibilities to organizations. Once this mapping is set up, a user logging into an Oracle Manufacturing product is restricted to the organizations mapped to the responsibility chosen. The Change Organization window is restricted as well.

Attention: Until you assign an organization to a responsibility in this window, all responsibilities have access to all organizations. Once you have restricted any responsibility to an organization, you must then explicitly define the organizations which all responsibilities can access.

Attention: This feature does not restrict access once the user is in the product. Users with access to functions that cross multiple organizations (such as ATP, Inter-organization Transfers, Item Search, Multi-organization Quantity Report, and so on) can still specify any valid organization when running these functions.

How to get the Distinct Values in TABLE Value set?

Method 1

Create the VIEW based on DISTINCT values; use the VIEW for creates the VALUESET.

Method 2

Paste the QUERY in TABLE field with alias name, and give the column name (with alias name).

(For an example)

Table : (SELECT DISTINCT PERIOD_NAME FROM GL_PERIODS) GL

Value : GL.PERIOD_NAME

FRM-40831: Truncation Occurred: value too long for field XXXXX

FRM-40831: Truncation Occurred: value too long for field XXXXX

Cause:

1. Form field length and database column length may not be same.
2. Minimum query length Property.
3. Data Length Semantics property and Unicode.

Solution:

1. Check the Form field length and database column length.

2. Your base table field query length (in the Results block) must be long enough to contain the query criteria. If it is not, you get an error that the value is too long for your field. All fields should have a minimum query length of 255.

3. If you have radio groups, list items, or check boxes based on database fields in your Results block, you should only copy those values from the Find window if they are not NULL.

4. If you ever need to adjust the default WHERE clause, remember to set it back when you do a non-query-find query.