F
Embedded SQL Statements and Precompiler Directives

This appendix describes of both SQL92 embedded SQL statements and directives as well as the Oracle9i embedded SQL extensions. These statements and directives are prefaced in your source code with the keywords, EXEC SQL.

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 Oracle9i SQL Reference.

This appendix contains the following sections:

Summary of Precompiler Directives and Embedded SQL Statements

Embedded SQL statements place DDL, DML, and Transaction Control statements within a procedural language program. Embedded SQL is supported by the Oracle Precompilers. Table F-2 provides a functional summary of the embedded SQL statements and directives.

The Source/Type column in Table F-2 is displayed in the format source/type where:

Table F-1 Source/Type Column Meaning

SQL Statements	Directives
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-2 Precompiler Directives and Embedded SQL Statements and Clauses

EXEC SQL Statement	Source/Type	Purpose
ALLOCATE	O/E	To allocate memory for a cursor variable, LOB locator or ROWID.
ALLOCATE DESCRIPTOR	S/E	To allocate a descriptor for ANSI dynamic SQL.
CALL	S/E	Call a stored procedure.
CLOSE	S/E	To disable a cursor.
COMMIT	S/E	To make all database changes permanent.
CONNECT	O/E	To log on to a database instance.
CONTEXT ALLOCATE	O/E	To allocate memory for a SQLLIB runtime context.
CONTEXT FREE	O/E	To free memory for a SQLLIB runtime context.
CONTEXT USE	O/E	To specify a SQLLIB runtime context.
DEALLOCATE DESCRIPTOR	S/E	To deallocate a descriptor area to free memory.
DECLARE CURSOR	S/D	To declare a cursor, associating it with a query.
DECLARE DATABASE	O/D	To declare an identifier for a nondefault database to be accessed in subsequent embedded SQL statements.
DECLARE STATEMENT	S/D	To assign a SQL variable name to a SQL statement.
DECLARE TABLE	O/D	To declare the table structure for semantic checking of embedded SQL statements by the Oracle Precompiler.
DELETE	S/E	To remove rows from a table or from a view's base table.
DESCRIBE	S/E	To initialize a descriptor, a structure holding host variable descriptions.
DESCRIBE DECRIPTOR	S/E	To obtain information about an ANSI SQL statement, and store it in a descriptor.
ENABLE THREADS	O/E	To initialize a process that supports multiple threads.
EXECUTE...END-EXEC	O/E	To execute an anonymous PL/SQL block.
EXECUTE	S/E	To execute a prepared dynamic SQL statement.
EXECUTE DESCRIPTOR	S/E	To execute a prepared statement using ANSI Dynamic SQL.
EXECUTE IMMEDIATE	S/E	To prepare and execute a SQL statement with no host variables.
FETCH	S/E	To retrieve rows selected by a query.
FETCH DESCRIPTOR	S/E	To retrieve rows selected by a query using ANSI Dynamic SQL.
FREE	S/E	To free memory used by a cursor, LOB locator, or ROWID.
GET DESCRIPTOR	S/E	To move information from an ANSI SQL descriptor area into host variables.
INSERT	S/E	To add rows to a table or to a view's base table.
LOB APPEND	O/E	To append a LOB to the end of another lOB.
LOB ASSIGN	O/E	To assign a LOB or BFILE locator to another locator.
LOB CLOSE	O/E	To close an open LOB or BFILE.
LOB COPY	O/E	To copy all or part of a LOB value into another LOB.
LOB CREATE TEMPORARY	O/E	To create a temporary LOB.
LOB DESCRIBE	O/E	To retrieve attributes from a LOB.
LOB DISABLE BUFFERING	O/E	To disable LOB buffering.
LOB ENABLE BUFFERING	O/E	To enable LOB buffering.
LOB ERASE	O/E	To erase a given amount of LOB data starting from a given offset.
LOB FILE CLOSE ALL	O/E	To close all open BFILE.
LOB FILE SET	O/E	To set DIRECTORY and FILENAME in a BFILE locator.
LOB FLUSH BUFFER	O/E	To write the LOB buffers to the database server.
LOB FREE TEMPORARY	O/E	To free temporary space for the LOB locator.
LOB LOAD	O/E	To copy all or part of a BFILE into an internal LOB.
LOB OPEN	O/E	To open a LOB or BFILE to read or read/write access.
LOB READ	O/E	To read all or part of a LOB or BFILE into a buffer.
LOB TRIM	O/E	To truncate a lob value.
LOB WRITE	O/E	To write the contents of a buffer to a LOB.
OPEN	S/E	To execute the query associated with a cursor.
OPEN DESCRIPTOR	S/E	To execute the query associated with a cursor in ANSI Dynamic SQL.
PREPARE	S/E	To parse a dynamic SQL statement.
ROLLBACK	S/E	To end the current transaction and discard all changes.
SAVEPOINT	S/E	To identify a point in a transaction to which you can later roll back.
SELECT	S/E	To retrieve data from one or more tables, views, or snapshots, assigning the selected values to host variables.
SET DESCRIPTOR	S/E	To set information in the ANSI SQL descriptor area from host variables.
UPDATE	S/E	To change existing values in a table or in a view's base table.
VAR	O/D	To override the default datatype and assign a specific Oracle9i external datatype to a host variable.
WHENEVER	S/D	To specify handling for error and warning conditions.

