F
Embedded SQL Statements and Directives

This appendix contains descriptions of both the SQL92 embedded statements and directives, as well as the Oracle embedded SQL extensions.

Note: Only statements which differ in syntax from non-embedded SQL are described in this appendix. For details of the non-embedded SQL statements, see the Oracle8i SQL Reference.

This appendix has the following sections:

Summary of Precompiler Directives and Embedded SQL Statements

Embedded SQL statements place DDL, DML, and Transaction Control statements within a Pro*C/C++ program. Table F-1 provides a functional summary of the embedded SQL statements and directives.

The Source/Type column in Table F-1 is displayed in the format:

Source

Is either SQL92 standard SQL (S) or an Oracle extension (O).

Type

Is either an executable (E) statement or a directive (D).

Table F-1 Precompiler Directives and Embedded SQL Statements and Clauses

EXEC SQL Statement	Source/Type	Purpose
ALLOCATE	O/E	To allocate memory for a cursor variable or an Object type.
ALLOCATE DESCRIPTOR	S/E	Allocate a descriptor for ANSI dynamic SQL.
CACHE FREE ALL	O/E	Frees all allocated object cache memory.
CALL	S/E	Call a stored procedure.
CLOSE	S/E	To disable a cursor, releasing the resources it holds.
COLLECTION APPEND	O/E	To append elements of one collection to the end of another collection.
COLLECTION DESCRIBE	O/E	To obtain information about a collection.
COLLECTION GET	O/E	To retrieve the elements of a collection.
COLLECTION RESET	O/E	To reset the collection slice endpoints back to the beginning of the collection.
COLLECTION SET	O/E	To update values of a collection.
COLLECTION TRIM	O/E	To remove elements from the end of a collection.
COMMIT	S/E	To end the current transaction, making all database change permanent (optionally frees resources and disconnects from the database)
CONNECT	O/E	To log on to an instance.
CONTEXT ALLOCATE	O/E	To allocate memory for a SQLLIB runtime context.
CONTEXT FREE	O/E	To free memory for a SQLLIB runtime context.
CONTEXT OBJECT OPTION GET	O/E	To determine how options are set.
CONTEXT OBJECT OPTION SET	O/E	To set options.
CONTEXT USE	O/D	To specify which SQLLIB runtime context to use for subsequent executable SQL statements.
DEALLOCATE DESCRIPTOR	S/E	To deallocate a descriptor area to free memory.
DECLARE CURSOR	S/D	To declare a cursor, associating it with a query.
DECLARE DATABASE	O/D	To declare an identifier for a non-default database to be accessed in subsequent embedded SQL statements.
DECLARE STATEMENT	S/D	To assign a SQL variable name to a SQL statement.
DECLARE TABLE	O/D	To declare the table structure for semantic checking of embedded SQL statements by Pro*C/C++.
DECLARE TYPE	O/D	To declare the type structure for semantic checking of embedded SQL statements by Pro*C/C++.
DELETE	S/E	To remove rows from a table or from a view's base table.
DESCRIBE	S/E	To initialize a descriptor, a structure holding host variable descriptions.
DESCRIBE DESCRIPTOR	S/E	Obtain information about the variables in an ANSI SQL statement.
ENABLE THREADS	O/E	To initialize a process that supports multiple threads.
EXECUTE...END-EXEC	O/E	To execute an anonymous PL/SQL block.
EXECUTE	S/E	To execute a prepared dynamic SQL statement.
EXECUTE DESCRIPTOR	S/E	To execute an ANSI Method 4 dynamic SQL statement.
EXECUTE IMMEDIATE	S/E	To prepare and execute a SQL statement with no host variables.
FETCH	S/E	To retrieve rows selected by a query.
FETCH DESCRIPTOR	S/E	To retrieve rows selected using ANSI Method 4 Dynamic SQL.
FREE	O/E	To free memory allocated in the object cache, or cursor.
GET DESCRIPTOR	S/E	To move information from an ANSI SQL descriptor area into host variables.
INSERT	S/E	To add rows to a table or to a view's base table.
LOB APPEND	O/E	To append a LOB to the end of another lOB.
LOB ASSIGN	O/E	To assign a LOB or BFILE locator to another locator.
LOB CLOSE	O/E	To close an open LOB or BFILE.
LOB COPY	O/E	To copy all or part of a LOB value into another LOB.
LOB CREATE TEMPORARY	O/E	To create a temporary LOB.
LOB DESCRIBE	O/E	To retrieve attributes from a LOB.
LOB DISABLE BUFFERING	O/E	To disable LOB buffering.
LOB ENABLE BUFFERING	O/E	To enable LOB buffering.
LOB ERASE	O/E	To erase a given amount of LOB data starting from a given offset.
LOB FILE CLOSE ALL	O/E	To close all open BFILEs.
LOB FILE SET	O/E	To set DIRECTORY and FILENAME in a BFILE locator.
LOB FLUSH BUFFER	O/E	To write the LOB buffers to the database server.
LOB FREE TEMPORARY	O/E	To free temporary space for the LOB locator.
LOB LOAD	O/E	To copy all or part of a BFILE into an internal LOB.
LOB OPEN	O/E	To open a LOB or BFILE to read or read/write access.
LOB READ	O/E	To read all or part of a LOB or BFILE into a buffer.
LOB TRIM	O/E	To truncate a lob value.
LOB WRITE	O/E	To write the contents of a buffer to a LOB.
OBJECT CREATE	O/E	To create a referenceable object in the cache.
OBJECT DELETE	O/E	To mark an object as deleted.
OBJECT DEREF	O/E	To dereference an object.
OBJECT FLUSH	O/E	To transmit persistent objects to server.
OBJECT GET	O/E	To convert an object attribute to a C type.
OBJECT RELEASE	O/E	To "unpin" an object in the cache.
OBJECT SET	O/E	To update object attributes in the cache.
OBJECT UPDATE	O/E	To mark an object in the cache as updated.
OPEN	S/E	To execute the query associated with a cursor.
OPEN DESCRIPTOR	S/E	To execute the query associated with a cursor (ANSI Dynamic SQL Method 4).
PREPARE	S/E	To parse a dynamic SQL statement.
REGISTER CONNECT	O/E	To enable a call to an external procedure.
ROLLBACK	S/E	To end the current transaction, discard all changes in the current transaction, and release all locks (optionally release resources and disconnect from the database).
SAVEPOINT	S/E	To identify a point in a transaction to which you can later roll back.
SELECT	S/E	To retrieve data from one or more tables, views, or snapshots, assigning the selected values to host variables.
SET DESCRIPTOR	S/E	To set information in the descriptor area from host variables.
TYPE	O/D	To assign an external datatype to a whole class of host variables by equivalencing the external datatype to a user-defined datatype.
UPDATE	S/E	To change existing values in a table or in a view's base table.
VAR	O/D	To override the default datatype and assign a specific external datatype to a host variable.
WHENEVER	S/D	To specify handling for error and warning conditions.

