Home » SQL & PL/SQL » SQL & PL/SQL » Writing multiple Files using UTL_FILE (11g, Windows XP)
Writing multiple Files using UTL_FILE [message #659954] Mon, 06 February 2017 05:31 Go to next message
siva27
Messages: 38
Registered: April 2007
Location: Chennai
Member

Dear All,

I have code for UTL_FILE, every thing is working ok, multiple files are creating.
But the problem for each file all the data is coming.

Assume that there are 20 records

11111.SIF -- 15 records
22222.SIF -- 5 records

After running the program for each file 20 records created, but for me 15 records file and 5 records in 2nd should create.

Please check and advice me.

Code
------
create or replace procedure xxaj_utl_file
as

v_file_handle    utl_file.file_type;
p_end_date date := '31-DEC-2016';

cursor c1 is

  select distinct employer_id,to_char(sysdate, 'YYMMDD') cuur_year,to_char (sysdate, 'HH24MISS') time_stamp
  from   xxnmc_hr_wps_employee_det
  where  income_fixed <> 0;
  
cursor c_supplier(p_employer_id number) is

  select z.employer_id, z.test_results
  from(
  select  employer_id,trim(edr)||','||personal_number||','||agent_id||','||account_number||','||pay_start_date||','||pay_end_date||','||days_in _period
         ||','||income_fixed||'.'||'00'||','||'0.00'||','||' 0' test_results
  from   xxnmc_hr_wps_employee_det
  where  income_fixed <> 0
  union 
  select employer_id,'SCR'||','||employer_id||','||nvl (bank_routing_number,'401920110')||','||to_char (sysdate, 'YYYY-MM-DD')
         ||','||to_char (sysdate, 'HH24MI')||','||to_char(to_date(pay_end_date,'YYYY-MM-DD'),'MMYYYY')||','||count(personal_number)
         ||','||sum(income_fixed)||'.'||'00'||','||'AED'||','||to_char(to_date(p_end_date,'DD-MON-YYYY'),'MONYY')
  from   xxnmc_hr_wps_employee_det
  where  income_fixed <> 0
  group by employer_id,'SCR'||','||employer_id||','||nvl (bank_routing_number,'401920110')||','||to_char (sysdate, 'YYYY-MM-DD')
         ||','||to_char (sysdate, 'HH24MI')||','||to_char(to_date(pay_end_date,'YYYY-MM-DD'),'MMYYYY')           
  ) z;
     
begin

  for r1 in c1 loop
 
    v_file_handle := utl_file.fopen('XXNMC_PAYROLL_WPS',r1.employer_id||r1.cuur_year||r1.time_stamp||'.SIF', 'W',32767);
    
  for c_rec in c_supplier (r1.employer_id) loop
  
    utl_file.put_line(v_file_handle,c_rec.test_results);  
   
    dbms_output.put_line('Read access OK');
    
  end loop;
  
    utl_file.fclose(v_file_handle);

  commit;
  
  end loop;

exception
  when others then
    utl_file.fclose_all;
    dbms_output.put_line('other exception');

end xxaj_utl_file;
/
please help me in breaking the file with proper count.

Thanks



CM: added code tags

[Updated on: Mon, 06 February 2017 05:56] by Moderator

Report message to a moderator

Re: Writing multiple Files using UTL_FILE [message #659955 is a reply to message #659954] Mon, 06 February 2017 05:58 Go to previous messageGo to next message
cookiemonster
Messages: 13265
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read? - I've added them for you this time.

So the inner loop is supposed to get all the records for the employer_id from the outer loop?
You need to have something in the where clause c_supplier that actually does that.
Re: Writing multiple Files using UTL_FILE [message #659956 is a reply to message #659954] Mon, 06 February 2017 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

exception
  when others then
    utl_file.fclose_all;
    dbms_output.put_line('other exception');

There should be "RAISE;" after this.
Read WHEN OTHERS.

Re: Writing multiple Files using UTL_FILE [message #659957 is a reply to message #659956] Mon, 06 February 2017 06:13 Go to previous messageGo to next message
siva27
Messages: 38
Registered: April 2007
Location: Chennai
Member

You mean this way, please help me


CREATE OR REPLACE PROCEDURE xxaj_utl_file
AS
v_file_handle UTL_FILE.file_type;
p_end_date DATE := '31-DEC-2016';

CURSOR c1
IS
SELECT DISTINCT
employer_id,
TO_CHAR (SYSDATE, 'YYMMDD') cuur_year,
TO_CHAR (SYSDATE, 'HH24MISS') time_stamp
FROM xxnmc_hr_wps_employee_det
WHERE income_fixed <> 0;

CURSOR c_supplier (p_employer_id NUMBER)
IS
SELECT z.employer_id, z.test_results
FROM (SELECT employer_id,
TRIM (edr)
|| ','
|| personal_number
|| ','
|| agent_id
|| ','
|| account_number
|| ','
|| pay_start_date
|| ','
|| pay_end_date
|| ','
|| days_in_period
||','||income_fixed||'.'||'00'||','||'0.00'||','||' 0' test_results
from xxnmc_hr_wps_employee_det
where income_fixed <> 0
union
select employer_id,'SCR'||','||employer_id||','||nvl (bank_routing_number,'401920110')||','||to_char (sysdate, 'YYYY-MM-DD')
||','||to_char (sysdate, 'HH24MI')||','||to_char(to_date(pay_end_date,'YYYY-MM-DD'),'MMYYYY')||','||count(personal_number)
||','||sum(income_fixed)||'.'||'00'||','||'AED'||','||to_char(to_date(p_end_date,'DD-MON-YYYY'),'MONYY')
from xxnmc_hr_wps_employee_det
where income_fixed <> 0
group by employer_id,'SCR'||','||employer_id||','||nvl (bank_routing_number,'401920110')||','||to_char (sysdate, 'YYYY-MM-DD')
||','||to_char (sysdate, 'HH24MI')||','||to_char(to_date(pay_end_date,'YYYY-MM-DD'),'MMYYYY')
) z;

begin

for r1 in c1 loop

v_file_handle := utl_file.fopen('XXNMC_PAYROLL_WPS',r1.employer_id||r1.cuur_year||r1.time_stamp||'.SIF', 'W',32767);

for c_rec in c_supplier (r1.employer_id) loop

utl_file.put_line(v_file_handle,c_rec.test_results);

dbms_output.put_line('Read access OK');

end loop;

utl_file.fclose(v_file_handle);

commit;

end loop;

exception
when others then
utl_file.fclose_all;
dbms_output.put_line('other exception');
raise;
end xxaj_utl_file;

Re: Writing multiple Files using UTL_FILE [message #659958 is a reply to message #659957] Mon, 06 February 2017 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
please help me
You have been told many times to format your post.
You ask for help then FIRST help us to help you.

Please read How to use [code] tags and make your code easier to read.

Re: Writing multiple Files using UTL_FILE [message #659959 is a reply to message #659958] Mon, 06 February 2017 06:30 Go to previous messageGo to next message
siva27
Messages: 38
Registered: April 2007
Location: Chennai
Member

Thanks alot Mr cookiemonster,


It helped me alot.

Thanks once again.
Re: Writing multiple Files using UTL_FILE [message #659960 is a reply to message #659959] Mon, 06 February 2017 08:00 Go to previous message
Michel Cadot
Messages: 65833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OraFAQ Forum Guide

Point #12. If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

Previous Topic: Performance tuning in delete operation
Next Topic: Function To Reverese The Phone Number
Goto Forum:
  


Current Time: Tue Oct 16 03:55:14 CDT 2018