Programmer's Guide to the Pro*Ada Precompiler Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Quick Reference to Embedded SQL


Rather than trying to memorize the syntax for SQL constructs you do not use every day, simply refer to this appendix. It focuses on the differences between embedded and interactive SQL, giving you the purpose of each embedded SQL statement, its syntax diagram, keyword and parameter descriptions, brief usage notes, and one or more programming examples. For detailed usage notes, see Oracle7 Server SQL Reference. This chapter includes:


How to Read Syntax Diagrams

Easy-to-understand syntax diagrams are used to illustrate embedded SQL syntax. They are line-and-arrow drawings that depict valid syntax. If you have never used them, do not worry. This section tells you all you need to know.

Once you understand the logical flow of a syntax diagram, it becomes a helpful guide. You can verify or construct any embedded SQL statement by tracing through its syntax diagram.

Syntax diagrams use lines and arrows to show how commands, parameters, and other language elements are sequenced to form statements. Trace each diagram from left to right, in the direction shown by the arrows. The following symbols will guide you:

Commands and other keywords appear in UPPER CASE. Parameters appear in lower case. Operators, delimiters, and terminators appear as usual. 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

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

Optional Keywords and Parameters

If keywords and parameters appear in a vertical list below the main path, they are optional. That is, you need not choose one of them. 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; 

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.

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:

According to the diagram, the following statement is valid:

EXEC SQL PREPARE sql_statement FROM :sql_string; 

Database Objects

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.


CLOSE

Purpose

The CLOSE statement disables a cursor, frees the resources acquired by OPENing the cursor, and releases parse locks.

Syntax

The following syntax diagram shows how to construct a CLOSE statement:

where cursor_name refers to a previously OPENed cursor.

Usage Notes

Attempting to CLOSE an unDECLAREd or unOPENed cursor generates an error.

You need not CLOSE a cursor to reOPEN it when MODE=ORACLE.

When MODE={ANSI13 | ORACLE}, issuing a COMMIT or ROLLBACK closes cursors referenced in a CURRENT OF clause. Other cursors are unaffected by COMMIT or ROLLBACK and if open, remain open. However, when MODE={ANSI | ANSI14}, issuing a COMMIT or ROLLBACK closes all explicit cursors.

The HOLD_CURSOR and RELEASE_CURSOR options alter the effect of CLOSE. To make sure that associated resources are freed when you CLOSE a cursor, you must specify RELEASE_CURSOR=YES. For more information, see "Using the Precompiler Options" [*].

Example

The following example illustrates the use of CLOSE:

EXEC SQL CLOSE emp_cursor; 


COMMIT

Purpose

The COMMIT statement ends the current transaction and makes changes to the database permanent. The RELEASE parameter frees all resources and logs off the database.

Syntax

The following syntax diagram shows how to construct a COMMIT statement:

where db_name identifies a non-default connection, WORK provides ANSI compatibility, COMMENT specifies a comment to be associated with the current transaction, RELEASE frees all resources and logs off the database, FORCE commits an in-doubt distributed transaction, trans_id identifies an in-doubt transaction, and integer is a system change number.

Usage Notes

The db_name is an undeclared identifier used by the precompiler, not a host or program variable. Always explicitly COMMIT or ROLLBACK the last transaction in your program, specifying the RELEASE parameter to disconnect from Oracle. If the program terminates abnormally, Oracle automatically rolls back changes.

When MODE={ANSI13 | ORACLE}, issuing a COMMIT closes cursors referenced in a CURRENT OF clause. Other cursors are unaffected by COMMIT and if open, remain open. However, when MODE={ANSI | ANSI14}, issuing a COMMIT closes all explicit cursors. COMMIT has no effect on host variables or the flow of control in a program.

You use FORCE to commit an in-doubt distributed transaction. The transaction must be identified by a quoted literal containing the transaction ID, which can be found in the data dictionary view DBA_2PC_PENDING. FORCE commits only the specified transaction and does not affect your current transaction.

Optionally, you can use integer to assign a system change number (SCN) to the transaction. If you omit integer, the current SCN is assigned to the transaction.

If ever a distributed transaction is in doubt, ORACLE stores the text specified by COMMENT in the data dictionary view DBA_2PC_PENDING along with the transaction ID. The text must be a quoted literal <= 50 characters in length.

Example

The following examples illustrate the use of COMMIT:

EXEC SQL COMMIT WORK RELEASE; 
... 
EXEC SQL COMMIT COMMENT 'In-doubt trans; notify Order Entry'; 
... 
EXEC SQL AT oracle2 COMMIT RELEASE; 
... 
EXEC SQL COMMIT FORCE '22.31.83'; 


CONNECT

Purpose

The CONNECT statement logs on to an Oracle database.

Syntax

The following syntax diagram shows how to construct a CONNECT statement:

where username and password make up a valid Oracle userid, username_password contains username/password, db_name identifies a non-default connection, and db_string is a SQL*Net string used to connect to a non-default database.

Usage Notes

The db_name is an undeclared identifier used by the precompiler, not a host or program variable. You must declare db_name in a DECLARE DATABASE statement before executing a CONNECT ... AT db_name statement.