About The Statement Descriptions

The directives and statements appear alphabetically. The description of each contains the following sections:

Purpose

Describes the basic uses of the statement.

Prerequisites

Lists privileges you must have and steps that you must take before using the statement. Unless otherwise noted, most statements also require that the database be open by your instance.

Syntax

Shows the keywords and parameters of the statement.

Keywords and Parameters

Describes the purpose of each keyword and parameter.

Usage Notes

Discusses how and when to use the statement.

Examples

Shows example statements of the statement.

Related Topics

Lists related statements, clauses, and sections of this manual.

How to Read Syntax Diagrams

Syntax diagrams are used to illustrate embedded SQL syntax. They are drawings that depict valid syntax paths.

Trace each diagram from left to right, in the direction shown by the arrows.

Statements and other keywords appear in UPPER CASE inside rectangles. Type them exactly as shown in the rectangles. Parameters appear in lower case inside ovals. Substitute variables for the parameters in statements you write. Operators, delimiters, and terminators appear in circles. Following the conventions defined in the Preface, a semicolon terminates statements.

If the syntax diagram has more than one path, you can choose any path to travel.

If you have the choice of more than one keyword, operator, or parameter, your options appear in a vertical list. In the following example, you can travel down the vertical line as far as you like, then continue along any horizontal line:

