F Embedded SQL Statements and Directives

F.1 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-2 is displayed in the format:

F.2 About The Statement Descriptions

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

F.3 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 ... 

F.3.1 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:

Description of the illustration reqp.gif

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:

Description of the illustration reqp1.gif

F.3.2 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:

Description of the illustration optional.gif

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; 

F.3.3 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.

Description of the illustration syntax.gif

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

F.3.4 Multipart Diagrams

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

Description of the illustration part.gif

According to the diagram, the following statement is valid:

EXEC SQL PREPARE statement_name FROM string_literal; 

F.4 ALLOCATE (Executable Embedded SQL Extension)

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

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.

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 Oracle Database SQL Reference.

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;
   ...
   }

CACHE FREE ALL (Executable Embedded SQL Extension).

CLOSE (Executable Embedded SQL) .

EXECUTE (Executable Embedded SQL) .

FETCH (Executable Embedded SQL) .

FETCH DESCRIPTOR (Executable Embedded SQL) .

FREE (Executable Embedded SQL Extension).

F.5 ALLOCATE DESCRIPTOR (Executable Embedded SQL)

An ANSI dynamic SQL statement that allocates a descriptor.

None.

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

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

DESCRIBE DESCRIPTOR (Executable Embedded SQL) .

DEALLOCATE DESCRIPTOR (Embedded SQL Statement)

GET DESCRIPTOR (Executable Embedded SQL).

SET DESCRIPTOR (Executable Embedded SQL).

F.6 CACHE FREE ALL (Executable Embedded SQL Extension)

To free all memory in the object cache.

An active database connection must exist.

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

EXEC SQL AT mydb CACHE FREE ALL ;

ALLOCATE (Executable Embedded SQL Extension).

FREE (Executable Embedded SQL Extension).

F.7 CALL (Executable Embedded SQL)

To call a stored procedure.

An active database connection must exist.

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

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

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 */
...

None

F.8 CLOSE (Executable Embedded SQL)

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

The cursor or cursor variable be open if 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 statement. For information on these options, see Chapter 10, " Precompiler Options".

This example illustrates the use of the CLOSE statement:

EXEC SQL CLOSE emp_cursor;

PREPARE (Executable Embedded SQL).

DECLARE CURSOR (Embedded SQL Directive).

OPEN (Executable Embedded SQL) .

F.9 COLLECTION APPEND (Executable Embedded SQL Extension)

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

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

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

See the other COLLECTION statements.

F.10 COLLECTION DESCRIBE (Executable Embedded SQL Extension)

To obtain information about a collection.

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

where attrib is:

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

See the other COLLECTION statements.

F.11 COLLECTION GET (Executable Embedded SQL Extension)

To retrieve the elements of a collection.

For usage notes as well as keywords, parameters, and examples, see "Collection Statements".

See the other COLLECTION statements.

F.12 COLLECTION RESET (Executable Embedded SQL Extension)

To reset the collection slice endpoints back to the beginning of the collection.

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

See the other COLLECTION statements.

F.13 COLLECTION SET (Executable Embedded SQL Extension)

To update element values in the current slice of a collection.

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

See the other COLLECTION statements.

F.14 COLLECTION TRIM (Executable Embedded SQL Extension)

To remove elements from the end of collection.

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

See the other COLLECTION statements.

F.15 COMMIT (Executable Embedded SQL)

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

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.

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

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

EXEC SQL AT sales_db COMMIT RELEASE;

ROLLBACK (Executable Embedded SQL) .

SAVEPOINT (Executable Embedded SQL) .

F.16 CONNECT (Executable Embedded SQL Extension)

To log on to a database.

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

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

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 the value of password separated by a "/", such as 'SCOTT/TIGER':

EXEC SQL CONNECT :userid ;

COMMIT (Executable Embedded SQL) .

DECLARE DATABASE (Oracle Embedded SQL Directive).

ROLLBACK (Executable Embedded SQL) .

F.17 CONTEXT ALLOCATE (Executable Embedded SQL Extension)

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

The runtime context must be declared of type sql_context.

