ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  Native SQL →  EXEC SQL - Embedded Native SQL →  EXEC SQL 

EXEC SQL - OPEN, FETCH, CLOSE

In embedded Native SQL, similar statements to those in ABAP SQL can be specified to read data using a database cursor.

Other versions: 7.31 | 7.40 | 7.54

Syntax

EXEC SQL. 
  OPEN dbcur FOR SELECT ... 
ENDEXEC. 

Effect

Opens a database cursor dbcur. For dbcur, a flat character-like host variable can be specified.

Note

The number of database cursor open simultaneously is restricted by platform. Any attempts to open too many database cursors raise an exception of the class CX_SY_NATIVE_SQL_ERROR.

Syntax

EXEC SQL. 
  FETCH NEXT dbcur INTO ... 
ENDEXEC. 

Effect

Uses an open database cursor dbcur to read data to the host variables specified after INTO.

Syntax

EXEC SQL. 
  CLOSE dbcur 
ENDEXEC. 

Effect

Closes an opened database cursor dbcur.

If no row can be read using FETCH, sy-subrc is set to 4 by ENDEXEC. After a FETCH statement, the system field sy-dbcnt is set to the number of rows read up to that point using the cursor in question. If an overflow occurs because the number or rows is greater than 2,147,483,647, sy-dbcnt is set to -1.

Note

It depends on the database system whether the database cursor in the database is closed implicitly after the extraction of the final row of the results set or not. For this reason, it is advisable to use the statement CLOSE dbcur explicitly.

Example

Reads multiple rows from the database table SPFLI using cursor handling and host variables in static Native SQL. If rows are found, sy-subrc is set to 0 and sy-dbcnt is increased by one for each row read.

DATA: carrid   TYPE spfli-carrid VALUE 'LH', 
      connid   TYPE spfli-connid, 
      cityfrom TYPE spfli-cityfrom, 
      cityto   TYPE spfli-cityto. 

cl_demo_input=>request( CHANGING field = carrid ). 

EXEC SQL. 
  OPEN dbcur FOR 
    SELECT connid, cityfrom, cityto 
           FROM spfli 
           WHERE mandt  = :sy-mandt AND 
                 carrid = :carrid 
ENDEXEC. 

DO. 
  EXEC SQL. 
    FETCH NEXT dbcur INTO :connid, :cityfrom, :cityto 
  ENDEXEC. 
  IF sy-subrc <> 0. 
    EXIT. 
  ELSE. 
    cl_demo_output=>write( 
      |{ carrid }, { connid }, { cityfrom }, { cityto }| ). 
  ENDIF. 
ENDDO. 
cl_demo_output=>display( ). 

EXEC SQL. 
  CLOSE dbcur 
ENDEXEC.