PL SQL Script to Read data from Flat file in Oracle Apps

PL SQL Script to Read data from Flat file in Oracle Apps

In this post I will share you the code to read data from flat files which is available in the server.

CREATE OR REPLACE PROCEDURE XXREAD_data(errbuff varchar2,errcode number)

AS

v_line VARCHAR2(2000); -- Data line read from input file

v_file UTL_FILE.FILE_TYPE; -- Data file handle

v_dir VARCHAR2(250); -- Directory containing the data file

v_filename VARCHAR2(50); -- filename

v_1st_Comma number;

v_2nd_Comma number;

v_3rd_Comma number;

v_4th_Comma number;

v_5th_Comma number;


v_suppno VARHAR2(4000);

v_suppname  VARHAR2(4000);

v_phone_no  VARHAR2(4000);

v_address VARHAR2(4000);

v_country VARHAR2(4000);


BEGIN

v_dir := '/usr/tmp01';

v_filename := 'xxtest.dat';

v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');



LOOP

BEGIN

UTL_FILE.GET_LINE(v_file, v_line);

EXCEPTION

WHEN no_data_found THEN

exit;

END;


-- ----------------------------------------------------------

v_1st_Comma := INSTR(v_line, ',' ,1 , 1);

v_2nd_Comma := INSTR(v_line, ',' ,1 , 2);

v_3rd_Comma := INSTR(v_line, ',' ,1 , 3);

v_4th_Comma := INSTR(v_line, ',' ,1 , 4);

v_5th_Comma := INSTR(v_line, ',' ,1 , 5);

v_suppno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1));

v_suppname := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);

v_phone_no := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1);

v_address := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1));

v_country := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),'DD-MON-YYYY');



DBMS_OUTPUT.PUT_LINE(v_suppno ||' '|| v_suppname || ' ' || v_phone_no || ' ' || v_address ||' ' || v_country);

-- ------------------------------------------

-- Insert the new record into the DEPT table.

-- ------------------------------------------

INSERT INTO XX_SUPPLIER

VALUES (v_suppno ,v_suppname ,v_phone_no,v_mgr,v_address);

END LOOP;

UTL_FILE.FCLOSE(v_file);

COMMIT;

END;

PL SQL Script to Read data from Flat file in Oracle Apps