Oracle7 Server SQL Reference

DECLARE STATEMENT (Embedded SQL)

Purpose

To declare an identifier for a SQL statement or PL/SQL block to be used in other embedded SQL statements.

Prerequisites

None.

Syntax

Keywords and Parameters

identifies the database on which the SQL statement or PL/SQL block is declared. The database can be identified by either:

db_name is a database identifier declared in a previous DECLARE DATABASE statement.

:host_variable is a host variable whose value is a previously declared db_name.

If you omit this clause, Oracle7 declares the SQL statement or PL/SQL block on your default database.

statement_name block_name

is the declared identifier for the statement.

Usage Notes

You must declare an identifier for a SQL statement or PL/SQL block with a DECLARE STATEMENT statement only if a DECLARE CURSOR statement referencing the identifier appears physically (not logically) in the embedded SQL program before the PREPARE statement that parses the statement or block and associates it with its identifier.

The scope of a statement declaration is global within its precompilation unit, like a cursor declaration. For more information on this command, see Programmer's Guide to the Oracle Precompilers.

Example I

This example illustrates the use of the DECLARE STATEMENT statement:

EXEC SQL AT remote_db

	DECLARE my_statement STATEMENT

EXEC SQL PREPARE my_statement FROM :my_string

EXEC SQL EXECUTE my_statement

Example II

In this example from a Pro*C embedded SQL program, the DECLARE STATEMENT statement is required because the DECLARE CURSOR statement precedes the PREPARE statement:

EXEC SQL DECLARE my_statement STATEMENT;

call prepare_my_statement;

EXEC SQL DECLARE emp_cursor CURSOR FOR my_statement;

...

PROCEDURE prepare_my_statement

BEGIN

	EXEC SQL PREPARE my_statement FROM :my_string;

END;