Understanding UTL_FILE in Oracle PL/SQL

On more than fewer occasions, while developing PL/SQL programs, we are required to read / write data from text files that reside on our operating systems.  UTL_FILE package lets us to read / write files from our PL/SQL programs.

In this post, I shall try to explain some of the procedures that are primarily used during this process and a sample code is provided at the end of the post. The objective of the sample code would be “read” the data from a csv file and then to “write” data into another file placed on the server.

Some of the primary procedures used to read and write using UTL_FILE are:

UTL_FILE.fgetattr: This procedure is used to check if the file exists or not in the specified directory location. It also returns the attributes (properties) of a file.

UTL_FILE.FGETATTR(location IN VARCHAR2,
                  filename IN VARCHAR2,
                  fexists OUT BOOLEAN,
                  file_length OUT NUMBER,
                  block_size OUT BINARY_INTEGER);

The input and output parameters  are:

1. location: Directory location of the source file, DIRECTORY_NAME from ALL_DIRECTORIESview (case sensitive).
2. filename: Name of the file to be processed.
3. fexists: The procedure returns TRUE value if the file exists in the directory.
4. file_length: Length of the file in bytes.
5. block_size: File system block size in bytes.

UTL_FILE.fopen: This function opens the file that needs to be processed (read or write).

UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER DEFAULT 1024) 
  RETURN FILE_TYPE;

The input parameters are:

1. location: Directory location of file. This string is a directory object name and must be specified in upper case.
2. filename: File name, including extension (file type), without directory path. If a directory path is given as a part of the filename, it is ignored by FOPEN.
3. open_mode: The mode in which the file has to be opened in. The mostly used attributes are: r  -> read and w -> write.

• UTL_FILE.get_line: Once the file is opened, the get_line procedure reads the text from the open file places the text in the output buffer parameter. If the len parameter is null – then the oracle provides the max_linesize value to that parameter.

UTL_FILE.GET_LINE (
   file        IN  FILE_TYPE,
   buffer      OUT VARCHAR2,
   len         IN  PLS_INTEGER DEFAULT NULL);

The parameters are:

1. file: Active file handle returned by an FOPEN call.
2. buffer: Data buffer to receive the line read from the file.

• UTL_FILE.put_line: The put_line procedure writes the text string stored in the buffer parameter to the open file identified by file handle.

UTL_FILE.PUT_LINE (
   file      IN FILE_TYPE,
   buffer    IN VARCHAR2,
   autoflush IN BOOLEAN DEFAULT FALSE);

The parameters are:

1. file: The file that is being processed and is called using the active file handle.
2. buffer: Text buffer that contains the text lines that need to be written to the file.

• UTL_FILE.fclose: This fclose procedure closes the file that is opened earlier. This is a mandatory statement during the UTL_File process. Not closing the file would keep the file open and make it unable to move or delete the file at a later stage.

UTL_FILE.fclose (l_file_handler);

The Input read file – Test_UTL_Read_File.csv has the following content:

In order to explain UTL_FILE, below is the sample code that uses some of the procedures explained above. The objective of this code is to:

• Check to see if the file exists.
• Open the file.
• Read the data from the file line by line.
• Write the data into another file on the same server.
• Close the file.

declare
l_file_handler       UTL_FILE.file_type; 
l_file_handler1      UTL_FILE.file_type;
l_file_handler2      UTL_FILE.file_type; 
l_file_exists      BOOLEAN := FALSE;
l_length           NUMBER;
l_blocksize        NUMBER;
l_dir      CONSTANT  VARCHAR2 (100) DEFAULT 'XXCMNDIR'; 
l_new_line           VARCHAR2 (500);
l_first_comma        NUMBER; 
l_second_comma       NUMBER;
l_delim              VARCHAR2 (1) := ',';
l_var1               VARCHAR2 (1000) := 'INSERT INTO XX_TEST_UTL_TBL (ID, STUDENT_NUM, SUBJECT) VALUES (';
l_var2               VARCHAR2 (1) := ')';
l_student_id        VARCHAR2 (50);
l_student_num       VARCHAR2 (50);
l_subject           VARCHAR2 (50); 
l_count             NUMBER; 
l_insert_stmnt      VARCHAR2 (1000);