According to the diagram, all of the following statements are valid:

EXEC SQL WHENEVER NOT FOUND ... 
EXEC SQL WHENEVER SQLERROR ... 
EXEC SQL WHENEVER SQLWARNING ...

Required Keywords and Parameters

Required keywords and parameters can appear singly or in a vertical list of alternatives. Single required keywords and parameters appear on the main path, that is, on the horizontal line you are currently traveling. In the following example, cursor is a required parameter:

If there is a cursor named emp_cursor, then, according to the diagram, the following statement is valid:

EXEC SQL CLOSE emp_cursor;

If any of the keywords or parameters in a vertical list appears on the main path, one of them is required. That is, you must choose one of the keywords or parameters, but not necessarily the one that appears on the main path. In the following example, you must choose one of the four actions:

Optional Keywords and Parameters

If keywords and parameters appear in a vertical list above the main path, they are optional. In the following example, AT :db_name and WORK are optional:

If there is a database named oracle2, then, according to the diagram, all of the following statements are valid:

EXEC SQL ROLLBACK; 
EXEC SQL ROLLBACK WORK; 
EXEC SQL AT oracle2 ROLLBACK;

Syntax Loops

Loops let you repeat the syntax within them as many times as you like. In the following example, column_name is inside a loop. So, after choosing one column name, you can go back repeatedly to choose another, separating the column names by a comma.

If DEBIT, CREDIT, and BALANCE are column names, then, according to the diagram, all of the following statements are valid:

EXEC SQL SELECT DEBIT INTO ... 
EXEC SQL SELECT CREDIT, BALANCE INTO ... 
EXEC SQL SELECT DEBIT, CREDIT, BALANCE INTO ...

Multi-part Diagrams

Read a multi-part diagram as if all the main paths were joined end-to-end. The following example is a two-part diagram:

According to the diagram, the following statement is valid:

EXEC SQL PREPARE statement_name FROM string_literal;

Database Objects

