1  OPEN
   Opens a cursor so that rows of its result table can be retrieved
   through FETCH statements. The OPEN statement places the cursor
   before the first row of its result table.
 

2  Environment
   You can use the OPEN statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module
 

2  Format

 OPEN -+-> <cursor-name> ++---------------->----------------------+->
       +-> <parameter> --++-> USING --+-> <parameter> -----------++
                          |           +-> <qualified-parameter> -+|
                          |           +------ , <----------------+|
                          +-> USING DESCRIPTOR <descriptor-name> -+
 

 

2  Arguments
 

3  cursor-name

   Specifies the name of the cursor you want to open. Use a
   parameter if the cursor referred to by the cursor name was
   declared at run time with a dynamic DECLARE CURSOR statement.
   Specify the parameter used for the cursor name in the extended
   dynamic DECLARE CURSOR statement.

   You can use a parameter to refer to the cursor name only when the
   OPEN statement refers to an extended dynamic cursor.
 

3  parameter

   Specifies the name of the cursor you want to open. Use a
   parameter if the cursor referred to by the cursor name was
   declared at run time with a dynamic DECLARE CURSOR statement.
   Specify the parameter used for the cursor name in the extended
   dynamic DECLARE CURSOR statement.

   You can use a parameter to refer to the cursor name only when the
   OPEN statement refers to an extended dynamic cursor.
 

3  USING

   Syntax options:

      USING parameter
      USING qualified-parameter
      USING DESCRIPTOR descriptor-name

   Specifies in dynamic SQL parameters (host language variables in
   a precompiled OPEN statement or formal parameters in an OPEN
   statement that is part of an SQL module language procedure)
   or qualified parameters (structures) whose values SQL uses to
   replace parameter markers in a prepared SELECT statement named
   in the cursor declaration. These parameters are not for use in
   interactive SQL. SQL replaces the parameter markers with the
   values of the host language variables when it evaluates the
   SELECT statement of the cursor. See the Oracle Rdb SQL Reference
   Manual for more information on the SQL module language and the
   SQL precompiler, respectively.

   You must specify the USING clause when both of the following
   conditions exist:

   o  The declaration of the cursor you are opening specifies a
      prepared SELECT statement name.

   o  The statement string for the prepared SELECT statement
      includes parameter markers.

   SQL does not allow the USING clause in an OPEN statement for a
   cursor that is not based on a prepared SELECT statement. For
   more information on parameter markers, see the PREPARE statement,
   and the chapter on dynamic SQL in the Oracle Rdb Guide to SQL
   Programming.

   There are two ways to specify parameters in a USING clause:

   o  With a list of parameters. The number of parameters in the
      list must be the same as the number of parameter markers in
      the prepared SELECT statement. (If any of the parameters in
      an OPEN statement is a host structure, SQL counts the number
      of variables in that structure when it compares the number of
      parameters in the USING clause with the number of parameter
      markers in the prepared SELECT statement.)

   o  With the name of a descriptor that corresponds to an SQLDA.
      Specify the name of the descriptor in the USING DESCRIPTOR
      clause. If you use the INCLUDE statement to insert the SQLDA
      into your program, the descriptor name is simply SQLDA.

      The SQLDA is a collection of variables used only in dynamic
      SQL. In an OPEN statement, the SQLDA points to a number
      of host language variables with which SQL replaces the
      parameter markers in a prepared SELECT statement. The number
      of variables must match the number of parameter markers.

   The data types of host language variables must be compatible with
   the values of the corresponding column of the cursor row.
 

2  Examples

   Example 1: Opening a cursor declared in a PL/I program

   This program fragment uses embedded DECLARE CURSOR, OPEN, and
   FETCH statements to retrieve and print the name and department of
   managers. The OPEN statement places the cursor at the beginning
   of rows to be fetched.

   /* Declare the cursor: */
   EXEC SQL DECLARE MANAGER CURSOR FOR
           SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME
                   FROM EMPLOYEES E, DEPARTMENTS D
                   WHERE E.EMPLOYEE_ID = D.MANAGER_ID ;

   /* Open the cursor: */
   EXEC SQL OPEN MANAGER;

   /* Start a loop to process the rows of the cursor: */
   DO WHILE (SQLCODE = 0);
           /* Retrieve the rows of the cursor
           and put the value in host language variables: */
           EXEC SQL FETCH MANAGER INTO :FNAME, :LNAME, :DNAME;
           /* Print the values in the variables: */
                           .
                           .
                           .
   END;

   /* Close the cursor: */
   EXEC SQL CLOSE MANAGER;

   Example 2: Opening a cursor to insert list data

   The following interactive SQL example uses cursors to add a new
   row to the RESUMES table of the sample personnel database:

   SQL> DECLARE TBLCURSOR INSERT ONLY TABLE CURSOR FOR
   cont> SELECT EMPLOYEE_ID, RESUME FROM RESUMES;
   SQL> DECLARE LSTCURSOR INSERT ONLY LIST CURSOR FOR
   cont> SELECT RESUME WHERE CURRENT OF TBLCURSOR;
   SQL> OPEN TBLCURSOR;
   SQL> INSERT INTO CURSOR TBLCURSOR (EMPLOYEE_ID)
   cont> VALUES ("00167");
   1 row inserted
   SQL> OPEN LSTCURSOR;
   SQL> INSERT INTO CURSOR LSTCURSOR
   cont> VALUES ("This is the resume for 00167");
   SQL> INSERT INTO CURSOR LSTCURSOR
   cont> VALUES ("Boston, MA");
   SQL> INSERT INTO CURSOR LSTCURSOR
   cont> VALUES ("Oracle Corporation");
   SQL> CLOSE LSTCURSOR;
   SQL> CLOSE TBLCURSOR;
   SQL> COMMIT;