Release 8.0
A54661_01
 Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Oracle Pro*C/C++ Precompiler Release 8.0 A54661_01 |
|
This appendix contains descriptions of both the SQL92 embedded commands and directives, as well as the Oracle embedded SQL extensions.
Note: Only statements which differ in syntax from non-embedded SQL are described in this appendix. For details of the non-embedded SQL statements, see the Oracle8 Server SQL Reference.
This appendix has the following sections:
Embedded SQL commands place DDL, DML, and Transaction Control statements within a Pro*C/C++ program. Table F-1 provides a functional summary of the embedded SQL commands and directives.
The Source/Type column in Table F-1 is displayed in the format::
|
Source |
is either SQL92 standard SQL (S) or an Oracle extension (O) |
|
Type |
is either an executable (E) statement or a directive (D) |
Table F-1: Precompiler Directives and Embedded SQL Commands and Clauses
The directives, commands, and clauses appear alphabetically. The description of each contains the following sections:
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.
Commands and other keywords appear in UPPER CASE inside rectangles. Type them exactly as shown in the rectangles. Parameters appear in lower case inside ovals. Substitute variables for the parameters in statements you write. Operators, delimiters, and terminators appear in circles. Following the conventions defined in the Preface, a semicolon terminates statements.
If the syntax diagram has more than one path, you can choose any path to travel.
If you have the choice of more than one keyword, operator, or parameter, your options appear in a vertical list. In the following example, you can travel down the vertical line as far as you like, then continue along any horizontal line:
According to the diagram, all of the following statements are valid:
EXEC SQL WHENEVER NOT FOUND ... EXEC SQL WHENEVER SQLERROR ... EXEC SQL WHENEVER SQLWARNING ...
Required keywords and parameters can appear singly or in a vertical list of alternatives. Single required keywords and parameters appear on the main path, that is, on the horizontal line you are currently traveling. In the following example, cursor is a required parameter:
If there is a cursor named emp_cursor, then, according to the diagram, the following statement is valid:
EXEC SQL CLOSE emp_cursor;
If any of the keywords or parameters in a vertical list appears on the main path, one of them is required. That is, you must choose one of the keywords or parameters, but not necessarily the one that appears on the main path. In the following example, you must choose one of the four actions:
If keywords and parameters appear in a vertical list above the main path, they are optional. In the following example, instead of traveling down a vertical line, you can continue along the main path:
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;
Loops let you repeat the syntax within them as many times as you like. In the following example, column_name is inside a loop. So, after choosing one column name, you can go back repeatedly to choose another, separating the column names by a comma.
If DEBIT, CREDIT, and BALANCE are column names, then, according to the diagram, all of the following statements are valid:
EXEC SQL SELECT DEBIT INTO ... EXEC SQL SELECT CREDIT, BALANCE INTO ... EXEC SQL SELECT DEBIT, CREDIT, BALANCE INTO ...
Read a multi-part diagram as if all the main paths were joined end-to-end. The following example is a two-part diagram:
According to the diagram, the following statement is valid:
EXEC SQL PREPARE statement_name FROM string_literal ;
The names of Oracle objects, such as tables and columns, must not exceed 30 characters in length. The first character must be a letter, but the rest can be any combination of letters, numerals, dollar signs ($), pound signs (#), and underscores (_).
However, if an Oracle identifier is enclosed by quotation marks ("), it can contain any combination of legal characters, including spaces but excluding quotation marks.
Oracle identifiers are not case-sensitive except when enclosed by quotation marks.
In all embedded SQL diagrams, each statement is understood to end with the statement terminator ";".
To allocate a cursor variable to be referenced in a PL/SQL bloc, or to allocate space in the object cache.
A cursor variable (see Chapter 3, "Developing a Pro*C/C++ Application") 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.
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.
For more information on this command, see PL/SQL User's Guide and Reference and Oracle8 Server SQL Reference.
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;
...
}
CACHE FREE ALL command on page F-10.
CLOSE command on page F-11
EXECUTE command on page F-36
FETCH command on page F-39
FREE command on page F-41.
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" on page 8-8.
EXEC SQL AT mydb CACHE FREE ALL ;
ALLOCATE command on page F-8.
FREE command on page F-41.
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.
|
cursor |
is a cursor to be closed. |
|
cursor_variable |
is a cursor variable to be closed. |
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 Chapter 9, "Running the Pro*C/C++ Precompiler".
This example illustrates the use of the CLOSE command:
EXEC SQL CLOSE emp_cursor;
PREPARE command on page F-56
DECLARE CURSOR command on page F-20
OPEN command on page F-54
To end your current transaction, making permanent all its changes to the database and optionally freeing all resources and disconnecting from the Oracle8 Server.
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 Trusted Oracle in DBMS MAC mode, you can only commit an in-doubt transaction if your DBMS label matches 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:
Always explicitly commit or rollback the last transaction in your program by using the COMMIT or ROLLBACK command and the RELEASE option. Oracle8 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 Chapter 10, "Defining and Controlling Transactions".
This example illustrates the use of the embedded SQL COMMIT command:
EXEC SQL AT sales_db COMMIT RELEASE;
ROLLBACK command on page F-58
SAVEPOINT command on page F-61
To log on to an Oracle8 database.
You must have CREATE SESSION system privilege in the specified database.
If you are using Trusted 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 Trusted Oracle in OS 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 "Connecting to Oracle8" on page 4-18.
The following example illustrate the use of CONNECT:
EXEC SQL CONNECT :username
IDENTIFIED BY :password
You can also use this statement in which the value of :userid is the value of :username and :password separated by a "/" such as 'SCOTT/TIGER':
EXEC SQL CONNECT :userid
COMMIT command on page F-12
DECLARE DATABASE command on page F-22
ROLLBACK command on page F-58
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.
|
:context |
is the SQLLIB runtime context for which memory is to be allocated. |
In a multi-threaded application, execute this function once for each runtime context.
For more information on this command, see "Developing Multi-threaded Applications" on page 4-35.
This example illustrates the use of a CONTEXT ALLOCATE command in a Pro*C/C++ embedded SQL program:
EXEC SQL CONTEXT ALLOCATE :ctx1;
CONTEXT FREE on page F-17
CONTEXT USE on page F-18
ENABLE THREADS on page F-33
To free all memory associated with a runtime context and place a null pointer in the host program variable.
The CONTEXT ALLOCATE command must be used to allocate memory for the specified runtime context before the CONTEXT FREE command can free the memory allocated for it.
|
context |
is the allocated runtime context for which the memory is to be deallocated. |
For more information on this command, see "Developing Multi-threaded Applications" on page 4-35.
This example illustrates the use of a CONTEXT FREE command in a Pro*C/C++ embedded SQL program:
EXEC SQL CONTEXT FREE :ctx1;
CONTEXT ALLOCATE on page F-16
CONTEXT USE on page F-18
ENABLE THREADS on page F-33
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 allocated using the CONTEXT ALLOCATE command.
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 command, see "Developing Multi-threaded Applications" on page 4-35.
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 on page F-16
CONTEXT FREE on page F-17
ENABLE THREADS on page F-33
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, provided that the cursor has been opened with an OPEN statement and positioned on a row with a FETCH statement. For more information on this command, see "Using Cursors" on page 6-17.
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 command on page F-11
DECLARE DATABASE command on page F-22
DECLARE STATEMENT command on page F-23
DELETE command on page F-28
FETCH command on page F-39
OPEN command on page F-54
PREPARE command on page F-56
SELECT command on page F-62
UPDATE command on page F-66
To declare an identifier for a non-default database to be accessed in subsequent embedded SQL statements.
You must have access to a username on the non-default database.
|
db_name |
is the identifier established for the non-default database. |
You declare a db_name for a non-default database so that other embedded SQL statements can refer to that database using the AT clause. Before issuing a CONNECT statement with an AT clause, you must declare a db_name for the non-default database with a DECLARE DATABASE statement.
For more information on this command, see "Single Explicit Connections" on page 4-23.
This example illustrates the use of a DECLARE DATABASE directive:
EXEC SQL DECLARE oracle3 DATABASE
COMMIT command on page F-12
CONNECT command on page F-14
DECLARE CURSOR command on page F-20
DECLARE STATEMENT command on page F-23
DELETE command on page F-28
EXECUTE command on page F-36
EXECUTE IMMEDIATE command on page F-37
INSERT command on page F-42
SELECT command on page F-62
UPDATE command on page F-66
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 Chapter 3, "Developing a Pro*C/C++ Application" and Chapter 13, "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 command on page F-11
DECLARE DATABASE command on page F-22
FETCH command on page F-39
PREPARE command on page F-56
OPEN command on page F-54
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:.
|
table |
is the name of the declared table. |
|
column |
is a column of the table. |
|
datatype |
is the datatype of a column. For information on Oracle8 datatypes, see "Oracle Datatypes" on page 3-18. If the datatype is a user-defined object, a size may be entered in parentheses. The size cannot be a macro or a complex C expression. The size can be omitted. See examples. |
|
DEFAULT |
specifies the default value of a column. |
|
expr |
is any expression usable as a default column value in a CREATE TABLE statement. |
|
NULL |
specifies that a column can contain nulls. |
|
NOT NULL |
specifies that a column cannot contain nulls. |
|
WITH DEFAULT |
is supported for compatibility with the IBM DB2 database. |
For information on using this command, see "Using DECLARE TABLE" on page D-5.
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);
For an object datatype, the use of size in a parenthesis is optional:
EXEC SQL DECLARE person TYPE AS OBJECT (name VARCHAR2(20), age INT);
...
EXEC SQL DECLARE odjtab1 TABLE OF person;
Alternatively, the equivalent table declaration can be (size cannot be given as a macro or a complex C expression):
EXEC SQL DECLARE odjtab2 TABLE OF person(20);
See DECLARE TYPE on page F-27.
To define the attributes of a type for a semantics check by the precompiler.
None.
|
type |
is the name of the declared type |
|
column |
is a column |
|
datatype |
is the datatype of the column |
For information on using this command, see "Using DECLARE TYPE" on page D-5.
EXEC SQL DECLARE project_type TYPE (
pno CHAR(5),
pname CHAR(20),
budget NUMBER);
DECLARE TABLE directive on page F-25.
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 allows you to delete rows from any table or any view's base table.
If you are using Trusted 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:
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:
|
AT |
identifies the database to which the DELETE statement is issued. The database can be identified by either: |
|
|
|
db_name |
is a database identifier declared in a previous |
|
|
:host_integer |
is a host variable whose value is a previously declared db_name |
|
|
If you omit this clause, the DELETE statement is issued to your default database. |
|
|
FOR :host_integer |
limits the number of times the statement is executed if the WHERE clause contains array host variables. If you omit this clause, Oracle8 executes the statement once for each component of the smallest array. |
|
|
schema |
is the schema containing the table or view. If you omit schema, Oracle8 assumes the table or view is in your own schema. |
|
|
table view |
is the name of a table from which the rows are to be deleted. If you specify view, Oracle8 deletes rows from the view's base table. |
|
|
db_link |
is the complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see the Oracle8 Server SQL Reference . You can only delete rows from a remote table or view if you are using Oracle8 with the distributed option. |
|
|
|
If you omit dblink, Oracle8 assumes that the table or view is located on the local database. |
|
|
part_name |
is a partition within the table |
|
|
alias |
is an alias assigned to the table. Aliases are generally used in DELETE statements with correlated queries. |
|
|
WHERE |
specifies which rows are deleted: |
|
|
|
condition |
deletes only rows that satisfy the condition. This condition can contain host variables and optional indicator variables. See the syntax description of condition in Oracle8 Server SQL Reference . |
|
|
CURRENT OF |
deletes only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join, unless its FOR UPDATE clause specifically locks only one table. |
|
|
If you omit this clause entirely, Oracle8 deletes all rows from the table or view. |
|
The host variables in the WHERE clause must be either all scalars or all arrays. If they are scalars, Oracle8 executes the DELETE statement only once. If they are arrays, Oracle8 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 Oracle8 executes the statement is determined by the smaller of the following values:
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, Oracle8 returns an error through the SQLCODE of the SQLCA. If you omit the WHERE clause, Oracle8 raises a warning flag in the fifth component of SQLWARN in the SQLCA. For more information on this command and the SQLCA, see Chapter 11, "Handling Runtime Errors".
You can use Comments in a DELETE statement to pass instructions, or hints, to the Oracle8 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle8 Server Tuning.
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 command on page F-22
DECLARE STATEMENT command on page F-23
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 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 command, see Chapter 13, "Using 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 command on page F-56
To initialize a process that supports multiple threads.
You must be developing a precompiler application for and compiling it on a platform that supports multi-threaded applications, and THREADS=YES must be specified on the command line.
None.
The ENABLE THREADS command must be executed before any other executable SQL statement and before spawning any thread. This command does not require a host-variable specification.
For more information on this command, see "Developing Multi-threaded Applications" on page 4-35.
This example illustrates the use of the ENABLE THREADS command in a Pro*C/C++ embedded SQL program:
EXEC SQL ENABLE THREADS;
CONTEXT ALLOCATE on page F-16
CONTEXT FREE on page F-17
CONTEXT USE on page F-18
To embed an anonymous PL/SQL block into an Oracle Precompiler program.
None.
|
AT |
identifies the database on which the PL/SQL block is executed. The database can be identified by either: |
|
|
|
db_name |
is a database identifier declared in a previous |
|
|
:host_variable |
is a host variable whose value is a previously declared db_name. |
|
|
If you omit this clause, the PL/SQL block is executed on your default database. |
|
|
pl/sql_block |
For information on PL/SQL, including how to write PL/SQL blocks, see the PL/SQL User's Guide and Reference. |
|
|
END-EXEC |
must appear after the embedded PL/SQL block, regardless of which programming language your Oracle Precompiler program uses. The keyword END-EXEC must be followed by the C/C++ statement terminator, ";". |
|
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 Chapter 6, "Using 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 embedded SQL command on page F-37
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 Chapter 13, "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 command on page F-22
PREPARE command on page F-56
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, Oracle8 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 Chapter 13, "Using Dynamic SQL".
This example illustrates the use of the EXECUTE IMMEDIATE statement:
EXEC SQL EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = 9460'
PREPARE command on page F-56
EXECUTE command on page F-36
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, Oracle8 fetches only one row. If they are arrays, Oracle8 fetches enough rows to fill the arrays.
Array host variables can have different sizes. In this case, the number of rows Oracle8 fetches is determined by the smaller of the following values:
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. For more information, see "Using the FETCH Statement" on page 5-14.
This example illustrates the FETCH command:
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;
EXEC SQL FETCH emp_cursor INTO :job_title2, :salary2;
...
}
PREPARE command on page F-56
DECLARE CURSOR command on page F-20
OPEN command on page F-54
CLOSE command on page F-11
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" on page 8-7 for more information.
EXEC SQL FREE :ptr;
ALLOCATE command on page F-8.
CACHE FREE ALL command on page F-10.
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 allows you to insert rows into any table or any view's base table.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
|
AT |
identifies the database on which the INSERT statement is executed. The database can be identified by either: |
|
|
|
db_name |
is a database identifier declared in a previous DECLARE DATABASE statement. |
|
|
:host_variable |
is a host variable whose value is a previously declared db_name |
|
|
If you omit this clause, the INSERT statement is executed on your default database. |
|
|
FOR :host_integer |
limits the number of times the statement is executed if the VALUES clause contains array host variables. If you omit this clause, Oracle8 executes the statement once for each component in the smallest array. |
|
|
schema |
is the schema containing the table or view. If you omit schema, Oracle8 assumes the table or view is in your own schema. |
|
|
table |
is the name of the table into which rows are to be inserted. If you specify view, Oracle8 inserts rows into the view's base table. |
|
|
db_link |
is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see the Oracle8 Server SQL Reference . You can only insert rows into a remote table or view if you are using Oracle8 with the distributed option. |
|
|
|
If you omit dblink, Oracle8 assumes that the table or view is on the local database. |
|
|
part_name |
is a partition in the table |
|
|
column |
is a column of the table or view. In the inserted row, each column in this list is assigned a value from the VALUES clause or the query. |
|
|
part_name |
is the name of partition in the table |
|
|
|
If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. If you omit the column list altogether, the VALUES clause or query must specify values for all columns in the table. |
|
|
VALUES |
specifies a row of values to be inserted into the table or view. See the syntax description in Oracle8 Server SQL Reference. Note that the expressions can be host variables with optional indicator variables. You must specify an expression in the VALUES clause for each column in the column list. |
|
|
subquery |
is a subquery that returns rows that are inserted into the table. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. For the syntax description of a subquery, see "SELECT" in Oracle8 Server SQL Reference. |
|
Any host variables that appear in the WHERE clause must be either all scalars or all arrays. If they are scalars, Oracle8 executes the INSERT statement once. If they are arrays, Oracle8 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 Oracle8 executes the statement is determined by the smaller of the following values:
For more information on this command, see "Using the INSERT Statement" on page 5-10.
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;
DECLARE DATABASE command on page F-22
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" on page 8-11.
person *pers_p;
person_ind *pers_ind;
person_ref *pers_ref;
...
EXEC SQL CREATE :pers_p:pers_ind TABLE PERSON_TAB
RETURNING REF INTO :pers_ref;
See all other OBJECT statements in this appendix.
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" on page 8-13.
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.
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" on page 8-12.
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)" on page F-8.
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" on page 8-14.
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.
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" on page 8-18.
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.
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" on page 8-13.
person *pers_p;
...
EXEC SQL OBJECT RELEASE :pers_p;
See all other OBJECT statements in this Appendix.
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 SET" on page 8-16.
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.
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.
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.
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 command.
You can reopen a cursor without first closing it. For more information on this command, see "Using the INSERT Statement" on page 5-10.
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;
PREPARE command on page F-56
DECLARE CURSOR command on page F-20
FETCH command on page F-39
CLOSE command on page F-11
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. For more information, see "PREPARE" on page 13-18.
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;
DECLARE CURSOR command on page F-20
OPEN command on page F-54
FETCH command on page F-39
CLOSE command on page F-11
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 Oracle8 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 Oracle8 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:
Using the ROLLBACK command with the TO SAVEPOINT clause performs the following operations:
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, Oracle8 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;
Oracle8 with the distributed option allows you 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.
For more information on when to roll back in-doubt transactions, see Oracle8 Server Distributed Systems.
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 command on page F-12
SAVEPOINT command on
To identify a point in a transaction to which you can later roll back.
None.
For more information on this command, see "Using the SAVEPOINT Statement" on page 10-5.
This example illustrates the use of the embedded SQL SAVEPOINT command:
EXEC SQL SAVEPOINT save3;
COMMIT command on page F-12
ROLLBACK command on page F-58
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 allows you to select data from any table or any snapshot or any view's base table.
If you are using Trusted 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.
|
AT |
Identifies the database to which the SELECT statement is issued. The database can be identified by either: |
|
|
|
db_name |
A database identifier declared in a previous DECLARE DATABASE statement. |
|
|
:host_variable |
Host variable whose value is a previously declared db_name. |
|
|
If you omit this clause, the SELECT statement is issued to your default database. |
|
|
select_list |
Identical to the non-embedded SELECT command except that a host variables can be used in place of literals. |
|
|
INTO |
Specifies output host variables and optional indicator variables to receive the data returned by the SELECT statement. Note that these variables must be either all scalars or all arrays, but arrays need not have the same size. |
|
|
WHERE |
Restricts the rows returned to those for which the condition is TRUE. See the syntax description of condition in Oracle8 Server SQL Reference . The condition can contain host variables, but cannot contain indicator variables. These host variables can be either scalars or arrays. |
|
|
All other keywords and parameters are identical to the non-embedded SQL SELECT command. ASC, ascending, is the default for the ORDER BY clause. |
||
If no rows meet the WHERE clause condition, no rows are retrieved and Oracle8 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 Oracle8 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle8 Server Tuning.
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 DATABASE command on page F-22
DECLARE CURSOR command on page F-20
EXECUTE command on page F-36
FETCH command on page F-39
PREPARE command on page F-56
To perform user-defined type equivalencing, or to assign an Oracle8 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.
|
type |
is the user-defined datatype to be equivalenced with an Oracle8 external datatype. |
|
datatype |
is an Oracle8 external datatype recognized by the Oracle Precompilers (not an Oracle8 internal datatype). The datatype may include a length, precision, or scale. This external datatype is equivalenced to the user-defined type and assigned to all host variables assigned the type. For a list of external datatypes, see Chapter 3. |
|
REFERENCE |
makes the equivalenced type a pointer type. |
User defined type equivalencing is one kind of datatype equivalencing. You can only perform user-defined type equivalencing with the embedded SQL TYPE command in a Pro*C/C++ program. You may want to use datatype equivalencing for one of the following purposes:
Pro*C/C++ also suppors the embedded SQL VAR command for host variable equivalencing. For more information, see "User-Defined Type Equivalencing" on page 3-59.
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 directive on page page F-71
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 allows you to update values in any table or any view's base table.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
|
AT |
identifies the database to which the UPDATE statement is issued. The database can be identified by either: |
|
|
|
dbname |
is a database identifier declared in a previous DECLARE DATABASE statement. |
|
|
:host_variable |
is a host variable whose value is a previously declared db_name. |
|
|
If you omit this clause, the UPDATE statement is issued to your default database. |
|
|
FOR :host_integer |
limits the number of times the UPDATE statement is executed if the SET and WHERE clauses contain array host variables. If you omit this clause, Oracle8 executes the statement once for each component of the smallest array. |
|
|
schema |
is the schema containing the table or view. If you omit schema, Oracle8 assumes the table or view is in your own schema. |
|
|
table |
is the name of the table to be updated. If you specify view, Oracle8 updates the view's base table. |
|
|
dblink |
is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see the Oracle8 Server SQL Reference. . You can only use a database link to update a remote table or view if you are using Oracle8 with the distributed option. |
|
|
part_name |
name of partition |
|
|
alias |
is a name used to reference the table, view, or subquery elsewhere in the statement. |
|
|
column |
is the name of a column of the table or view that is to be updated. If you omit a column of the table from the SET clause, that column's value remains unchanged. |
|
|
expr |
is the new value assigned to the corresponding column. This expression can contain host variables and optional indicator variables. See the syntax of expr in the Oracle8 Server SQL Reference. . |
|
|
subquery_1 |
is a subquery that returns new values that are assigned to the corresponding columns. For the syntax of a subquery, see "SELECT" in Oracle8 Server SQL Reference. . |
|
|
subquery_2 |
is a subquery that return a new value that is assigned to the corresponding column. For the syntax of a subquery, see "SELECT" in the Oracle8 Server SQL Reference. . |
|
|
WHERE |
specifies which rows of the table or view |
|
|
|
condition |
updates only rows for which this condition is true. This condition can contain host variables and optional indicator variables. See the syntax of condition in the Oracle8 Server SQL Reference. . |
|
|
CURRENT OF |
updates only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join unless its FOR UPDATE clause explicitly locks only one table. |
|
|
If you omit this clause entirely, Oracle8 updates all rows of the table or view. |
|
Host variables in the SET and WHERE clauses must be either all
scalars or all arrays. If they are scalars, Oracle8 executes the UPDATE statement only once. If they are arrays, Oracle8 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 Oracle8 executes the statement is determined by the smaller
of the following values:
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 Oracle8 returns an error message through the SQLCODE element of the SQLCA. If you omit the WHERE clause, all rows are updated and Oracle8 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 Oracle8 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle8 Server Tuning.
For more information on this command, see Chapter 5, "Using Embedded SQL", and Chapter 10, "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 command on page F-22
To perform host variable equivalencing, or to assign a specific Oracle8 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.
|
host_variable |
is the host variable to be assigned an Oracle8 external datatype. |
|
dtyp |
is an Oracle8 external datatype recognized by the Oracle Precompilers (not an Oracle8 internal datatype). The datatype may include a length, precision, or scale. This external datatype is assigned to the host_variable. For a list of external datatypes, see Chapter 3. |
|
len |
length of the datatype |
|
prec |
precision |
|
scale |
scale |
|
size |
is the size in bytes of a buffer in the Oracle8 runtime library used to perform conversion between character sets of the host_variable |
len, prec, scale and buf can be constant expressions.
Host variable equivalencing is one kind of datatype equivalencing. Datatype equivalencing is useful for any of the following purposes:
The Pro*C/C++ Precompiler also supports the precompiler TYPE directive for user-defined type equivalencing. See also "Host Variable Equivalencing" on page 3-58.
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;
...
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(2000); -- refer to RAW
...
EXEC SQL END DECLARE SECTION;
TYPE directive on page page F-65
To specify the action to be taken when an error or warning results from executing an embedded SQL program.
None.
The following syntax diagram shows how to construct a WHENEVER statement: 
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 "Using the WHENEVER Statement" on page 11-24.
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 an 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
