Extract Data to a Flat File using UTL_FILE in Oracle Apps

In Oracle PL/SQL, UTL_FILE is an Oracle supplied package which is used for file operations (read and write).Let us discuss about how you  can use this package to extract data to a flat file like *.csv.

Follow the below steps

Step 1:

Find the valid directory to place the file at server.  Execute below query to get the valid directory in Oracle Apps

 SELECT VALUE

 FROM v$parameter

 WHERE NAME = 'utl_file_dir'

Step 2:

 Need to declare a variable of the type UTL_FILE.FILE_TYPE in the procedure/plsql block.

DECLARE

--Cursor to fetch the data

f_handle              UTL_FILE.FILE_TYPE;

BEGIN

--Remaining logic

UTL_FILE.FILE_TYPE will point to the file at server

Step 3:

Write the code to open the file in write mode using UTL_FILE.FOPEN

DECLARE

--Cursor to fetch the data

f_handle              UTL_FILE.FILE_TYPE;

BEGIN

--Remaining logic

 f_handle :=UTL_FILE.FOPEN('/usr/tmp',l_filename,'w',32767);

--Remaining logic

Step 4:

Open the cursor and put the data to the file using UTL_FILE.put_line

UTL_FILE.put_line (f_handle,order_data.order_number

                               ||'|'

                               ||order_data.Ordered_date

                               ||'|'

                               ||order_data.ordered_item

                               ||'|'

                               ||order_data.line_number);

Step 5:

Close the filel using UTL_FILE.FCLOSE.

UTL_FILE.FCLOSE(f_handle);

Example:

Here we have created a simple plsql block which extracts data from oe_order_headers_all and oe_order_lines_all and will write the data to *.csv file with delimiter ‘|’ 

DECLARE

  CURSOR cur_order_detail                                                                                                                                                     --Cursor to fetch records

  IS

    SELECT ooha.order_number

          ,ooha.ordered_date

          ,oola.ordered_item

          , oola.line_number || '.' || oola.shipment_number line_number

      FROM oe_order_headers_all ooha

          ,oe_order_lines_all oola

     WHERE ooha.header_id = oola.header_id;

  f_handle                      UTL_FILE.file_type;

  l_filename                    VARCHAR2 (100);

 BEGIN

  l_filename := REPLACE ('order_detail', '.', '_') || '.csv';

  f_handle := UTL_FILE.fopen ('/usr/tmp', l_filename, 'w', 32767); 

  FOR order_data IN cur_order_detail

  LOOP

    UTL_FILE.put_line (f_handle, order_data.order_number 

                                 || '|' 

                                 || order_data.ordered_date 

                                 || '|' 

                                 || order_data.ordered_item 

                                 || '|' 

                                 || order_data.line_number);

  END LOOP;

  UTL_FILE.fclose (f_handle);

EXCEPTION

  WHEN OTHERS

  THEN

--  retcode:=2;

    fnd_file.put_line (fnd_file.LOG, 'Error Occured while creating  ' || l_filename || '  ' || SQLERRM);

END;

UTL_FILE Package Exception:

Some exceptions might raise by the utl_file package . You can find below exceptions.

INVALID_PATH                 -> File location or filename was invalid. 

INVALID_MODE               ->The open_mode parameter in FOPEN was invalid. 

INVALID_FILEHANDLE    ->File handle was invalid. 

INVALID_OPERATION    ->File could not be opened or operated on as requested. 

READ_ERROR                  ->Operating system error occurred during the read operation. 

WRITE_ERROR               ->Operating system error occurred during the write operation. 

INTERNAL_ERROR         ->Unspecified PL/SQL error.