The names of Oracle objects, such as tables and columns, must not exceed 30 characters in length. The first character must be a letter, but the rest can be any combination of letters, numerals, dollar signs ($), pound signs (#), and underscores (_).

However, if an Oracle identifier is enclosed by quotation marks ("), it can contain any combination of legal characters, including spaces but excluding quotation marks.

Oracle identifiers are not case-sensitive except when enclosed by quotation marks.

Statement Terminator

In all embedded SQL diagrams, each statement is understood to end with the statement terminator ";".

ALLOCATE (Executable Embedded SQL Extension)

Purpose

To allocate a cursor variable to be referenced in a PL/SQL block, or to allocate space in the object cache.

Prerequisites

A cursor variable (see Chapter 4, "Datatypes and Host Variables") of type sql_cursor must be declared before allocating memory for the cursor variable.

Pointers to a host struct and, optionally, an indicator struct must be declared before allocating memory in the object cache.

An active connection to a database is required.

Syntax

Keywords and Parameters

db_name

A null-terminated string containing the database connection name, as established previously in a CONNECT statement. If it is omitted, or if it is an empty string, the default database connection is assumed.

host_variable

A host variable containing the name of the database connection.

cursor_variable

A cursor variable to be allocated.

host_ptr

A pointer to a host struct generated by OTT for object types, a context variable of type sql_context, a ROWID variable of type pointer to OCIRowid, or a LOB locator variable corresponding to the type of LOB.

ind_ptr

An optional pointer to an indicator struct.

Usage Notes

While a cursor is static, a cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query.

For more information on this statement, see PL/SQL User's Guide and Reference and Oracle8i SQL Reference.

Example

This partial example illustrates the use of the ALLOCATE statement in a Pro*C/C++ program:

EXEC SQL BEGIN DECLARE SECTION;
   SQL_CURSOR emp_cv;
   struct{ ... } emp_rec;
EXEC SQL END DECLARE SECTION;
EXEC SQL ALLOCATE :emp_cv;
EXEC SQL EXECUTE
   BEGIN
      OPEN :emp_cv FOR SELECT * FROM emp;
   END;
END-EXEC;
for (;;)
   {
   EXEC SQL FETCH :emp_cv INTO :emp_rec;
   ...
   }

ALLOCATE DESCRIPTOR (Executable Embedded SQL)

Purpose

An ANSI dynamic SQL statement that allocates a descriptor.

Prerequisites

None.

Syntax

Keywords and Parameters

array_size
integer

Host variable containing number of rows to be processed.
Number of rows to be processed.

descriptor_name
descriptor name

Host variable containing the name of the ANSI descriptor.
Name of the ANSI descriptor.

GLOBAL | LOCAL

LOCAL (the default) means file scope, as opposed to GLOBAL, which means application scope.

WITH MAX integer

Maximum number of host variables. The default is 100.

Usage Notes

Use DYNAMIC=ANSI precompiler option. For information on using this statement, see "ALLOCATE DESCRIPTOR".

Example

EXEC SQL FOR :batch ALLOCATE DESCRIPTOR GLOBAL :binddes WITH MAX 25 ;

CACHE FREE ALL (Executable Embedded SQL Extension)

Purpose

To free all memory in the object cache.

Prerequisites

An active database connection must exist.

Syntax

Keywords and Parameters

Usage Notes

When the connection count drops to zero, SQLLIB automatically frees all object cache memory. For more information, see "CACHE FREE ALL".

Example

EXEC SQL AT mydb CACHE FREE ALL ;

CALL (Executable Embedded SQL)

Purpose

To call a stored procedure.

Prerequisites

An active database connection must exist.

Syntax

Keywords and Parameters

schema

Is the schema containing the procedure. If you omit schema, Oracle8i assumes the procedure is in your own schema.

pkg

The package where the procedure is stored.

st_proc

The stored procedure to be called.

db_link

The complete or partial name of a database link to a remote database where the procedure is located. For information on referring to database links, see the Oracle8i SQL Reference.

expr

The list of expressions that are the parameters of the procedure.

ret_var

The host variable that receives the returned value of a function.

ret_ind

The indicator variable for ret_var.

Usage Notes

For more about this statement, see "Calling a Stored PL/SQL or Java Subprogram".

For a complete discussion of stored procedures, see Oracle8i Application Developer's Guide - Fundamentals, "External Routines" chapter.

Example

int emp_no;
char emp_name[10];
float salary;
char dept_name[20];
...
emp_no = 1325;
EXEC SQL CALL get_sal(:emp_no, :emp_name, :salary) INTO :dept_name ;
/* Print emp_name, salary, dept_name */
...

CLOSE (Executable Embedded SQL)

Purpose

To disable a cursor, freeing the resources acquired by opening the cursor, and releasing parse locks.

Prerequisites

The cursor or cursor variable be open if MODE=ANSI.

Syntax

Keywords and Parameters

cursor

A cursor to be closed.

cursor_variable

A cursor variable to be closed.

Usage Notes

Rows cannot be fetched from a closed cursor. A cursor need not be closed to be reopened. The HOLD_CURSOR and RELEASE_CURSOR precompiler options alter the effect of the CLOSE statement. For information on these options, see Chapter 10, "Precompiler Options".

Example

This example illustrates the use of the CLOSE statement:

EXEC SQL CLOSE emp_cursor;

COLLECTION APPEND (Executable Embedded SQL Extension)

Purpose

To append elements of one collection to the end of another collection.

Prerequisites

You cannot append to a NULL collection, or append beyond the upper bound of a collection.

Syntax

Usage Notes

For usage notes as well as keywords, parameters, and examples, see "COLLECTION APPEND".

COLLECTION DESCRIBE (Executable Embedded SQL Extension)

Purpose

To obtain information about a collection.

Prerequisites

Use the ALLOCATE and OBJECT GET statements to allocate a descriptor and to store collection attributes in the descriptor.

Syntax

Prerequisites

To commit your current transaction, no privileges are necessary.

To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.

Syntax

Keyword and Parameters

AT

Identifies the database to which the COMMIT statement is issued. 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 db_name.

If you omit this clause, Oracle8i issues the statement to your default database.

WORK

Is supported only for compliance with standard SQL. The statements COMMIT and COMMIT WORK are equivalent.

COMMENT

Specifies a Comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle8i stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.

RELEASE

Frees all resources and disconnects the application from the server.

FORCE

Manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can also use the optional integer to explicitly assign the transaction a system change number (SCN). If you omit the integer, the transaction is committed using the current SCN.

Usage Notes

Always explicitly commit or rollback the last transaction in your program by using the COMMIT or ROLLBACK statement and the RELEASE option. Oracle8i automatically rolls back changes if the program terminates abnormally.

The COMMIT statement has no effect on host variables or on the flow of control in the program. For more information on this statement, see Chapter 3, "Database Concepts".

Example

This example illustrates the use of the embedded SQL COMMIT statement:

EXEC SQL AT sales_db COMMIT RELEASE;

CONNECT (Executable Embedded SQL Extension)

Purpose

To log on to a database.

Prerequisites

You must have CREATE SESSION system privilege in the specified database.

Syntax

Keyword and Parameters

user
password

Specifies your username and password separately.

user_password

A single host variable containing the username and password separated by a slash (/).

To allow Oracle8i to verify your connection through your operating system, specify "/" as the :user_password value.

AT

Identifies the database to which the connection is made. 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.

USING

Uses the Net8 database specification string used to connect to a non-default database. If you omit this clause, you are connected to your default database.

ALTER AUTHORIZATION

Change password to the following string.

new_password

The new password.

IN SYSDBA MODE
IN SYSOPER MODE

Connect with SYSDBA or SYSOPER system privileges. Not allowed when ALTER AUTHORIZATION is used, or precompiler option AUTO_CONNECT is set to YES.

Usage Notes

A program can have multiple connections, but can only connect once to your default database. For more information on this statement, see "Embedding (OCI Release 7) Calls".

Example

The following example illustrate the use of CONNECT:

EXEC SQL CONNECT :username 
    IDENTIFIED BY :password ;

You can also use this statement in which the value of :userid is the value of :username and :password separated by a "/" such as 'SCOTT/TIGER':

EXEC SQL CONNECT :userid ;

CONTEXT ALLOCATE (Executable Embedded SQL Extension)

Purpose

To initialize a SQLLIB runtime context that is referenced in an EXEC SQL CONTEXT USE statement.

Prerequisites

The runtime context must be declared of type sql_context.

Syntax

Keywords and Parameters

context

Is the SQLLIB runtime context for which memory is to be allocated.

Usage Notes

In a multi-threaded application, execute this function once for each runtime context.

For more information on this statement, see "SQLLIB Extensions for OCI Release 8 Interoperability".

Example

This example illustrates the use of a CONTEXT ALLOCATE statement in a Pro*C/C++ program:

EXEC SQL CONTEXT ALLOCATE :ctx1;

CONTEXT FREE (Executable Embedded SQL Extension)

Purpose

To free all memory associated with a runtime context and place a null pointer in the host program variable.

Prerequisites

The CONTEXT ALLOCATE statement must be used to allocate memory for the specified runtime context before the CONTEXT FREE statement can free the memory allocated for it.

Syntax

Keywords and Parameters

:context

The allocated runtime context for which the memory is to be deallocated.

Usage Notes

For more information on this statement, see "SQLLIB Extensions for OCI Release 8 Interoperability".

Example

This example illustrates the use of a CONTEXT FREE statement in a Pro*C/C++ program:

EXEC SQL CONTEXT FREE :ctx1;

CONTEXT OBJECT OPTION GET (Executable Embedded SQL Extension)

Purpose

To determine the values of options set by CONTEXT OBJECT OPTION SET for the context in use.

Prerequisites

Precompiler option OBJECTS must be set to YES.

Syntax

Keywords and Parameters

option DATEFORMAT (format for Date conversion) or DATELANG (language for conversion)

host_variable Output of type STRING, VARCHAR, or CHARZ, in the same order as the option list.

Usage Notes

See "CONTEXT OBJECT OPTION SET".

Example

char EuroFormat[50];
...
EXEC SQL CONTEXT OBJECT OPTION GET DATEFORMAT INTO :EuroFormat ;
printf("Date format is %s\n", EuroFormat);

CONTEXT OBJECT OPTION SET (Executable Embedded SQL Ext)

Purpose

To set options to specified values of Date attributes: DATEFORMAT, DATELANG for the context in use.

Prerequisites

Precompiler option OBJECTS must be set to YES.

Syntax

Keywords and Parameters

option

DATEFORMAT (format for Date conversion) or DATELANG (language for Date conversion)

expr

Input of type STRING, VARCHAR, or CHARZ. In the same order as the option list.

Usage Notes

See "CONTEXT OBJECT OPTION GET".

Example

char *new_format = "DD-MM-YYY";
char *new_lang = "French";
...
EXEC SQL CONTEXT OBJECT OPTION SET DATEFORMAT, DATELANG to :new_format, 
:new_lang;

CONTEXT USE (Oracle Embedded SQL Directive)

Purpose

To instruct the precompiler to use the specified SQLLIB runtime context on subsequent executable SQL statements.

Prerequisites

The runtime context specified by the CONTEXT USE directive must be previously declared.

Syntax

Keywords and Parameters

context

The allocated runtime context to use for subsequent executable SQL statements that follow it. For example, after specifying in your source code which context to use (multiple contexts can be allocated), you can connect to the Oracle Server and perform database operations within the scope of that context. DEFAULT indicates that the global context that you worked with is to be used.

DEFAULT

Indicates that the global context is to be used.

Usage Notes

This statement has no effect on declarative statements such as EXEC SQL INCLUDE or EXEC ORACLE OPTION. It works similarly to the EXEC SQL WHENEVER directive in that it affects all executable SQL statements which positionally follow it in a given source file without regard to standard C scope rules.

For more information on this statement, see "SQLLIB Extensions for OCI Release 8 Interoperability".

Example

This example illustrates the use of a CONTEXT USE directive in a Pro*C/C++ embedded SQL program:

EXEC SQL CONTEXT USE :ctx1;

DEALLOCATE DESCRIPTOR (Embedded SQL Statement)

Purpose

An ANSI dynamic SQL statement that deallocates a descriptor area to free memory.

Prerequisites

The descriptor specified by the DEALLOCATE DESCRIPTOR statement must be previously allocated using the ALLOCATE DESCRIPTOR statement.

Syntax

Keywords and Parameters

GLOBAL | LOCAL

LOCAL (the default) means file scope, as opposed to GLOBAL, which means application scope.

descriptor_name
'descriptor name'

Host variable containing the name of the allocated ANSI descriptor.
Name of the allocated ANSI descriptor.

'

Usage Notes

Use DYNAMIC=ANSI precompiler option.

For more information on this statement, see "DEALLOCATE DESCRIPTOR".

Example

EXEC SQL DEALLOCATE DESCRIPTOR GLOBAL 'SELDES' ;

DECLARE CURSOR (Embedded SQL Directive)

Purpose

To declare a cursor, giving it a name and associating it with a SQL statement or a PL/SQL block.

Prerequisites

If you associate the cursor with an identifier for a SQL statement or PL/SQL block, you must have declared this identifier in a previous DECLARE STATEMENT statement.

Syntax

Keywords and Parameters

AT

Identifies the database on which the cursor 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, Oracle8i declares the cursor on your default database.

cursor

Is the name of the cursor to be declared.

SELECT statement

Is a SELECT statement to be associated with the cursor. The following statement cannot contain an INTO clause.

statement_name

Identifies a SQL statement or PL/SQL block to be associated with the cursor. The statement_name or block_name must be previously declared in a DECLARE STATEMENT statement.

Usage Notes

You must declare a cursor before referencing it in other embedded SQL statements. The scope of a cursor declaration is global within its precompilation unit and the name of each cursor must be unique in its scope. You cannot declare two cursors with the same name in a single precompilation unit.

You can reference the cursor in the WHERE clause of an UPDATE or DELETE statement using the CURRENT OF syntax, provided that the cursor has been opened with an OPEN statement and positioned on a row with a FETCH statement. For more information on this statement, see "Cursor Usage in Embedded PL/SQL".

Example

This example illustrates the use of a DECLARE CURSOR statement:

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

DECLARE DATABASE (Oracle Embedded SQL Directive)

Purpose

To declare an identifier for a non-default database to be accessed in subsequent embedded SQL statements.

Prerequisites

You must have access to a username on the non-default database.

Syntax

Keywords and Parameters

db_name

Is the identifier established for the non-default database.

Usage Notes

You declare a db_name for a non-default database so that other embedded SQL statements can refer to that database using the AT clause. Before issuing a CONNECT statement with an AT clause, you must declare a db_name for the non-default database with a DECLARE DATABASE statement.

For more information on this statement, see "Single Explicit Connections".

Example

This example illustrates the use of a DECLARE DATABASE directive:

EXEC SQL DECLARE oracle3 DATABASE ;

DECLARE STATEMENT (Embedded SQL Directive)

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

AT

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

Source	Is either SQL92 standard SQL (S) or an Oracle extension (O).
Type	Is either an executable (E) statement or a directive (D).

Purpose	Describes the basic uses of the statement.
Prerequisites	Lists privileges you must have and steps that you must take before using the statement. Unless otherwise noted, most statements also require that the database be open by your instance.
Syntax	Shows the keywords and parameters of the statement.
Keywords and Parameters	Describes the purpose of each keyword and parameter.
Usage Notes	Discusses how and when to use the statement.
Examples	Shows example statements of the statement.
Related Topics	Lists related statements, clauses, and sections of this manual.

db_name	A null-terminated string containing the database connection name, as established previously in a CONNECT statement. If it is omitted, or if it is an empty string, the default database connection is assumed.
host_variable	A host variable containing the name of the database connection.
cursor_variable	A cursor variable to be allocated.
host_ptr	A pointer to a host struct generated by OTT for object types, a context variable of type `sql_context`, a ROWID variable of type pointer to `OCIRowid,` or a LOB locator variable corresponding to the type of LOB.
ind_ptr	An optional pointer to an indicator struct.

array_size integer	Host variable containing number of rows to be processed. Number of rows to be processed.
descriptor_name descriptor name	Host variable containing the name of the ANSI descriptor. Name of the ANSI descriptor.
GLOBAL \| LOCAL	LOCAL (the default) means file scope, as opposed to GLOBAL, which means application scope.
WITH MAX integer	Maximum number of host variables. The default is 100.

db_name	A null-terminated string containing the database connection name, as established previously in a CONNECT statement. If it is omitted, or if it is an empty string, the default database connection is assumed.
host_variable	A host variable containing the name of the database connection.

schema	Is the schema containing the procedure. If you omit schema, Oracle8i assumes the procedure is in your own schema.
pkg	The package where the procedure is stored.
st_proc	The stored procedure to be called.
db_link	The complete or partial name of a database link to a remote database where the procedure is located. For information on referring to database links, see the Oracle8i SQL Reference.
expr	The list of expressions that are the parameters of the procedure.
ret_var	The host variable that receives the returned value of a function.
ret_ind	The indicator variable for ret_var.

cursor	A cursor to be closed.
cursor_variable	A cursor variable to be closed.

AT	Identifies the database to which the COMMIT statement is issued. 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 db_name.
	If you omit this clause, Oracle8i issues the statement to your default database.
WORK	Is supported only for compliance with standard SQL. The statements COMMIT and COMMIT WORK are equivalent.
COMMENT	Specifies a Comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle8i stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.
RELEASE	Frees all resources and disconnects the application from the server.
FORCE	Manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can also use the optional integer to explicitly assign the transaction a system change number (SCN). If you omit the integer, the transaction is committed using the current SCN.

user password	Specifies your username and password separately.
user_password	A single host variable containing the username and password separated by a slash (/).
	To allow Oracle8i to verify your connection through your operating system, specify "/" as the :user_password value.
AT	Identifies the database to which the connection is made. 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.
USING	Uses the Net8 database specification string used to connect to a non-default database. If you omit this clause, you are connected to your default database.
ALTER AUTHORIZATION	Change password to the following string.
new_password	The new password.
IN SYSDBA MODE IN SYSOPER MODE	Connect with SYSDBA or SYSOPER system privileges. Not allowed when ALTER AUTHORIZATION is used, or precompiler option AUTO_CONNECT is set to YES.

option	DATEFORMAT (format for Date conversion) or DATELANG (language for conversion)
host_variable	Output of type STRING, VARCHAR, or CHARZ, in the same order as the option list.

context	The allocated runtime context to use for subsequent executable SQL statements that follow it. For example, after specifying in your source code which context to use (multiple contexts can be allocated), you can connect to the Oracle Server and perform database operations within the scope of that context. DEFAULT indicates that the global context that you worked with is to be used.
DEFAULT	Indicates that the global context is to be used.

AT	Identifies the database on which the cursor 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, Oracle8i declares the cursor on your default database.
cursor	Is the name of the cursor to be declared.
SELECT statement	Is a SELECT statement to be associated with the cursor. The following statement cannot contain an INTO clause.
statement_name	Identifies a SQL statement or PL/SQL block to be associated with the cursor. The statement_name or block_name must be previously declared in a DECLARE STATEMENT statement.

FEmbedded SQL Statements and Directives

Summary of Precompiler Directives and Embedded SQL Statements

Table F-1 Precompiler Directives and Embedded SQL Statements and Clauses

About The Statement Descriptions

How to Read Syntax Diagrams

Required Keywords and Parameters

Optional Keywords and Parameters

Syntax Loops

Multi-part Diagrams

Database Objects

Statement Terminator

ALLOCATE (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

ALLOCATE DESCRIPTOR (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

CACHE FREE ALL (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

CALL (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

CLOSE (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

COLLECTION APPEND (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Usage Notes

Related Topics

COLLECTION DESCRIBE (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Usage Notes

Related Topics

COLLECTION GET (Executable Embedded SQL Extension)

Purpose

Syntax

Usage Notes

Related Topics

COLLECTION RESET (Executable Embedded SQL Extension)

Purpose

Syntax

Usage Notes

Related Topics

COLLECTION SET (Executable Embedded SQL Extension)

Purpose

Syntax

Usage Notes

Related Topics

COLLECTION TRIM (Executable Embedded SQL Extension)

Purpose

F
Embedded SQL Statements and Directives