pl/sql magic help request

From: Bill Kincaid <wkincaid_at_mindspring.com>
Date: 1998/03/12
Message-ID: <35081754.1846@mindspring.com>#1/1

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.
1032OW THANKS with 312 records requiring 312 record written to 1032OWTH.txt
1032OW SERVICE with 123 records requiring 123 written to 1032OWSE.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 LOOP;
  CLOSE client_cur;

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