Re: Help with CURSOR question, please?

From: Krishnan Ranganathan <krishnan_at_cdgpd.com>
Date: Thu, 23 Jul 1998 09:22:52 -0700
Message-ID: <6p7oap$eoa$1@brokaw.wa.com>

I had written a simple PL/SQL block to display the table name, column name and the constraints. This uses the concept of restricting the second cursor based on the values of the first cursor. Here it goes

----------------------------------------Begin PL/SQL
lock  ------------------------
DECLARE
         CURSOR csr_airline IS
          SELECT tname from tab;

          CURSOR csr_table(wk_tname char) IS
          select cname,coltype,width
           from col
           where tname = wk_tname;

         CURSOR csr_constraint(wk_tname char) IS
         SELECT constraint_name,column_name
         from dba_cons_columns
          where table_name= wk_tname;

BEGIN
  dbms_output.enable(100000);
  BEGIN   FOR ipc_record IN csr_airline
  LOOP

  dbms_output.put_line('_            _');
  dbms_output.put_line('_            _');

dbms_output.put_line('_________________Table_name:--->'||ipc_record.tname||'
_______________________');
   dbms_output.put_line('COLUMN NAME  COLUMN TYPE WIDTH     ');
   FOR temp_table in csr_table(ipc_record.tname)
        LOOP
            dbms_output.put_line(temp_table.cname||'

'||temp_table.coltype||' '||temp_table.width);

          END LOOP;

dbms_output.put_line('______________________________________________________
_________________');
 dbms_output.put_line('_________________Constraints for
'||ipc_record.tname||'________________________');

 dbms_output.put_line('CONSTRAINT NAME COLUMNNAME');  FOR temp_constraint in csr_constraint(ipc_record.tname)

        LOOP
         dbms_output.put_line(temp_constraint.constraint_name||'

'||temp_constraint.column_name);

   END LOOP;

dbms_output.put_line('______________________________________________________

_______________');
  END LOOP;
  END;
END;

----------------------------- End of the PL/SQL
block -------------------------------

Hope it helps....

agibbons_at_erols.com wrote in message <6p7klg$dh0$1_at_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:
> a) open cursor cur2 and retreive only those rows
> from table b where a.id = b.e
> b) 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 - 11:22:52 CDT