Your program can have multiple CONNECT statements, but can only CONNECT once (implicitly specifying the default connection) without the AT clause.

You cannot use the AT clause without the USING clause. However, you can use the USING clause without the AT clause, in which case the database referenced in the USING clause becomes the default connection.

Examples

The following example illustrates the use of CONNECT:

EXEC SQL CONNECT :username IDENTIFIED BY :password; 

You can also use

EXEC SQL CONNECT :userid; 

where userid contains username/password. Or, provided it is a valid Oracle userid, you can automatically log on to Oracle with

OPS$username 

where username is your current operating system user or task name. You simply pass to Oracle a one-character string containing a slash, as follows:

EXEC SQL BEGIN DECLARE SECTION; 
    userid     CHARACTER(1); 
EXEC SQL END DECLARE SECTION; 
set userid = '/'; 
EXEC SQL CONNECT :userid; 

This automatically connects you as user OPS$username.


DECLARE CURSOR

Purpose

The DECLARE CURSOR statement defines a cursor by giving it a name and associating it with a specific query.

Syntax

The following syntax diagram shows how to construct a DECLARE CURSOR statement:

where cursor_name identifies a cursor, db_name identifies a non-default connection, subquery is a SELECT statement with no INTO clause, statement_name identifies a PREPAREd SQL statement, and block_name identifies a PREPAREd PL/SQL block.

Usage Notes

The db_name is an undeclared identifier used by the precompiler, not a host or program variable. The cursor_name, statement_name, and block_name are undeclared identifiers used by the precompiler, not host or program variables. The statement_name is used with dynamic SQL Methods 3 and 4. The block_name is used with dynamic SQL Method 4.

You must DECLARE a cursor before referencing it in other SQL statements. The scope of a cursor declaration is global within the current precompilation unit. So, every DECLARE CURSOR statement must be unique. You cannot DECLARE two cursors with the same name in one precompilation unit.

Cursor names cannot be hyphenated. They can be any length, but only the first 31 characters are significant. For ANSI compatibility, use cursor names no longer than 18 characters.

You can reference the cursor in an UPDATE or DELETE statement using CURRENT OF cursor provided the cursor is open and positioned on a row.

If a PL/SQL block contains an unknown number of input or output host variables, you must use Method 4 to process the PL/SQL string. To use Method 4, you set up one bind descriptor for all the input and output host variables.

Executing DESCRIBE BIND VARIABLES stores information about input and output host variables in the bind descriptor. The precompiler treats all PL/SQL host variables as input host variables, so executing DESCRIBE SELECT LIST has no effect. The use of bind descriptors with Method 4 is detailed[*], "Implementing Dynamic SQL Method 4."

Example

The following example illustrates the use of DECLARE CURSOR:

EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT ENAME, EMPNO, JOB, SAL FROM EMP 
    WHERE DEPTNO = :dept_number 
    FOR UPDATE OF SAL; 


DECLARE DATABASE

Purpose

The DECLARE DATABASE statement declares the name of a non-default database for use in the AT clause of SQL statements.

Syntax

The following syntax diagram shows how to construct a DECLARE DATABASE statement:

where db_name identifies a non-default database.

Usage Notes

The db_name is an undeclared identifier used by the precompiler, not a host or program variable.

You declare the non-default database so that other SQL statements can refer to that database in the AT clause.

Example

The following example illustrates the use of DECLARE DATABASE:

EXEC SQL DECLARE oracle3 DATABASE; 


DECLARE STATEMENT

Purpose

The DECLARE STATEMENT statement declares the name of a dynamic SQL statement that will be PREPAREd and/or EXECUTEd.

Syntax

The following syntax diagram shows how to construct a DECLARE STATEMENT statement:

where db_name identifies a non-default connection, and statement_name identifies a PREPAREd SQL statement.

Usage Notes

The db_name parameter is an undeclared identifier used by the precompiler, not a host or program variable. The statement_id parameter is a precompiler identifier that will be associated with a SQL statement or PL/SQL block by use of the embedded SQL PREPARE command.

You must use DECLARE STATEMENT with dynamic SQL Methods 2, 3, and 4 if you want to execute the SQL statement at a remote database. With Methods 3 and 4, DECLARE STATEMENT is also required if the DECLARE CURSOR statement physically (not logically) precedes the PREPARE statement. Like that of a cursor declaration, the scope of a statement declaration is global within a precompilation unit.

Example

The following examples illustrate the use of DECLARE STATEMENT. In the first example, Method 2 is used to execute a dynamic SQL statement at a remote database:

EXEC SQL AT oracle2 DECLARE sql_statement STATEMENT; 
EXEC SQL PREPARE sql_statement FROM :sql_string; 
EXEC SQL EXECUTE sql_statement; 

In the next example, DECLARE STATEMENT is required because DECLARE CURSOR physically precedes PREPARE:

EXEC SQL DECLARE sql_statement STATEMENT; 
  call prepare_sql_statement; 
