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

EXEC SQL

Quick 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 statically. 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:

• 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, multiple Native SQL statements can be included between EXEC and ENDEXEC. Generally, the semicolon (;) is used as the separator character.
• SAP-specific Native SQL language elements can also be included 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:
  

• Literals

• Host variables

• INTO clause

• Statements for cursor processing

• Database procedure calls

• Statements for establishing database connections

All Native SQL statements bypass table buffering and no implicit client handling is 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 statement ENDEXEC 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 tested on a SAP HANA database (unless otherwise stated).
• If insertions or modifications using the Native SQL statements INSERT or UPDATE would produce duplicate rows with respect 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 produce a duplicate row, an exception would be raised.
• The client ID of a database table or a view must be specified explicitly. Note that application programs should only use data from the current client. See also the associated security note and the programming guideline.
  

• When the CDS database view of a client-specific CDS view is accessed using Native SQL and the client handling of the view is specified by the annotation @ClientHandling.algorithm:#SESSION_VARIABLE, the session variable $session.client is generally evaluated here. Only on SAP HANA databases, is an instance of this session variables created as a HANA session variable CDS_CLIENT. Its current value is used here. On other platforms, there can be unexpected behavior or the program may crash.
  

• 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.
• Native SQL statements used for transaction control (COMMIT and ROLLBACK) are detected by the database interface and the actions required at the end of a transaction are performed.
• The static embedding of Native SQL statements between EXEC SQL and ENDEXEC is replaced by dynamic passes to objects from ADBC classes. New features in the Native SQL interface are now developed only in ADBC. Only ADBC should be used in new programs.
  

Example

The following example demonstrates how an embedded Native SQL statement can be replaced by ADBC. For single statements, the use of the instance operator NEW removes the need for a helper variable of type CL_SQL_STATEMENT when creating objects.

"Embedded Native SQL 
EXEC SQL. 
  INSERT INTO demo_update VALUES( :sy-mandt, 'X', 1, 2, 3, 4 ) 
ENDEXEC. 

"ADBC 
NEW cl_sql_statement( )->execute_update( 
  |INSERT INTO demo_update VALUES( '{ sy-mandt }', 'X', 1, 2, 3, 4 )| ). 

Executable Example

Static Native SQL

Exceptions

Handleable Exceptions

CX_SY_NATIVE_SQL_ERROR

• Cause: Error when making a secondary connection.
  Runtime error: DBIF_DSQL2_CONNECTERR
• Cause: Illegal interruption of a database selection. The cursor was closed.
  Runtime error: DBIF_DSQL2_INVALID_CURSOR:
• Cause: There is insufficient memory available for a Native SQL statement.
  Runtime error: DBIF_DSQL2_NO_MEMORY
• Cause: Database object already exists in the database. An attempt was made to create a database object (for example, table, view, index) on the database, but this object already exists.
  Runtime error: DBIF_DSQL2_OBJ_EXISTS
• Cause: The name of a table or view that does not exist on the database was used.
  Runtime error: DBIF_DSQL2_OBJ_UNKNOWN
• Cause: An SQL error occurred while executing a native SQL command.
  Runtime error: DBIF_DSQL2_SQL_ERROR
• Cause: The maximum number of secondary connections was exceeded.
  Runtime error: EXSQL_CONNECTION_LIMIT
• Cause: The maximum number of database cursors was exceeded.
  Runtime error: EXSQL_DSQL_CURSOR_LIMIT
  

Non-Handleable Exceptions

• Cause: The specified cursor does not exist. A CLOSE or SELECT command in Native SQL has a specified cursor, but this cursor is unknown to cursor administration.
  Runtime error: EXSQL_DSQL_CURSOR_NOT_FOUND
• Cause: The specified cursor is already open. An OPEN command in Native SQL has a specified cursor, which the cursor administration knows is already open.
  Runtime error: EXSQL_DSQL_DUPLICATE_CURSOR
• Cause: There is insufficient roll memory available. When a Native SQL statement is processed, the internal memory is required to prepare the SQL Call.
  Runtime error: EXSQL_DSQL_NOROLL
• Cause: An indicator variable has an incorrect type. It must be the type INT2.
  Runtime error: EXSQL_DSQL_WRONG_IVAR_TYPE
• Cause: The connection name has already been used for another connection. A CONNECT command in Native SQL has a specified connection name, which is already used for another connection.
  Runtime error: EXSQL_ILLEGAL_CONNECTION
• Cause: The connection "DEFAULT" was specified for a DISCONNECT command in Native SQL. This connection cannot be terminated with DISCONNECT.
  Runtime error: EXSQL_ILLEGAL_DISCONNECTION
• Cause: A Native SQL command contains too many variables. In this context, variables means all ABAP fields preceded by a colon (":"). If the INTO clause has the form INTO :wa for a work area wa, then all the fields of wa also count as variables.
  Runtime error: EXSQL_TOO_MANY_VARS
• Cause: A SET CONNECTION or DISCONNECT command in Native SQL has a specified connection that is unknown to the connection administration.
  Runtime error: EXSQL_UNKNOWN_CONNECTION
• Cause: The target area specified in the INTO clause is not structured despite the assertion INTO STRUCTURE.
  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

EXEC SQL - Host Variables

EXEC SQL - INTO

EXEC SQL - OPEN, FETCH, CLOSE

EXEC SQL - EXECUTE PROCEDURE

EXEC SQL - CONNECT

ENDEXEC