Oracle7 Server SQL Reference

Contents Index Home Previous Next

OPEN (Embedded SQL)

Purpose

To open a cursor, evaluating the associated query and substituting the host variable names supplied by the USING clause into the WHERE clause of the query.

Prerequisites

You must declare the cursor with a DECLARE CURSOR embedded SQL statement before opening it.

Syntax

Syntax

Keywords and Parameters

cursor

is the cursor to be opened.

USING

specifies the host variables to be substituted into the WHERE clause of the associated query.

:host_variable specifies a host variable with an optional indicator variable to be substituted into the statement associated with the cursor.

DESCRIPTOR

specifies a descriptor that describes the host variables to be substituted into the WHERE clause of the associated query. The descriptor must be initialized in a previous DESCRIBE statement.

The substitution is based on position. The host variable names specified in this statement can be different from the variable names in the associated query.

Usage Notes

The OPEN command defines the active set of rows and initializes the cursor just before the first row of the active set. The values of the host variables at the time of the OPEN are substituted in the statement. This command does not actually retrieve rows; rows are retrieved by the FETCH command.

Once you have opened a cursor, its input host variables are not reexamined until you reopen the cursor. To change any input host variables and therefore the active set, you must reopen the cursor.

All cursors in a program are in a closed state when the program is initiated or when they have been explicitly closed using the CLOSE command.

You can reopen a cursor without first closing it. For more information on this command, see Programmer's Guide to the Oracle Precompilers.

Example

This example illustrates the use of the OPEN command in a Pro*C embedded SQL program: 

EXEC SQL DECLARE emp_cursor CURSOR FOR 
	SELECT ename, empno, job, sal 
	FROM emp 
	WHERE deptno = :deptno; 
EXEC SQL OPEN emp_cursor;

Related Topics

PREPARE command [*] DECLARE CURSOR command [*] FETCH command [*] CLOSE command [*]

Contents Index Home Previous Next