Help with CURSOR question, please?

From: <agibbons_at_erols.com>
Date: Thu, 23 Jul 1998 11:25:06 -0400
Message-ID: <6p7klg$dh0$1@winter.news.erols.com>

Can you offer any suggestions please?

I am trying to do the following:

1. open cursor cur1 to retreive all a.id rows in table a
2. for each a.id retreived from table a do the following:
   1. open cursor cur2 and retreive only those rows from table b where a.id = b.e
   2. 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
   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');

 FOR cur1_rec IN cur1 LOOP

  TEXT_IO.PUT_LINE(out_file1, 'more text');

  OPEN cur2;
   LOOP
    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 - 10:25:06 CDT