6.6.1 Matching Column List with INTO Clause

When you fetch into a list of variables, the number of variables must match the number of expressions in the SELECT list of the cursor. When you fetch into a record, the number of columns in the record must match the number of expressions in the SELECT list of the cursor.

If you do not match up the column list in the cursor's query with the INTO elements in the FETCH, you will receive the following compile error:

PLS-00394: wrong number of values in the INTO list of a FETCH statement

Let's look at variations of FETCH to see what will and will not work. Suppose I have declared the following cursor, records, and variables:

• Cursor that selects just three columns from dr_seuss table and a record based on that cursor:

  CURSOR green_eggs_cur (character_in IN VARCHAR2)
  IS
     SELECT ham_quantity, times_refused, excuse
       FROM dr_seuss
     WHERE book_title = 'GREEN EGGS AND HAM'
        AND character_name = character_in;
  green_eggs_rec green_eggs_cur%ROWTYPE;

• Cursor for all columns in dr_seuss table and a record based on the dr_seuss table:

  CURSOR dr_seuss_cur
  IS
     SELECT * FROM dr_seuss;
  dr_seuss_rec dr_seuss_cur%ROWTYPE;

• Programmer-defined record type which contains all three of the green_eggs_cur columns, followed by declaration of record:

  TYPE green_eggs_rectype IS RECORD
     (ham# dr_seuss.ham_quantity%TYPE;
      saidno# dr_seuss.times_refused%TYPE,
      reason dr_seuss.excuse%TYPE);
  full_green_eggs_rec green_eggs_rectype;

• Programmer-defined record type which contains only two of the three cursor columns, following by declaration of record:

  TYPE partial_green_eggs_rectype IS RECORD
     (ham# dr_seuss.ham_quantity%TYPE;
      saidno# dr_seuss.times_refused%TYPE);
  partial_rec partial_green_eggs_rectype;

• A set of local PL/SQL variables:

  ham_amount NUMBER;
  refused_count INTEGER;
  lousy_excuse VARCHAR2(60);

Now that everything is declared, I then OPEN the cursor for the "Sam I Am" character (passed as an argument) as follows:

OPEN green_eggs_cur ('Sam I Am');

All of the following fetches will compile without error because the number and type of items in the INTO clause match those of the cursor:

FETCH green_eggs_cur INTO green_eggs_rec;
FETCH green_eggs_cur INTO ham_amount, refused_count, lousy_excuse;
FETCH green_eggs_cur INTO full_green_eggs_rec;
FETCH dr_seuss_cur INTO dr_seuss_rec;

Notice that you can FETCH a cursor's row into either a table-based or a programmer-defined record. You do not have to worry about record type compatibility in this situation. PL/SQL just needs to be able to match up a cursor column/expression with a variable/field in the INTO clause.

As you can see from the above FETCHes, you are not restricted to using any single record or variable list for a particular FETCH -- even in the same program. You can declare multiple records for the same cursor and use all those different records for different fetches. You can also fetch once INTO a record and then later INTO a variable list, as shown below:

OPEN green_eggs_cur ('Sam I Am');
FETCH green_eggs_cur INTO green_eggs_rec;
FETCH green_eggs_cur INTO amount_of_ham, num_rejections, reason;
CLOSE green_eggs_cur;

PL/SQL is very flexible in how it matches up the cursor columns with the INTO clause.

6.6.2 Fetching Past the Last Row

Once you open a cursor, you can FETCH from it until there are no more records left in the active set. At this point the %NOTFOUND cursor attribute for the cursor is set to TRUE.

Actually, you can even FETCH past the last record! In this case, PL/SQL will not raise any exceptions. It just won't do anything for you. Because there is nothing left to fetch, though, it also will not modify the values in the INTO list of the FETCH. The fetch will not set those values to NULL.

You should, therefore, never test the values of INTO variables to determine if the FETCH against the cursor succeeded. Instead, you should check the value of the %NOTFOUND attribute, as explained in Section 6.9.