EXEC SQL DECLARE emp_cursor CURSOR FOR sql_statement; 
... 
ROUTINE prepare_sql_statement 
BEGIN 
    EXEC SQL PREPARE sql_statement FROM :sql_string; 
END; 


DECLARE TABLE

Purpose

The DECLARE TABLE statement defines the structure of a table or view, including each column's datatype, optional default value, and optional NULL or NOT NULL specification.

Syntax

The following syntax diagram shows how to construct a DECLARE TABLE statement:

Usage Notes

You can use the optional keywords WITH DEFAULT to maintain compatibility with IBM's DB2.

If the database name in the AT clause of a SQL statement is a host variable, all database tables referenced by the SQL statement must be defined in DECLARE TABLE statements. Otherwise, the precompiler issues a warning.

Example

The following example illustrates the use of DECLARE TABLE:

EXEC SQL DECLARE PARTS TABLE 
    (PARTNO NUMBER NOT NULL, 
     BIN    NUMBER, 
     QTY    NUMBER); 


DELETE

Purpose

The DELETE statement removes unwanted rows from a table.

Syntax

The following syntax diagram shows how to construct a DELETE statement:

where db_name identifies a non-default connection, FOR :host_integer specifies the maximum number of host array elements processed, table_name identifies the table from which rows will be deleted, alias is another name assigned to the table, search condition is a boolean expression containing references to host variables or host arrays, and cursor_name refers to a cursor defined in a previous DECLARE CURSOR statement.

Usage Notes

The db_name is an undeclared identifier, not a host variable.

When MODE=ANSI14, array DELETEs are not allowed; that is, you can reference host arrays in a DELETE statement only when MODE={ANSI | ANSI13 | ORACLE}. If one of the host variables in the WHERE clause is an array, all must be arrays. The host arrays can have different dimensions, in which case the number of array elements processed is determined by comparing the dimension of the smallest host array in the SQL statement with the optional FOR-clause variable. The lesser value is used. If the host variables in the WHERE clause are arrays, the DELETE statement is executed once for each set of array elements.

The cumulative number of rows deleted is returned to the third element of SQLERRD in the SQLCA. The number does not include rows processed by a delete cascade.

If no rows meet the search_condition, none are deleted, and the "no data found" Oracle error code is returned to SQLCODE in the SQLCA.

If you omit the WHERE clause, all rows of the table are deleted and the fifth element of SQLWARN in the SQLCA is set.

The following restrictions apply to the CURRENT OF clause:

Examples

The following examples illustrate the use of DELETE:

EXEC SQL DELETE FROM EMP 
    WHERE DEPTNO = :dept_number AND JOB = :job_title; 
... 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT EMPNO, COMM FROM EMP 
        WHERE DEPTNO = :dept_number 
        FOR UPDATE OF COMM; 
EXEC SQL OPEN emp_cursor; 
EXEC SQL FETCH emp_cursor INTO :emp_number, :commission; 
EXEC SQL DELETE FROM EMP 
    WHERE CURRENT OF emp_cursor; 


DESCRIBE

Purpose

The DESCRIBE statement initializes a descriptor to hold descriptions of the select-list items or input host variables in a PREPAREd dynamic SQL statement.

Syntax

The following syntax diagram shows how to construct a DESCRIBE statement:

where BIND VARIABLES FOR specifies a list of input host variables, SELECT LIST FOR specifies a list of output host variables, statement_name identifies a PREPAREd SQL statement, block_name identifies a PREPAREd PL/SQL block, and descriptor_name identifies a descriptor.

Usage Notes

The statement_name, block_name, and descriptor_name are undeclared identifiers used by the precompiler, not host or program variables.

DESCRIBE is used with dynamic SQL Method 4. You must execute DESCRIBE before calling host functions that manipulate bind or select descriptors. You cannot use the same descriptor for input and output host variables simultaneously.

The number of variables found by DESCRIBE equals the total number of placeholders in the PREPAREd string, not the number of uniquely named placeholders.

Explicit use of the SELECT LIST FOR clause is optional.

Example

The following example illustrates the use of DESCRIBE:

EXEC SQL PREPARE sql_statement FROM :sql_string; 
EXEC SQL DECLARE emp_cursor FOR ... 
EXEC SQL DESCRIBE BIND VARIABLES FOR sql_statement 
    INTO bind_descriptor; 
EXEC SQL OPEN emp_cursor USING ... 
EXEC SQL DESCRIBE SELECT LIST FOR sql_statement 
    INTO select_descriptor; 


ENABLE THREADS

Purpose

The ENABLE THREADS statement initializes a process that supports multiple threads.

Syntax

The following syntax diagram shows how to construct an ENABLE THREADS statement:

Usage Notes

In order to use the ENABLE THREADS statement, you must be developing a precompiler application for and compiling it on a platform that supports tasking, and THREADS=YES must be specified on the command line.

The ENABLE THREADS command must be executed before any calls to subprograms that use tasking. This command does not require any host-variable argument.

For more information on this command, see the section "Tasking" [*].

Example

The following example illustrates the use of ENABLE THREADS:

procedure TASK_DRV is

