DROP TABLE people;

CREATE TABLE people (name VARCHAR2(20));

INSERT INTO people (name) VALUES ('John Smith');

INSERT INTO people (name) VALUES ('Mary Jones');

INSERT INTO people (name) VALUES ('Joe Brown');

INSERT INTO people (name) VALUES ('Jane White');

CREATE OR REPLACE PACKAGE sr_pkg IS

PRAGMA SERIALLY_REUSABLE;

CURSOR c IS SELECT name FROM people;

END sr_pkg;

/

CREATE OR REPLACE PROCEDURE fetch_from_cursor IS

name_ VARCHAR2(200);

BEGIN

IF sr_pkg.c%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('Cursor is open.');

ELSE

DBMS_OUTPUT.PUT_LINE('Cursor is closed; opening now.');

OPEN sr_pkg.c;

END IF;

FETCH sr_pkg.c INTO name_;

DBMS_OUTPUT.PUT_LINE('Fetched: ' || name_);

FETCH sr_pkg.c INTO name;

DBMS_OUTPUT.PUT_LINE('Fetched: ' || name_);

END fetch_from_cursor;

/

First call to server:

BEGIN

fetch_from_cursor;

fetch_from_cursor;

END;

/

Result:

Cursor is closed; opening now.

Fetched: John Smith

Fetched: Mary Jones

Cursor is open.

Fetched: Joe Brown

Fetched: Jane White

New call to server:

BEGIN

fetch_from_cursor;

fetch_from_cursor;

END;

/

Result:

Cursor is closed; opening now.

Fetched: John Smith

Fetched: Mary Jones

Cursor is open.

Fetched: Joe Brown

Fetched: Jane White