Oracle Database

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

If you have a cursor opened ready to use, you can also use the FETCH statement to retrieve data from the cursor into a RECORD variable as shown in the tutorial exercise below:

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

CURSOR t_list IS SELECT first_name, last_name

FROM employees;

TYPE name_rec IS RECORD (

f_name VARCHAR2(10),

l_name VARCHAR2(10)

);

n name_rec;

BEGIN

OPEN t_list;

FETCH t_list INTO n;

DBMS_OUTPUT.PUT_LINE('Name = ' || n.f_name || ' '

|| n.l_name);

FETCH t_list INTO n;

DBMS_OUTPUT.PUT_LINE('Name = ' || n.f_name || ' '

|| n.l_name);

CLOSE t_list;

END;

/

Name = Ellen Abel

Name = Sundar Ande

If you have a cursor opened ready to use, you can also use the FETCH statement in a loop to retrieve data from the cursor more efficiently. But you need to remember to use an EXIT statement break the loop when the cursor pointer reaches the end. The script below gives you a good example:

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

CURSOR emp_cur IS SELECT * FROM employees

WHERE manager_id = 101;

emp_rec employees%ROWTYPE;

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur INTO emp_rec;

EXIT WHEN emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name = ' ||

emp_rec.first_name || ' ' || emp_rec.last_name);

END LOOP;

CLOSE emp_cur;

END;

/

Name = Nancy Greenberg

Name = Jennifer Whalen

Name = Susan Mavris

Name = Hermann Baer

Name = Shelley Higgins

Yes, multiple cursors can be opened at the same time. See the following example:

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

CURSOR emp_cur IS SELECT * FROM employees;

emp_rec employees%ROWTYPE;

CURSOR dpt_cur IS SELECT * FROM departments;

dpt_rec departments%ROWTYPE;

BEGIN

OPEN emp_cur;

OPEN dpt_cur;

FETCH emp_cur INTO emp_rec;

FETCH dpt_cur INTO dpt_rec;

DBMS_OUTPUT.PUT_LINE('Department name = ' ||

dpt_rec.department_name);

DBMS_OUTPUT.PUT_LINE('Employee name = ' ||

emp_rec.first_name || ' ' || emp_rec.last_name);

CLOSE emp_cur;

CLOSE dpt_cur;

END;

/

Department name = Administration

Employee name = Steven King

When you define a cursor, you can set a formal parameter in the cursor. The formal parameter will be replaced by an actual parameter in the OPEN cursor statement. Here is a good example of a cursor with two parameters:

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

CURSOR emp_cur(low NUMBER, high NUMBER)

IS SELECT * FROM employees WHERE salary >= low

AND salary <= high;

BEGIN

FOR row IN emp_cur(12000,15000) LOOP

DBMS_OUTPUT.PUT_LINE(row.first_name || ' '

|| row.last_name

|| ': ' || row.salary);

END LOOP;

END;

/

Nancy Greenberg: 12000

John Russell: 14000

Karen Partners: 13500

Alberto Errazuriz: 12000

Michael Hartstein: 13000

Shelley Higgins: 12000

To define cursor variable, you must decide which REF CURSOR data type to use. There are 3 ways to select a REF CURSOR data type:

► Define your own specific REF CURSOR types using the TYPE ... RETURN statement.

► Define your own generic REF CURSOR type using the TYPE ... statement.

► Use the system defined REF CURSOR type: SYS_REFCURSOR.

The follwoing tutorial exercise defines 3 cursor variables in 3 different ways:

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;

TYPE any_ref IS REF CURSOR;

emp_cur emp_ref;

any_cur any_ref;

sys_cur SYS_REFCURSOR;

BEGIN

NULL;

END;

/

A cursor variable must be opened with a specific query statement before you can fetch data fields from its data rows. To open a cursor variable, you can use the OPEN ... FOR statement as shown in the following tutorial exercise:

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;

TYPE any_ref IS REF CURSOR;

emp_cur emp_ref;

any_cur any_ref;

sys_cur SYS_REFCURSOR;

