To allocate a cursor variable to be referenced in a PL/SQL block.

A cursor variable of type SQL_CURSOR must be declared before allocating memory for the cursor variable.

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

Example

This partial example illustrates the use of the ALLOCATE command in a Pro*C/C++ embedded SQL 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; 
}

"CLOSE (Executable Embedded SQL)", "EXECUTE (Executable Embedded SQL)", and "FETCH (Executable Embedded SQL)"

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

The cursor or cursor variable must be open and MODE=ANSI.

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 command. For information on these options, see Running the Oracle Precompilers.

This example illustrates the use of the CLOSE command:

EXEC SQL CLOSE emp_cursor;

"DECLARE CURSOR (Embedded SQL Directive)", "OPEN (Executable Embedded SQL)", and "PREPARE (Executable Embedded SQL)"

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

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.

If you are using Oracle in DBMS MAC mode, you can only commit an in-doubt transaction if your DBMS label matches the label the transaction's label and the creation label of the user who originally committed the transaction or if you satisfy one of the following criteria:

• If the transaction's label or the user's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

• If the transaction's label or the user's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

• If the transaction's label or the user's creation label is not comparable with your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

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 previously declared 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 Oracle database.

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.

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

The COMMIT command has no effect on host variables or on the flow of control in the program. For more information on this command, see Defining and Controlling Transactions.

Example

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

EXEC SQL AT sales_db COMMIT RELEASE;

"ROLLBACK (Executable Embedded SQL)" and "SAVEPOINT (Executable Embedded SQL)"

To log on to an Oracle database.

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

If you are using Oracle in DBMS MAC mode, your operating system label must dominate both your creation label and the label at which you were granted CREATE SESSION system privilege. Your operating system label must also fall between the operating system equivalents of DBHIGH and DBLOW, inclusive.

If you are using Oracle in operating system MAC mode, your operating system label must match the label of the database to which you are connecting.

A program can have multiple connections, but can only connect once to your default database. For more information on this command, see Meeting Program Requirements.

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 'AAAAA/BBBBB':

EXEC SQL CONNECT :userid

"COMMIT (Executable Embedded SQL)", "DECLARE DATABASE (Oracle Embedded SQL Directive)", and "ROLLBACK (Executable Embedded SQL)"

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

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.

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, then the cursor has been opened with an OPEN statement and positioned on a row with a FETCH statement. For more information on this command, see Meeting Program Requirements.

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

"CLOSE (Executable Embedded SQL)", "DECLARE DATABASE (Oracle Embedded SQL Directive)", "DECLARE STATEMENT (Embedded SQL Directive)", "DELETE (Executable Embedded SQL)", "FETCH (Executable Embedded SQL)", "OPEN (Executable Embedded SQL)", "PREPARE (Executable Embedded SQL)", "SELECT (Executable Embedded SQL)", and "UPDATE (Executable Embedded SQL) "

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

You must have access to a username on the nondefault database.

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.

For more information on this command, see Meeting Program Requirements.

This example illustrates the use of a DECLARE DATABASE directive:

EXEC SQL DECLARE oracle3 DATABASE

"COMMIT (Executable Embedded SQL)", "CONNECT (Executable Embedded SQL Extension)", "DECLARE CURSOR (Embedded SQL Directive)", "DECLARE STATEMENT (Embedded SQL Directive)", "DELETE (Executable Embedded SQL)", "EXECUTE (Executable Embedded SQL)", "EXECUTE IMMEDIATE (Executable Embedded SQL)", "INSERT (Executable Embedded SQL)", "SELECT (Executable Embedded SQL)", and "UPDATE (Executable Embedded SQL) "

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

None.

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 Meeting Program Requirements and Using Dynamic SQL.

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

In this example from a Pro*C/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; 
EXEC SQL DECLARE emp_cursor CURSOR FOR my_statement; 
EXEC SQL PREPARE my_statement FROM :my_string; 
...