begin
  EXEC SQL ENABLE THREADS;
  text_io.put_line("Enabled for tasking");
  MY_TASK_DRIVER.STARTEM;
end TASK_DRV; 


EXECUTE

Purpose

The EXECUTE statement executes a previously PREPAREd dynamic SQL statement or PL/SQL block.

Syntax

The following syntax diagram shows how to construct an EXECUTE statement:

where FOR :host_integer specifies the maximum number of host array elements processed, descriptor_name identifies a descriptor referenced in a previous DESCRIBE statement, and indicator stands for the following syntax:

The statement_id parameter is a precompiler identifier associated with the SQL statement or PL/SQL block to be executed. Use the embedded SQL PREPARE command to associate the precompiler identifier with the SQL statment or PL/SQL block.

Usage Notes

The statement_id, and descriptor_name are undeclared identifiers used by the precompiler, not host or program variables. The statement_id must not identify a SELECT statement.

The EXECUTE statement is used mainly with dynamic SQL Method 2 but can also be used for nonqueries with Method 4.

Every placeholder in the PREPAREd string must correspond to a host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd string, each appearance must correspond to a host variable in the USING clause.

The names of the placeholders need not match the names of the host variables. However, the order of the placeholders in the PREPAREd string must match the order of the host variables in the USING clause. If one of the host variables in the USING clause is an array, all must be arrays.

If a PL/SQL block contains a known number of input and output host variables, you can use Method 2 to PREPARE and EXECUTE the PL/SQL string. The precompiler treats all PL/SQL host variables as input host variables whether they serve as input or output host variables (or both) inside the PL/SQL block. So, you must put all host variables in the USING clause.

Example

The following example illustrates the use of EXECUTE:

EXEC SQL PREPARE sql_statement FROM :sql_string; 
EXEC SQL EXECUTE sql_statement USING :emp_number; 


EXECUTE IMMEDIATE

Purpose

The EXECUTE IMMEDIATE statement prepares and executes dynamic SQL statements (except queries) and PL/SQL blocks, provided they contain no host variables.

Syntax

The following syntax diagram shows how to construct an EXECUTE IMMEDIATE statement:

where db_name identifies a non-default connection, host_string is a host variable that contains the text of a SQL statement or PL/SQL block, and string_literal is a string literal that contains the text of a SQL statement or PL/SQL block.

Usage Notes

The db_name is an undeclared identifier, not a host variable.

EXECUTE IMMEDIATE is used with dynamic SQL Method 1. When an EXECUTE IMMEDIATE statement is executed, the specified SQL statement is parsed and checked for errors. If the statement is invalid, it is not executed, and the appropriate Oracle error code is returned to SQLCODE in the SQLCA.

EXECUTE IMMEDIATE is most useful for SQL statements executed only once. For statements executed repeatedly, use dynamic SQL Method 2 because a PREPAREd statement is not reparsed every time it is reexecuted.

If a PL/SQL block contains no host variables, you can use Method 1 to EXECUTE the PL/SQL string.

When you store a SQL statement in host_string or string_literal, omit the keywords EXEC SQL and the statement terminator. Likewise, when you store a PL/SQL block in host_string or string_literal, omit the keywords EXEC SQL EXECUTE, the keyword END-EXEC, and the statement terminator.

Examples

The following examples illustrate the use of EXECUTE IMMEDIATE:

set sql_string = 'DELETE FROM EMP WHERE EMPNO = 7839'; 
EXEC SQL EXECUTE IMMEDIATE :sql_string; 


EXECUTE plsql_block

Purpose

The EXECUTE plsql_block statement executes an embedded PL/SQL block.

Syntax

The following syntax diagram shows how to construct an EXECUTE plsql_block statement:

where db_name identifies a non-default connection, variable_declaration declares PL/SQL constants and variables, cursor_declaration declares PL/SQL cursors, exception_declaration declares PL/SQL exceptions (error conditions), seq_of_statements is a sequence of PL/SQL statements, and exception_handler stands for the following syntax:

where exception_name identifies a PL/SQL predefined or user-defined exception.

Usage Notes

The db_name is an undeclared identifier, not a host variable.

DECLARE marks the start of local declarations. Only the current block and its sub-blocks can reference locally declared objects.

The EXCEPTION_INIT pragma (compiler directive) can also be used in the declarative part of a block. For details, see the PL/SQL User's Guide and Reference.

A cursor declaration names a cursor and associates it with a query. The cursor name is not a PL/SQL variable. You cannot assign values to a cursor name or use it as a value in a PL/SQL expression. However, cursors and variables follow the same scoping rules.

Exceptions are unlike variables and constants in that they cannot be passed as arguments to functions or procedures.

An exception handler executes a sequence of statements in response to a raised exception. Some exceptions are predefined and need not be declared. For a list of these exceptions, see the PL/SQL User's Guide and Reference.

The scope rules for exception names and variables are the same. So, an exception declared in a sub-block overrides an exception declared with the same name in an enclosing block, and an enclosing block cannot reference exceptions declared in a sub-block.

Embedding PL/SQL Blocks