In a multithreaded application, execute this function once for each runtime context.

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

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

CONTEXT USE (Oracle Embedded SQL Directive).

EENABLE THREADS (Executable Embedded SQL Extension).

F.18 CONTEXT FREE (Executable Embedded SQL Extension)

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

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.

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

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

EXEC SQL CONTEXT FREE :ctx1;

CONTEXT ALLOCATE (Executable Embedded SQL Extension).

CONTEXT USE (Oracle Embedded SQL Directive).

ENABLE THREADS (Executable Embedded SQL Extension).

F.19 CONTEXT OBJECT OPTION GET (Executable Embedded SQL Extension)

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

Precompiler option OBJECTS must be set to YES.

See "CONTEXT OBJECT OPTION SET".

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

CONTEXT ALLOCATE (Executable Embedded SQL Extension).

CONTEXT FREE (Executable Embedded SQL Extension).

CONTEXT OBJECT OPTION SET (Executable Embedded SQL Ext) .

CONTEXT USE (Oracle Embedded SQL Directive).

F.20 CONTEXT OBJECT OPTION SET (Executable Embedded SQL Ext)

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

Precompiler option OBJECTS must be set to YES.

See "CONTEXT OBJECT OPTION GET".

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

CONTEXT ALLOCATE (Executable Embedded SQL Extension) .

CONTEXT FREE (Executable Embedded SQL Extension) .

CONTEXT USE (Oracle Embedded SQL Directive).

CONTEXT OBJECT OPTION SET (Executable Embedded SQL Ext)

F.21 CONTEXT USE (Oracle Embedded SQL Directive)

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

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

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

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

EXEC SQL CONTEXT USE :ctx1; 

CONTEXT ALLOCATE (Executable Embedded SQL Extension).

CONTEXT FREE (Executable Embedded SQL Extension).

ENABLE THREADS (Executable Embedded SQL Extension).

F.22 DEALLOCATE DESCRIPTOR (Embedded SQL Statement)

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

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

Use DYNAMIC=ANSI precompiler option.

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

EXEC SQL DEALLOCATE DESCRIPTOR GLOBAL 'SELDES' ;

ALLOCATE DESCRIPTOR (Executable Embedded SQL).

DESCRIBE (Executable Embedded SQL Extension) .

GET DESCRIPTOR (Executable Embedded SQL).

PREPARE (Executable Embedded SQL).

SET DESCRIPTOR (Executable Embedded SQL).

F.23 DECLARE CURSOR (Embedded SQL Directive)

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

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 DESCRIPTOR (Executable Embedded SQL) .

PREPARE (Executable Embedded SQL).

SELECT (Executable Embedded SQL).

UPDATE (Executable Embedded SQL) .

F.24 DECLARE DATABASE (Oracle Embedded SQL Directive)

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 nondefaultnondefault 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 statement, see "Single Explicit Connection ".

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 ... END-EXEC (Executable Embedded SQL Extension) .

EXECUTE IMMEDIATE (Executable Embedded SQL).

INSERT (Executable Embedded SQL) .

SELECT (Executable Embedded SQL).

UPDATE (Executable Embedded SQL) .

F.25 DECLARE STATEMENT (Embedded SQL Directive)

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 statement, see Chapter 4, " Datatypes and Host Variables" and Chapter 13, " Oracle 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 DESCRIPTOR (Executable Embedded SQL) .

PREPARE (Executable Embedded SQL).

F.26 DECLARE TABLE (Oracle Embedded SQL Directive)

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 relational tables, the syntax is:

For object tables, the syntax is:.

For information on using this statement, see "Using DECLARE TABLE".

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); 

Use of an object type:

EXEC SQL DECLARE person TYPE AS OBJECT (name VARCHAR2(20), age INT);
EXEC SQL DECLARE odjtab1 TABLE OF person;

DECLARE TYPE (Oracle Embedded SQL Directive).

F.27 DECLARE TYPE (Oracle Embedded SQL Directive)

To define the attributes of a type for a semantics check by the precompiler.

None.