begin

    
    /* Check to see if the file exists using the UTL_FILE.fgetattr function.*/
    
    UTL_FILE.fgetattr (l_dir, 
                      'Test_UTL_Read_File.csv',
                      l_file_exists,
                      l_length,
                      l_blocksize);
   
     dbms_output.put_line ('l_length is: '||l_length);
     dbms_output.put_line ('l_blocksize is: '||l_blocksize); 
    
    /* Opening the File. 
       It is mandatory to open the file before starting any operations on the files. 
       The File is opened using the UTL_FILE.fopen function. It has three parameters. 
       Directory_name, file_name, read (r) or write (w) mode. */
    BEGIN 
    
    l_file_handler  := UTL_FILE.fopen (l_dir,'Test_UTL_Read_File.csv','r');
    l_file_handler1 := UTL_FILE.fopen (l_dir,'Test_UTL_Write_File.csv','w');
    l_file_handler2 := UTL_FILE.fopen (l_dir,'Test_UTL_Error_File.csv','w');
    
    dbms_output.put_line ('File is successfully readable...'); 
    
    EXCEPTION 
        --Below are the different UTL_FILE Exceptions
        WHEN UTL_FILE.invalid_path 
        THEN 
             UTL_FILE.put_line (l_file_handler2, 'File location is invalid: '||SUBSTR (SQLERRM, 1, 200));
        WHEN UTL_FILE.invalid_mode
        THEN 
             UTL_FILE.put_line (l_file_handler2, 
                                'The open_mode parameter in FOPEN is invalid. '||SUBSTR (SQLERRM, 1, 200));
        WHEN UTL_FILE.invalid_filehandle
        THEN 
             UTL_FILE.put_line (l_file_handler2, 
                                'File handle is invalid: '||SUBSTR (SQLERRM, 1, 200));
        WHEN UTL_FILE.invalid_operation
        THEN 
             UTL_FILE.put_line (l_file_handler2, 
                                'File could not be opened or operated on as requested: '
                                ||SUBSTR (SQLERRM, 1,200));
        WHEN UTL_FILE.read_error
        THEN 
             UTL_FILE.put_line (l_file_handler2, 
                                'Destination buffer too small, or operating system error 
                                occurred during the read operation: '||SUBSTR (SQLERRM, 1, 200));
        WHEN UTL_FILE.write_error
        THEN 
             UTL_FILE.put_line (l_file_handler2, 
                                'Operating system error occurred during the write operation: '
                                ||SUBSTR (SQLERRM,1,200));      
        WHEN OTHERS 
        THEN  
             UTL_FILE.put_line (l_file_handler2, 'Other Error: '||SQLERRM);
    END; 
    
    /*Now once the file is opened - read the contents of the file
    The UTL_FILE.GET_LINE procedure reads the text from the open files 
    identified by the file handle and places the text 
    in the output buffer paramter.*/
    
    l_count := 0;
    
    LOOP 
        
         l_count := l_count+1;
         
        BEGIN
             UTL_FILE.GET_LINE (l_file_handler, l_new_line);
          EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
             EXIT; 
          END;
                
        l_first_comma  :=   INSTR (l_new_line, ',', 1, 1);
        l_second_comma :=   INSTR (l_new_line, ',', 1, 2);

        l_student_id   :=   SUBSTR (l_new_line, 1, l_first_comma-1);
        l_student_num  :=   SUBSTR (l_new_line, l_first_comma+1, l_second_comma - l_first_comma - 1 );
        l_subject      :=   SUBSTR (l_new_line, l_second_comma + 1);   
        
        l_subject  := REGEXP_REPLACE(l_subject,'(^[[:space:]]*|[[:space:]]*$)');
        
        l_insert_stmnt :=   l_var1
                            ||l_student_id  ||l_delim
                            ||l_student_num ||l_delim
                            ||l_subject     ||l_var2;

        dbms_output.put_line (l_insert_stmnt);
    
        UTL_FILE.put_line (l_file_handler1,l_insert_stmnt);
    
    END LOOP;
    
UTL_FILE.fclose (l_file_handler);
UTL_FILE.fclose (l_file_handler1);
UTL_FILE.fclose (l_file_handler2);

exception when others
then 

    dbms_output.put_line ('Exception occurred in main block: '||SQLERRM);

end;

After the UTL_File completes the Write process into a file Test_UTL_Write_File.csv, then the final output is as below:

PL/SQL – TRIM function

TRIM Function
The TRIM function removes all the specified characters from the beginning or the end of the string.
SYNTAX
TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )
Parameters or Arguments
LEADING
The function will remove trim_character from the front of string1.
TRAILING
The function will remove trim_character from the end of string1.
BOTH

