Referring to a cursor in an anonymous block

Question

How can you refer to a cursor's specific value if there are multiple values returned?

DECLARE
  X INTEGER;
  CURSOR c1 IS SELECT col1, col2, col3.....;
BEGIN
  OPEN c1;
  LOOP
    EXIT WHEN c1%NOTFOUND;
    FETCH (col2 from c1) INTO X;
  END LOOP;
END;

Answer 1

Why would you want to? Presumably, if you are selecting three columns in your cursor declaration, you need all three columns in your code, so you would need to fetch all three columns into three separate local variables, i.e.

DECLARE
  x      integer;
  y      integer;
  z      integer;
  CURSOR c1
      IS SELECT column1, column2, column3
           FROM some_table;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 
     INTO x, y, z;
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
  CLOSE c1;
END;

Alternately, you can declare a record type based on the cursor declaration and fetch into that

DECLARE
  CURSOR c1
      IS SELECT column1, column2, column3
           FROM some_table;
  c1_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 
     INTO c1_rec;
    dbms_output.put_line( c1_rec.column2 );
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
  CLOSE c1;
END;

You can also get rid of the explicit loop entirely which is generally the preferred approach since you don't have to worry about cursor leaks and (in modern versions) Oracle can automatically do bulk collects for you

DECLARE
  CURSOR c1
      IS SELECT column1, column2, column3
           FROM some_table;
BEGIN
  FOR c1_rec IN c1
  LOOP
    dbms_output.put_line( c1_rec.column2 );
  END LOOP;
END;