The Oracle Precompilers treat a PL/SQL block like a single embedded SQL statement. So, you can place a PL/SQL block anywhere in a host program that you can place a SQL statement.

Inside a PL/SQL block, host variables are treated as global to the entire block and can be used anywhere a PL/SQL variable is allowed. Like host variables in a SQL statement, host variables in a PL/SQL block must be prefixed with a colon.

When entering a PL/SQL block, Oracle automatically checks the length fields of VARCHAR host variables. So, you must set the length fields before the block is entered. For input host variables, set the length field to the actual length of the value stored in the string field. For output host variables, set the length field to the maximum length allowed by the string field.

In a PL/SQL block, you cannot refer to an indicator variable by itself. It must be appended to its associated host variable. Also, if you refer to a host variable with its indicator variable, you must always refer to it that way in the same block.

When entering a block, if an indicator variable has a value of -1, PL/SQL automatically assigns a null to the host variable. When exiting the block, if a host variable is null, PL/SQL automatically assigns a value of -1 to the indicator variable.

PL/SQL does not raise an exception when a truncated string value is assigned to a host variable. However, if you use an indicator variable, PL/SQL sets it to the original length of the string.

Example

The following example illustrates the use of EXECUTE plsql_block:

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; 


FETCH

Purpose

The FETCH statement retrieves a row from the database, assigns column values to host variables, and advances the cursor to the next qualified row.

Syntax

The following syntax diagram shows how to construct a FETCH statement:

where FOR :host_integer specifies the maximum number of host array elements processed, cursor_name refers to a cursor defined in a previous DECLARE CURSOR statement, INTO :host_variable identifies one or more output host variables (each optionally associated with an indicator variable), descriptor_name identifies a descriptor referenced in a previous DESCRIBE statement, and indicator stands for the following syntax:

Usage Notes

The cursor_name and descriptor_name are undeclared identifiers used by the precompiler, not host or program variables.

The cursor must be DECLAREd and OPENed before a FETCH is executed.

The associated SELECT statement cannot have an INTO clause. Rather, the INTO clause and list of output host variables are part of the FETCH statement.

The FETCH statement reads the rows of the active set and specifies the output host variables. The number of rows retrieved is determined by the dimensions of the output host variables in the FETCH statement and the value of the optional FOR variable. The cursor is positioned on the next row in the table (if any), and values are assigned to host variables in the INTO clause of the FETCH statement or the USING clause of the OPEN statement.

If you want to change the active set, you must assign new values to the input host variables in the query associated with the cursor, then reOPEN the cursor. When MODE={ANSI | ANSI14 | ANSI13}, you must CLOSE the cursor before reOPENing it.

You can FETCH from the same cursor using different sets of output host variables. However, corresponding host variables in the INTO clause of each FETCH statement must have the same datatype.

If the cursor is currently positioned after the last row of the target table, the "no data found" Oracle error code is returned to SQLCODE in the SQLCA.

When MODE=ANSI14, array FETCHes are not allowed; that is, you can reference host arrays in a FETCH statement only when MODE={ANSI | ANSI13 | ORACLE}. If one of the host variables in the INTO clause is an array, all must be arrays.

Example

The following example illustrates the use of FETCH:

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 ... 
LOOP 
    EXEC SQL FETCH emp_cursor INTO :job_title1, :salary1; 
    EXEC SQL FETCH emp_cursor INTO :job_title2, :salary2; 
    ... 
ENDLOOP; 
... 


FOR

Purpose

The FOR clause limits the number of repetitions for array processing in an UPDATE, DELETE, INSERT, OPEN, FETCH, or EXECUTE statement.

Syntax

The following syntax diagram shows how to construct a FOR clause:

where FOR :host_integer specifies the maximum number of host array elements processed.

Usage Notes

You cannot use the FOR clause in a SELECT statement or with the CURRENT OF clause. For an explanation, see "Using the FOR Clause" [*].

The host arrays can have different dimensions, in which case the number of array elements processed is determined by comparing the dimension of the smallest host array in the SQL statement with the FOR-clause variable. The lesser value is used.

If the value of the FOR-clause variable is less than or equal to zero, no rows are processed.

The FOR clause is optional. If you omit the FOR clause, the dimension of the smallest array in the SQL statement determines the number of array elements processed.

Example

The following example illustrates the use of the FOR clause:

set limit = 10; 
EXEC SQL FOR :limit DELETE FROM EMP 
    WHERE ENAME = :ename_array AND JOB = :job_array; 


INSERT

Purpose

The INSERT statement adds new rows to a table or view.

Syntax

The following syntax diagram shows how to construct an INSERT statement:

where db_name identifies a non-default connection, FOR :host_integer specifies the maximum number of host array elements processed, INTO table_name identifies the table to which rows will be added, column_name identifies a column for which a value is provided, VALUES introduces a row of values to be inserted, subquery is a SELECT statement with no INTO clause, and value stands for the following syntax:

Usage Notes

The db_name is an undeclared identifier, not a host variable.