BEGIN

OPEN emp_cur FOR SELECT * FROM employees;

OPEN any_cur FOR SELECT * FROM employees;

OPEN sys_cur FOR SELECT * FROM employees;

CLOSE sys_cur;

CLOSE any_cur;

CLOSE emp_cur;

END;

/

Once a cursor variable is opened with a query statement, it will have the same attributes as a normal cursor and it can be used in the same way a normal cursor too. The following sample script shows you how to loop through a cursor variable:

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;

emp_cur emp_ref;

emp_rec employees%ROWTYPE;

BEGIN

OPEN emp_cur FOR SELECT * FROM employees

WHERE manager_id = 101;

LOOP

FETCH emp_cur INTO emp_rec;

EXIT WHEN emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name = ' ||

emp_rec.first_name || ' ' || emp_rec.last_name);

END LOOP;

CLOSE emp_cur;

END;

/

Name = Nancy Greenberg

Name = Jennifer Whalen

Name = Susan Mavris

Name = Hermann Baer

Name = Shelley Higgins

A cursor variable can be passed into a procedure like a normal variable. The sample script below gives you a good example:

CREATE OR REPLACE PROCEDURE ggl_CENTER AS

sys_cur SYS_REFCURSOR;

PROCEDURE emp_print(cur SYS_REFCURSOR) AS

emp_rec employees%ROWTYPE;

BEGIN

LOOP

FETCH cur INTO emp_rec;

EXIT WHEN cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name = ' ||

emp_rec.first_name || ' ' || emp_rec.last_name);

END LOOP;

END;

BEGIN

OPEN sys_cur FOR SELECT * FROM employees

WHERE manager_id = 101;

emp_print(sys_cur);

CLOSE sys_cur;

END;

/

Name = Nancy Greenberg

Name = Jennifer Whalen

Name = Susan Mavris

Name = Hermann Baer

Name = Shelley Higgins

The quickest way to export a table to a flat file is probably to use the SQL*Plus SPOOL command. It allows you to record SELECT query result to a text file on the operating system. The following tutorial exercise shows you how control the output format, start the spooler, and dump all record from a table to a flat text file:

>mkdir oraclexe est

>sqlplus /nolog

SQL> connect HR/globalguideline

SQL> SET HEADING OFF;

SQL> SET FEEDBACK OFF;

SQL> SET LINESIZE 1000;

SQL> SPOOL oraclexe estemployees.txt;

SQL> SELECT * FROM EMPLOYEES;

......

SQL> SPOOL OFF;

You should get all records in employees.txt with fixed length fields.

The previous exercise allows you to export data with fixed field lengths. If you want export data with variable field lengths and field delimiters, you can concatenate your fields with an expression in the SELECT clause as shown in the tutorial exercise bellow:

SQL> SET HEADING OFF;

SQL> SET FEEDBACK OFF;

SQL> SET LINESIZE 1000;

SQL> SPOOL oraclexe estggl_links.txt;

SQL> SELECT id ||','|| url ||','|| notes ||','|| counts

||','|| created FROM ggl_links;

......

SQL> SPOOL OFF;

You should see all records in ggl_links.txt with ',' delimited fields as shown here:

101,rendc.org,Session 1,,17-MAY-06

110,rendc.org,Session 1,,17-MAY-06

Let's say you have a table defined as:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

There is an input data file stored at oraclexe extstudent.txt with the following records:

1,Steven,King,17-JUN-77,515.123.4567

2,Neena,Kochhar,21-SEP-79,515.123.4568

3,Lex,De Haan,13-JAN-83,515.123.4569

4,Alexander,Hunold,03-JAN-80,590.423.4567

5,Bruce,Ernst,21-MAY-81,590.423.4568

6,David,Austin,25-JUN-87,590.423.4569

7,Valli,Pataballa,05-FEB-88,590.423.4560

8,Diana,Lorentz,07-FEB-89,590.423.5567

9,Nancy,Greenberg,17-AUG-84,515.124.4569

10,Daniel,Faviet,16-AUG-84,515.124.4169