Hello
I am writing a .txt file from an Oracle db using the utl_file function and so far I am able to create the file however the result in the file is not being the way it should be displayed. I need to make several records, to make it simple I will list 3 type of records
1 record(header)
5 record(batch header)
6 record ( details record)
so I wrote a pl/sql package and in this procedure I have 3 cursors ( 1 per record type),
the 1 record will be always the same
and in this example let's say I have 3 suppliers so :
cursor_1 returns 1 line( will not change)
cursor_5 returns 3 lines( this has the lines for the supplier_no with the country and the bank_id, so each supplier has it's own bank_id, the supplier_no is unique )
cursor_6 returns 3 lines( this has the total amount per supplier_no)
each one of these lines belong to a particular supplier_no so I should I have a total of six
I have a for loop that's creating the file and in there I have something like this below
CREATE OR REPLACE PACKAGE BODY create_file_test
as
procedure create_file(
p_company in varchar2,
p_supplier_no varchar2
)
is
begin
declare
v_file_handle utl_file.FILE_TYPE;
v_file_name varchar2(50);
-- record 1
cursor cursor_1
is
select company_info_ ,company_info_2
from tables;
-- record 5
cursor cursor_2
is
select supplier_no,supplier_name,supplier_bank,supplier_country
from tables
where supplier_no =nvl(p_supplier_no,supplier_no)
and company=P_company;
-- record 6
cursor cursor_6
is
select supplier_no,supplier_name,supplier_currency,supplier_total_paid_amount
from tables
where supplier_no =nvl(p_supplier_no,supplier_no)
and company=P_company;
v_file_handle := utl_file.FOPEN('location'
, v_file_name
|| '.'
|| to_char(sysdate, 'YYYYMMDDHHMISS')
|| '.txt', 'W');
for rec1 in cursor_1 loop -
utl_file.PUT_LINE(v_file_handle
,rec1.company_info_
|| rec1.company_info_2
end loop;
for rec5 in cursor_5 loop
utl_file.PUT_LINE(v_file_handle
,rec5.supplier_no
|| rec5.supplier_name
|| rec5.supplier_bank
|| rec5.supplier_country
end loop;
for rec6 in cursor_6 loop
utl_file.PUT_LINE(v_file_handle
,rec5.supplier_no
|| rec5.supplier_name
|| rec5.supplier_currency
|| rec5.supplier_total_paid_amount
end loop;
utl_file.FCLOSE(v_file_handle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);
NULL;
end create_file;
end create_file_test;
and I am getting the result like this
1companyinformation
5supplier1,supplier_name,supplier_country
5supplier2,supplier_name,supplier_country
5supplier3,supplier_name,supplier_country
6supplier1,supplier_name,supplier_paid_amount
6supplier2,supplier_name,supplier_paid_amount
6supplier2,supplier_name,supplier_paid_amount
what I want to is to list the
1 record then
5 record for supplier1 then
6 record for supplier1 then start over
5 record for supplier2
6 record for supplier2 then
5 record for supplier3
6 record for supplier3
so it should look like this
1companynformation
5supplier1,supplier_name,supplier_country
6supplier1,supplier_name,supplier_paid_amount
5supplier2,supplier_name,supplier_country
6supplier2,supplier_name,supplier_paid_amount
5supplier3,supplier_name,supplier_country
6supplier2,supplier_name,supplier_paid_amount
Hope this make sense and someone can help
Thank you