Each of the specified columns must belong to the table named in the INTO clause. The number of values in the VALUES clause (or number of items in the subquery select list) must equal the number of names in the column list. However, you can omit the column list if the VALUES clause contains a value for each column in the table.

When MODE=ANSI14, array INSERTs are not allowed; that is, you can reference host arrays in a INSERT statement only when MODE={ANSI | ANSI13 | ORACLE}. If one of the host variables in the VALUES or WHERE clause is an array, all must be arrays. The host arrays can have different dimensions, in which case the number of array elements processed is determined by comparing the dimension of the smallest host array in the SQL statement with the optional FOR-clause variable. The lesser value is used. If the host variables in the WHERE clause are arrays, the INSERT statement is executed once for each set of array elements.

Examples

The following examples illustrate the use of INSERT:

EXEC SQL INSERT INTO EMP (ENAME, EMPNO, DEPTNO) 
    VALUES (:emp_name, :emp_number, :dept_number); 
... 
EXEC SQL INSERT INTO EMP2 (ENAME, EMPNO, DEPTNO) 
    SELECT ENAME, EMPNO, DEPTNO FROM EMP 
        WHERE JOB = :job_title; 


OPEN

The OPEN statement opens a previously DECLAREd cursor, evaluates the query associated with the cursor, and substitutes in the WHERE clause of the query any host variable names supplied by the USING clause.

Purpose

The OPEN statement opens a previously DECLAREd cursor, evaluates the query associated with cursor, and substitutes in the WHERE clause of the query any host variable names supplies by the USING clause.

Syntax

The following syntax diagram shows how to construct an OPEN statement:

where FOR :host_integer specifies the maximum number of host array elements processed, cursor_name refers to a cursor defined in a previous DECLARE CURSOR statement, USING specifies a descriptor or a list of host variables substituted for placeholders in a PREPAREd string, descriptor_name identifies a descriptor referenced in a previous DESCRIBE statement, and indicator stands for the following syntax:

Usage Notes

OPEN positions the cursor just before the first row of the active set. No rows are actually retrieved at this point; that is done by the FETCH statement.

Once you OPEN a cursor, its input host variables are not reexamined unless you reOPEN it. To change the active set, you must reOPEN the cursor with new values for the input host variables.

You need not CLOSE a cursor before reOPENing it when MODE=ORACLE.

The USING clause is used with dynamic SQL Methods 3 and 4. Typically, variable names in the USING clause differ from placeholder names in the PREPAREd string. Host variables are substituted for placeholders based on position.

Example

The following example illustrates the use of OPEN:

EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT ENAME, EMPNO, JOB, SAL FROM EMP 
    WHERE DEPTNO = :dept_number; 
EXEC SQL OPEN emp_cursor; 


PREPARE

Purpose

The PREPARE statement parses the text of a dynamic SQL statement stored in a string host variable or string literal.

Syntax

The following syntax diagram shows how to construct a PREPARE statement:

where statement_id identifies a PREPAREd SQL statement, host_string is a host variable that contains the text of a SQL statement or PL/SQL block, and string_literal is a string literal that contains the text of a SQL statement or PL/SQL block.

Usage Notes

The statement_id parameter is an undeclared identifier used by the precompiler, not a host or program variable. If this identifier has previously been assigned to another PREPAREd statement, that assignment is superseded.

The PREPARE statement is used with dynamic SQL Methods 2, 3, and 4. When you store a SQL statement in host_string or string_literal, omit the keywords EXEC SQL and the statement terminator. Likewise, when you store a PL/SQL block in host_string or string_literal, omit the keywords EXEC SQL EXECUTE, the keyword END-EXEC, and the statement terminator.

The variables referenced in host_string are just placeholders. The actual host variable names are assigned in the OPEN USING clause or in the FETCH INTO clause. The substitution is based on position and has nothing to do with the placeholder names used. The number of variables in the USING clause and the number of placeholders in the PREPAREd statement must be the same.

A SQL statement is parsed only once, but can be executed many times. SQL data definition statements are executed when they are PREPAREd.

Example

The following example illustrates the use of PREPARE:

EXEC SQL PREPARE sql_statement FROM :sql_string; 
EXEC SQL EXECUTE sql_statement; 


ROLLBACK

Purpose

The ROLLBACK statement ends the current transaction, undoes changes to the database, and releases all locks except parse locks held by cached cursors. The RELEASE parameter frees all resources and logs off the database.

Syntax

The following syntax diagram shows how to construct a ROLLBACK statement:

where db_name identifies a non-default connection, WORK provides ANSI compatibility, savepoint_name identifies a marked savepoint, TO rolls back the current transaction to a previously marked savepoint, RELEASE frees all resources and logs off the database, FORCE rolls back an in-doubt distributed transaction, and trans_id identifies an in-doubt transaction.

Usage Notes

The db_name is an undeclared identifier, not a host variable.

Always explicitly COMMIT or ROLLBACK the last transaction in your program, specifying the RELEASE parameter to disconnect from Oracle. If the program terminates abnormally, Oracle automatically rolls back changes.

