UTL Package to read lines from the same file differently.
Hi all
Can someone please help me with this. I have the below .txt file sitting in the server:
FHSTMTDIM152008062020080622010302
SH200806200225222122222000
TD20080620000000026871428C00000000002222235307C00000000006088
TD20080620000000033335113C00000000002222200420C00000000006088
TD20080620000000030422560C00000000002222222980C00000000006088
ST000000222261569C00000284660029
FT000040001511000015404857190000083500001569731444100023460000002924
I have a UTL Package that reads this file into a temp table. My problem is that I want to read each line differently, depending on the 1st two characters of the line(which I read into a variable and will always be 'FH','SH','TD','ST'or 'FT'). It's not working. It only inserts one row into ce_stmt_int_tmp instead of 7 rows. Please tell me what I'm doing wrong. Here's part of my proc below:
PROCEDURE bank_stmt_load(errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY NUMBER
,p_file_name VARCHAR2) IS
f_datfile utl_file.file_type; /* file handler for incoming data file */
v_buffer VARCHAR2(150); /* buffer to hold single line data from flat file */
v_run_id NUMBER := Fnd_Global.conc_request_id;
v_sttemp ce_stmt_int_tmp%ROWTYPE;
v_filename VARCHAR2(40);
v_msg VARCHAR2(250);
v_dm VARCHAR2(2);
BEGIN
v_org_id :=Fnd_Profile.value('ORG_ID');
v_filename := LTRIM(RTRIM('my_file.txt'));
f_datfile := utl_file.fopen(g_dir_name,v_filename,'R');
DELETE FROM ce.ce_stmt_int_tmp;
LOOP
BEGIN
utl_file.get_line(f_datfile ,v_buffer);
v_sttemp.rec_id_no := 'L';
v_sttemp.column1 := substr(v_buffer ,1 ,2 );
v_dm := v_sttemp.column1;
IF v_dm = 'FH' THEN
v_sttemp.column2 := substr(v_buffer ,3 ,8);
ELSIF = 'SH' THEN
v_sttemp.column3 := substr(v_buffer ,11 ,4);
ELSIF = 'TD' THEN
v_sttemp.column4 := substr(v_buffer ,15 ,9);
ELSIF = 'ST' THEN
v_sttemp.column5 := substr(v_buffer ,24 ,3);
ELSE
v_sttemp.column6 := substr(v_buffer ,27 ,10);
END IF;
INSERT INTO ce_stmt_int_tmp
(rec_no
,rec_id_no
,column1
,column2
,column3
,column4
,column5
,column6)
VALUES
(v_sttemp.rec_no
,v_sttemp.rec_id_no
,LTRIM(RTRIM(v_sttemp.column1))
,LTRIM(RTRIM(v_sttemp.column2))
,LTRIM(RTRIM(v_sttemp.column3))
,LTRIM(RTRIM(v_sttemp.column4))
,LTRIM(RTRIM(v_sttemp.column5))
,LTRIM(RTRIM(v_sttemp.column6)) );
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;
utl_file.fclose_all;