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:

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

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;