For information on using this statement, see "Using DECLARE TYPE".

EXEC SQL DECLARE project_type TYPE AS OBJECT(
              pno           CHAR(5),
              pname         CHAR(20),
              budget        NUMBER);
EXEC SQL DECLARE project_array TYPE as VARRAY(20) OF project_type ;
EXEC SQL DECLARE employees TYPE AS TABLE OF emp_objects ;

DECLARE TABLE (Oracle Embedded SQL Directive) .

F.28 DELETE (Executable Embedded SQL)

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 provides the ability to delete rows from any table or any view's base table.

Where the DML returning clause is:

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.

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

DECLARE STATEMENT (Embedded SQL Directive) .

F.29 DESCRIBE (Executable Embedded SQL Extension)

To populate an Oracle descriptor with information about 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 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".

This example illustrates the use of the DESCRIBE statement in a Pro*C/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).

F.30 DESCRIBE DESCRIPTOR (Executable Embedded SQL)

An ANSI dynamic SQL statement used to obtain information about a SQL statement, and to store it in a descriptor.

You must have prepared the SQL statement in a previous embedded SQL PREPARE statement.

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

EXEC SQL PREPARE s FROM :my_stament; 
EXEC SQL DESCRIBE INPUT s USING DESCRIPTOR 'in' ;

ALLOCATE DESCRIPTOR (Executable Embedded SQL).

DEALLOCATE DESCRIPTOR (Embedded SQL Statement).

GET DESCRIPTOR (Executable Embedded SQL).

PREPARE (Executable Embedded SQL).

SET DESCRIPTOR (Executable Embedded SQL).

F.31 ENABLE THREADS (Executable Embedded SQL Extension)

To initialize a process that supports multiple threads.

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.

None.

The ENABLE THREADS statement must be executed before any other executable SQL statement and before spawning any thread. This statement does not require a host-variable specification.

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

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

EXEC SQL ENABLE THREADS;

CONTEXT ALLOCATE (Executable Embedded SQL Extension).

CONTEXT FREE (Executable Embedded SQL Extension).

CONTEXT USE (Oracle Embedded SQL Directive).

F.32 EXECUTE ... END-EXEC (Executable Embedded SQL Extension)

To embed an anonymous PL/SQL block into a Pro*C/C++ program.

None.

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

Placing this EXECUTE statement in a Pro*C/C++ 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).

F.33 EXECUTE (Executable Embedded SQL)

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

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

For more information on this statement, see Chapter 13, " Oracle Dynamic SQL" for the Oracle version.

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

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

DECLARE DATABASE (Oracle Embedded SQL Directive).

PREPARE (Executable Embedded SQL).

F.34 EXECUTE DESCRIPTOR (Executable Embedded SQL)

In ANSI SQL Method 4, 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 statement, see Chapter 14, " ANSI Dynamic SQL".

The ANSI dynamic SQL Method 4 allows DML Returning clauses in a SELECT to be supported by the INTO clause in EXECUTE:

EXEC SQL EXECUTE S2 USING DESCRIPTOR :bv1 INTO DESCRIPTOR 'SELDES' ;

DECLARE DATABASE (Oracle Embedded SQL Directive).

PREPARE (Executable Embedded SQL).

