Purpose
Opens a declared cursor for an embedded SQL application and executes the SQL statement specified in its declaration.
Invocation
Executable.
Embedded SQL only.
Syntax
OPEN cursor_name [ USING { using_spec [,...] | DESCRIPTOR [:] descriptor_area } ]
- using_spec
-
[:] host_variable_name [ [INDICATOR] :host_indicator_name ]
- cursor_name
- The name of the cursor to be opened.
- host_variable_name
- The variable to be used as input data for the cursor request.
- host_indicator_name
- The indicator variable.
- descriptor_area
- An SQLDA.
ANSI Compliance
ANSI/ISO SQL:2011-compliant.
Authorization
None.
General Rules
- You should define an SQLDA.
- You must previously declare the cursor identified by cursor_name.
- The cursor identified by cursor_name must be closed.
- Once the cursor is open, the system executes the associated static or dynamic SQL statement or statements. The system then creates the result spool file and positions the cursor before the first row of the spool file.
- OPEN processing returns a 0 in the SQLCODE field of the SQLCA and ‘00000’ to SQLSTATE, unless a failure (implicit rollback) occurs. For an SQLCODE of 0, the system places the activity count in the third SQLERRD element of the SQLCA structure.
- If the cursor is updatable, or a C or COBOL application program contains a REWIND or POSITION TO STATEMENT request for the cursor, execute the OPEN statement with KEEPRESP; otherwise, execute it with NOKEEPRESP. For PL/I applications, KEEPRESP is the default.
- You cannot execute OPEN as a dynamic SQL statement.
- No more than 16 cursors can be open at one time because the processing of non-cursor-related statements is increasingly affected for the worse as more cursors are opened.
If an application has 16 cursors open, no other request can be issued until one or more cursors are closed.
Rules for USING Clause
- The USING clause identifies variables used as input to the SQL statement by cursor_name.
-
host_variable_name must be a valid client language variable you declared before the OPEN statement, to be used as an input variable.
You can use a client structure to identify the input variables.
The number of variables you specify must be the same as the number of parameter markers (the question mark character) in the identified statement.
The n th variable corresponds to the n th marker.
Use of the colon character prefix for host_variable_name is optional.
-
descriptor_area identifies an input SQLDA structure, previously defined by the application, that contains necessary information about the input variable set.
The number of variables identified by the SQLD field of the SQLDA must be the same as the number of parameter markers (the question mark character) in the identified statement.
The n th variable described by the SQLDS corresponds to the n th marker.
Related Topics
- Cursors referenced in the ALLOCATE statement, see ALLOCATE.
- Creating casts and using the AS ASSIGNMENT option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- descriptor_area, see Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446.
- OPEN (Embedded SQL Form), see CLOSE.