"CLOSE (Executable Embedded SQL)", "DECLARE DATABASE (Oracle Embedded SQL Directive)", "FETCH (Executable Embedded SQL)", "OPEN (Executable Embedded SQL)", and "PREPARE (Executable Embedded SQL)"

To define the structure of a table or view, including each column's datatype, default value, and NULL or NOT NULL specification for semantic checking by the Oracle Precompilers.

None.

For information on using this command, see Meeting Program Requirements.

The following statement declares the PARTS table with the PARTNO, BIN, and QTY columns:

EXEC SQL DECLARE parts TABLE 
 (partno NUMBER NOT NULL, 
 bin NUMBER, 
 qty NUMBER)

None.

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

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 enables delete rows from any table or any view's base table.

If you are using Oracle in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view or you must meet one of the following criteria:

• If the creation label of the table or view is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

• If the creation label of your table or view is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

In addition, for each row to be deleted, your DBMS label must match the row's label or you must meet one of the following criteria:

• If the row's label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

• If the row's label is lower than your DBMS label, you must have WRITEDOWN system privilege.

• If the row label is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

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

EXEC SQL DELETE FROM emp 
 WHERE deptno = :deptno 
 AND job = :job; ... 
EXEC SQL DECLARE emp_cursor CURSOR 
 FOR SELECT empno, comm 
 FROM emp; 
EXEC SQL OPEN emp_cursor; 
EXEC SQL FETCH c1 
 INTO :emp_number, :commission; 
EXEC SQL DELETE FROM emp 
 WHERE CURRENT OF emp_cursor;

"DECLARE DATABASE (Oracle Embedded SQL Directive)" and "DECLARE STATEMENT (Embedded SQL Directive)"

To initialize a descriptor to hold descriptions of host variables for a dynamic SQL statement or PL/SQL block.

You must have prepared the SQL statement or PL/SQL block in a previous embedded SQL PREPARE statement.

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 placeholders in the prepare SQL statement or PL/SQL block, rather than the total number of uniquely named placeholders. For more information on this command, see Using Dynamic SQL.

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

EXEC SQL PREPARE my_statement FROM :my_string; 
EXEC SQL DECLARE emp_cursor 
 FOR SELECT empno, ename, sal, comm 
 FROM emp 
 WHERE deptno = :dept_number 
EXEC SQL DESCRIBE BIND VARIABLES FOR my_statement 
 INTO bind_descriptor; 
EXEC SQL OPEN emp_cursor 
 USING bind_descriptor; 
EXEC SQL DESCRIBE SELECT LIST FOR my_statement 
 INTO select_descriptor; 
EXEC SQL FETCH emp_cursor 
 INTO select_descriptor;

"PREPARE (Executable Embedded SQL)"

To embed an anonymous PL/SQL block into an Oracle Precompiler program.

None.

Since the Oracle Precompilers treat an embedded PL/SQL block like a single embedded SQL statement, you can embed a PL/SQL block anywhere in an Oracle Precompiler program that you can embed a SQL statement. For more information on embedding PL/SQL blocks in Oracle Precompiler programs, see Using Embedded PL/SQL

Placing this EXECUTE statement in an Oracle Precompiler program embeds a PL/SQL block in the program:

EXEC SQL EXECUTE 
 BEGIN 
 SELECT ename, job, sal 
 INTO :emp_name:ind_name, :job_title, :salary 
 FROM emp 
 WHERE empno = :emp_number; 
 IF :emp_name:ind_name IS NULL 
 THEN RAISE name_missing; 
 END IF; 
 END; 
END-EXEC

"EXECUTE IMMEDIATE (Executable Embedded 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.

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

For more information on this command, see Using Dynamic SQL.

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

EXEC SQL PREPARE my_statement 
 FROM :my_string; 
EXEC SQL EXECUTE my_statement
 USING :my_var;

"DECLARE DATABASE (Oracle Embedded SQL Directive)" and "PREPARE (Executable Embedded SQL)"

To prepare and execute a DELETE, INSERT, or UPDATE statement or a PL/SQL block containing no host variables.

None.

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.

For more information on this command, see Using Dynamic SQL.

This example illustrates the use of the EXECUTE IMMEDIATE statement:

EXEC SQL EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = 9460'

"EXECUTE (Executable Embedded SQL)" and "PREPARE (Executable Embedded SQL)"

To retrieve one or more rows returned by a query, assigning the select list values to host variables.

You must first open the cursor with an the OPEN statement.

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 :host_integer 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.

Note that the FETCH command 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.

This example illustrates the FETCH command in a pseudo-code embedded SQL program:

EXEC SQL DECLARE emp_cursor CURSOR FOR 
 SELECT job, sal FROM emp WHERE deptno = 30; 
... 
EXEC SQL WHENEVER NOT FOUND GOTO ... 
LOOP 
 EXEC SQL FETCH emp_cursor INTO :job_title1, :salary1; 
 EXEC SQL FETCH emp_cursor INTO :job_title2, :salary2; 
... 
END LOOP; 
...

"CLOSE (Executable Embedded SQL)", "DECLARE CURSOR (Embedded SQL Directive)", "OPEN (Executable Embedded SQL)", and "PREPARE (Executable Embedded SQL)"

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

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 enables insert rows into any table or any view's base table.

If you are using Oracle in DBMS MAC mode, your DBMS label must match the creation label of the table or view:

• If the creation label of the table or view is higher than your DBMS label, you must have WRITEUP system privileges.

• If the creation label of the table or view is lower than your DBMS label, you must have WRITEDOWN system privilege.

• If the creation label of your table or view is not comparable to your DBMS label, you must have WRITEUP and WRITEDOWN system privileges.

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:

• size of the smallest array

• the value of the :host_integer in the optional FOR clause.

For more information on this command, see  Using Embedded SQL .

This example illustrates the use of the embedded SQL INSERT command:

EXEC SQL 
 INSERT INTO emp (ename, empno, sal) 
 VALUES (:ename, :empno, :sal);

This example shows an embedded SQL INSERT command with a subquery:

EXEC SQL 
 INSERT INTO new_emp (ename, empno, sal) 
 SELECT ename, empno, sal FROM emp
 WHERE deptno = :deptno;

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.

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

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

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

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

You can reopen a cursor without first closing it. For more information on this command, see  Using Embedded SQL .

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

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

"CLOSE (Executable Embedded SQL)". "DECLARE CURSOR (Embedded SQL Directive)", "FETCH (Executable Embedded SQL)", and "PREPARE (Executable Embedded SQL)"

To parse a SQL statement or PL/SQL block specified by a host variable and associate it with an identifier.

None.

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 command (input host variables) or in the INTO clause of the FETCH command (output host variables).

A SQL statement is prepared only once, but can be executed any number of times.

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

EXEC SQL PREPARE my_statement FROM :my_string;
EXEC SQL EXECUTE my_statement;

"CLOSE (Executable Embedded SQL)", "DECLARE CURSOR (Embedded SQL Directive)", "FETCH (Executable Embedded SQL)", and "OPEN (Executable Embedded SQL)"

To undo work done in the current transaction.

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

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.

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. Note that Oracle issues an implicit COMMIT statement before and after processing any data definition language statement.

Using the ROLLBACK command 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 command 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. Note that 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. Note that 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.

The following statement rolls back your entire current transaction:

EXEC SQL ROLLBACK;

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

EXEC SQL ROLLBACK TO SAVEPOINT sp5;

Oracle with the distributed option enables 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.

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

EXEC SQL
 ROLLBACK WORK
 FORCE '25.32.87';

"COMMIT (Executable Embedded SQL)" and "SAVEPOINT (Executable Embedded SQL)"

None.

For more information on this command, see Defining and Controlling Transactions.

Example

This example illustrates the use of the embedded SQL SAVEPOINT command:

EXEC SQL SAVEPOINT save3;