When MODE={ANSI13 | ORACLE}, issuing a ROLLBACK closes cursors referenced in a CURRENT OF clause. Other cursors are unaffected by ROLLBACK and if open, remain open. However, when MODE={ANSI | ANSI14}, issuing a ROLLBACK closes all explicit cursors. ROLLBACK has no effect on host variables or the flow of control in a program.

You use FORCE to roll back an in-doubt distributed transaction. The transaction must be identified by a quoted literal containing the transaction ID, which can be found in the data dictionary view DBA_2PC_PENDING. FORCE rolls back only the specified transaction and does not affect your current transaction. You cannot roll back in-doubt transactions to a savepoint.

Example

The following examples illustrate the use of ROLLBACK:

EXEC SQL ROLLBACK WORK RELEASE; 
EXEC SQL ROLLBACK TO SAVEPOINT begin_insert; 
EXEC SQL ROLLBACK FORCE '22.31.83'; 


SAVEPOINT

Purpose

The SAVEPOINT statement names and marks the current point in the processing of a transaction. It is used with the ROLLBACK statement to undo part of a transaction.

Syntax

The following syntax diagram shows how to construct a SAVEPOINT statement:

where db_name identifies a non-default connection, and savepoint_name identifies a marked savepoint.

Usage Notes

The db_name is an undeclared identifier, not a host variable. The savepoint_name is an undeclared identifier used by the precompiler, not a host or program variable.

To undo part of a transaction, you use savepoints with the ROLLBACK statement and its TO SAVEPOINT clause. Savepoints let you divide long, complex transactions, giving you more control over them.

Rolling back to a savepoint erases any savepoints marked after that savepoint. The savepoint to which you roll back, however, is not erased. For example, if you mark five savepoints, then roll back to the third, only the fourth and fifth are erased. If you give two savepoints the same name, the earlier savepoint is erased. A COMMIT or ROLLBACK statement erases all savepoints.

You can use the FORCE clause in a ROLLBACK statement to roll back in-doubt distributed transactions. However, you cannot roll back in-doubt transactions to a savepoint.

Example

The following example illustrates the use of SAVEPOINT:

EXEC SQL SAVEPOINT begin_update; 
EXEC SQL UPDATE EMP SET ... 


SELECT

Purpose

The SELECT statement retrieves rows from one or more tables and assigns returned column values to output host variables.

Syntax

The following syntax diagram shows how to construct a SELECT statement:

where db_name identifies a non-default connection, table_name identifies the table from which rows will be selected, column_name identifies a column whose value is selected, search condition is a boolean expression containing references to host variables or host arrays, subquery is a SELECT statement with no INTO clause, and indicator stands for the following syntax:

For descriptions of the other keywords and parameters, refer to the Oracle7 Server SQL Language Reference Manual.

Usage Notes

The db_name is an undeclared identifier, not a host variable.

When MODE=ANSI14, array SELECTs are not allowed; that is, you can reference host arrays in a SELECT statement only when MODE={ANSI | ANSI13 | ORACLE}. If one of the host variables in the INTO clause is an array, all must be arrays. The host arrays can have different dimensions, in which case the number of array elements processed is determined by comparing the dimension of the smallest host array in the SQL statement with the optional FOR-clause variable. The lesser value is used. Using host arrays in the WHERE clause is not allowed.

The cumulative number of rows selected is returned to the third element of SQLERRD in the SQLCA. However, if no rows meet the search_condition, none are selected and the "no data found" Oracle error code is returned to SQLCODE in the SQLCA.

The FOR clause is not allowed with SELECT. Instead, DECLARE a cursor and use the FOR clause in a FETCH statement, as follows:

EXEC SQL FOR :limit FETCH emp_cursor INTO ... 

Example

The following example illustrates the use of SELECT:

EXEC SQL SELECT ENAME, SAL + 100, JOB 
    INTO :emp_name, :salary, :job_title 
    FROM EMP WHERE EMPNO = :emp_number; 


UPDATE

Purpose

The UPDATE statement changes the values of specified columns in a table or view.

Syntax

The following syntax diagram shows how to construct an UPDATE statement:

where db_name identifies a non-default connection, FOR :host_integer specifies the maximum number of host array elements processed, table_name identifies the table that will be updated, alias is another name assigned to the table, column_name identifies a column for which a value is provided, subquery is a SELECT statement with no INTO clause, search condition is a boolean expression containing references to host variables or host arrays, CURRENT OF refers to the latest row processed by the FETCH statement, cursor_name refers to a cursor defined in a previous DECLARE CURSOR statement, and value stands for the following syntax:

Usage Notes

The db_name is an undeclared identifier, not a host variable.

When MODE=ANSI14, array UPDATEs are not allowed; that is, you can reference host arrays in a UPDATE statement only when MODE={ANSI | ANSI13 | ORACLE}. If one of the host variables in the SET or WHERE clause is an array, all must be arrays. The host arrays can have different dimensions, in which case the number of array elements processed is determined by comparing the dimension of the smallest host array in the SQL statement with the optional FOR-clause variable. The lesser value is used. If the host variables in the WHERE clause are arrays, the UPDATE statement is executed once for each set of array elements.

