How To Retrieve Data from an Explicit Cursor in Oracle?

If you have a cursor opened ready to use, you can use the FETCH ... INTO statement to retrieve data from the cursor into variables. FETCH statement will:

• Retrieve all the fields from the row pointed by the current cursor pointer and assign them to variables listed in the INTO clause.
• Move the cursor pointer to the next row.
• Update cursor attributes like FOUND and NOTFOUND.

Here is a sample script showing you how to use FETCH statement:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  CURSOR t_list IS SELECT first_name, last_name
    FROM employees;
  f_name VARCHAR2(10);
  l_name VARCHAR2(10);
BEGIN
  OPEN t_list;
  FETCH t_list INTO f_name, l_name;
  DBMS_OUTPUT.PUT_LINE('Name = ' || f_name || ' '
    || l_name); 
  FETCH t_list INTO f_name, l_name;
  DBMS_OUTPUT.PUT_LINE('Name = ' || f_name || ' '
    || l_name); 
  -- FETCH t_list INTO l_name; -- must have two variables
  CLOSE t_list;
END;
/
Name = Ellen Abel
Name = Sundar Ande

⇒ Retrieve Data from a Cursor to a RECORD in Oracle

⇐ Open and Close an Explicit Cursor in Oracle

⇑ Working with Cursors in Oracle PL/SQL

⇑⇑ Oracle Database Tutorials