Wednesday 27 December 2017

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

4 comments:

BroddyAdams said...

Hey are using Wordpress for your blog platform? I'm new to the blog world but I'm trying to get started and create my own. Do you need any coding expertise to make your own blog? Any help would be really appreciated! Oracle Fusion FBDI

Anonymous said...

Thanks for Sharing This informative article. It would be helpful if you write a all.
Oracle Fusion HCM Online Training

shaik shah said...

Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Oracle Fusion HCM Online Training

Goutham Raj said...

Good Article, Alots of information provided, Thanks for sharing this information.
Oracle Fusion HCM online Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *