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 nondefault 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:

Directives and Statements

Description

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:

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:

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.

Oracle Names

The names of Oracle database 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 a name is enclosed by quotation marks ("), it can contain any combination of legal characters, including spaces but excluding quotation marks.

Oracle names 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.

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.

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.

Usage Notes

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

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".

COLLECTION TRIM (Executable Embedded SQL Extension)

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

Related Topics

See the other COLLECTION statements.

COMMIT (Executable Embedded SQL)

Purpose

To end your current transaction, making permanent all its changes to the database and optionally freeing all resources and disconnecting.

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.

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, Oracle 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 Oracle 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. Oracle 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:

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.

If you omit this clause, Oracle declares the cursor on your default database.

cursor

Name of the cursor to be declared.

WITH HOLD

Cursor remains open after a COMMIT. The cursor must not be declared for UPDATE.

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 if 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".

You declare a db_name for a nondefault 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 nondefault database with a DECLARE DATABASE statement.

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 db_name. If you omit this clause, Oracle declares the SQL statement or PL/SQL block to your default database.

statement_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.

DELETE (Executable Embedded SQL)

Purpose

To remove rows from a table or from a view's base table.

Prerequisites

For you to delete rows from a table, the table must be in your own schema or you must have DELETE privilege on the table.

For you to delete rows from the base table of a view, the owner of the schema containing the view must have DELETE privilege on the base table. Also, if the view is in a schema other than your own, you must be granted DELETE privilege on the view.

The DELETE ANY TABLE system privilege also provides the ability to delete rows from any table or any view's base table.

Identifies the database to which the DELETE 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 previously declared db_name. If you omit this clause, the DELETE statement is issued to your default database.

FOR :host_integer

Limits the number of times the statement is executed if the WHERE clause contains array host variables. If you omit this clause, Oracle executes the statement once for each component of the smallest array.

Is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.

table

The name of a table from which the rows are to be deleted.

view

The name of a view. Oracle deletes rows from the view's base table.

FOR :host_integer

Limits the number of times the statement is executed if the WHERE clause contains array host variables. If you omit this clause, Oracle executes the statement once for each component of the smallest array.

Is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.

table

The name of a table from which the rows are to be deleted.

view

The name of a view. Oracle deletes rows from the view's base table.

Usage Notes

The host variables in the WHERE clause must be either all scalars or all arrays. If they are scalars, Oracle executes the DELETE statement only once. If they are arrays, Oracle executes the statement once for each set of array components. Each execution may delete zero, one, or multiple rows.

Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle executes the statement is determined by the smaller of the following values:

The size of the smallest array

The value of the :host_integer in the optional FOR clause

If no rows satisfy the condition, no rows are deleted and the SQLCODE returns a NOT_FOUND condition.

The cumulative number of rows deleted is returned through the SQLCA. If the WHERE clause contains array host variables, this value reflects the total number of rows deleted for all components of the array processed by the DELETE statement.

If no rows satisfy the condition, Oracle returns an error through the SQLCODE of the SQLCA. If you omit the WHERE clause, Oracle raises a warning flag in the fifth component of SQLWARN in the SQLCA. For more information on this statement and the SQLCA, see Chapter 9, "Handling Runtime Errors".

You can use Comments in a DELETE statement to pass instructions, or hints, to the optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle Database Performance Tuning Guide.

Example

This example illustrates the use of the DELETE statement within a Pro*C/C++ embedded SQL program:

Initializes the descriptor to hold information about the input variables for the SQL statement or PL/SQL block.

SELECT LIST FOR

Initializes the descriptor to hold information about the select list of a SELECT statement.

The default is SELECT LIST FOR.

statement_name

Identifies a SQL statement or PL/SQL block previously prepared with a PREPARE statement.

descriptor

Is the name of the descriptor to be populated.

Usage Notes

You must issue a DESCRIBE statement before manipulating the bind or select descriptor within an embedded SQL program.

You cannot describe both input variables and output variables into the same descriptor.

The number of variables found by a DESCRIBE statement is the total number of place-holders in the prepare SQL statement or PL/SQL block, rather than the total number of uniquely named place-holders. For more information on this statement, see Chapter 13, "Oracle Dynamic SQL".

Example

This example illustrates the use of the DESCRIBE statement in a Pro*C/C++ embedded SQL program:

The name of the previously prepared SQL statement or PL/SQL block. OUTPUT is the default.

desc_name

Host variable containing the name of the descriptor that will hold information about the SQL statement.

'descriptor name'

The name of the descriptor.

GLOBAL | LOCAL

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

Usage Notes

Use DYNAMIC=ANSI precompiler option.

Only COUNT and NAME are implemented for the INPUT descriptor.

The number of variables found by a DESCRIBE statement is the total number of place-holders in the prepare SQL statement or PL/SQL block, rather than the total number of uniquely named place-holders. For more information on this statement, see "DESCRIBE INPUT" , and "DESCRIBE OUTPUT".

ENABLE THREADS (Executable Embedded SQL Extension)

You must be developing a precompiler application for and compiling it on a platform that supports multithreaded applications, and THREADS=YES must be specified on the command line.

Note:

When using XA with the Pro*C/C++ Precompiler, you must use multithreaded processing provided by XA. Use of multithreaded processing provided by Pro*C/C++ using the statement EXEC SQL ENABLE THREADS will result in an error.

This keyword must appear after the embedded PL/SQL block, regardless of which programming language your Oracle Precompiler program uses. The keyword END-EXEC must be followed by the C/C++ statement terminator, ";".

Usage Notes

Since the Pro*C/C++ treats an embedded PL/SQL block like a single embedded SQL statement, you can embed a PL/SQL block anywhere in a program that you can embed a SQL statement. For more information on embedding PL/SQL blocks in Oracle Precompiler programs, see Chapter 7, " Embedded PL/SQL".

Example

Placing this EXECUTE statement in a Pro*C/C++ program embeds a PL/SQL block in the program:

EXECUTE (Executable Embedded SQL)

Purpose

In Oracle dynamic SQL, to execute a DELETE, INSERT, or UPDATE statement or a PL/SQL block that has been previously prepared with an embedded SQL PREPARE statement. For ANSI Dynamic SQL Method 4 see "EXECUTE DESCRIPTOR (Executable Embedded SQL)".

Prerequisites

You must first prepare the SQL statement or PL/SQL block with an embedded SQL PREPARE statement.

Limits the number of times the statement is executed when the USING clause contains array host variables If you omit this clause, Oracle executes the statement once for each component of the smallest array.

statement_id

A precompiler identifier associated with the SQL statement or PL/SQL block to be executed. Use the embedded SQL PREPARE statement to associate the precompiler identifier with the statement or PL/SQL block.

USING DESCRIPTOR SQLDA_descriptor

Uses an Oracle descriptor. CANNOT be used together with an ANSI descriptor (INTO clause).

USING

Specifies a list of host variables with optional indicator variables that Oracle substitutes as input variables into the statement to be executed. The host and indicator variables must be either all scalars or all arrays.

Limits the number of times the statement is executed. Oracle executes the statement once for each component of the smallest array.

statement_id

A precompiler identifier associated with the SQL statement or PL/SQL block to be executed. Use the embedded SQL PREPARE statement to associate the precompiler identifier with the statement or PL/SQL block.

GLOBAL | LOCAL

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

USING

An ANSI descriptor.

descriptor_name

Host variable containing name of the input descriptor.

'descriptor name'

Name of the input descriptor.

INTO

An ANSI descriptor.

descriptor_name

Host variable containing name of the output descriptor.

'descriptor name'

Name of the output descriptor.

GLOBAL | LOCAL

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

Identifies the database on which the SQL statement or PL/SQL block is executed. 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, the statement or block is executed on your default database.

text

Is a quoted text literal (or a text literal without quotes) containing the SQL statement or PL/SQL block to be executed.

The SQL statement can only be a DELETE, INSERT, or UPDATE statement.

host_string

A host variable containing a SQL statement.

Usage Notes

When you issue an EXECUTE IMMEDIATE statement, Oracle parses the specified SQL statement or PL/SQL block, checking for errors, and executes it. If any errors are encountered, they are returned in the SQLCODE component of the SQLCA.

Limits the number of rows fetched if you are using array host variables. If you omit this clause, Oracle fetches enough rows to fill the smallest array.

cursor

A cursor that is declared by a DECLARE CURSOR statement. The FETCH statement returns one of the rows selected by the query associated with the cursor.

cursor_variable

A cursor variable is allocated an ALLOCATE statement. The FETCH statement returns one of the rows selected by the query associated with the cursor variable.

INTO

Specifies a list of host variables and optional indicator variables into which data is fetched. These host variables and indicator variables must be declared within the program.

host_variable

Host variables to receive data,

indicator_variables

Host indicator variables.

USING SQLDA_variable

Specifies the Oracle descriptor referenced in a previous DESCRIBE statement. Only use this clause with dynamic embedded SQL, method 4. The USING clause does not apply when a cursor variable is used.

Usage Notes

The FETCH statement reads the rows of the active set and names the output variables which contain the results. Indicator values are set to -1 if their associated host variable is NULL. The first FETCH statement for a cursor also sorts the rows of the active set, if necessary.

The number of rows retrieved is specified by the size of the output host variables and the value specified in the FOR clause. The host variables to receive the data must be either all scalars or all arrays. If they are scalars, Oracle fetches only one row. If they are arrays, Oracle fetches enough rows to fill the arrays.

Array host variables can have different sizes. In this case, the number of rows Oracle fetches is determined by the smaller of the following values:

The size of the smallest array

The value of the :array_size in the optional FOR clause

Of course, the number of rows fetched can be further limited by the number of rows that actually satisfy the query.

If a FETCH statement does not retrieve all rows returned by the query, the cursor is positioned on the next returned row. When the last row returned by the query has been retrieved, the next FETCH statement results in an error code returned in the SQLCODE element of the SQLCA.

The FETCH statement does not contain an AT clause. You must specify the database accessed by the cursor in the DECLARE CURSOR statement.

You can only move forward through the active set with FETCH statements. If you want to revisit any of the previously fetched rows, you must reopen the cursor and fetch each row in turn. If you want to change the active set, you must assign new values to the input host variables in the cursor's query and reopen the cursor.

Limits the number of rows fetched if you are using array host variables. If you omit this clause, Oracle fetches enough rows to fill the smallest array.

cursor

A cursor that has been declared by a DECLARE CURSOR statement. The FETCH statement returns one of the rows selected by the query associated with the cursor.

cursor_variable

A cursor variable is allocated an ALLOCATE statement. The FETCH statement returns one of the rows selected by the query associated with the cursor variable.

GLOBAL | LOCAL

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

INTO

Specifies a list of host variables and optional indicator variables into which data is fetched. These host variables and indicator variables must be declared within the program.

descriptor name'

:descriptor_name

Name of the output ANSI descriptor.

Host variable containing the name of the output descriptor.

Usage Notes

The number of rows retrieved is specified by the size of the output host variables and the value specified in the FOR clause. The host variables to receive the data must be either all scalars or all arrays. If they are scalars, Oracle fetches only one row. If they are arrays, Oracle fetches enough rows to fill the arrays.

Array host variables can have different sizes. In this case, the number of rows Oracle fetches is determined by the smaller of the following values:

The size of the smallest array.

The value of the :array_size in the optional FOR clause.

Of course, the number of rows fetched can be further limited by the number of rows that actually satisfy the query.

If a FETCH statement does not retrieve all rows returned by the query, the cursor is positioned on the next returned row. When the last row returned by the query has been retrieved, the next FETCH statement results in an error code returned in the SQLCODE element of the SQLCA.

The FETCH statement does not contain an AT clause. You must specify the database accessed by the cursor in the DECLARE CURSOR statement.

You can only move forward through the active set with FETCH statements. If you want to revisit any of the previously fetched rows, you must reopen the cursor and fetch each row in turn. If you want to change the active set, you must assign new values to the input host variables in the cursor's query and reopen the cursor.

Use DYNAMIC=ANSI precompiler option for the ANSI SQL Method 4 application. For more information, see "FETCH" for the ANSI SQL Method 4 application.

INSERT (Executable Embedded SQL)

Purpose

To add rows to a table or to a view's base table.

Prerequisites

For you to insert rows into a table, the table must be in your own schema or you must have INSERT privilege on the table.

For you to insert rows into the base table of a view, the owner of the schema containing the view must have INSERT privilege on the base table. Also, if the view is in a schema other than your own, you must have INSERT privilege on the view.

The INSERT ANY TABLE system privilege also provides the ability to insert rows into any table or any view's base table.

Identifies the database on which the INSERT statement is executed. 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, the INSERT statement is executed on your default database.

FOR :host_integer

integer

Limits the number of times the statement is executed if the VALUES clause contains array host variables.

If you omit this clause, Oracle executes the statement once for each component in the smallest array.

schema

The schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.

table

view

The name of the table into which rows are to be inserted.

If you specify view, Oracle inserts rows into the view's base table.

db_link

A complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see the Oracle Database SQL Language Reference.

You can only insert rows into a remote table or view if you are using Oracle with the distributed option.

If you omit dblink, Oracle assumes that the table or view is on the local database.

part_name

Is the name of a partition of the table.

column

If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. If you omit the column list altogether, the VALUES clause or query must specify values for all columns in the table.

VALUES

Specifies a row of values to be inserted into the table or view. See the syntax description in Oracle Database SQL Language Reference. The expressions can be host variables with optional indicator variables. You must specify an expression in the VALUES clause for each column in the column list.

subquery

Is a subquery that returns rows that are inserted into the table. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. For the syntax description of a subquery, see "SELECT" in Oracle Database SQL Language Reference.

Any host variables that appear in the WHERE clause must be either all scalars or all arrays. If they are scalars, Oracle executes the INSERT statement once. If they are arrays, Oracle executes the INSERT statement once for each set of array components, inserting one row each time.

Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle executes the statement is determined by the smaller of the following values:

OPEN (Executable 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. For the ANSI Dynamic SQL Method 4 version, see "OPEN DESCRIPTOR (Executable Embedded SQL)".

Prerequisites

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

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

CANNOT be used together with an ANSI descriptor (INTO clause).

DESCRIPTOR SQLDA_descriptor

Specifies an Oracle 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.

CANNOT be used together with an ANSI descriptor (INTO clause).

Usage Notes

The OPEN statement 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 statement does not actually retrieve rows; rows are retrieved by the FETCH statement.

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 statement.

You can reopen a cursor without first closing it. For more information on this statement, see "The INSERT Statement".

Example

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

OPEN DESCRIPTOR (Executable Embedded SQL)

Purpose

To open a cursor (for ANSI Dynamic SQL Method 4), evaluating the associated query and substituting the input host variable names supplied by the USING clause into the WHERE clause of the query. The INTO clause denotes the output descriptor.

Prerequisites

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

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

Usage Notes

Set the precompiler option DYNAMIC to ANSI.

The OPEN statement 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 SQL statement. This statement does not actually retrieve rows; rows are retrieved by the FETCH statement.

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 statement.

You can reopen a cursor without first closing it. For more information on this statement, see "The INSERT Statement".

Is the identifier to be associated with the prepared SQL statement or PL/SQL block. If this identifier was previously assigned to another statement or block, the prior assignment is superseded.

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.

text

Is a string literal containing a SQL statement or PL/SQL block to be prepared.

select_command

Is a select statement

Usage Notes

Any variables that appear in the host_string or text are placeholders. The actual host variable names are assigned in the USING clause of the OPEN statement (input host variables) or in the INTO clause of the FETCH statement (output host variables).

A SQL statement is prepared only once, but can be executed any number of times. For more information, see "PREPARE".

Example

This example illustrates the use of a PREPARE statement in a Pro*C/C++ embedded SQL program:

ROLLBACK (Executable Embedded SQL)

You can also use this statement to manually undo the work done by an in-doubt distributed transaction.

Prerequisites

To roll back your current transaction, no privileges are necessary.

To manually roll back an in-doubt distributed transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.

Identifies the database on which the savepoint is created. The database can be identified by either:

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. If you omit this clause, the savepoint is created on your default database.

WORK

Is optional and is provided for ANSI compatibility.

TO

Rolls back the current transaction to the specified savepoint. If you omit this clause, the ROLLBACK statement rolls back the entire transaction.

FORCE

Manually rolls back 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.

ROLLBACK statements with the FORCE clause are not supported in PL/SQL.

RELEASE

Frees all resources and disconnects the application from the database. The RELEASE clause is not allowed with SAVEPOINT and FORCE clauses.

savepoint

savepoint to which you roll back

Usage Notes

A transaction (or a logical unit of work) is a sequence of SQL statements that Oracle treats as a single unit. A transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK or connection to the database. A transaction ends with a COMMIT statement, a ROLLBACK statement, or disconnection (intentional or unintentional) from the database. Oracle issues an implicit COMMIT statement before and after processing any data definition language statement.

Using the ROLLBACK statement without the TO SAVEPOINT clause performs the following operations:

Ends the transaction

Undoes all changes in the current transaction

Erases all savepoints in the transaction

Releases the transaction's locks

Using the ROLLBACK statement with the TO SAVEPOINT clause performs the following operations:

Rolls back just the portion of the transaction after the savepoint.

Loses all savepoints created after that savepoint. The named savepoint is retained, so you can roll back to the same savepoint multiple times. Prior savepoints are also retained.

Releases all table and row locks acquired since the savepoint. Other transactions that have requested access to rows locked after the savepoint must continue to wait until the transaction is committed or rolled back. Other transactions that have not already requested the rows can request and access the rows immediately.

It is recommended that you explicitly end transactions in application programs using either a COMMIT or ROLLBACK statement. If you do not explicitly commit the transaction and the program terminates abnormally, Oracle rolls back the last uncommitted transaction.

Example 1

The following statement rolls back your entire current transaction:

EXEC SQL ROLLBACK;

Example 2

The following statement rolls back your current transaction to savepoint SP5:

EXEC SQL ROLLBACK TO SAVEPOINT sp5;

Distributed Transactions

Oracle with the distributed option provides the ability to perform distributed transactions, or transactions that modify data on multiple databases. To commit or roll back a distributed transaction, you need only issue a COMMIT or ROLLBACK statement as you would any other transaction.

If there is a network failure during the commit process for a distributed transaction, the state of the transaction may be unknown, or in-doubt. After consultation with the administrators of the other databases involved in the transaction, you may decide to manually commit or roll back the transaction on your local database. You can manually roll back the transaction on your local database by issuing a ROLLBACK statement with the FORCE clause.

You cannot manually roll back an in-doubt transaction to a savepoint.

A ROLLBACK statement with a FORCE clause only rolls back the specified transaction. Such a statement does not affect your current transaction.

Example III

The following statement manually rolls back an in-doubt distributed transaction:

SELECT (Executable Embedded SQL)

To retrieve data from one or more tables, views, or snapshots, assigning the selected values to host variables.

Prerequisites

For you to select data from a table or snapshot, the table or snapshot must be in your own schema or you must have SELECT privilege on the table or snapshot.

For you to select rows from the base tables of a view, the owner of the schema containing the view must have SELECT privilege on the base tables. Also, if the view is in a schema other than your own, you must have SELECT privilege on the view.

The SELECT ANY TABLE system privilege also provides the ability to select data from any table or any snapshot or any view's base table.

host_variable: Host variable whose value is a previously declared db_name. If you omit this clause, the SELECT statement is issued to your default database.

select_list

Identical to the non-embedded SELECT statement except that a host variables can be used in place of literals.

INTO

Specifies output host variables and optional indicator variables to receive the data returned by the SELECT statement. These variables must be either all scalars or all arrays, but arrays need not have the same size.

WHERE

Restricts the rows returned to those for which the condition is TRUE. See the syntax description of condition in Oracle Database SQL Language Reference. The condition can contain host variables, but cannot contain indicator variables. These host variables can be either scalars or arrays.

All other keywords and parameters are identical to the non-embedded SQL SELECT statement. ASC, ascending, is the default for the ORDER BY clause.

Usage Notes

If no rows meet the WHERE clause condition, no rows are retrieved and Oracle returns an error code through the SQLCODE component of the SQLCA.

You can use Comments in a SELECT statement to pass instructions, or hints, to the optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle Database Performance Tuning Guide.

Example

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

Is the user-defined datatype to be equivalenced with an external datatype.

datatype

Is an external datatype recognized by the precompiler (not an internal datatype). The datatype may include a length, precision, or scale. This external datatype is equivalenced to the user-defined type and assigned to all host variables assigned the type. For a list of external datatypes, see "Oracle Datatypes".

REFERENCE

Makes the equivalenced type a pointer type.

Usage Notes

User-defined type equivalencing is one kind of datatype equivalencing. You can only perform user-defined type equivalencing with the embedded SQL TYPE statement in a Pro*C/C++ program. You may want to use datatype equivalencing for one of the following purposes:

To automatically null-terminate a character host variable

To store program data as binary data in the database

To override default datatype conversion

Pro*C/C++ expects VARCHAR and VARRAW arrays to be word-aligned. When you equivalence an array type to the VARCHAR or VARRAW datatype, make sure that length+2 is divisible by 4.

UPDATE (Executable Embedded SQL)

For you to update values in a table or snapshot, the table must be in your own schema or you must have UPDATE privilege on the table.

For you to update values in the base table of a view, the owner of the schema containing the view must have UPDATE privilege on the base table. Also, if the view is in a schema other than your own, you must have UPDATE privilege on the view.

The UPDATE ANY TABLE system privilege also provides the ability to update values in any table or any view's base table.

Identifies the database to which the UPDATE statement is issued. The database can be identified by either:

dbname: 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, the UPDATE statement is issued to your default database.

FOR :host_integer

integer

Limits the number of times the UPDATE statement is executed if the SET and WHERE clauses contain array host variables. If you omit this clause, Oracle executes the statement once for each component of the smallest array.

schema

Is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.

table, view

Is the name of the table to be updated. If you specify view, Oracle updates the view's base table.

dblink

Is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see the Oracle Database SQL Language ReferenceYou can only use a database link to update a remote table or view if you are using Oracle with the distributed option.

part_name

Name of a partition of the table.

column

Is the name of a column of the table or view that is to be updated. If you omit a column of the table from the SET clause, that column's value remains unchanged.

expr

Is the new value assigned to the corresponding column. This expression can contain host variables and optional indicator variables. See the syntax of expr in the Oracle Database SQL Language Reference.

Updates only rows for which this condition is true. This condition can contain host variables and optional indicator variables. See the syntax of condition in the Oracle Database SQL Language Reference.

Updates only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join unless its FOR UPDATE clause explicitly locks only one table.

If you omit this clause entirely, all rows of the table or view are updated.

Host variables in the SET and WHERE clauses must be either all scalars or all arrays. If they are scalars, Oracle executes the UPDATE statement only once. If they are arrays, Oracle executes the statement once for each set of array components. Each execution may update zero, one, or multiple rows.

Array host variables can have different sizes. In this case, the number of times Oracle executes the statement is determined by the smaller of the following values:

The size of the smallest array

The value of the :host_integer in the optional FOR clause

The cumulative number of rows updated is returned through the third element of the SQLERRD component of the SQLCA. When arrays are used as input host variables, this count reflects the total number of updates for all components of the array processed in the UPDATE statement. If no rows satisfy the condition, no rows are updated and Oracle returns an error message through the SQLCODE element of the SQLCA. If you omit the WHERE clause, all rows are updated and Oracle raises a warning flag in the fifth component of the SQLWARN element of the SQLCA.

You can use Comments in an UPDATE statement to pass instructions, or hints, to the optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle Database Performance Tuning Guide.

VAR (Oracle Embedded SQL Directive)

Purpose

To perform host variable equivalencing, or to assign a specific external datatype to an individual host variable, overriding the default datatype assignment. Also has an optional CONVBUFSZ clause that specifies the size of a buffer for character set conversion.

Prerequisites

The host variable must be previously declared in the Pro*C/C++ program.

The VARCHAR and VARRAW external datatypes have a 2-byte length field followed by an n-byte data field, where n lies in the range 1 .. 65533. So, if type_name is VARCHAR or VARRAW, host_variable must be at least 3 bytes long.

The LONG VARCHAR and LONG VARRAW external datatypes have a 4-byte length field followed by an n-byte data field, where n lies in the range 1 .. 2147483643. So, if type_name is LONG VARCHAR or LONG VARRAW, host_variable must be at least 5 bytes long.

dtyp

Is an external datatype recognized by Pro*C/C++ (not an internal datatype). The datatype may include a length, precision, or scale. This external datatype is assigned to the host_variable. For a list of external datatypes, see "External Datatypes".

length

Length of the datatype. It is a constant expression or a constant integer specifying a valid length in bytes. The value of length must be large enough to accommodate the external datatype.

When type_name is ROWID or DATE, you cannot specify length because it is predefined. For other external datatypes, length is optional. It defaults to the length of host_variable.

When specifying length, if type_name is VARCHAR, VARRAW, LONG VARCHAR, or LONG VARRAW, use the maximum length of the data field. Pro*C/C++ accounts for the length field. If type_name is LONG VARCHAR or LONG VARRAW and the data field exceeds 65533 bytes, put "-1" in the length field.

precision and scale

Are constant expressions or constants that represent, respectively, the number of significant digits and the point at which rounding will occur. For example, a scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46); a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000).

You can specify a precision of 1 .. 99 and a scale of -84 .. 99. However, the maximum precision and scale of a database column are 38 and 127, respectively. So, if precision exceeds 38, you cannot insert the value of host_variable into a database column. On the other hand, if the scale of a column value exceeds 99, you cannot select or fetch the value into host_variable.

size

The size, in bytes, of a buffer used to perform conversion of the specified host_variable to another character set. A constant or constant expression.

Is the size in bytes of a buffer in the runtime library used to perform conversion between character sets of the host_variable

Usage Notes

length, precision, scale and size can be constant expressions.

Host variable equivalencing is one kind of datatype equivalencing. Datatype equivalencing is useful for any of the following purposes:

To automatically null-terminate a character host variable

To store program data as binary data in the database

To override default datatype conversion

Note that size, length, precision and scale can be any arbitrarily complex C constant expression whose value is known when the precompiler is run.

When you have not used the CONVBUFSZ clause, the Oracle runtime automatically determines a buffer size based on the ratio of the host variable character size (determined by NLS_LANG) and the character size of the database character set. This can sometimes result in the creation of a buffer of LONG size. Database tables are allowed to have only one LONG column. An error is raised if there is more than one LONG value.

To avoid such errors, you use a length shorter than the size of a LONG. If a character set conversion results in a value longer than the length specified by CONVBUFSZ, then an error is returned at runtime. The Pro*C/C++ Precompiler also supports the precompiler TYPE directive for user-defined type equivalencing. See also "Host Variable Equivalencing".

Example

This example equivalences the host variable DEPT_NAME to the datatype STRING and the host variable BUFFER to the datatype RAW(200):

Identifies any exception condition that returns an error code of +1403 to SQLCODE (or a +100 code when MODE=ANSI).

SQLERROR

Identifies a condition that results in a negative return code.

SQLWARNING

Identifies a non-fatal warning condition.

CONTINUE

Indicates that the program should progress to the next statement.

GOTO label

Indicates that the program should branch to the statement named by label.

STOP

Stops program execution.

DO routine

Indicates that the program should call a function that is named routine

DO BREAK

Performs a break statement from a loop when the condition is met.

DO CONTINUE

Performs a continue statement from a loop when the condition is met.

Usage Notes

The WHENEVER directive allows your program to transfer control to an error handling routine in the event an embedded SQ

L statement results in an error or warning.

The scope of a WHENEVER directive is positional, rather than logical. A WHENEVER statement applies to all embedded SQL statements that textually follow it in the source file, not in the flow of the program logic. A WHENEVER directive remains in effect until it is superseded by another WHENEVER directive checking for the same condition.