Development of Out Bound Interface

By Using UTL_FILE We can Develop the Out Bound Interface.

UTL_FILE Package:-         This is one of the Pl/Sql file package will be use to transfer data from Table to file or File to Table.

1.         We will create and open the file by using UTL_FILE.FOPEN.

2.         We will insert the data into file by using UTL_FILE.PUT_LINE.

3.         We will close the file by using UTL_FILE.FCLOSE.

OUT BOND INTERFACE DEVELOPMENT PROCESS:-  

1.     Develop a Procedure.

2.     Define the cursor to fetch data from Table.

3.     Create file by using UTL_FILE.

4.     Open Cursor, Fetch data from the cursor insert into flat file,

5.     Close the Cursor,

6.     Close the file.

To find utl_file directory:-

          

           Select * from V$Parameter where name like ‘%UTL_FILE%’;

Example:-

Pl/Sql Procedure:-

Create or Replace Procedure Supp_interface (errbuf out varchar2, Retcode out varchar2) as

Cursor c1 is select Pv.venodr_id                           Vid,

                          Pv.vendor_name                        Vname,

                         Pvs.vendor_site_code                 Vsite,

                         Pvs.address_line1                       Vaddress,

                        Pvs.City                                      Vcity

From

           Po_vendors           pv, po_vendor_sites_all        pvs

Where

           Pv.vendor_id  =  pvs.venodr_id;

V_File           UTL_FILE.file_type;

V_Count        number (10) Default     0;

Begin

v_file    :=   UTL_FILE,fopen(‘d:\Oracle\Proddb\8.1.7\pl/Sql\temp’,’suppliers.dat’,’w’);

for c2 in c1

Loop

v_count := v_count+1

UTL_FILE.PUT_LINE (v_file, C2.Vid        ||’,’||

                                           C2.Vname     ||’,’||

                                          C2.Address    ||’,’||

                                          C2.Vcity);

End loop;

UTL_FILE.fclose (V_file);

FND_FILE.put_line (FND_FILE.output, ’Number of Records Transferred  =’||V_count);

End Supp_interface;

utl_file handling exceptions

invalid path,

invalid mode