About the Statement Descriptions

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

Directives	Description
Purpose	Describes the basic uses of the statement.
Prerequisites	Lists privileges you must have and steps that you must take before using the statement. Unless otherwise noted, most statements also require that the database be open by your instance.
Syntax	Shows the syntax diagram with the keywords and parameters of the statement.
Keywords and Parameters	Describes the purpose of each keyword and parameter.
Usage Notes	Discusses how and when to use the statement.
Prerequisites	Lists privileges you must have and steps that you must take before using the statement. Unless otherwise noted, most statements also require that the database be open by your instance.
Syntax	Shows the syntax diagram with the keywords and parameters of the statement.

How to Read Syntax Diagrams

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

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

Statements keywords appear in UPPER CASE inside rectangles. Type them exactly as shown in the rectangles. Parameters appear in lower case inside ovals. Variables are used for the parameters. Operators, delimiters, and terminators appear inside circles.

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:

Text description of the illustration first.gif

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

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

Statement Terminator

In all Pro*COBOL EXEC SQL diagrams, each statement is understood to end with the token END-EXEC.

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:

Text description of the illustration reqp.gif

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

EXEC SQL CLOSE EMPCURSOR END-EXEC.

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:

Text description of the illustration reqp1.gif

Optional Keywords and Parameters

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:

Text description of optional.gif follows

Text 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 END-EXEC. 
     EXEC SQL ROLLBACK WORK END-EXEC. 
     EXEC SQL AT ORACLE2 ROLLBACK END-EXEC.

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.

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

Multi-part Diagrams

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

Text description of the illustration part.gif

According to the diagram, the following statement is valid:

     EXEC SQL PREPARE statement_name FROM :host_string END-EXEC.

Oracle Names

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

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

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

ALLOCATE (Executable Embedded SQL Extension)

Purpose

To allocate a cursor variable to be referenced in a PL/SQL block, or to allocate a LOB locator, or a ROWID .

Prerequisites

A cursor variable (see Chapter 6, "Embedded PL/SQL") of type SQL-CURSOR must be declared before allocating memory for the cursor variable.

Syntax

Text description of the illustration allocob.gif

Keywords and Parameters

Keywords and Parameters	Description
cursor_variable	A cursor variable of type SQL-CURSOR
host_ptr	A variable of type SQL-ROWID for a ROWID, or SQL-BLOB, SQL-CLOB, or SQL-NCLOB for a LOB

Usage Notes

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 statement, seePL/SQL User's Guide and Reference and Oracle9i SQL Reference.

Example

