Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic File Creation using UTL_FILE (Oracle 11G)
| Dynamic File Creation using UTL_FILE [message #649746] |
Tue, 05 April 2016 02:58  |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Hello,
I have the following code which is creating 32 output files based on 32 different states. For each state I have hard coded the values in validation conditions but I want to know if I could create this dynamically. I have tried embedding the concatenated state with output file but its not working. I also tried declaring a varchar variable for output file name but UTL file still throws an error that I could not use char variable inside it. Please provide some pointers so I can modify accordingly.
Thanks!
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_AL_output_file VARCHAR2(50);
w_AR_output_file VARCHAR2(50);
w_AZ_output_file VARCHAR2(50);
w_CO_output_file VARCHAR2(50);
w_CT_output_file VARCHAR2(50);
w_DE_output_file VARCHAR2(50);
w_FL_output_file VARCHAR2(50);
w_GA_output_file VARCHAR2(50);
w_HI_output_file VARCHAR2(50);
w_IA_output_file VARCHAR2(50);
w_ID_output_file VARCHAR2(50);
w_IL_output_file VARCHAR2(50);
w_IN_output_file VARCHAR2(50);
w_KS_output_file VARCHAR2(50);
w_KY_output_file VARCHAR2(50);
w_MD_output_file VARCHAR2(50);
w_MI_output_file VARCHAR2(50);
w_MN_output_file VARCHAR2(50);
w_MO_output_file VARCHAR2(50);
w_MS_output_file VARCHAR2(50);
w_MT_output_file VARCHAR2(50);
w_NC_output_file VARCHAR2(50);
w_NE_output_file VARCHAR2(50);
w_NM_output_file VARCHAR2(50);
w_NV_output_file VARCHAR2(50);
w_OK_output_file VARCHAR2(50);
w_OR_output_file VARCHAR2(50);
w_SC_output_file VARCHAR2(50);
w_TN_output_file VARCHAR2(50);
w_VA_output_file VARCHAR2(50);
w_WI_output_file VARCHAR2(50);
w_WV_output_file VARCHAR2(50);
w_newline VARCHAR2(2000);
w_val VARCHAR2(200);
w_polnum VARCHAR2(20);
w_polid NUMBER;
w_state CHAR(2);
w_wcrtg ops$argdba.arg_pr_wc_rtg_basis%ROWTYPE;
input_file utl_file.file_type;
AL_output_file utl_file.file_type;
AR_output_file utl_file.file_type;
AZ_output_file utl_file.file_type;
CO_output_file utl_file.file_type;
CT_output_file utl_file.file_type;
DE_output_file utl_file.file_type;
FL_output_file utl_file.file_type;
GA_output_file utl_file.file_type;
HI_output_file utl_file.file_type;
IA_output_file utl_file.file_type;
ID_output_file utl_file.file_type;
IL_output_file utl_file.file_type;
IN_output_file utl_file.file_type;
KS_output_file utl_file.file_type;
KY_output_file utl_file.file_type;
MD_output_file utl_file.file_type;
MI_output_file utl_file.file_type;
MN_output_file utl_file.file_type;
MO_output_file utl_file.file_type;
MS_output_file utl_file.file_type;
MT_output_file utl_file.file_type;
NC_output_file utl_file.file_type;
NE_output_file utl_file.file_type;
NM_output_file utl_file.file_type;
NV_output_file utl_file.file_type;
OK_output_file utl_file.file_type;
OR_output_file utl_file.file_type;
SC_output_file utl_file.file_type;
TN_output_file utl_file.file_type;
VA_output_file utl_file.file_type;
WI_output_file utl_file.file_type;
WV_output_file utl_file.file_type;
cursor c1(w_polnum varchar2) is
select pol_id
from ops$argdba.arg_pr_pol
where pol_pfx_cd || div || myc || pol_ser_num || ml = w_polnum;
cursor c2(w_polid number) is
select distinct st_cd_alpha
from ops$argdba.arg_pr_pol_st
where pol_id = w_polid;
cursor c3(w_polid number, w_state varchar2) is
select *
from ops$argdba.arg_pr_wc_rtg_basis
where pol_id = w_polid
and st_cd_alpha = w_state;
BEGIN
dbms_output.enable(2000000);
w_dir := 'TEST2'; --AIS Directory
--w_dir := '/cent/dev0/batch/output'; --GCIS Directory
w_input_file := 'wcpols_test.txt'; --Original WCPOLS file;
w_AL_output_file := 'wcpolstest.AL';
w_AR_output_file := 'wcpolstest.AR';
w_AZ_output_file := 'wcpolstest.AZ';
w_CO_output_file := 'wcpolstest.CO';
w_CT_output_file := 'wcpolstest.CT';
w_DE_output_file := 'wcpolstest.DE';
w_FL_output_file := 'wcpolstest.FL';
w_GA_output_file := 'wcpolstest.GA';
w_HI_output_file := 'wcpolstest.HI';
w_IA_output_file := 'wcpolstest.IA';
w_ID_output_file := 'wcpolstest.ID';
w_IL_output_file := 'wcpolstest.IL';
w_IN_output_file := 'wcpolstest.IN';
w_KS_output_file := 'wcpolstest.KS';
w_KY_output_file := 'wcpolstest.KY';
w_MD_output_file := 'wcpolstest.MD';
w_MI_output_file := 'wcpolstest.MI';
w_MN_output_file := 'wcpolstest.MN';
w_MO_output_file := 'wcpolstest.MO';
w_MS_output_file := 'wcpolstest.MS';
w_MT_output_file := 'wcpolstest.MT';
w_NC_output_file := 'wcpolstest.NC';
w_NE_output_file := 'wcpolstest.NE';
w_NM_output_file := 'wcpolstest.NM';
w_NV_output_file := 'wcpolstest.NV';
w_OK_output_file := 'wcpolstest.OK';
w_OR_output_file := 'wcpolstest.OR';
w_SC_output_file := 'wcpolstest.SC';
w_TN_output_file := 'wcpolstest.TN';
w_VA_output_file := 'wcpolstest.VA';
w_WI_output_file := 'wcpolstest.WI';
w_WV_output_file := 'wcpolstest.WV';
input_file := utl_file.fopen(w_dir, w_input_file, 'r',32000);
AL_output_file := utl_file.fopen(w_dir, w_AL_output_file, 'w',32000);
AR_output_file := utl_file.fopen(w_dir, w_AR_output_file, 'w',32000);
AZ_output_file := utl_file.fopen(w_dir, w_AZ_output_file, 'w',32000);
CO_output_file := utl_file.fopen(w_dir, w_CO_output_file, 'w',32000);
CT_output_file := utl_file.fopen(w_dir, w_CT_output_file, 'w',32000);
DE_output_file := utl_file.fopen(w_dir, w_DE_output_file, 'w',32000);
FL_output_file := utl_file.fopen(w_dir, w_FL_output_file, 'w',32000);
GA_output_file := utl_file.fopen(w_dir, w_GA_output_file, 'w',32000);
HI_output_file := utl_file.fopen(w_dir, w_HI_output_file, 'w',32000);
IA_output_file := utl_file.fopen(w_dir, w_IA_output_file, 'w',32000);
ID_output_file := utl_file.fopen(w_dir, w_ID_output_file, 'w',32000);
IL_output_file := utl_file.fopen(w_dir, w_IL_output_file, 'w',32000);
IN_output_file := utl_file.fopen(w_dir, w_IN_output_file, 'w',32000);
KS_output_file := utl_file.fopen(w_dir, w_KS_output_file, 'w',32000);
KY_output_file := utl_file.fopen(w_dir, w_KY_output_file, 'w',32000);
MD_output_file := utl_file.fopen(w_dir, w_MD_output_file, 'w',32000);
MI_output_file := utl_file.fopen(w_dir, w_MI_output_file, 'w',32000);
MN_output_file := utl_file.fopen(w_dir, w_MN_output_file, 'w',32000);
MO_output_file := utl_file.fopen(w_dir, w_MO_output_file, 'w',32000);
MS_output_file := utl_file.fopen(w_dir, w_MS_output_file, 'w',32000);
MT_output_file := utl_file.fopen(w_dir, w_MT_output_file, 'w',32000);
NC_output_file := utl_file.fopen(w_dir, w_NC_output_file, 'w',32000);
NE_output_file := utl_file.fopen(w_dir, w_NE_output_file, 'w',32000);
NM_output_file := utl_file.fopen(w_dir, w_NM_output_file, 'w',32000);
NV_output_file := utl_file.fopen(w_dir, w_NV_output_file, 'w',32000);
OK_output_file := utl_file.fopen(w_dir, w_OK_output_file, 'w',32000);
OR_output_file := utl_file.fopen(w_dir, w_OR_output_file, 'w',32000);
SC_output_file := utl_file.fopen(w_dir, w_SC_output_file, 'w',32000);
TN_output_file := utl_file.fopen(w_dir, w_TN_output_file, 'w',32000);
VA_output_file := utl_file.fopen(w_dir, w_VA_output_file, 'w',32000);
WI_output_file := utl_file.fopen(w_dir, w_WI_output_file, 'w',32000);
WV_output_file := utl_file.fopen(w_dir, w_WV_output_file, 'w',32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
w_polnum := substr(w_newline,6,14);
for i in c1(w_polnum) loop
for j in c2(i.pol_id) loop
for k in c3(i.pol_id, j.st_cd_alpha) loop
w_val := k.st_cd_alpha||' '||k.prem_amt;
if k.st_cd_alpha = 'AL' then
utl_file.put_line(AL_output_file,w_val);
end if;
if k.st_cd_alpha = 'AR' then
utl_file.put_line(AR_output_file,w_val);
end if;
if k.st_cd_alpha = 'AZ' then
utl_file.put_line(AZ_output_file,w_val);
end if;
if k.st_cd_alpha = 'CO' then
utl_file.put_line(CO_output_file,w_val);
end if;
if k.st_cd_alpha = 'CT' then
utl_file.put_line(CT_output_file,w_val);
end if;
if k.st_cd_alpha = 'DE' then
utl_file.put_line(DE_output_file,w_val);
end if;
if k.st_cd_alpha = 'FL' then
utl_file.put_line(FL_output_file,w_val);
end if;
if k.st_cd_alpha = 'GA' then
utl_file.put_line(GA_output_file,w_val);
end if;
if k.st_cd_alpha = 'HI' then
utl_file.put_line(HI_output_file,w_val);
end if;
if k.st_cd_alpha = 'IA' then
utl_file.put_line(IA_output_file,w_val);
end if;
if k.st_cd_alpha = 'ID' then
utl_file.put_line(ID_output_file,w_val);
end if;
if k.st_cd_alpha = 'IL' then
utl_file.put_line(IL_output_file,w_val);
end if;
if k.st_cd_alpha = 'IN' then
utl_file.put_line(IN_output_file,w_val);
end if;
if k.st_cd_alpha = 'KS' then
utl_file.put_line(KS_output_file,w_val);
end if;
if k.st_cd_alpha = 'KY' then
utl_file.put_line(KY_output_file,w_val);
end if;
if k.st_cd_alpha = 'MD' then
utl_file.put_line(MD_output_file,w_val);
end if;
if k.st_cd_alpha = 'MI' then
utl_file.put_line(MI_output_file,w_val);
end if;
if k.st_cd_alpha = 'MN' then
utl_file.put_line(MN_output_file,w_val);
end if;
if k.st_cd_alpha = 'MO' then
utl_file.put_line(MO_output_file,w_val);
end if;
if k.st_cd_alpha = 'MS' then
utl_file.put_line(MS_output_file,w_val);
end if;
if k.st_cd_alpha = 'MT' then
utl_file.put_line(MT_output_file,w_val);
end if;
if k.st_cd_alpha = 'NC' then
utl_file.put_line(NC_output_file,w_val);
end if;
if k.st_cd_alpha = 'NE' then
utl_file.put_line(NE_output_file,w_val);
end if;
if k.st_cd_alpha = 'NM' then
utl_file.put_line(NM_output_file,w_val);
end if;
if k.st_cd_alpha = 'NV' then
utl_file.put_line(NV_output_file,w_val);
end if;
if k.st_cd_alpha = 'OK' then
utl_file.put_line(OK_output_file,w_val);
end if;
if k.st_cd_alpha = 'OR' then
utl_file.put_line(OR_output_file,w_val);
end if;
if k.st_cd_alpha = 'SC' then
utl_file.put_line(SC_output_file,w_val);
end if;
if k.st_cd_alpha = 'TN' then
utl_file.put_line(TN_output_file,w_val);
end if;
if k.st_cd_alpha = 'VA' then
utl_file.put_line(VA_output_file,w_val);
end if;
if k.st_cd_alpha = 'WI' then
utl_file.put_line(WI_output_file,w_val);
end if;
if k.st_cd_alpha = 'WV' then
utl_file.put_line(WV_output_file,w_val);
end if;
end loop;
end loop;
end loop;
EXCEPTION WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
END IF; --is_open
utl_file.fclose(input_file);
utl_file.fclose(AL_output_file);
utl_file.fclose(AR_output_file);
utl_file.fclose(AZ_output_file);
utl_file.fclose(CO_output_file);
utl_file.fclose(CT_output_file);
utl_file.fclose(DE_output_file);
utl_file.fclose(FL_output_file);
utl_file.fclose(GA_output_file);
utl_file.fclose(HI_output_file);
utl_file.fclose(IA_output_file);
utl_file.fclose(ID_output_file);
utl_file.fclose(IL_output_file);
utl_file.fclose(IN_output_file);
utl_file.fclose(KS_output_file);
utl_file.fclose(KY_output_file);
utl_file.fclose(MD_output_file);
utl_file.fclose(MI_output_file);
utl_file.fclose(MN_output_file);
utl_file.fclose(MO_output_file);
utl_file.fclose(MS_output_file);
utl_file.fclose(MT_output_file);
utl_file.fclose(NC_output_file);
utl_file.fclose(NE_output_file);
utl_file.fclose(NM_output_file);
utl_file.fclose(NV_output_file);
utl_file.fclose(OK_output_file);
utl_file.fclose(OR_output_file);
utl_file.fclose(SC_output_file);
utl_file.fclose(TN_output_file);
utl_file.fclose(VA_output_file);
utl_file.fclose(WI_output_file);
utl_file.fclose(WV_output_file);
END;
[Edit MC: add code tags]
[Updated on: Tue, 05 April 2016 03:28] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #649751 is a reply to message #649750] |
Tue, 05 April 2016 04:17  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
I'd use an external table for the input file then query the data in st_cd_alpha order so you only have to have one output file open at a time. Just concatenate the appropriate extension into the file_name variable each time.
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #649760 is a reply to message #649754] |
Tue, 05 April 2016 08:22 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's over-complicating it. If you use my approach you can have something like this:
DECLARE
..........
BEGIN
FOR rec IN (<select that queries external table and joins to three tables from the existing cursors, ordered by st_cd_alpha>) LOOP
IF rec.st_cd_alpha != l_prev_alpha OR l_prev_alpha IS NULL THEN
--got a new alpha code
IF l_prev_alpha IS NOT NULL THEN
--close existing file
utl_file.fclose(l_output_file);
END IF;
--define and open new file
l_output_file_name := 'wcpolstest.'||rec.st_cd_alpha;
l_output_file := utl_file.fopen(w_dir, l_output_file_name, 'w',32000);
END IF;
utl_file.put_line(l_output_file,rec.val);
--record current alpha
l_prev_alpha := rec.st_cd_alpha;
END LOOP;
--close last file
utl_file.fclose(l_output_file);
END;
Create the external table, fill in the query and the variable declarations and you should be good to go.
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650804 is a reply to message #649760] |
Wed, 04 May 2016 02:34 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Hello-
I am not sure where I am making mistake but the ouput_file in condition if "l_exists then" is not getting the correct file. The below code should write some lines in file 1, file 2, file 3 and again in file 1 but the last file 1 records are getting into file 3 because the current file is file 3. Depending on file_num from wcpols_submission_file table it should print the lines from input file into multiple files like PPEP_MI_1, _2 and _3.txt. Now the last records which should go to _1 file are getting into _3. Please guide me, Thanks
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
w_NewPolNum VARCHAR2(20);
w_NewRecTyp CHAR(2);
w_NewTranDt CHAR(5);
w_NewTranCd CHAR(2);
w_file_num NUMBER;
input_file utl_file.file_type;
output_file utl_file.file_type;
w_Pol_Header number := 0;
l_exists boolean;
l_size number;
l_block_size number;
BEGIN
dbms_output.enable(null);
w_dir := 'CENGROUP_OUTPUT';
w_input_file := 'wcpols_04152016_04222016_MI.txt';
input_file := utl_file.fopen(w_dir, w_input_file, 'r',32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
w_NewPolNum := substr(w_newline,6,14);
w_NewRecTyp := substr(w_newline,46,2);
w_NewTranDt := substr(w_newline,37,5);
w_TranTypCd := substr(w_newline,42,2);
if w_NewPolNum = substr(w_newline,6,14) and w_NewTranDt = substr(w_newline,37,5) and w_NewRecTyp in ('01','08') then
w_pol_header := w_pol_header + 1;
end if;
select file_num
into w_file_num
from wcpols_submission_files
where pol_header = w_pol_header;
utl_file.fgetattr( w_dir, 'PPEP_MI_'||w_file_num||'.txt', l_exists, l_size, l_block_size );
IF NOT ( l_exists ) THEN
output_file := utl_file.fopen(w_dir, 'PPEP_MI_'||w_file_num||'.txt', 'w',32000);
END IF;
if l_exists then
utl_file.put_line(output_file,w_file_num||'-'||w_NewPolNum||'-'||w_NewTranDt||'-'||w_TranTypCd||'-'||w_NewRecTyp);
end if;
EXCEPTION WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP; --main loop
END IF; --InputFile is_open
utl_file.fclose(input_file);
utl_file.fclose(output_file);
END;
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650805 is a reply to message #650804] |
Wed, 04 May 2016 03:04 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're only setting output_file when the file doesn't exist. You need to set it every time file_num changes. So you need to store the previous value of file_num to compare it to the current value in each iteration of the loop. You'll also need to close each file before opening a new one - so you need a close in the loop.
|
|
|
|
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650809 is a reply to message #650808] |
Wed, 04 May 2016 03:59 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Thank you SOO much @cookiemonster. I modified the code based on your inputs for checking previous file num with current one and now its working. Could you also please advise which exceptions should I handle in my code? and also is there any way I can get the line number frim input file while trapping the errors? Here is the modified code:-
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
w_NewPolNum VARCHAR2(20);
w_NewRecTyp CHAR(2);
w_NewTranDt CHAR(5);
w_TranTypCd CHAR(2);
w_file_num NUMBER;
input_file utl_file.file_type;
output_file utl_file.file_type;
w_Pol_Header number := 0;
w_prev_file_num number :=0;
BEGIN
dbms_output.enable(null);
w_dir := 'CENGROUP_OUTPUT';
w_input_file := 'wcpols_04152016_04222016_MI.txt';
--w_input_file := 'wcpols_04152016_04222016_NCCI.txt';
input_file := utl_file.fopen(w_dir, w_input_file, 'r',32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
w_NewPolNum := substr(w_newline,6,14);
w_NewRecTyp := substr(w_newline,46,2);
w_NewTranDt := substr(w_newline,37,5);
w_TranTypCd := substr(w_newline,42,2);
if w_NewPolNum = substr(w_newline,6,14) and w_NewTranDt = substr(w_newline,37,5) and w_NewRecTyp in ('01','08') then
w_pol_header := w_pol_header + 1;
end if;
select file_num
into w_file_num
from wcpols_submission_files
where pol_header = w_pol_header;
if w_file_num <> w_prev_file_num then
utl_file.fclose(output_file);
output_file := utl_file.fopen(w_dir, 'PPEP_MI_'||w_file_num||'.txt', 'a',32000);
end if;
utl_file.put_line(output_file,w_file_num||'-'||w_NewPolNum||'-'||w_NewTranDt||'-'||w_TranTypCd||'-'||w_NewRecTyp);
EXCEPTION WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP; --main loop
END IF; --InputFile is_open
w_prev_file_num := w_file_num;
utl_file.fclose(input_file);
utl_file.fclose(output_file);
END;
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650813 is a reply to message #650809] |
Wed, 04 May 2016 05:11 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're setting w_prev_file_num in the wrong place.
It'll always be 0 until after the loop is finished and at that point it's value doesn't matter.
So it's closing and opening files on every iteration of the loop.
You need to set w_prev_file_num inside the loop.
If you want the line number of the input file I'd just maintain a counter variable inside the loop.
|
|
|
|
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650824 is a reply to message #650822] |
Wed, 04 May 2016 09:12 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Are you saying that if you run the whole block of code again it appends the data to files created by the first run?
If so then, obviously that will happen, but will it actually be run multiple times in the real system?
And if it is why aren't you either:
a) moving the files out the way once you're done
b) embedding a timestamp in the file name so each run generates different files.
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650830 is a reply to message #650825] |
Wed, 04 May 2016 14:59 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Checking for an existing file is easy (pseudo code follows)
begin
output_file := utl_file.fopen(w_dir,'PPEP_MI_' || w_file_num || '.txt','r',32000);
utl_file.fclose(output_file);
-- skip to the next file. It already exists
when utl_file.invalid_operation then
utl_file.fopen(w_dir,'PPEP_MI_' || w_file_num || '.txt','a',32000);
end;
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650837 is a reply to message #650830] |
Thu, 05 May 2016 02:42 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Thank you Bill, I'll try for this later but now I need some input for this issue. The below code is generating 3 files (PPEP_1, PPEP_2 and PPEP_3) based on file_num from wcpos_submission_files table. I need to print the '9999' record at the end of each files (1,2 and 3) but this code is writing just on files 2 and 3. Its not writing on file 1. I am trying to write this record at last line when file num chages but its not working as desired. Please help to locate my mistake.
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
w_NewPolNum VARCHAR2(20);
w_NewRecTyp CHAR(2);
w_NewTranDt CHAR(5);
w_LinkRec VARCHAR2(100);
w_StateCd CHAR(2);
w_RecTypCd CHAR(2);
w_ExpRtgCancReinsCd CHAR(1);
w_CancTypCd CHAR(1);
w_ResnCancCd CHAR(2);
w_EmpLeasCd CHAR(1);
w_ExpModCd CHAR(1);
w_OutRecord VARCHAR2(301);
w_if_NCCI NUMBER;
w_TranTypCd CHAR(2);
w_file_num NUMBER;
input_file utl_file.file_type;
output_file utl_file.file_type;
w_Pol_Header number := 0;
w_prev_file_num number := 0;
BEGIN
dbms_output.enable(null);
w_dir := 'CENGROUP_OUTPUT';
w_input_file := 'wcpols_04152016_04222016_MI.txt';
w_if_NCCI := instr(w_input_file,'NCCI');
input_file := utl_file.fopen(w_dir, w_input_file, 'r', 32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
IF substr(w_newline, 46, 2) <> '99' THEN
w_NewPolNum := substr(w_newline, 6, 14);
w_NewRecTyp := substr(w_newline, 46, 2);
w_NewTranDt := substr(w_newline, 37, 5);
w_TranTypCd := substr(w_newline, 42, 2);
IF w_NewPolNum = substr(w_newline, 6, 14) AND
w_NewTranDt = substr(w_newline, 37, 5) AND
w_NewRecTyp in ('01', '08') then
w_pol_header := w_pol_header + 1;
END IF;
SELECT file_num
INTO w_file_num
FROM wcpols_submission_files
WHERE pol_header = w_pol_header;
IF w_file_num <> w_prev_file_num THEN
if utl_file.is_open(output_file) then --i'm assuming at this location it'll write 9999 at each file.
utl_file.put_line(output_file, '9999');--but its skipping file 1. (writing just on 2 and 3)
end if;
utl_file.fclose(output_file);
output_file := utl_file.fopen(w_dir,
'PPEP_MI_' || w_file_num || '.txt',
'a',
32000);
IF w_if_NCCI > 0 THEN
utl_file.put_line(output_file, 'This is NCCI');
END IF;
END IF;
utl_file.put_line(output_file, w_newline);
END IF; --99
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
w_prev_file_num := w_file_num;
END LOOP; --main loop
END IF; --InputFile is_open
utl_file.fclose(input_file);
utl_file.fclose(output_file);
END;
|
|
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650849 is a reply to message #650848] |
Thu, 05 May 2016 07:54 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
I have tried that too but in that case the 9999 record is being written just on file 1 and not in 2 and 3. Also I need to print the line counts too so I could not use the w_line_count variable if I put this line after end loop. Please advise. Here is the code with this change-
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
w_NewPolNum VARCHAR2(20);
w_NewRecTyp CHAR(2);
w_NewTranDt CHAR(5);
w_RecTypCd CHAR(2);
w_if_NCCI NUMBER;
w_TranTypCd CHAR(2);
w_file_num NUMBER;
input_file utl_file.file_type;
output_file utl_file.file_type;
w_Pol_Header NUMBER := 0;
w_prev_file_num NUMBER := 0;
w_line_count NUMBER := 0;
w_FileSt VARCHAR(4);
w_Lable CHAR(14);
w_Email CHAR(31);
w_DataTypCd CHAR(3);
w_DataRecCd CHAR(5);
w_VersId CHAR(8);
w_DataPrvdr CHAR(5) := '14095';
BEGIN
dbms_output.enable(null);
w_dir := 'CENGROUP_OUTPUT';
w_input_file := 'wcpols_04152016_04222016_MI.txt';
w_if_NCCI := instr(w_input_file,'MI');
w_FileSt := substr(substr(w_input_file,26),1,(instr(substr(w_input_file,26),'.txt')-1));
input_file := utl_file.fopen(w_dir, w_input_file, 'r', 32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
IF substr(w_newline, 46, 2) <> '99' THEN
w_NewPolNum := substr(w_newline, 6, 14);
w_NewRecTyp := substr(w_newline, 46, 2);
w_NewTranDt := substr(w_newline, 37, 5);
w_TranTypCd := substr(w_newline, 42, 2);
IF w_NewPolNum = substr(w_newline, 6, 14) AND
w_NewTranDt = substr(w_newline, 37, 5) AND
w_NewRecTyp in ('01', '08') then
w_pol_header := w_pol_header + 1;
END IF;
SELECT file_num
INTO w_file_num
FROM wcpols_submission_files
WHERE pol_header = w_pol_header;
IF w_file_num <> w_prev_file_num THEN
/* IF utl_file.is_open(output_file) THEN
utl_file.put_line(output_file, '9999'||w_line_count||CHR(13));
END IF; */
utl_file.fclose(output_file);
w_line_count := 0;
output_file := utl_file.fopen(w_dir,'PPEP_MI_' || w_file_num || '.txt','a',32000);
IF w_if_NCCI > 0 THEN
utl_file.put_line(output_file, 'This is NCCI');
w_line_count := w_line_count + 1;
END IF;
END IF;
utl_file.put_line(output_file, w_newline);
w_line_count := w_line_count + 1;
END IF; --99
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
w_prev_file_num := w_file_num;
END LOOP; --main loop
END IF; --InputFile is_open
utl_file.put_line(output_file, '9999'||/*w_line_count||*/CHR(13));
utl_file.fclose(input_file);
utl_file.fclose(output_file);
END;
|
|
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650882 is a reply to message #650853] |
Fri, 06 May 2016 01:41 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
I am facing another issue while writing the files. E.g. the input file contains the following records and based on validations it should go to specific file.
rec1 this should go into file 1
rec2 this should go into file 1
rec3 this should go into file 2
rec4 this should go into file 3
rec5 this should go into file 1
All the 3 output files should contain 'This is NCCI' line at the top line and total line_count at the last line. The below code generates the 3 files but in the file 1 there are 2 'This is NCCI' at the top of rec 1 and rec2 becuase I put the code in 'IF w_file_num <> w_prev_file_num THEN' this line is to check which file the record should go. Actually it should write 'This is NCCI' just for 1 time at top of file 1. Also the line_count is for coming for each record. When rec1 and rec5 is written in file1 then line count should be 2 and printed at last of rec5 but now its coming at last of both records and invidual line count of a record. I am not sure where should I put the code so it should treat the rec5 as the part of rec1 as its being written on file1 itself and not at a separate file. Please advise your thoughts so I could modify accordingly.
say the desired output is
File 1
---------
This is NCCI
rec1
rec5
99992
but currently its writing as
File 1
---------
This is NCCI
rec1
99991
This is NCCI
rec5
99991
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
w_NewPolNum VARCHAR2(20);
w_NewRecTyp CHAR(2);
w_NewTranDt CHAR(5);
w_RecTypCd CHAR(2);
w_if_Not_NCCI NUMBER;
w_TranTypCd CHAR(2);
w_file_num NUMBER;
input_file utl_file.file_type;
output_file utl_file.file_type;
w_Pol_Header NUMBER := 0;
w_prev_file_num NUMBER := 0;
w_line_count NUMBER := 0;
BEGIN
dbms_output.enable(null);
w_dir := 'CENGROUP_OUTPUT';
w_input_file := 'wcpols_04152016_04222016_MI.txt';
w_if_Not_NCCI := instr(w_input_file,'NCCI');
w_FileSt := substr(substr(w_input_file,26),1,(instr(substr(w_input_file,26),'.txt')-1));
input_file := utl_file.fopen(w_dir, w_input_file, 'r', 32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
IF substr(w_newline, 46, 2) <> '99' THEN
w_NewPolNum := substr(w_newline, 6, 14);
w_NewRecTyp := substr(w_newline, 46, 2);
w_NewTranDt := substr(w_newline, 37, 5);
w_TranTypCd := substr(w_newline, 42, 2);
IF w_NewPolNum = substr(w_newline, 6, 14) AND
w_NewTranDt = substr(w_newline, 37, 5) AND
w_NewRecTyp in ('01', '08') then
w_pol_header := w_pol_header + 1;
END IF;
SELECT file_num
INTO w_file_num
FROM wcpols_submission_files
WHERE pol_header = w_pol_header;
IF w_file_num <> w_prev_file_num THEN
IF utl_file.is_open(output_file) THEN
utl_file.put_line(output_file, '9999'||w_line_count||CHR(13)); -- this is writing for rec1 and rec5 separately
END IF;
utl_file.fclose(output_file);
w_line_count := 0;
output_file := utl_file.fopen(w_dir,'PPEP_MI_' || w_file_num || '.txt','a',32000);
IF w_if_Not_NCCI = 0 THEN
utl_file.put_line(output_file, 'This IS NCCI'||CHR(13)); -- this is writing for rec1 and rec5 separately
w_line_count := w_line_count + 1;
END IF;
END IF;
utl_file.put_line(output_file, w_newline);
w_line_count := w_line_count + 1;
END IF; --99
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
w_prev_file_num := w_file_num;
END LOOP; --main loop
END IF; --InputFile is_open
utl_file.put_line(output_file, '9999'||w_line_count||CHR(13));
utl_file.fclose(input_file);
utl_file.fclose(output_file);
END;
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650883 is a reply to message #650882] |
Fri, 06 May 2016 02:34 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
I used utl_file.fgetattr to check if file size is more than zero and if its 0 then print 'This is NCCI' so now this line is coming at the top of file 1 just for 1 time and not on every time the record changes. BUT still the line count is not correct. For file1 its coming as
File 1
-------
This is NCCI
rec1
99991
rec5
99991
it should be
File 1
-------
This is NCCI
rec1
rec5
99992
Any thoughts on how I should modify the code? I tried to set w_line_count := 0 IF l_size = 0 but it didn't give the correct line count. The line count should be the total for rec1 and rec5 lines. Please assist.
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
w_NewPolNum VARCHAR2(20);
w_NewRecTyp CHAR(2);
w_NewTranDt CHAR(5);
w_RecTypCd CHAR(2);
w_if_Not_NCCI NUMBER;
w_TranTypCd CHAR(2);
w_file_num NUMBER;
input_file utl_file.file_type;
output_file utl_file.file_type;
w_Pol_Header NUMBER := 0;
w_prev_file_num NUMBER := 0;
w_line_count NUMBER := 0;
l_exists boolean;
l_size number;
l_block_size number;
BEGIN
dbms_output.enable(null);
w_dir := 'CENGROUP_OUTPUT';
w_input_file := 'wcpols_04152016_04222016_MI.txt';
w_if_Not_NCCI := instr(w_input_file,'NCCI');
w_FileSt := substr(substr(w_input_file,26),1,(instr(substr(w_input_file,26),'.txt')-1));
input_file := utl_file.fopen(w_dir, w_input_file, 'r', 32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
IF substr(w_newline, 46, 2) <> '99' THEN
w_NewPolNum := substr(w_newline, 6, 14);
w_NewRecTyp := substr(w_newline, 46, 2);
w_NewTranDt := substr(w_newline, 37, 5);
w_TranTypCd := substr(w_newline, 42, 2);
IF w_NewPolNum = substr(w_newline, 6, 14) AND
w_NewTranDt = substr(w_newline, 37, 5) AND
w_NewRecTyp in ('01', '08') then
w_pol_header := w_pol_header + 1;
END IF;
SELECT file_num
INTO w_file_num
FROM wcpols_submission_files
WHERE pol_header = w_pol_header;
IF w_file_num <> w_prev_file_num THEN
IF utl_file.is_open(output_file) THEN
utl_file.put_line(output_file, '9999'||w_line_count||CHR(13));
END IF;
utl_file.fclose(output_file);
w_line_count := 0;
output_file := utl_file.fopen(w_dir,'PPEP_MI_' || w_file_num || '.txt','a',32000);
utl_file.fgetattr( w_dir, 'PPEP_MI_'||w_file_num||'.txt', l_exists, l_size, l_block_size );
IF w_if_Not_NCCI = 0 and l_size = 0 THEN --if file is new then print this line
utl_file.put_line(output_file, 'This is NCCI'CHR(13));
w_line_count := w_line_count + 1;
END IF;
END IF;
utl_file.put_line(output_file, w_newline);
w_line_count := w_line_count + 1;
END IF; --99
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
w_prev_file_num := w_file_num;
END LOOP; --main loop
END IF; --InputFile is_open
utl_file.put_line(output_file, '9999'||w_line_count||CHR(13));
utl_file.fclose(input_file);
utl_file.fclose(output_file);
END;
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650884 is a reply to message #650883] |
Fri, 06 May 2016 02:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You really should follow my initial suggestion of using an external table for the input file.
This has the big advantage of letting you order the data as it is read from the input file so you can ensure data is read in the order it should be written to the other files.
This makes it trivally easy to know:
a) when you're at the start of a file
b) when you're at the end of a file
c) How many records you've written to the file.
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650885 is a reply to message #650884] |
Fri, 06 May 2016 02:44 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you don't do that the only way to get correct line counts is to have an array that stores file number (or name) and line count.
Then after you've written all lines to all files you can loop over the array and append the correct line count to each file.
|
|
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650888 is a reply to message #650887] |
Fri, 06 May 2016 03:54 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
How many times do I have to say "use an external table" before you actually try it?
I get the feeling you don't know what external tables are, if so - have a read in the documentation. They are the perfect solution to all the problems you are facing.
|
|
|
|
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650894 is a reply to message #650891] |
Fri, 06 May 2016 05:33 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well if that's true how is your current approach going to work?
If you can write a statement that just uses substr to get the data you want from each line then you can create an external table that does the same.
|
|
|
|
|
|
| Re: Dynamic File Creation using UTL_FILE [message #650907 is a reply to message #650895] |
Fri, 06 May 2016 08:01 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
To do the array approach you need to add a new entry to the array each time you use a file number you've not used before then update the count for that file for each line written to it.
Then after you've finished looping over the input file, so after the loop, loop over the array and append the 9999 line to each file in turn.
You can't do this inside the existing loop as you can't know that you're not going to encounter more rows for the current file later on.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 23:38:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
