OPEN CURSOR [WITH HOLD] @dbcur|@DATA(dbcur) FOR
+cte1 AS ( SELECT subquery_clauses )[,
+cte2 AS ( SELECT subquery_clauses )
[UP TO ...] [OFFSET ...]
The ABAP 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
The main query is specified after
FOR as follows:
The main query is specified using language element
SELECT, and its clauses and additions
mainquery_clausesdefine the result set.
The language element
UNIONcan be used to combine the result sets of multiple queries. In this case, special rules
query_clausesapply for specifying clauses.
Finally, the optional additions UP
abap_optionscan be specified.
table expressions can be defined in the main query by using the language element
WITH. When defining and using common table expressions, the same applies as when using
WITHto introduce a standalone statement.
The following can be specified for the cursor:
A host variable
dbcurdeclared with the special predefined data type
cursor. A database cursor
dbcurthat has already been opened cannot be opened again.
An inline declaration of a corresponding
dbcur. The declaration operator
DATAmust be prefixed with the escape character
A line of the result set is always assigned to an opened database cursor as a cursor position. After
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
ABAP 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. Here, any open database cursors are closed by
database commits or
if these occur after the first use of the cursor in a
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.
It is not recommended that cursor variables are assigned to each other and they should be set only using the statements
- 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 ABAP SQL reads using
SELECTloops, the ABAP 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
OPEN CURSOR, the statement
FETCHaccesses 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
dbcuractivates the strict mode with release 7.51 and higher. If a statement
OPEN CURSORis checked in accordance with the rules for the strict mode from Release 7.50, each statement
FETCHthat accesses the database cursor is also checked in strict mode. Conversely, the strict syntax check mode from Release 7.54 also applies the strict mode for the associated statement
OPEN CURSORin the statement
FETCH. If the rules specified by
OPEN CURSORare broken, an exception of the class CX_SY_DYNAMIC_OSQL_SEMANTICS is raised.
Opens two cursors for the database table SPFLI. For more information on how to use this function, see the example for
OPEN CURSOR @DATA(dbcur1) FOR SELECT carrid, COUNT(*) AS count FROM spfli GROUP BY carrid ORDER BY carrid. OPEN CURSOR @DATA(dbcur2) FOR SELECT * FROM spfli ORDER BY carrid.
... WITH HOLD
WITH HOLD can be used only in reads performed on the standard
database. It cannot be specified together with the addition
A Native SQL database commit closes the database cursor only after the cursor is used in a
FETCHstatement. A Native SQL database commit between the statement OPEN CURSOR and the first
FETCHstatement does not close the cursor.
WITH HOLDis ignored by the following:
- Implicit database commits
- Commits made by the statement
- Any rollbacks
A Native SQL database commit can be made explicitly using the statement
WITH HOLD stops the database cursor from being closed using
an explicit database commit and the statement
COMMIT CONNECTION and hence stops an exception from being raised in the second
FETCH statement. An exception is, however, raised after the statement
DATA wa TYPE scarr. OPEN CURSOR WITH HOLD @DATA(dbcur) FOR SELECT * FROM scarr. FETCH NEXT CURSOR @dbcur INTO @wa. COMMIT CONNECTION default. FETCH NEXT CURSOR @dbcur INTO @wa. CLOSE CURSOR @dbcur. TRY. OPEN CURSOR WITH HOLD @dbcur FOR SELECT * FROM scarr. COMMIT WORK. FETCH NEXT CURSOR @dbcur INTO @wa. CLOSE CURSOR @dbcur. CATCH cx_sy_open_sql_db. ... ENDTRY.