The function will remove trim_character from the front and end of string1.
trim_character

select TRIM ('super') from dual;
--
select TRIM (LEADING 0 from '008899000') from dual; -- removes the initial 0s from the string and gives 1223
--
select TRIM (LEADING 1 from '1123445111') from dual; -- remvoes the initial 1s and leaves the trailing 1s. Gives 23445111 as the answer.
--
select TRIM(' ' from '     orace   ') from dual; -- removes all the spaces from the string - gives oracle as answer
-- 
select TRIM (leading ' ' from '    oracle  ') from dual; -- removes the initial spaces from the string - gives oracle   (with trailing spaces still existing). 
-- 
select TRIM (trailing ' ' from '    oracle   ') from dual;   --removes trailing spaces from the string -- gives     oracle as answer
--no parameter
select TRIM (1 from '112233111') from dual;   -- since there is no parameter - 1 is taken from front and back - gives answer 2233
-- using BOTH
select TRIM (BOTH 'a' from 'a123bbcaaa') from dual; -- removes the initial 'a' as well as trailing 'aaa' from the string -- gives 123bbc as the answer.
--
select TRIM (BOTH 'bbc' from 'bbccnnabccbsbbc') from dual; -- this will give an error because the TRIM Set should have only one character - here we have 3 - bbc
--
select TRIM (BOTH 'b' from 'bbccnnabccbsbbc') from dual; -- gives ccnnabccbsbbc

Oracle Payables Invoice Interface – Validate Vendor Number

During the Payables Invoice Interface process, one of the validations involve validating the Vendor Number for which the Invoice is being created for. The Validation logic checks in the system to see if a vendor already exists for that vendor number. If Yes, then an Invoice can be created for that Supplier. Else, since that Supplier doesn’t exist, Invoices cannot be created against that supplier and the validation should result in error.

DECLARE
   p_vendor_num   ap_suppliers.segment1%TYPE;
   l_vendor_id    ap_suppliers.vendor_id%TYPE;

   CURSOR cur_vendor_num_chk (cp_vendor_num ap_suppliers.segment1%TYPE)
   IS
      SELECT vendor_id
        FROM ap_suppliers
       WHERE segment1 = cp_vendor_num;
BEGIN
   OPEN cur_vendor_num_chk (p_vendor_num);

   FETCH cur_vendor_num_chk INTO l_vendor_id;

   IF (cur_vendor_num_chk%FOUND)
   THEN
      DBMS_OUTPUT.put_line (
            'Supplier exists in the system '
         || CHR (10)
         || ' Thus, Invoice Can be created for this vendor.');
   ELSE
      DBMS_OUTPUT.put_line (
         'Supplier does not exist and Invoice cannot be created using this Supplier');
   END IF;

   CLOSE cur_vendor_num_chk;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error while validation: ' || SQLERRM);
END;

Oracle Payables Invoice Interface – Validating Invoice Number

During the Payables Invoice Interface process, one of the validations involve validating the Invoice Number. If the Invoice Number already exists in the system for a given Vendor in a given Operating Unit, then another Invoice cannot be created with the same Invoice Number. The following logic can be used for validation:

