Help with CURSOR question, please?
Date: Thu, 23 Jul 1998 11:26:23 -0400
Message-ID: <6p7knt$dq1$1_at_winter.news.erols.com>
Can you offer any suggestions please?
I am trying to do the following:
- open cursor cur1 to retreive all a.id rows in table a
- for each a.id retreived from table a do the following:
- open cursor cur2 and retreive only those rows from table b where a.id = b.e
- write some text to a file
The problem I am having is with how to restrict cur2 to retreive only those rows from table b based on the value of a.id in cur1 each time the LOOP is encountered.
Basically, where does the where statement go???
Any advice would be much appreciated.
Yours,
Elliot G.
My routine is below:
DECLARE out_file1 TEXT_IO.FILE_TYPE;
CURSOR cur1
IS
SELECT id
FROM a
[Quoted] ORDER BY a.id ASC;
id1 a.id%type;
CURSOR cur2
IS
SELECT e, f
FROM b
ORDER BY e, f ASC;
e1 b.e%type;
f1 b.f%type;
BEGIN out_file1 := TEXT_IO.FOPEN('c:\myfile', 'w');
TEXT_IO.PUT_LINE(out_file1, 'mytext');
[Quoted] FOR cur1_rec IN cur1 LOOP
TEXT_IO.PUT_LINE(out_file1, 'more text');
OPEN cur2;
LOOP
[Quoted] FETCH cur2 INTO e1, f1;
IF (cur2%found) THEN TEXT_IO.PUT_LINE(out_file1, to_char(e1)||','||to_char(f1)); ELSE EXIT; END IF;
END LOOP;
CLOSE cur2;
TEXT_IO.PUT_LINE(out_file1, 'some more text');
END LOOP; TEXT_IO.FCLOSE(out_file1);
END; Received on Thu Jul 23 1998 - 17:26:23 CEST