Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

OPEN statement [ESQL] [SP]

Opens a previously declared cursor to access information from the database.

  • Embedded SQL
    OPEN cursor-name
    [ USING { DESCRIPTOR sqlda-name | hostvar, ... } ]
    [ WITH HOLD ]
    [ ISOLATION LEVEL isolation-level ]
    [ BLOCK n ]  
  • Stored procedures
    OPEN cursor-name
    [ WITH HOLD ]
    [ ISOLATION LEVEL isolation-level ]
    cursor-name : identifier | hostvar
    sqlda-name : identifier

    The USING DESCRIPTOR clause is for Embedded SQL only. It specifies the host variables to be bound to the placeholder bind variables in the SELECT statement for which the cursor has been declared.

    OPEN...USING cannot be used in a stored procedure.

  • WITH HOLD clause

    By default, all cursors are automatically closed at the end of the current transaction (COMMIT or ROLLBACK). The optional WITH HOLD clause keeps the cursor open for subsequent transactions. It remains open until the end of the current connection or until an explicit CLOSE statement is executed. Cursors are automatically closed when a connection is terminated.

    Upon COMMIT or ROLLBACK, all long-term row locks held by the connection are released, including those rows that constitute the result set of a WITH HOLD cursor. However, cursor stability locks, which are acquired at isolation levels 1, 2, and 3, are retained for the life of the cursor and are only released when the cursor is closed or when the connection terminates.

    Upon completion of a ROLLBACK statement, the contents of, and positioning within, a WITH HOLD cursor are unpredictable and are not guaranteed. You can use the ansi_close_cursors_on_rollback option to control whether or not a ROLLBACK statement will close WITH HOLD cursors automatically.


    The ISOLATION LEVEL clause allows this cursor to be opened at an isolation level different from the current setting of the isolation_level option. All operations on this cursor are performed at the specified isolation level regardless of the option setting. If this clause is not specified, then the cursor's isolation level for the entire time the cursor is open is the value of the isolation_level option when the cursor is opened.

    The following values are supported:

    • 0
    • 1
    • 2
    • 3

    The cursor is positioned before the first row.

  • BLOCK clause

    This clause is for Embedded SQL use only. Rows may be fetched by the client application more than one at a time. This is referred to as block fetching, prefetching, or multi-row fetching. The BLOCK clause can reduce the number of rows prefetched. Specifying the BLOCK clause on OPEN is the same as specifying the BLOCK clause on each FETCH.


The OPEN statement opens the named cursor. The cursor must be previously declared.

The OPEN statement may return a SQL warning if the cursor type does not match the characteristics of the cursor's underlying statement.

When the cursor is on a CALL statement, OPEN causes the procedure to execute until the first result set (SELECT statement with no INTO clause) is encountered. If the procedure completes and no result set is found, the SQLSTATE_PROCEDURE_COMPLETE warning is set.

  • Embedded SQL usage

    After successful execution of the OPEN statement, the sqlerrd[3] field of the SQLCA (SQLIOESTIMATE) is filled in with an estimate of the number of input/output operations required to fetch all rows of the query. Also, the sqlerrd[2] field of the SQLCA (SQLCOUNT) is filled with either the actual number of rows in the cursor (a value greater than or equal to 0), or an estimate thereof (a negative number whose absolute value is the estimate). It is the actual number of rows if the database server can compute it without counting the rows. The database can also be configured to always return the actual number of rows, but this can be expensive.

    If cursor-name is specified by an identifier or string, the corresponding DECLARE CURSOR statement must appear before the OPEN in the C program; if the cursor-name is specified by a host variable, the DECLARE CURSOR statement must execute before the OPEN statement.


When the cursor is on a SELECT statement, you must be the owner of the object referenced in the cursor, or have SELECT privilege on the object, or have the appropriate SELECT system privilege (for example, SELECT ANY TABLE).

When the cursor is on a CALL statement, you must be the owner of the procedure or have EXECUTE privilege on the procedure, or have the EXECUTE ANY PROCEDURE system privilege.

Side effects

OPEN causes the complete materialization of an INSENSITIVE cursor's result set.

If access plan caching is enabled, some SQL warnings that would be returned to the application at OPEN time may be suppressed. The suppressed warnings include warnings to indicate that the cursor type has changed, that the underlying query is not deterministic, or that string truncation has occurred with one or more literal constants embedded in the statement.

  • ANSI/ISO SQL Standard

    Use of the OPEN statement within Embedded SQL is part of optional ANSI/ISO SQL Language Feature B031, "Basic dynamic SQL". The use of the OPEN statement within a stored procedure is a Core Feature. The ISOLATION LEVEL and BLOCK clauses are not in the standard, as is the ability to OPEN a cursor over a CALL statement. In the ANSI/ISO SQL Standard, WITH HOLD is specified as part of the DECLARE CURSOR statement, and not on OPEN.

    The setting of specific values in the SQLCA is not in the standard.

  • Transact-SQL

    The OPEN statement is supported by Adaptive Server Enterprise. Adaptive Server Enterprise does not support the ISOLATION LEVEL, BLOCK, and WITH HOLD clauses.


The following examples show the use of OPEN in Embedded SQL.

EXEC SQL OPEN employee_cursor;
'SELECT empnum, empname FROM GROUPO.Employees WHERE name like ?';
EXEC SQL DECLARE employee_cursor CURSOR FOR emp_stat;
EXEC SQL OPEN employee_cursor USING :pattern;

This example fragment shows an OPEN statement in a procedure or trigger.

   DECLARE cur_employee CURSOR FOR
   SELECT Surname
   FROM GROUPO.Employees;
   DECLARE name CHAR(40);
   OPEN cur_employee;
         FETCH NEXT cur_employee INTO name;
   CLOSE cur_employee;