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

• Semantics

• Examples

• Related Topics

Syntax

fetch_statement ::=

Description of the illustration fetch_statement.gif

See:

• "bulk_collect_into_clause ::="

• "into_clause ::="

• "numeric_expression ::="

Semantics

cursor

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 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 of a cursor variable declared in a PL/SQL host environment, passed to PL/SQL as a bind variable, and then opened. To open a host cursor variable, use the "OPEN FOR Statement". Do not put space between the colon (:) and host_cursor_variable.

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

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 into_clause, see "into_clause".

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 "bulk_collect_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 

• You cannot use bulk_collect_into_clause in client programs.

• When the FETCH statement requires implicit data type conversions, bulk_collect_into_clause can have only one collection or host_array.

Examples

• Example 5-46, "FETCH Assigns Values to Record that Function Returns"

• Example 6-6, "FETCH Statements Inside LOOP Statements"

• Example 6-7, "Fetching Same Explicit Cursor into Different Variables"

• Example 6-26, "Fetching Data with Cursor Variables"

• Example 6-27, "Fetching from Cursor Variable into Collections"

• Example 6-42, "FETCH with FOR UPDATE Cursor After COMMIT Statement"

• Example 7-4, "Native Dynamic SQL with OPEN FOR, FETCH, and CLOSE Statements"

• Example 12-22, "Bulk-Fetching into Two Nested Tables"

• Example 12-23, "Bulk-Fetching into Nested Table of Records"

• Example 12-24, "Limiting Bulk FETCH with LIMIT"

Related Topics

In this chapter:

• "Assignment Statement"

• "CLOSE Statement"

• "Cursor Variable Declaration"

• "Explicit Cursor Declaration and Definition"

• "OPEN Statement"

• "OPEN FOR Statement"

• "RETURNING INTO Clause"

• "%ROWTYPE Attribute"

• "SELECT INTO Statement"

• "%TYPE Attribute"

In other chapters:

• "FETCH Statement for Assigning Row to Record Variable"

• "Fetching Data with Explicit Cursors"

• "Query Result Set Processing With Cursor FOR LOOP Statements"

• "Fetching Data with Cursor Variables"

• "OPEN FOR, FETCH, and CLOSE Statements"

• "FETCH Statement with BULK COLLECT Clause"

• "Fetching from Results of Pipelined Table Functions"