This partial example illustrates the use of the ALLOCATE statement:

       ...
 01  EMP-CUR       SQL-CURSOR.
 01  EMP-REC.
     ...
     EXEC SQL ALLOCATE :EMP-CUR END-EXEC.
     ...

ALLOCATE DESCRIPTOR (Executable Embedded SQL)

Purpose

An ANSI dynamic SQL statement that allocates a descriptor.

Prerequisites

None.

Syntax

Text description of the illustration alldesc.gif

Keywords and Parameters

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

Usage Notes

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

Example

     EXEC SQL 
       FOR :batch ALLOCATE DESCRIPTOR GLOBAL :binddes WITH MAX 25 
     END-EXEC.

CALL (Executable Embedded SQL)

Purpose

To call a stored procedure.

Prerequisites

An active database connection must exist.

Syntax

Text description of the illustration call.gif

Keywords and Parameters

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

Usage Notes

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

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

Example

 ...
     05  EMP-NAME      PIC X(10) VARYING.
     05  EMP-NUMBER    PIC S9(4) COMP VALUE ZERO.
     05  SALARY        PIC S9(5)V99 COMP-3 VALUE ZERO.
...
      05  D-EMP-NUMBER  PIC 9(4).
...
      ACCEPT D-EMP-NUMBER.
      EXEC SQL 
        CALL mypkge.getsal(:EMP-NUMBER, :D-EMP-NUMBER, :EMP-NAME) INTO :SALARY 
      END-EXEC.
...

CLOSE (Executable Embedded SQL)

Purpose

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

Prerequisites

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

Syntax

Text description of the illustration close.gif

Keywords and Parameters

Keywords and Parameters	Description
cursor	The cursor to be closed
cursor_variable	The cursor variable to be closed.

Usage Notes

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

Example

This example illustrates the use of the CLOSE statement:

     EXEC SQL CLOSE EMP-CUR END-EXEC.

COMMIT (Executable Embedded SQL)

Purpose

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

Prerequisites

To commit your current transaction, no privileges are necessary.

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

Syntax

Text description of the illustration commit.gif

Keyword and Parameters

Keywords and Parameters	Description

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

Usage Notes

Always explicitly commit or rollback the last transaction in your program by using the COMMIT or ROLLBACK statement and the RELEASE option. Oracle9i 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 "Using the COMMIT Statement".

Example

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

     EXEC SQL AT SALESDB COMMIT RELEASE END-EXEC.

CONNECT (Executable Embedded SQL Extension)

Purpose

To logon to an Oracle9i database.

Prerequisites

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

Syntax

Text description of the illustration connect.gif

Keyword and Parameters

Keywords and Parameters	Description
user password	Specifies your username and password separately.
user_password	Is a single host variable containing the connect string username/password[@dbname]. To allow Oracle9i to verify your connection through your operating system, specify "/" as the :user_password value.
AT	Identifies the database to which the connection is made. The database can be identified by either:
db_name	A database identifier declared in a previous DECLARE DATABASE statement.
host_variable	A host variable whose value is a previously declared db_name.
USING	Specifies the Oracle Net database specification string used to connect to a nondefault database. If you omit this clause, you are connected to your default database.
ALTER AUTHORIZATION	Change password to the following string.
new_password	New password string.
IN SYSDBA MODE IN SYSOPER MODE	Connect with SYSDBA or SYSOPER system privileges. Not allowed when ALTER AUTHORIZATION is used, or precompiler option AUTO_CONNECT is set to YES.

Usage Notes

A program can have multiple connections, but can only connect once to your default database. For more information on this statement, see: "Concurrent Logons".

Example

The following example illustrate the use of CONNECT:

     EXEC SQL CONNECT :USERNAME 
          IDENTIFIED BY :PASSWORD 
     END-EXEC.

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

CONTEXT ALLOCATE (Executable Embedded SQL Extension)

Purpose

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

Prerequisites

The runtime context must be declared of type SQL-CONTEXT.

Syntax

Text description of the illustration conall.gif

Keywords and Parameters