F.35 EXECUTE IMMEDIATE (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 statement, see Chapter 13, " Oracle Dynamic SQL" and Chapter 14, " ANSI 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).

PREPARE (Executable Embedded SQL).

F.36 FETCH (Executable Embedded SQL)

In Oracle dynamic SQL, to retrieve one or more rows returned by a query, assigning the select list values to host variables. For ANSI Dynamic SQL Method 4 see "FETCH DESCRIPTOR (Executable Embedded SQL) ".

You must first open the cursor with an 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 :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.

For more information, see "The FETCH Statement " for the Oracle descriptor.

This example illustrates the FETCH statement:

EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT job, sal FROM emp WHERE deptno = 30; 
EXEC SQL OPEN emp_cursor;
... 
EXEC SQL WHENEVER NOT FOUND GOTO ... 
for(;;)
    {
    EXEC SQL FETCH emp_cursor INTO :job_title1, :salary1; 
    ... 
    }

CLOSE (Executable Embedded SQL) .

DECLARE CURSOR (Embedded SQL Directive).

OPEN (Executable Embedded SQL) .

PREPARE (Executable Embedded SQL).

F.37 FETCH DESCRIPTOR (Executable Embedded SQL)

To retrieve one or more rows returned by a query, assigning the select list values to host variables. Used in ANSI Dynamic SQL Method 4.

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

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.

...
EXEC SQL ALLOCATE DESCRIPTOR 'output_descriptor' ;
...
EXEC SQL PREPARE S FROM :dyn_statement ;
EXEC SQL DECLARE mycursor CURSOR FOR S ;
...
EXEC SQL FETCH mycursor INTO DESCRIPTOR 'output_descriptor' ;
...

CLOSE (Executable Embedded SQL) .

DECLARE CURSOR (Embedded SQL Directive).

OPEN DESCRIPTOR (Executable Embedded SQL) .

PREPARE (Executable Embedded SQL).

F.38 FREE (Executable Embedded SQL Extension)

To free memory in the object cache.

The memory has to have been already allocated.

An active database connection must exist.

Any memory in the object cache will be freed automatically when the connection is terminated. See "FREE" for more information.

EXEC SQL FREE :ptr ;

ALLOCATE (Executable Embedded SQL Extension).

CACHE FREE ALL (Executable Embedded SQL Extension).

F.39 GET DESCRIPTOR (Executable Embedded SQL)

To obtain information about host variables from a SQL descriptor area.

Use only with value semantics.

where item_name can be one of these choices:

Use DYNAMIC=ANSI precompiler option. The array size clause can be used with DATA, RETURNED_LENGTH, and INDICATOR item names. See GET DESCRIPTOR.

EXEC SQL GET DESCRIPTOR GLOBAL 'mydesc' :mydesc_num_vars = COUNT ;

ALLOCATE DESCRIPTOR (Executable Embedded SQL).

DEALLOCATE DESCRIPTOR (Embedded SQL Statement).

SET DESCRIPTOR (Executable Embedded SQL).

F.40 INSERT (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 provides the ability to insert rows into any table or any view's base table.

Where the DML returning clause is:

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 statement, see "The INSERT Statement ".

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

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

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

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

DECLARE DATABASE (Oracle Embedded SQL Directive).

F.41 LOB APPEND (Executable Embedded SQL Extension)

To append a LOB to the end of another LOB.

LOB buffering must not be enabled.The destination LOB must have been initialized.

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

See the other LOB statements.

F.42 LOB ASSIGN (Executable Embedded SQL Extension)

To assign a LOB or BFILE locator to another locator.

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

See the other LOB statements.

F.43 LOB CLOSE (Executable Embedded SQL Extension)

To close an open LOB or BFILE.

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

See the other LOB statements.

F.44 LOB COPY (Executable Embedded SQL Extension)

To copy all or part of a LOB value into another LOB.

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

See the other LOB statements.

F.45 LOB CREATE TEMPORARY (Executable Embedded SQL Extension)

To create a temporary LOB.

For usage notes as well as keywords, parameters, and examples, see "CREATE TEMPORARY".

See the other LOB statements.

F.46 LOB DESCRIBE (Executable Embedded SQL Extension)

To retrieve attributes from a LOB.

where attrib is:

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

See the other LOB statements.

F.47 LOB DISABLE BUFFERING (Executable Embedded SQL Extension)

To disable LOB buffering.

For usage notes as well as keywords, parameters, and examples, see "DISABLE BUFFERING".

See the other LOB statements.

F.48 LOB ENABLE BUFFERING (Executable Embedded SQL Extension)

To enable LOB buffering.

For usage notes as well as keywords, parameters, and examples, see "ENABLE BUFFERING"

See the other LOB statements.

F.49 LOB ERASE (Executable Embedded SQL Extension)

To erase a given amount of LOB data starting from a given offset.

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

See the other LOB statements.

F.50 LOB FILE CLOSE ALL (Executable Embedded SQL Extension)

To close all open BFILEs in the current session.

For usage notes as well as keywords, parameters, and examples, see "FILE CLOSE ALL".

See the other LOB statements.

F.51 LOB FILE SET (Executable Embedded SQL Extension)

To set DIRECTORY and FILENAME in a BFILE locator.

For usage notes as well as keywords, parameters, and examples, see "FILE SET".

See the other LOB statements.

F.52 LOB FLUSH BUFFER (Executable Embedded SQL Extension)

To write the LOB buffers to the database server.

For usage notes as well as keywords, parameters, and examples, see "FLUSH BUFFER".

See the other LOB statements.

F.53 LOB FREE TEMPORARY (Executable Embedded SQL Extension)

To free temporary space for the LOB locator.

For usage notes as well as keywords, parameters, and examples, see "FREE TEMPORARY".

See the other LOB statements.

F.54 LOB LOAD (Executable Embedded SQL Extension)

To copy all or part of a BFILE into an internal LOB.

For usage notes as well as keywords, parameters, and examples, see "LOAD FROM FILE".

See the other LOB statements.

F.55 LOB OPEN (Executable Embedded SQL Extension)

To open a LOB or BFILE for read or read/write access.

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

See the other LOB statements.

F.56 LOB READ (Executable Embedded SQL Extension)

To read all or part of a LOB or BFILE into a buffer.

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

See the other LOB statements.

F.57 LOB TRIM (Executable Embedded SQL Extension)

To truncate a LOB value.

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

See the other LOB statements.

F.58 LOB WRITE (Executable Embedded SQL Extension)

To write the contents of a buffer to a LOB.

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

See the other LOB statements.

F.59 OBJECT CREATE (Executable Embedded SQL Extension)

To create a referenceable object in the object cache.

Precompiler option OBJECTS must be set to YES. The INTYPE option must specify the OTT-generated type files. Include OTT-generated header files in your program.

where tab is:

For usage notes as well as keywords and parameters, see "OBJECT CREATE".

person *pers_p;
person_ind *pers_ind;
person_ref *pers_ref;
...
EXEC SQL OBJECT CREATE :pers_p:pers_ind TABLE PERSON_TAB
          RETURNING REF INTO :pers_ref ;

See all other OBJECT statements in this appendix.

F.60 OBJECT DELETE (Executable Embedded SQL Extension)

To mark a persistent object or array of objects as deleted in the object cache.

Precompiler option OBJECTS must be set to YES. The INTYPE option must specify the OTT-generated type files. Include OTT-generated header files in your program.

For usage notes as well as keywords and parameters, see "OBJECT DELETE".

customer *cust_p;
...
EXEC SQL OBJECT DELETE :cust_p;

See all other OBJECT statements in this Appendix.For persistent objects, this statement marks an object or array of objects as deleted in the object cache.

F.61 OBJECT DEREF (Executable Embedded SQL Extension)

To pin an object or array of objects in the object cache.

Precompiler option OBJECTS must be set to YES. The INTYPE option must specify the OTT-generated type files. Include OTT-generated header files in your program.

For usage notes as well as keywords and parameters, see "OBJECT DEREF".

person *pers_p;
person_ref *pers_ref;
...
/* Pin the person REF, returning a pointer to the person object */
EXEC SQL OBJECT DEREF :pers_ref INTO :pers_p;

See all other OBJECT statements in this Appendix. See "ALLOCATE (Executable Embedded SQL Extension)".

F.62 OBJECT FLUSH (Executable Embedded SQL Extension)

To flush persistent objects that have been marked as updated, deleted, or created, to the server.

Precompiler option OBJECTS must be set to YES. The INTYPE option must specify the OTT-generated type files. Include OTT-generated header files in your program.

For usage notes as well as keywords and parameters, see "OBJECT FLUSH".

person *pers_p;
...
EXEC SQL OBJECT DELETE :pers_p;
/* Flush the changes, effectively deleting the person object */
EXEC SQL OBJECT FLUSH :pers_p;
/* Finally, free all object cache memory and logoff */
EXEC SQL OBJECT CACHE FREE ALL;
EXEC SQL COMMIT WORK RELEASE;

See all other OBJECT statements in this Appendix.

F.63 OBJECT GET (Executable Embedded SQL Extension)

To convert attributes of an object type to native C types

Precompiler option OBJECTS must be set to YES. The INTYPE option must specify the OTT-generated type files. Include OTT-generated header files in your program.

For usage notes as well as keywords and parameters, see "OBJECT GET".

person *pers_p;
struct { char lname[21], fname[21]; int age; } pers;
...
/* Convert object types to native C types */
EXEC SQL OBJECT GET lastname, firstname, age FROM :pers_p INTO :pers;
printf("Last Name: %s\nFirstName: %s\nAge: %d\n",
        pers.lname, pers.fname, pers.age );

See all other OBJECT statements in this Appendix.

F.64 OBJECT RELEASE (Executable Embedded SQL Extension)

To unpin an object in the object cache. When an object is not pinned and not updated, it is eligible for implicit freeing.

Precompiler option OBJECTS must be set to YES. The INTYPE option must specify the OTT-generated type files. Include OTT-generated header files in your program.

For usage notes as well as keywords and parameters, see "OBJECT RELEASE".

person *pers_p;
...
EXEC SQL OBJECT RELEASE :pers_p;

See all other OBJECT statements in this Appendix.

F.65 OBJECT SET (Executable Embedded SQL Extension)

To update attributes of persistent objects, marking them eligible for writing to the server when the object is flushed or the cache is flushed.

To update the attributes of a transient object.

Precompiler option OBJECTS must be set to YES. The INTYPE option must specify the OTT-generated type files. Include OTT-generated header files in your program.

For usage notes as well as keywords and parameters, see "OBJECT FLUSH".

person *pers_p;
struct {int num; char street[61], city[31], state[3], zip[11];} addr1;
...
addr1.num = 500;
strcpy((char *)addr1.street , (char *)"Oracle Parkway");
strcpy((char *)addr1.city,    (char *)"Redwood Shores");
strcpy((char *)addr1.state,   (char *)"CA");
strcpy((char *)addr1.zip,     (char *)"94065");

/* Convert native C types to object types */
EXEC SQL OBJECT SET :pers_p->addr TO :addr1;

See all other OBJECT statements in this Appendix.

F.66 OBJECT UPDATE (Executable Embedded SQL Extension)

To mark a persistent object or array of objects as updated in the object cache.

Precompiler option OBJECTS must be set to YES. The INTYPE option must specify the OTT-generated type files. Include OTT-generated header files in your program.

For usage notes as well as keywords and parameters, see "OBJECT UPDATE".

person *pers_p;
...
/* Mark as updated */
EXEC SQL OBJECT UPDATE :pers_p;

See all other OBJECT statements in this Appendix.

F.67 OPEN (Executable Embedded SQL)

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

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

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

This example illustrates the use of the OPEN statement in a Pro*C/C++ 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 STATEMENT (Embedded SQL Directive) .

FETCH (Executable Embedded SQL) .

PREPARE (Executable Embedded SQL).

F.68 OPEN DESCRIPTOR (Executable Embedded SQL)

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.

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

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

char dyn_statement[1024] ;
...
EXEC SQL ALLOCATE DESCRIPTOR 'input_descriptor' ;
EXEC SQL ALLOCATE DESCRIPTOR 'output descriptor'
...
EXEC SQL PREPARE S FROM :dyn_statement ;
EXEC SQL DECLARE C CURSOR FOR S ;
...
EXEC SQL OPEN C USING DESCRIPTOR 'input_descriptor' ;
...

CLOSE (Executable Embedded SQL) .

DECLARE CURSOR (Embedded SQL Directive).

FETCH DESCRIPTOR (Executable Embedded SQL) .

PREPARE (Executable Embedded SQL).

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

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

OPEN (Executable Embedded SQL) .

F.70 REGISTER CONNECT (Executable Embedded SQL Extension)

To allow an external C procedure to be called from a Pro*C/C++ application.

None.

For a complete discussion of how to write an external procedure, and the restrictions that are in effect, see External Procedures.

void myfunction(epctx)
OCIExtProcContext *epctx;
sql_context context;
...
{
EXEC SQL REGISTER CONNECT USING :epctx ;
EXEC SQL USE :context;
...

None

F.71 ROLLBACK (Executable Embedded SQL)

To undo work done in the current transaction.

You can also use this statement 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. 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.

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

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

EXEC SQL
    ROLLBACK WORK
    FORCE '25.32.87' ;

COMMIT (Executable Embedded SQL) .

SAVEPOINT (Executable Embedded SQL) .

F.72 SAVEPOINT (Executable Embedded SQL)

To identify a point in a transaction to which you can later roll back.

None.

For more information on this statement, see "Using the SAVEPOINT Statement ".

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

EXEC SQL SAVEPOINT save3;

COMMIT (Executable Embedded SQL) .

ROLLBACK (Executable Embedded SQL) .

F.73 SELECT (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 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.

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.

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

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

PREPARE (Executable Embedded SQL).

F.74 SET DESCRIPTOR (Executable Embedded SQL)

Use this ANSI dynamic SQL statement to set information in the descriptor area from host variables.

Use after a DESCRIBE DESCRIPTOR statement.

where item_name can be one of these choices:

Use DYNAMIC=ANSI precompiler option.

Set CHARACTER_SET_NAME to UTF16 for client-side Unicode support.

See "SET DESCRIPTOR" for complete details, including tables of descriptor item names.

EXEC SQL SET DESCRIPTOR GLOBAL :mydescr COUNT = 3 ;

ALLOCATE DESCRIPTOR (Executable Embedded SQL).

DEALLOCATE DESCRIPTOR (Embedded SQL Statement).

DESCRIBE (Executable Embedded SQL Extension) .

GET DESCRIPTOR (Executable Embedded SQL).

PREPARE (Executable Embedded SQL).

F.75 TYPE (Oracle Embedded SQL Directive)

To perform user-defined type equivalencing, or to assign an external datatype to a whole class of host variables by equivalencing the external datatype to a user-defined datatype.

The user-defined datatype must be previously declared in an embedded SQL program.

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.

Pro*C/C++ also supports the embedded SQL VAR statement for host variable equivalencing. For more information, see "User-Defined Type Equivalencing ".

This example shows an embedded SQL TYPE statement in a Pro*C/C++ Precompiler program:

struct screen {
    short len; 
    char  buff[4002];
}; 

typedef struct screen graphics; 

    EXEC SQL TYPE graphics IS VARRAW(4002); 
    graphics crt;  -- host variable of type graphics 
    ... 

VAR (Oracle Embedded SQL Directive) .

F.76 UPDATE (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 provides the ability to update values in any table or any view's base table.

where the DML returning clause is:

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.

For more information on this statement, see Chapter 6, " Embedded SQL", and Chapter 3, " Database Concepts".

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

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

F.77 VAR (Oracle Embedded SQL Directive)

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.

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

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.

For example:

#define LENGTH 10
...
char character set is nchar_cs ename[LENGTH+1];
exec sql var ename is string(LENGTH+1) convbufsz is (LENGTH*2);

Note also that macros are permitted in this statement.

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

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

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

TYPE (Oracle Embedded SQL Directive) .

F.78 WHENEVER (Embedded SQL Directive)

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

None.

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.

For more information on this directive, see "Using the WHENEVER Directive".

Do not confuse the WHENEVER embedded SQL directive with the WHENEVER SQL*Plus command.

The following two examples illustrates the uses of the WHENEVER directive in an embedded SQL program:

Example 1:

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

Example 2:

EXEC SQL WHENEVER SQLERROR GOTO connect_error; 
... 
connect_error: 
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL ROLLBACK RELEASE; 
    printf("\nInvalid username/password\n"); 
    exit(1); 

None