Oracle Database Interview Question:

How To Retrieve Data from an Explicit Cursor?

Submitted by: Administrator

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 ggl_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

Submitted by: Administrator