DECLARE
   p_inv_number   ap_invoices_all.invoice_num%TYPE;
   p_vendor_id    ap_invoices_all.vendor_id%TYPE;
   p_org_id       ap_invoices_all.org_id%TYPE;
   l_inv_id       ap_invoices_all.invoice_id%TYPE;

   CURSOR cur_inv_num_chk (
      cp_inv_number    ap_invoices_all.invoice_num%TYPE,
      cp_vendor_id     ap_invoices_all.vendor_id%TYPE,
      cp_org_id        ap_invoices_all.org_id%TYPE)
   IS
      SELECT invoice_id
        FROM ap_invoices_all
       WHERE     invoice_number = cp_inv_number
             AND vendor_id = cp_vendor_id
             AND org_id = cp_org_id;
BEGIN
   OPEN cur_inv_num_chk (p_inv_number, p_vendor_id, p_org_id);

   FETCH cur_inv_num_chk INTO l_inv_id;

   IF (cur_inv_num_chk%FOUND)
   THEN
      DBMS_OUTPUT.put_line (
            'Invoice already exists in the system'
         || CHR (10)
         || ' Thus, Invoice Cannot be created with this Invoice Number
                        for this Vendor '
         || p_vendor_id
         || ' in this organization: '
         || p_org_id);
   ELSE
      DBMS_OUTPUT.put_line (
         'Invoice does not exist and can be created using this Invoice Number');
   END IF;

   CLOSE cur_inv_num_chk;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error while validation: ' || SQLERRM);
END;

Understanding AP Invoice Interface

Objective: The primary objective of this post is to understand why and how to import data into Oracle Applications in order to create Payables Invoices.

As one can understand the difference between a conversion process and an Interface – A conversion process is conducted once during a cycle to import legacy data into the Oracle, whereas an Interface is used on a daily basis to import external data into Oracle.

Many a times, users are given the task of importing external data into Oracle and create AP Invoices. Invoice data comes from sources including:

• EDI invoices from your suppliers that you load through Oracle e-Commerce Gateway.
• Supplier invoices that you transfer through the Oracle XML Gateway.
• Invoices that you load using Oracle SQL*Loader lease invoices from Oracle Property Manager.
• Lease payments from Oracle Assets, credit card transaction data that you load using the Credit Card Invoice Interface Summary.
• Invoices that you enter through the Invoice Gateway.

An Interface process goes through the following stages. A brief overview of the process is as below.

Invoice_Interface_Process

Step1: Loading into Staging table

Also, the inbound data can be loaded into the staging table in either of the following ways:

1.  Interfaced into the staging table using middleware technologies like webmethods or SOA.
2. The data can be created and placed in a csv file and can be loaded into the staging table using SQL Loader. (Please check my sql loader tutorial for understanding this process).

Usually the data and the staging tables are designed in such a way that one row in the staging table has all the header and Line Information associated with the Payables Invoice. Also, the staging table is designed in such a way that it mimics the columns in the AP Invoice Header and Line tables. (AP_INVOICES_ALL and AP_INVOICE_LINES_ALL) tables.

Step 2: Validating Staging table

Once, the data is placed in the staging table, the data here goes through validation. Some of the basic validations that are required for AP Invoice Interface are:

1. Validate Invoice Number.
2. Validate Vendor Name.
3. Validate Vendor Number
4. Validate Invoice Type
5. Validate Invoice Amount
6. Validate Payment Terms.
7. Validate Payment Method.
8. Validate GL Date.
9. Validate Payment Reason Code.
10. Validate Line Type.
11. Validate Sum of Line Amount
12. Validate POET (Project, Organization, Expenditure, Task).
13. Validate Tax Code.
14. Validate Operating Unit.
15. Validate Exchange Rate.
16. Validate Exchange Type.
17. Validate Bank Account.
18. Validate Tax Location and Tax Code.

And depending on the data / columns in the staging table – more validations may be required based on the current requirement.

During the validation process, each record in the staging table is marked either as a Processed record or a failed record, depending on whether the record passed validation or not.

