Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E17126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

FETCH Statement

The FETCH statement retrieves rows of data from the result set of a multiple-row query—one row at a time, several rows at a time, or all rows at once—and stores the data in variables, records, or collections.

Topics:

Syntax

fetch_statement ::=

fetch_statement
Description of the illustration fetch_statement.gif

See:

Semantics

cursor_name

The name of an open explicit cursor. To open an explicit cursor, use the "OPEN Statement".

If you try to fetch from an explicit cursor before opening it or after closing it, PL/SQL raises the predefined exception INVALID_CURSOR.

cursor_variable_name

The name of an open cursor variable. To open a cursor variable, use the "OPEN FOR Statement". The cursor variable can be a formal subprogram parameter (see "Cursor Variables as Subprogram Parameters").

If you try to fetch from a cursor variable before opening it or after closing it, PL/SQL raises the predefined exception INVALID_CURSOR.

host_cursor_variable_name

The name of a cursor variable declared in a PL/SQL host environment, passed to PL/SQL as a bind argument, and then opened. To open a cursor variable, use the "OPEN FOR Statement". Do not put space between the colon (:) and host_cursor_variable_name.

into_clause

To have the FETCH statement retrieve one row at a time, use this clause to specify the variables or record in which to store the column values of a row that the cursor returns. For more information about this clause, see "RETURNING INTO Clause".

The data type of a host cursor variable is compatible with the return type of any PL/SQL cursor variable.

bulk_collect_into_clause [ LIMIT numeric_expression ]

Use bulk_collect_into_clause to specify one or more collections in which to store the rows that the FETCH statement returns. For more information about bulk_collect_into_clause, see "RETURNING INTO Clause".

To have the FETCH statement retrieve all rows at once, omit LIMIT numeric_expression.

To limit the number of rows that the FETCH statement retrieves at once, specify LIMIT numeric_expression.

Restrictions on bulk_collect_into_clause 

Examples

Related Topics

In this chapter:

In other chapters: