Outbound interface with UTL_FILE

Outbound Interface:

Outbound is a process extracting data from oracle database tool text files or excel files

We should use utl file package for outbound purpas.

Ex:

ð  Bank requisting Employee details to make the payment.

ð  Suppliers or vendors are requesting send a mail or fax with payment details.

UTL_FILE:  it is a package utility where we can read or write data into text file or excel file.

The main attributes are:

ð  UTL_FILE.FILE_TYPE

ð  UTL_FILE.OPEN

ð  UTL_FILE.PUT_LINE

ð  UTL_FILE.FCLOSE

ð  UTL_FILE.FCLOSE_ALL

UTL_FILE.FILE_TYPE:  

   This attribute is used to declare the variable as a UTL_FILE variable.

UTL_FILE.OPEN:

This attribute is used to open the utl_file a specific directary and writing the data it is having 3 parameters.

1. Path: UTL_FILE will always wright and generate file in specific directory path by default file generated in tmp directory.

By follwing query we can find UTL_FILE path

Select * form V$parameter where name like ‘%UTL_FILE%’

/user/tmp

/apps/oradev/devdb/9.2.0/appsutil/outbound/dev_ione

To change utl file directory path go to “init. Orafile” (init dat orafile pronounce) and change the value for utl_file_dir parameter.

From 9i onwords we can change directory path at runtime also.

2. File name: user difined file name which will generate automatically.

3. Method: we have 3 methods

Outbound:  W-à Write    AàAppending

Inbound:  RàReading

UTL_FILE.PUT_LINE:

This attribute is use to generate a output in text file and fnd file is use to generate output are log file.

UTL_FILE.FCLOSE:  This attribute is used to close the UTL_FILE variable which is open

UTL_FILE.FCLOSE_ALL:  This attribute is used to close all the variables which are open and generally we            can use in exception.

UTL_FILE Exceptions

UTL_FILE.Invalid_path

UTL_FILE.invalid_mode

UTL_FILE.Invalid_operation

UTL_FILE.write_error

UTL_FILE.INVALID_FILEHANDLE