If the record fails validation, then the corresponding process_flag (or whatever the name of the column that you give) column in the staging table is marked as E (error). Else, if the record passes validation, then the process_flag for that record is marked as V (Validated)

Step 3: Loading into Interface Table. 

All the records from the staging table that has the process_flag as ‘V‘ are then inserted into the Interface tables.

All the Header information from the staging table is passed to AP_Invoices_Interface table and all the Line Information is passed to AP_Invoice_lines_interface table.

Some of the important columns that have to be populated in the Interface tables are:

AP_INVOICES_INTERFACE:

AP_INVOICE_LINES_INTERFACE:

Step 4: Submit Standard Program.

Once the data is inserted into the Interface table, submit the “Payables Open Interface Import” standard import program to import the Interface data and create the Payables Invoices.

l_request_id := FND_REQUEST.SUBMIT_REQUEST     ( application =>  application_short_name
                                               , program     => 'APXIIMPT' 
                                               , description => NULL
                                               , start_time  => SYSDATE
                                               , sub_request => FALSE
                                               , argument1   => org_id
                                               , argument2   => p_source
                                               , argument3   => NULL
                                               , argument4   => NULL
                                               , argument5   => NULL
                                               , argument6   => NULL
                                               , argument7   => NULL
                                               , argument8   => NULL
                                               , argument9   => NULL
                                               , argument10  => NULL -- Debug Flag
                                               , argument11  => NULL
                                               , argument12  => NULL
                                               , argument13  => NULL --g_user_id
                                               , argument14  => NULL --g_login_id

Step 5: Error Handling in Interface tables.

When data is being migrated from Interface tables to the base tables in the above step – some of the data in the Interface tables may get into error and will not be used to create the Invoices. These records can be found in the AP_INTERFACE_REJECTIONS table.

The join between the AP_Interface_Rejections and the two Interface tables is: 

select air.reject_lookup_code, air.* 
from ap_interface_rejections air 
    ,ap_invoices_interface ait
where air.parent_id = ait.invoice_id 
and air.parent_table = 'AP_INVOICES_INTERFACE'

select air.reject_lookup_code, air.* 
from ap_interface_rejections air 
    ,ap_invoice_lines_interface ail
where air.parent_id = ail.invoice_line_id 
and air.parent_table = 'AP_INVOICE_LINES_INTERFACE'

Step 6: Verify the created Invoices

Login into Oracle Applications and navigate to Payables Superuser to query the created Invoices from the front end application and in the following base tables:

select * from ap_invoices_all

select * from ap_invoice_lines_all

Join between fnd_concurrent_requests and fnd_concurrent_programs_tl

There will be many instances during which you are asked to find out the latest concurrent requests that are submitted for a given concurrent program. In order to achieve those results, you have to join the fnd_concurrent_programs_tl and the fnd_concurrent_requests tables. Below is the sample code:

  SELECT fcr.request_date,
         fcr.argument_text,
         fcr.argument1,
         fcr.argument2,
         fcr.argument3,
         fcr.*
    FROM fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp
   WHERE     fcr.concurrent_program_id = fcp.concurrent_program_id
         AND fcp.user_concurrent_program_name =
                'Payables Open Interface Import'
ORDER BY fcr.request_date DESC;

Validate Unit of Measure

While inserting Item related data into the MTL_SYSTEM_ITEMS_INTERFACE interface table, we need to validate the Unit of Measure field. In order to validate the Unit of Measure (UOM) already exists in the system, check if the UOM exists in the MTL_UNITS_OF_MEASURE table.

DECLARE

l_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE; 
p_uom_code        mtl_units_of_measure.uom_code%TYPE;

BEGIN

p_uom_code := 'EA';

   SELECT unit_of_measure
     INTO l_unit_of_measure
     FROM mtl_units_of_measure
    WHERE uom_code = p_uom_code;
    
    DBMS_OUTPUT.put_line ('Unit of Measure is: ' || l_unit_of_measure);
    
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unit of Measure does not exist: ' || SQLERRM);
END;