Keywords and Parameters	Description
context	The SQLLIB runtime context for which memory is to be allocated.

Usage Notes

For more information on this statement, see "Embedded SQL Statements and Directives for Runtime Contexts".

Example

This example illustrates the use of a CONTEXT ALLOCATE statement in a Pro*COBOL embedded SQL program:

     EXEC SQL CONTEXT ALLOCATE :ctx1 END-EXEC.

CONTEXT FREE (Executable Embedded SQL Extension)

Purpose

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

Prerequisites

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

Syntax

Text description of the illustration confree.gif

Keywords and Parameters

Keywords and Parameters	Description
context	The allocated runtime context for which the memory is to be deallocated.

Usage Notes

For more information on this statement, see "Embedded SQL Statements and Directives for Runtime Contexts".

Example

This example illustrates the use of a CONTEXT FREE statement in a Pro*COBOL embedded SQL program:

     EXEC SQL CONTEXT FREE :ctx1 END-EXEC.

CONTEXT USE (Oracle Embedded SQL Directive)

Purpose

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

Prerequisites

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

Syntax

Text description of the illustration conuse.gif

Keywords and Parameters

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

Usage Notes

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

For more information on this statement, see "Embedded SQL Statements and Directives for Runtime Contexts".

Example

This example illustrates the use of a CONTEXT USE directive in a Pro*COBOL program:

     EXEC SQL CONTEXT USE :ctx1 END-EXEC.

DEALLOCATE DESCRIPTOR (Embedded SQL Statement)

Purpose

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

Prerequisites

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

Syntax

Text description of dealdesc.gif follows

Text description of the illustration dealdesc.gif

Keywords and Parameters

Keywords and Parameters	Description
GLOBAL \| LOCAL	LOCAL (the default) means file scope, as opposed to GLOBAL, which means application scope.
descriptor_name 'descriptor name'	Host variable containing the name of the allocated ANSI descriptor. Name of the allocated ANSI descriptor.

Keywords and Parameters

Description

GLOBAL | LOCAL

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

descriptor_name

'descriptor name'

Host variable containing the name of the allocated ANSI descriptor.

Name of the allocated ANSI descriptor.

Usage Notes

Use DYNAMIC=ANSI precompiler option.

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

Example

     EXEC SQL DEALLOCATE DESCRIPTOR GLOBAL 'SELDES'  END-EXEC.

DECLARE CURSOR (Embedded SQL Directive)

Purpose

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

Prerequisites

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

Syntax

Text description of the illustration dclco.gif

Keywords and Parameters

Keywords and Parameters	Description
AT	Identifies the database on which the cursor is declared. The database can be identified by either:
db_name	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, Oracle9i declares the cursor on your default database.
cursor	Name of the cursor to be declared.
WITH HOLD	Cursor remains open after a COMMIT or a ROLLBACK. The cursor must not be declared for UPDATE.
SELECT statement	Is a SELECT statement to be associated with the cursor. The following statement cannot contain an INTO clause.
statement_name	Identifies a SQL statement or PL/SQL block to be associated with the cursor. The statement_name or block_name must be previously declared in a DECLARE STATEMENT statement.

Usage Notes

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

You can reference the cursor in the WHERE clause of an UPDATE or DELETE statement using the CURRENT OF syntax, if the cursor has been opened with an OPEN statement and positioned on a row with a FETCH statement. For more information on this statement, see "WITH HOLD Clause in DECLARE CURSOR Statements".

Example

This example illustrates the use of a DECLARE CURSOR statement:

     EXEC SQL DECLARE EMPCURSOR CURSOR 
         FOR SELECT ENAME, EMPNO, JOB, SAL 
         FROM EMP 
         WHERE DEPTNO = :DEPTNO 
     END-EXEC.

DECLARE DATABASE (Oracle Embedded SQL Directive)

Purpose

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

Prerequisites

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

Syntax

Text description of dcldbase.gif follows

Text description of the illustration dcldbase.gif

Keywords and Parameters

Keywords and Parameters	Description
db_name	The identifier established for the nondefault database.

Usage Notes

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 "Using Username/Password".

Example

This example illustrates the use of a DECLARE DATABASE directive:

     EXEC SQL DECLARE ORACLE3 DATABASE END-EXEC.

DECLARE STATEMENT (Embedded SQL Directive)

Purpose

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

Prerequisites

None.

Syntax

Text description of dcl_stmt.gif follows

Text description of the illustration dcl_stmt.gif

Keywords and Parameters

Keywords and Parameters	Description
AT	Identifies the database on which the SQL statement or PL/SQL block is declared. The database can be identified by either:
db_name	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, Oracle9i declares the SQL statement or PL/SQL block on your default database.
statement_name	Is the declared identifier for the statement or PL/SQL block.

Usage Notes

You must declare an identifier for a SQL statement or PL/SQL block with a DECLARE STATEMENT statement only if a DECLARE CURSOR statement referencing the identifier appears physically (not logically) in the embedded SQL program before the PREPARE statement that parses the statement or block and associates it with its identifier.

The scope of a statement declaration is global within its precompilation unit, like a cursor declaration. For more information on this statement, see "DECLARE".

Example I

This example illustrates the use of the DECLARE STATEMENT statement:

     EXEC SQL AT REMOTEDB 
         DECLARE MYSTATEMENT STATEMENT 
     END-EXEC.
     EXEC SQL PREPARE MYSTATEMENT FROM :MY-STRING
     END-EXEC.
     EXEC SQL EXECUTE MYSTATEMENT END-EXEC.

Example II

In this example, the DECLARE STATEMENT statement is required because the DECLARE CURSOR statement precedes the PREPARE statement:

     EXEC SQL DECLARE MYSTATEMENT STATEMENT END-EXEC. 
     ...
     EXEC SQL DECLARE EMPCURSOR CURSOR FOR MYSTATEMENT END-EXEC. 
     ...
     EXEC SQL PREPARE MYSTATEMENT FROM :MY-STRING END-EXEC. 
     ...

DECLARE TABLE (Oracle Embedded SQL Directive)

Purpose

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 precompiler when option SQLCHECK=SEMANTICS (or FULL).

Prerequisites

None.

Syntax

Text description of the illustration dcltab.gif

Keywords and Parameters

Keywords and Parameters	Description
table	The name of the declared table.
column	A column of the table.
datatype	The datatype of a column. For information on Oracle9i datatypes, see "The Oracle9i Datatypes".
NOT NULL	Specifies that a column cannot contain nulls.

Usage Notes

Datatypes can only use integers (not expressions) for length, precision, scale. For more information on using this statement, see "Specifying SQLCHECK=SEMANTICS" on page E-3.

Example

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

DELETE (Executable Embedded SQL)

Purpose

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

Prerequisites

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

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

The DELETE ANY TABLE system privilege also enables you to delete rows from any table or any view's base table.

Syntax

Text description of the illustration delete.gif

where the DML Returning clause is:

Text description of the illustration return.gif

Keywords and Parameters

Keywords and Parameters	Description
AT	Identifies the database to which the DELETE statement is issued. The database can be identified by either:
db_name	A database identifier declared in a previous DECLARE DATABASE statement.
host_variable	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.
host_integer integer	Limits the number of times the statement is executed if the WHERE clause contains array host variables. If you omit this clause, Oracle9i executes the statement once for each component of the smallest array.
schema	The schema containing the table or view. If you omit schema, Oracle9i assumes the table or view is in your own schema.
table view	The name of a table from which the rows are to be deleted. If you specify view, Oracle9i deletes rows from the view's base table.
dblink	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 Chapter 2 of theOracle9i SQL Reference. You can only delete rows from a remote table or view if you are using Oracle9i with the distributed option. If you omit dblink, Oracle9 assumes that the table or view is located on the local database.
part_name	Name of partition in the table
alias	The alias assigned to the table. Aliases are generally used in DELETE statements with correlated queries.
WHERE	Specifies which rows are deleted:
	condition
	CURRENT OF
	If you omit this clause entirely, Oracle9i deletes all rows from the table or view.
