OPEN CURSOR - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Reads →

OPEN CURSOR

Quick Reference

Syntax

OPEN CURSOR [WITH HOLD] @dbcur|@DATA(dbcur) FOR
  [WITH
    +cte1 AS ( SELECT subquery_clauses )[,
   +cte2 AS ( SELECT subquery_clauses )
    ...]]
  SELECT mainquery_clauses
  [UNION ...]
  [additional_options].

Addition:

... WITH HOLD

Effect

The Open SQL statement OPEN CURSOR opens a database cursor for the result set of the main query defined after FOR and links a cursor variable dbcur with this database cursor. The results set of the main query can be read with the statement FETCH.

The main query is specified after FOR as follows:

The main query is specified using language element SELECT, and its clauses and additions mainquery_clauses define the result set.
The language element UNION can be used to combine the result sets of multiple queries. In this case, additional rules query_clauses apply for specifying clauses.
Finally, you can specify optional additions additional_options.
You also have the option to define common table expressions in the main query by using language element WITH. When defining and using common table expressions, the same applies as when using WITH to introduce a standalone statement.

The following can be specified for the cursor:

A host variable dbcur declared using a specific predefined data type: cursor. The name of this host variable must be prefixed with the escape character @. A database cursor dbcur that has already been opened cannot be opened again.
An inline declaration of a corresponding host variable dbcur. The declaration operator DATA must be prefixed with the escape character @.

A line of the results set is always assigned to an opened database cursor as a cursor position. After the statement OPEN CURSOR, the database cursor is positioned in front of the first line of the results set.

In a single program, a maximum of 17 database cursors can be open simultaneously across the Open SQL interface. If more than 17 database cursors are opened, the runtime error DBSQL_TOO_MANY_OPEN_CURSOR occurs. An open database cursor can be closed using the statement CLOSE CURSOR. In addition, an open database cursor is closed for a database commit or a database rollback.

If a cursor variable dbcur of an open database cursor is assigned to another cursor variable or passed as a parameter, the latter is associated with the same database cursor at the same position. A cursor variable of an open database cursor can also be passed to procedures that have been called externally, to enable the database cursor to be accessed from there.

Notes

It is not recommended that cursor variables are assigned to each other and they should be set only using the statements OPEN CURSOR and CLOSE CURSOR.
If write accesses are made on a database table for which a database cursor is open, the results set is database-specific and undefined. Avoid this kind of parallel access if possible.
As well as explicit Open SQL reads using OPEN CURSOR and SELECT loops, the Open SQL interface also opens database cursors implicitly, such as when loading buffered tables. The runtime error DBSQL_TOO_MANY_OPEN_CURSOR can be avoided by not using explicit reads to exploit the maximum number of open database cursors.
If a CDS view is defined as a replacement object for a database table or database view specified as a data source of the SELECT statement of OPEN CURSOR, the statement FETCH accesses the CDS view and not the database table or the database view.
Host variables without the escape character @ are obsolete. The escape character @ must be specified in the strict modes of the syntax check from Release 7.40, SP05. The use of an inline declaration for dbcur activates the strict mode with release 7.51 and higher.

Example

See FETCH.

Addition

... WITH HOLD

Effect

If the addition WITH HOLD is specified, the database cursor is not closed by a database commit executed using Native SQL.

The addition WITH HOLD can be used only in reads performed on the standard database. It cannot be specified together with the addition CONNECTION.

Notes

The addition WITH HOLD is ignored by implicit database commits, by commits produced by the statement COMMIT WORK, or by any rollbacks that always close the database cursor.
A Native SQL database commit can be performed using the DB_COMMIT function module, for example.

Continue
OPEN CURSOR - mainquery_clauses
FETCH
CLOSE CURSOR
Example

Reading Data Through the Cursor