Skip to content

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

EXEC SQL

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


EXEC SQL. 
  ... 
ENDEXEC.

Effect

These statements define an area in an ABAP program in which one or more Native SQL statements can be specified. The area between EXEC and ENDEXEC is not checked completely by the syntax check. The statements entered there are passed to the Native SQL interface and processed there as follows:

  • Almost all SQL statements that are valid for the addressed database system can be included between EXEC and ENDEXEC, in particular the DDL statements. These SQL statements are passed from the Native SQL interface to the database system largely unchanged. The syntax rules are specified by the database system, in particular the case sensitivity rules for database objects. If the syntax allows a separator character between individual statements, you can include multiple Native-SQL statements between EXEC and ENDEXEC. Generally, the semicolon (;) is used as the separator character.
  • You can also include SAP-specific Native-SQL language elements between EXEC and ENDEXEC. These statements are not passed directly from the Native SQL interface to the database, but are converted appropriately. These SAP-specific language elements are:

All Native SQL statements bypass SAP buffering. Automatic client handling is not performed.

System fields

The statement ENDEXEC sets the system fields sy-subrc and sy-dbcnt. When using the obsolete addition PERFORMING, note that implicit cursor processing is carried out and the system fields are set for every read.

sy-subrc Meaning
0 The statements between EXEC and ENDEXEC were executed successfully.
4 The statements between EXEC and ENDEXEC werenot successful. After implicit cursor processing with PERFORMING, sy-subrc always contains the value 4.

The ENDEXEC statement sets sy-dbcnt to the number of table rows processed in the last Native SQL statement. After implicit cursor processing with PERFORMING, sy-dbcnt contains the total number of rows read. If an overflow occurs because the number or rows is greater than 2,147,483,647, sy-dbcnt is set to -1.


Notes

  • Programs with Native SQL statements are generally dependent on the database system used, so that they cannot be executed in all AS ABAP systems. This is especially true for the examples in this section, which were written for Informix database systems, unless otherwise stated.
  • If insertions or modifications with Native SQL statements INSERT or UPDATE would cause double rows with regard to the primary table key, no exception is raised. Instead, sy-subrc is set to 4. However, if another operation, such as executing a Stored Procedure, would cause a double row, an exception would be raised.
  • The client ID of a database table must be specified explicitly. Remember that application programs should only use the data in the current client. In megatenancy systems, this is checked by the ABAP runtime environment.
  • The obsolete addition PERFORMING (not allowed in classes) executes implicit cursor processing and must no longer be used. The obsolete statement EXIT FROM SQL can be used to exit this type of processing.


Example

See the executable example for Native SQL.

Exceptions


Catchable Exceptions

CX_SY_NATIVE_SQL_ERROR

  • Cause: Error when building up a secondary database connection.
    Runtime Error: DBIF_DSQL2_CONNECTERR
  • Cause: The specified secondary database connection is not registered in table DBCON.
    Runtime Error: DBIF_DSQL2_CONNECTSTR_ERROR
  • Cause: Environment variables with errors in table DBCON used for connection to SAP or another external database (Multi-Connect). To build up a connection, the environment variables are read from the table DBCON and analysed. The check resulted that the values are longer than allowed, or that environment variables are missing when building up a connection.
    Runtime Error: DBIF_DSQL2_INVALENV_ERROR
  • Cause: Illegal disruption of a database selection. The cursor was closed.
    Runtime Error: DBIF_DSQL2_INVALID_CURSOR:
  • Cause: There is already a dataset with the specified key.
    Runtime Error: DBIF_DSQL2_KEY_ALREADY_EXISTS
  • Cause: The specified key was not found in the database.
    Runtime Error: DBIF_DSQL2_KEY_NOT_FOUND on a Native SQL database connection.
  • Cause: No more memory available for a Native SQLStatement.
    Runtime Error: DBIF_DSQL2_NO_MEMORY
  • Cause: The database object already exists in the database. You tried to create a database object (table, View, Index, ...) in the database. This object already existed.
    Runtime Error: DBIF_DSQL2_OBJ_EXISTS
  • Cause: You used the name of a table or a view that does not exist in the database.
    Runtime Error: DBIF_DSQL2_OBJ_UNKNOWN
  • Cause: SQL Error at the execution of a Native SQLcommand.
    Runtime Error: DBIF_DSQL2_SQL_ERROR
  • Cause: The maximum number of secondary database connections was exceeded.
    Runtime Error: EXSQL_CONNECTION_LIMIT
  • Cause: The maximum number of cursors was exceeded.
    Runtime Error: EXSQL_DSQL_CURSOR_LIMIT


Non-Catchable Exceptions

  • Cause: The specified cursor does not exist. At a CLOSE or SELECT command in Native SQL, a cursor was specified which is unknown to the cursor administration.
    Runtime Error: EXSQL_DSQL_CURSOR_NOT_FOUND
  • Cause: The specified cursor is already open. A cursor was specified at a OPEN command in Native SQL which the cursor administration knows as already opened.
    Runtime Error: EXSQL_DSQL_DUPLICATE_CURSOR
  • Cause: No more roll memory available. When processing a Native SQL Statement, the internal memory is required to prepare the SQL Call.
    Runtime Error: EXSQL_DSQL_NOROLL
  • Cause: An indicator variable has the wrong type. It must have the type INT2.
    Runtime Error: EXSQL_DSQL_WRONG_IVAR_TYPE
  • Cause: The connection name has already been assigned. At an CONNECT command in Native SQL, a connection name was specified which is already used for another connection.
    Runtime Error: EXSQL_ILLEGAL_CONNECTION
  • Cause: At a DISCONNECT command in Native SQL, the connection "DEFAULT" was specified. This connection cannot be terminated with DISCONNECT.
    Runtime Error: EXSQL_ILLEGAL_DISCONNECTION
  • Cause: A Native SQL command contains too many variables. As variables are considered all ABAPfields that are marked with a preceding colon(":") . If the INTO clause has the form INTO :wa for a work area wa, then all the fields of wa are also considered variables.
    Runtime Error: EXSQL_TOO_MANY_VARS
  • Cause: At a SET CONNECTION or DISCONNECT command in Native SQL, a connection was specified that is unknown to the connection administration.
    Runtime Error: EXSQL_UNKNOWN_CONNECTION
  • Cause: Despite the assurance INTO STRUCTURE, the target option, specified in the INTO-clause, is not structured.
    Runtime Error: EXSQL_UNSTRUCTURED_INTO
  • Cause: A LOB Variable has the wrong type. LOB Variables must have the type string, xstring, c, x or n.
    Runtime Error: EXSQL_WRONG_TYPE_FOR_LOB

Continue

EXEC SQL - Literals and Host Variables

EXEC SQL - OPEN, CLOSE, FETCH

EXEC SQL - EXECUTE

EXEC SQL - CONNECT

ENDEXEC