DML returning clause	See "DML Returning Clause" for a discussion.

Usage Notes

The host variables in the WHERE clause should be either all scalars or all arrays. If they are scalars, Oracle9i executes the DELETE statement only once. If they are arrays, Oracle9i 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 Oracle9i 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, Oracle9i returns an error through the SQLCODE of the SQLCA. If you omit the WHERE clause, Oracle9i raises a warning flag in the fifth component of SQLWARN in the SQLCA. For more information on this statement and the SQLCA, see "Using the SQL Communications Area".

You can use comments in a DELETE statement to pass instructions, or hints, to the Oracle9i optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, seeOracle9i Database Performance Tuning Guide and Reference.

Example

This example illustrates the use of the DELETE statement:

     EXEC SQL DELETE FROM EMP
        WHERE DEPTNO = :DEPTNO
        AND JOB = :JOB
    END-EXEC.
    EXEC SQL DECLARE EMPCURSOR CURSOR
        FOR SELECT EMPNO, COMM
        FROM EMP
    END-EXEC.
    EXEC SQL OPEN EMPCURSOR END-EXEC.
    EXEC SQL FETCH EMPCURSOR
        INTO :EMP-NUMBER, :COMMISSION
    END-EXEC.
    EXEC SQL DELETE FROM EMP
        WHERE CURRENT OF EMPCURSOR
    END-EXEC.

DESCRIBE (Executable Embedded SQL)

Purpose

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

Prerequisites

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

Syntax

Text description of describe.gif follows

Text description of the illustration describe.gif

Keywords and Parameters

Keywords and Parameters	Description
BIND VARIABLES FOR	Initializes the descriptor to hold information about the input variables for the SQL statement or PL/SQL block.
SELECT LIST FOR	Initializes the descriptor to hold information about the select list of a SELECT statement.
-	The default is SELECT LIST FOR.
statement_name	Identifies a SQL statement or PL/SQL block previously prepared with a PREPARE statement.
descriptor	The name of the descriptor to be initialized.

Usage Notes

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

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

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

Example

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

     EXEC SQL PREPARE MYSTATEMENT FROM :MY-STRING END-EXEC. 
     EXEC SQL DECLARE EMPCURSOR 
         FOR SELECT EMPNO, ENAME, SAL, COMM 
         FROM EMP 
         WHERE DEPTNO = :DEPT-NUMBER 
     END-EXEC.
     EXEC SQL DESCRIBE BIND VARIABLES FOR MYSTATEMENT 
         INTO BINDDESCRIPTOR
     END-EXEC. 
     EXEC SQL OPEN EMPCURSOR 
         USING BINDDESCRIPTOR
     END-EXEC. 
     EXEC SQL DESCRIBE SELECT LIST FOR MY-STATEMENT 
         INTO SELECTDESCRIPTOR
     END-EXEC. 
     EXEC SQL FETCH EMPCURSOR 
         INTO SELECTDESCRIPTOR
     END-EXEC.

F Embedded SQL Statements and Precompiler Directives

Summary of Precompiler Directives and Embedded SQL Statements

Table F-1 Source/Type Column Meaning

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

About the Statement Descriptions

How to Read Syntax Diagrams

Statement Terminator

Required Keywords and Parameters

Optional Keywords and Parameters

Syntax Loops

Multi-part Diagrams

Oracle Names

ALLOCATE (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

ALLOCATE DESCRIPTOR (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

CALL (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

CLOSE (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

COMMIT (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keyword and Parameters

Usage Notes

Example

Related Topics

CONNECT (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Keyword and Parameters

Usage Notes

Example

Related Topics

CONTEXT ALLOCATE (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

CONTEXT FREE (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

CONTEXT USE (Oracle Embedded SQL Directive)

Purpose

Prerequisites

Syntax

F
Embedded SQL Statements and Precompiler Directives