"COMMIT (Executable Embedded SQL)" and "ROLLBACK (Executable Embedded SQL)"

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

For you to select data from a table or snapshot, the table or snapshot must be in your own schema or you must have READ or 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 READ or SELECT privilege on the base tables. Also, if the view is in a schema other than your own, you must have READ or SELECT privilege on the view.

The READ ANY TABLE or SELECT ANY TABLE system privilege also enables select data from any table or any snapshot or any view's base table.

If you are using Oracle in DBMS MAC mode, your DBMS label must dominate the creation label of each queried table, view, or snapshot or you must have READUP system privileges.

The READ privilege cannot be used for SELECT ... FOR UPDATE operations.

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 Oracle optimizer. The optimizer uses hints to choose an execution plan for the statement.

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

EXEC SQL SELECT ename, sal + 100, job 
 INTO :ename, :sal, :job 
 FROM emp 
 WHERE empno = :empno

"DECLARE CURSOR (Embedded SQL Directive)", "DECLARE DATABASE (Oracle Embedded SQL Directive)", "EXECUTE (Executable Embedded SQL)", "FETCH (Executable Embedded SQL)", and "PREPARE (Executable Embedded SQL)"

To change existing values in a table or in a view's base table.

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 enables update values in any table or any view's base table.

If you are using Oracle in DBMS MAC mode, your DBMS label must match the creation label of the table or view:

• If the creation label of the table or view is higher than your DBMS label, you must have READUP and WRITEUP system privileges

• If the creation label of the table or view is lower than your DBMS label, you must have WRITEDOWN system privilege.

• If the creation label of your table or view is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

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 Oracle optimizer. The optimizer uses hints to choose an execution plan for the statement.

For more information on this command, see  Using Embedded SQL and Defining and Controlling Transactions.

The following examples illustrate the use of the embedded SQL UPDATE command:

EXEC SQL UPDATE emp 
 SET sal = :sal, comm = :comm INDICATOR :comm_ind 
 WHERE ename = :ename; 
 
EXEC SQL UPDATE emp 
 SET (sal, comm) = 
 (SELECT AVG(sal)*1.1, AVG(comm)*1.1 
 FROM emp) 
 WHERE ename = 'JONES';

"DECLARE DATABASE (Oracle Embedded SQL Directive)"

To perform host variable equivalencing, or to assign a specific Oracle external datatype to an individual host variable, overriding the default datatype assignment.

The host variable must be previously declared in the Declare Section of the embedded SQL program.

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

This example equivalences the host variable DEPT_NAME to

the datatype STRING and the host variable BUFFER to the datatype RAW(2000):

EXEC SQL BEGIN DECLARE SECTION; 
 ... 
 dept_name CHARACTER(15); -- default datatype is CHAR 
 EXEC SQL VAR dept_name IS STRING; -- reset to STRING 
 ... 
 buffer CHARACTER(200); -- default datatype is CHAR 
 EXEC SQL VAR buffer IS RAW(200); -- refer to RAW 
 ...
EXEC SQL END DECLARE SECTION;

None.

To specify the action to be taken when an error or warning results from executing an embedded SQL program.

None.

The WHENEVER command allows your program to transfer control to an error handling routine in the event an embedded SQL statement results in an error or warning.

The scope of a WHENEVER statement 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 statement remains in effect until it is superseded by another WHENEVER statement checking for the same condition.

For more information on this command, see Defining and Controlling Transactions. Do not confuse the WHENEVER embedded SQL command with the WHENEVER SQL*Plus command.

The following example illustrates the use of the WHENEVER command in a Pro*C/C++ embedded SQL program:

EXEC SQL WHENEVER NOT FOUND CONTINUE;
... 
EXEC SQL WHENEVER SQLERROR GOTO sql_error: 
... 
sql_error: 
 EXEC SQL WHENEVER SQLERROR CONTINUE; 
 EXEC SQL ROLLBACK RELEASE;

None.