Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> pl/sql magic help request
Hi:
O 7.2 / Forms 4.5
I am having trouble preparing for an export to multiple text files. From a table called letters (some of the fields are listed below),
Client_No A6
Type A8
Due_Date D
Cust_Seq N10
name A40
.......etc.
I need to export all letter records which are in the same client_no and type subset into an ascii delimeted file.
I had hoped to perform this task with one pl/sql procedure but I met with no success.
There are multiple records per client_no, type.
Within each group,
Client_no Type
========= =======
0459EU SERVICE might have 58 records which I need to export 58 text
records to a file named 0459EOSE.txt. 0459EU THANKS might have 24 records which I need to export 24 text records to a file named 0459EUTH.txt.0637IW THANKS with 134 records which require a 134 records stored in 0637IWTH.txt.
etc.
You see, I need to group all records from the letters table by Client_No and Type into text files with the text file names indicating what is contained within.
I had hoped to use a cursor within a cursor to facilitate my efforts
however no success.
Then I felt if I created an intermediate storage table then I could
better operate using a cursor.
The following procedure populated a table called Client_Hold:
BEGIN
delete client_hold;
insert into client_hold select distinct client_no, type from letters
where processed_flag is null;
commit;
END;
Resulting in a Table Client_Hold with the
structure and values of:
Client_No Type
0459EU SERVICE
0459EU THANKS
0637IW SERVICE
1032OW THANKS
1032OW SERVICE
1032OW CONGRATS
1153EU THANKS
Using this ‘control file’ I felt I could perform my task as follows:
CURSOR client_cur IS
select client_no, type, ...... from client_hold; cc client_cur%rowtype; x_no varchar2(6); x_name varchar2(40); x_... ......., x_ ... .......; etc................
BEGIN
if not client_cur%ISOPEN then
open client_cur;
end if;
FETCH client_cur into cc;
WHILE client_cur%found
LOOP select client_no, name into x_no, x_name, ...., ...., ..... from letters where cc.client_no = client_no and cc.type = type and processed_flag is null; ...perform steps to write out ascii file here.... FETCH client_cur into cc;
END; Errors were the result.
It seems to me that one fairly simple procedure could group these records and allow writing to ascii text files simply.
Anyone have suggestions (perhaps even suggestions to avoid the intermediate Client_Hold table)?
Thanks in advance
Bill Kincaid Received on Thu Mar 12 1998 - 00:00:00 CST
![]() |
![]() |