The cumulative number of rows updated is returned to the third element of SQLERRD in the SQLCA. The number does not include rows processed by an update cascade. If no rows meet the search_condition, none are updated, and the "no date found" Oracle error code is returned to SQLCODE in the SQLCA.

If you omit the WHERE clause, the fifth element of SQLWARN in the SQLCA is set.

The following restrictions apply to the CURRENT OF clause:

Examples

The following examples illustrate the use of UPDATE:

EXEC SQL UPDATE EMP 
    SET SAL = :salary, JOB = :job_title 
    WHERE EMPNO = :emp_number; 
... 
EXEC SQL UPDATE EMP 
    SET SAL = (SELECT AVG(SAL)*1.1 FROM EMP WHERE DEPTNO = 20) 
    WHERE EMPNO = :emp_number; 


VAR

Purpose

The VAR statement assigns a specific Oracle external datatype to an individual host variable, overriding the default datatype assignment. This is called host variable equivalencing.

Syntax

The following syntax diagram shows how to construct a VAR statement:

where host_variable is an input or output host variable (or host array) declared earlier in the Declare Section, type_name is the name of a valid external datatype such as STRING or RAW, length is an integer literal specifying a valid length in bytes, and precision and scale are integer literals specifying a precision and scale allowed by the equivalenced external datatype.

Usage Notes

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

When type_name is DECIMAL or DISPLAY, you must specify precision and scale instead of length. When type_name is NUMBER, VARNUM, ROWID, or DATE, you cannot specify length because it is predefined. For other Oracle external datatypes, length is optional. It defaults to the length of host_variable. The value of length must be large enough to accommodate the external datatype.

When specifying length, if type_name is VARCHAR or VARRAW, use the maximum length of the data field only. The precompiler accounts for the length field.

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

The precision and scale parameters are for COBOL and PL/I packed decimals or COBOL "USAGE IS DISPLAY" numbers. Specify precision and scale only when type_name is DECIMAL or DISPLAY.

Examples

The following examples illustrate the use of VAR:

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


WHENEVER

Purpose

The WHENEVER statement specifies the action to be taken when an error or warning condition results from executing an embedded SQL statement.

Syntax

The following syntax diagram shows how to construct a WHENEVER statement:

where routine_call is a host language call to an error handling routine and label_name labels the statement to which WHENEVER GOTO branches.

Usage Notes

If a SQL statement fails, you can have your program transfer control to an error handling routine. When the end of the routine is reached, control transfers to the statement that follows the failed SQL statement.

A routine is any functional subprogram that can be invoked. The usual rules for entering and exiting a routine apply. However, passing parameters to the routine is not allowed. Furthermore, the routine cannot return a value.

The scope of a WHENEVER statement is positional, not logical. That is, it tests all executable SQL statements that physically follow it in the source file, not in the flow of program logic. A WHENEVER statement stays in effect until superseded by another WHENEVER statement checking for the same condition.

If a WHENEVER SQLERROR GOTO statement branches to an error handling routine that includes an executable SQL statement, be sure to code WHENEVER SQLERROR CONTINUE before the SQL statement.

That way, your program will not enter an infinite loop if the SQL statement fails with an error.

Declaring the SQLCA is optional when MODE={ANSI | ANSI14} but you cannot use the WHENEVER SQLWARNING statement without the SQLCA. So, if you want to use the WHENEVER SQLWARNING statement, you must declare the SQLCA.

Examples

The following examples illustrate the use of WHENEVER:

EXEC SQL WHENEVER NOT FOUND CONTINUE; 
... 
EXEC SQL WHENEVER SQLERROR GOTO sqlerror; 
... 
sqlerror: 
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL ROLLBACK RELEASE; 
... 
procedure sql_error is
begin
  put_line("Data not found");
end sql_error;
... 
EXEC SQL WHENEVER NOT FOUND DO sql_error; 
... 


WHERE

Purpose

The optional WHERE clause specifies a condition under which rows are SELECTed, DELETEd, or UPDATEd.

Syntax

The following syntax diagram shows how to construct a WHERE clause:

where search condition is a boolean expression containing references to host variables or host arrays, CURRENT OF refers to the latest row processed by a FETCH statement, and cursor_name refers to a cursor defined in a previous DECLARE CURSOR statement:

Usage Notes

If you omit the WHERE clause, all rows in the table are processed. If you omit the WHERE clause in a DELETE or UPDATE statement, the fifth element of SQLWARN in the SQLCA is set.

If one of the host variables used in the WHERE clause is an array, all must be arrays. The FOR-clause variable specifies the number of array elements to be processed. Make sure the number is not larger than the smallest array dimension. Also, the number must be positive. If it is negative or zero, no rows are processed and Oracle issues an error message.

The following restrictions apply to the CURRENT OF clause:

Example

The following example illustrates the use of WHERE:

EXEC SQL INSERT INTO NEW_EMP (ENAME, EMPNO, SAL) 
    SELECT ENAME, EMPNO, SAL FROM EMP 
    WHERE DEPTNO = :dept_number; 




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index