In the following example, you declare four host variables for use later in your program.

EXEC SQL BEGIN DECLARE SECTION; 
 emp_number INTEGER; 
 emp_name CHARACTER(10); 
 salary REAL; 
 commission REAL; 
EXEC SQL END DECLARE SECTION;

For more information about declaring host variables, see "Declaring and Referencing Host Variables".

SQL recognizes the pseudocolumns in Table 3-2, which return specific data items:

Pseudocolumns are not actual columns in a table. However, pseudocolumns are treated like columns, so their values must be SELECTed from a table. Sometimes it is convenient to select pseudocolumn values from a dummy table.

In addition, SQL recognizes the parameterless functions in Table 3-3, which also return specific data items.

You can refer to SQL pseudocolumns and functions in SELECT, INSERT, UPDATE, and DELETE statements. In the following example, you use SYSDATE to compute the number of months since an employee was hired:

EXEC SQL SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE)
 INTO :months_of_service
 FROM EMP
 WHERE EMPNO = :emp_number;

Brief descriptions of the SQL pseudocolumns and functions follow. For details, see the Oracle Database SQL Language Reference.

CURRVAL returns the current number in a specified sequence. Before you can reference CURRVAL, you must use NEXTVAL to generate a sequence number.

LEVEL returns the level number of a node in a tree structure. The root is level 1, children of the root are level 2, grandchildren are level 3, and so on.

LEVEL is used in the SELECT CONNECT BY statement to incorporate some or all the rows of a table into a tree structure. In an ORDER BY or GROUP BY clause, LEVEL segregates the data at each level in the tree.

You specify the direction in which the query walks the tree (down from the root or up from the branches) with the PRIOR operator. In the START WITH clause, you specify a condition that identifies the root of the tree.

NEXTVAL returns the next number in a specified sequence. After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. In the following example, you use the sequence named partno to assign part numbers:

EXEC SQL INSERT INTO PARTS
 VALUES (partno.NEXTVAL, :description, :quantity, :price);

If a transaction generates a sequence number, the sequence is incremented when you commit or rollback the transaction. A reference to NEXTVAL stores the current sequence number in CURRVAL.

ROWID returns a row address in hexadecimal.

ROWNUM returns a number indicating the sequence in which a row was selected from a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the selected rows before the sort is done.

You can use ROWNUM to limit the number of rows returned by a SELECT statement. Also, you can use ROWNUM in an UPDATE statement to assign unique values to each row in a table. Using ROWNUM in the WHERE clause does not stop the processing of a SELECT statement; it just limits the number of rows retrieved. The only meaningful use of ROWNUM in a WHERE clause is

... WHERE ROWNUM < constant;

because the value of ROWNUM increases only when a row is retrieved. The following search condition can never be met because the first four rows are not retrieved:

... WHERE ROWNUM = 5;

SYSDATE returns the current date and time.

UID returns the unique ID number assigned to an Oracle user.

USER returns the username of the current Oracle user.

In the following example, you declare three host variables, then use a SELECT statement to search the database for an employee number matching the value of host variable emp_number. When a matching row is found, Oracle sets output host variables dept_number and emp_name to the values of columns DEPTNO and ENAME in that row.

-- declare host variables
EXEC SQL BEGIN DECLARE SECTION;
 emp_number INTEGER;
 emp_name CHARACTER(10);
 dept_number INTEGER;
EXEC SQL END DECLARE SECTION;
...
display 'Employee number? ';
read emp_number;
EXEC SQL SELECT DEPTNO, ENAME INTO :dept_number, :emp_name
 FROM EMP
 WHERE EMPNO = :emp_number;

For more information about using host variables, see "About Using Host Variables".

The following guidelines apply to declaring and referencing host variables. A host variable must be

• declared explicitly in the Declare Section

• prefixed with a colon (:) in SQL statements and PL/SQL blocks

• of a datatype supported by the host language

• of a datatype compatible with that of its source or target database column

A host variable must not be

• subscripted

• prefixed with a colon in host language statements

• used to identify a column, table, or other Oracle object

• used in data definition statements such as ALTER and CREATE

• an Oracle reserved word (refer to Oracle Reserved Words_ Keywords_ and Namespaces)

A host variable can be

• used anywhere an expression can be used in a SQL statement

• associated with an indicator variable

Typically, you use indicator variables to assign nulls to input host variables and detect nulls or truncated values in output host variables. In the example, you declare three host variables and one indicator variable, then use a SELECT statement to search the database for an employee number matching the value of host variable emp_number. When a matching row is found, Oracle sets output host variables salary and commission to the values of columns SAL and COMM in that row and stores a return code in indicator variable ind_comm. The next statement uses ind_comm to select a course of action.

EXEC SQL BEGIN DECLARE SECTION;
 emp_number INTEGER;
 salary REAL;
 commission REAL;
 ind_comm SMALLINT; -- indicator variable
EXEC SQL END DECLARE SECTION; 
 pay REAL; -- not used in a SQL statement
display 'Employee number? ';
read emp_number;
EXEC SQL SELECT SAL, COMM
 INTO :salary, :commission:ind_comm
 FROM EMP
 WHERE EMPNO = :emp_number;
IF ind_comm = -1 THEN -- commission is null
 set pay = salary;
ELSE
 set pay = salary + commission;
ENDIF;

For more information, see "About Using Indicator Variables".

The following guidelines apply to declaring and referencing indicator variables. An indicator variable must be

• declared explicitly in the Declare Section as a 2-byte integer

• prefixed with a colon (:) in SQL statements

• appended to its host variable in SQL statements and PL/SQL blocks (unless preceded by the keyword INDICATOR)

An indicator variable must not be

• prefixed with a colon in host language statements

• appended to its host variable in host language statements

• an Oracle reserved word

Datatype equivalencing is useful in several ways. For example, suppose you want to use a null-terminated host string in a COBOL program. You can declare a PIC X host variable, then equivalence it to the external datatype STRING, which is always null-terminated.

You can use datatype equivalencing when you want Oracle to store but not interpret data. For example, if you want to store an integer host array in a LONG RAW database column, you can equivalence the host array to the external datatype LONG RAW.

Also, you can use datatype equivalencing to override default datatype conversions. Unless Globalization Support parameters in the Oracle initialization file specify otherwise, if you select a DATE column value into a character host variable, Oracle returns a 9-byte string formatted as follows:

DD-MON-YY

However, if you equivalence the character host variable to the DATE external datatype, Oracle returns a 7-byte value in the internal format.

By default, the Oracle Precompilers assign a specific external datatype to every host variable. (These default assignments are tabulated in your supplement to this Guide.) You can override the default assignments by equivalencing host variables to Oracle external datatypes in the Declare Section. This is called host variable equivalencing.

The syntax you use is:

EXEC SQL VAR <host_variable>
 IS <ext_type_name> [({<length> | <precision>,<scale>})];

where, host_variable is an input or output host variable (or host array) declared earlier in the Declare Section. The VARCHAR and VARRAW external datatypes have a 2-byte length field followed by an n-byte data field, where n lies in the range 1 .. 65533. So, if type_name is VARCHAR or VARRAW, host_variable must be at least 3 bytes long.

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

ext_type_name is the name of a valid external datatype such as RAW or STRING.

length is an integer literal specifying a valid length in bytes. The value of length must be large enough to accommodate the external datatype.

When type_name is DECIMAL or DISPLAY, you must specify precision and scale instead of length. When type_name is VARNUM, ROWID, or DATE, you cannot specify length because it is predefined. For other external datatypes, length is optional. It defaults to the length of host_variable.

When specifying length, if type_name is VARCHAR, VARRAW, LONG VARCHAR, or LONG VARRAW, use the maximum length of the data field. The precompiler accounts for the length field. If type_name is LONG VARCHAR or LONG VARRAW and the data field exceeds 65533 bytes, put "-1" in the length field.

precision and scale are integer literals that represent, respectively, the number of significant digits and the point at which rounding will occur. For example, a scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46); a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000).

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. However, if the scale of a column value exceeds 99, you cannot select or fetch the value into host_variable.

Specify precision and scale only when type_name is DECIMAL or DISPLAY.

Table 3-7 shows which parameters to use with each external datatype.

Suppose you want to select employee names from the EMP table, then pass them to a routine that expects null-terminated strings. You need not explicitly null-terminate the names. Simply equivalence a host variable to the STRING external datatype, as follows:

EXEC SQL BEGIN DECLARE SECTION;
 ...
 emp_name CHARACTER(11);
 EXEC SQL VAR emp_name IS STRING (11);
EXEC SQL END DECLARE SECTION;

The width of the ENAME column is 10 characters, so you allocate the new emp_name 11 characters to accommodate the null terminator. (Here, length is optional because it defaults to the length of the host variable.) When you select a value from the ENAME column into emp_name, Oracle null-terminates the value for you.

You can use the datatype specifier CHARF in VAR and TYPE statements to equivalence host-language datatypes to the fixed-length ANSI datatype CHAR--regardless of the DBMS setting.

When MODE=ANSI, specifying the datatype CHAR in a TYPE statement equivalences the host-language datatype to the fixed-length ANSI datatype CHAR (Oracle external datatype code 96). However, when MODE=ORACLE, the host-language datatype is equivalenced to the variable-length datatype VARCHAR2 (code 1), which might not be what you want.

However, you can always equivalence host-language datatypes to the fixed-length ANSI datatype CHAR. Simply specify the datatype CHARF in the VAR statement. If you use CHARF, the host-language datatype is equivalenced to the fixed-length ANSI datatype CHAR even when MODE=ORACLE.

To input VARNUM or DATE values, you must use the Oracle internal format. Keep in mind that Oracle uses the internal format to output VARNUM and DATE values.

After selecting a column value into a VARNUM host variable, you can check the first byte to get the length of the value. Table 3-8 gives some examples of returned VARNUM values.

Convert DATE values to a character format such as "DD-MON-YY" because, normally, that is how your program outputs (displays for example) or inputs them.

If no Oracle external datatype suits your needs exactly, use a VARCHAR2-based or RAW-based external datatype.

A multibyte Globalization Support character string in an embedded SQL statement consists of a character literal that identifies the string as a multibyte string, followed by the string enclosed in single quotes.

For example, an embedded SQL statement like

EXEC SQL
 SELECT empno INTO :emp_num FROM emp
 WHERE ename=N'Kuroda'
 END-EXEC.

contains a multibyte character string, since the N character literal preceding the string "Kuroda" identifies it as a multibyte string.

Because dynamic SQL statements are not processed at precompile time, and Oracle does not process multibyte Globalization Support strings itself, you cannot embed multibyte Globalization Support strings in dynamic SQL statements.

Columns storing multibyte Globalization Support data cannot be used in embedded data definition language (DDL) statements. This restriction cannot be enforced when precompiling, so the use of extended column types, such as NCHAR, within embedded DDL statements results in an execution error rather than a precompile error.

The Pro*COBOL Precompiler uses the ANSI standard PIC N clause to declare host variables for multibyte character data. Variables declared using the PIC N clause are recognized as string variables of double-byte characters.

• Globalization Support_LOCAL

• VARCHAR

For more information about these options, see Running the Oracle Precompilers.

Tables Disallowed.

Host variables declared using the PIC N datatype must not be tables.

No Odd Byte Widths. Oracle CHAR columns should not be used to store multibyte Globalization Support characters. A run-time error is generated if data with an odd number of bytes is fetched from a single-byte column into a multibyte Globalization Support (PIC N) host variable.

No Host Variable Equivalencing. multibyte Globalization Support character variables cannot be equivalenced using an EXEC SQL VAR statement.

No Dynamic SQL. Dynamic SQL is not available for Globalization Support multibyte character string host variables in Pro*COBOL.

When a Pro*COBOL character variable is defined as a multibyte Globalization Support variable, the following blank padding and blank stripping rules apply, depending on the external datatype of the variable. See External Datatypes.

CHARF. This is the default character type when a multibyte character string is defined. Input data is stripped of any trailing double-byte spaces. However, if a string consists only of double-byte spaces, a single double-byte space is left in the buffer to act as a sentinel.

Output host variables are blank padded with double-byte spaces.

VARCHAR. On input, host variables are not stripped of trailing double-byte spaces. The length component is assumed to be the length of the data in characters, not bytes.

On output, the host variable is not blank padded at all. The length of the buffer is set to the length of the data in characters, not bytes.

STRING/LONG VARCHAR. These host variables are not supported for Globalization Support data, since they can only be specified using dynamic SQL or datatype equivalencing, neither of which is supported for Globalization Support data.

You can use indicator variables with multibyte Globalization Support character variables as use you would with any other variable, except column length values are expressed in characters instead of bytes. For a list of possible values, see "About Using Indicator Variables".

The communicating points in a network are called nodes. SQL*Net lets you transmit information (SQL statements, data, and status codes) over the network from one node to another.

A protocol is a set of rules for accessing a network. The rules establish such things as procedures for recovering after a failure and formats for transmitting data and checking errors.

The SQL*Net syntax for connecting to the default database in the local domain is simply to use the service name for the database.

If the service name is not in the default (local) domain, you must use a global specification (all domains specified). For example:

HR.XX.ORACLE.COM

Each node has a default database. If you specify a node but no database in your CONNECT statement, you connect to the default database on the named local or remote node. If you specify no database and no node, you connect to the default database on the current node. Although it is unnecessary, you can specify the default database and current node in your CONNECT statement.

A default connection is made using a CONNECT statement without an AT clause. The connection can be to any default or nondefault database at any local or remote node. SQL statements without an AT clause are executed against the default connection. Conversely, a nondefault connection is made by a CONNECT statement that has an AT clause. A SQL statement with an AT clause is executed against the nondefault connection.

All database names must be unique, but two or more database names can specify the same connection. That is, you can have multiple connections to any database on any node.

Usually, you establish a connection to Oracle as follows:

EXEC SQL CONNECT :userid IDENTIFIED BY :password

Or, you might use

EXEC SQL CONNECT :usr_pwd; 

where usr_pwd contains username/password.

You can also log on automatically as shown. If you do not specify a database and node, you are connected to the default database at the current node. If you want to connect to a different database, you must explicitly identify that database.

With explicit logons, you connect to another database directly, giving the connection a name that will be referenced in SQL statements. You can connect to several databases at the same time and to the same database multiple times.

In the following example, you connect to a single nondefault database at a remote node:

-- Declare necessary host variables.
EXEC SQL BEGIN DECLARE SECTION;
 username CHARACTER(10);
 password CHARACTER(10);
 db_string CHARACTER(20);
EXEC SQL END DECLARE SECTION;
set username = 'scott';
set password = 'tiger';
set db_string = 'd:newyork-nondef';
-- Assign a unique name to the database connection.
EXEC SQL DECLARE db_name DATABASE;
-- Connect to the nondefault database
EXEC SQL CONNECT :username IDENTIFIED BY :password
 AT db_name USING :db_string;

The identifiers in this example serve the following purposes:

• The host variables username and password identify a valid user.

• The host variable db_string contains the SQL*Net syntax for logging on to a nondefault database at a remote node using the DECnet protocol.

• The undeclared identifier db_name names a nondefault connection; it is an identifier used by Oracle, not a host or program variable.

The USING clause specifies the network, computer, and database to be associated with db_name. Later, SQL statements using the AT clause (with db_name) are executed at the database specified by db_string.

Alternatively, you can use a character host variable in the AT clause, as the following example shows:

EXEC SQL BEGIN DECLARE SECTION;
 username CHARACTER(10);
 password CHARACTER(10);
 db_name CHARACTER(10);
 db_string CHARACTER(20);
EXEC SQL END DECLARE SECTION;
set username = 'scott';
set password = 'tiger';
set db_name = 'oracle1';
set db_string = 'd:newyork-nondef';
-- connect to the nondefault database
EXEC SQL CONNECT :username IDENTIFIED BY :password
 AT :db_name USING :db_string;
...

If db_name is a host variable, the DECLARE DATABASE statement is not needed. Only if db_name is an undeclared identifier must you execute a DECLARE db_name DATABASE statement before executing a CONNECT ... AT db_name statement.

SQL Operations. If granted the privilege, you can execute any SQL data manipulation statement at the nondefault connection. For example, you might execute the following sequence of statements:

EXEC SQL AT db_name SELECT ...
EXEC SQL AT db_name INSERT ...
EXEC SQL AT db_name UPDATE ...

In the next example, db_name is a host variable:

EXEC SQL AT :db_name DELETE ...

If db_name is a host variable, all database tables referenced by the SQL statement must be defined in DECLARE TABLE statements.

Cursor Control. Cursor control statements such as OPEN, FETCH, and CLOSE are exceptions--they never use an AT clause. If you want to associate a cursor with an explicitly identified database, use the AT clause in the DECLARE CURSOR statement, as follows:

EXEC SQL AT :db_name DECLARE emp_cursor CURSOR FOR ...
EXEC SQL OPEN emp_cursor ...
EXEC SQL FETCH emp_cursor ...
EXEC SQL CLOSE emp_cursor;

If db_name is a host variable, its declaration must be within the scope of all SQL statements that refer to the declared cursor. For example, if you open the cursor in one subprogram, then fetch from it in another, you must declare db_name globally or pass it to each subprogram.

When opening, closing, or fetching from the cursor, you do not use the AT clause. The SQL statements are executed at the database named in the AT clause of the DECLARE CURSOR statement or at the default database if no AT clause is used in the cursor declaration.

The AT :host_variable clause enables change the connection associated with a cursor. However, you cannot change the association while the cursor is open. Consider the following example:

EXEC SQL AT :db_name DECLARE emp_cursor CURSOR FOR ...
set db_name = 'oracle1';
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO ...
set db_name = 'oracle2';
EXEC SQL OPEN emp_cursor; -- illegal, cursor still open
EXEC SQL FETCH emp_cursor INTO ...

This is illegal because emp_cursor is still open when you try to execute the second OPEN statement. Separate cursors are not maintained for different connections; there is only one emp_cursor, which must be closed before it can be reopened for another connection. To debug the last example, simply close the cursor before reopening it, as follows:

EXEC SQL CLOSE emp_cursor; -- close cursor first
set db_name = 'oracle2';
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO ...

Dynamic SQL. Dynamic SQL statements are similar to cursor control statements in that some never use the AT clause. For dynamic SQL Method 1, you must use the AT clause if you want to execute the statement at a nondefault connection. An example follows:

EXEC SQL AT :db_name EXECUTE IMMEDIATE :sql_stmt;

For Methods 2, 3, and 4, you use the AT clause only in the DECLARE STATEMENT statement if you want to execute the statement at a nondefault connection. All other dynamic SQL statements such as PREPARE, DESCRIBE, OPEN, FETCH, and CLOSE never use the AT clause. The next example shows Method 2:

EXEC SQL AT :db_name DECLARE sql_stmt STATEMENT;
EXEC SQL PREPARE sql_stmt FROM :sql_string;
EXEC SQL EXECUTE sql_stmt;

The following example shows Method 3:

EXEC SQL AT :db_name DECLARE sql_stmt STATEMENT;
EXEC SQL PREPARE sql_stmt FROM :sql_string;
EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
EXEC SQL OPEN emp_cursor ...
EXEC SQL FETCH emp_cursor INTO ...
EXEC SQL CLOSE emp_cursor;

You need not use the AT clause when connecting to a remote database unless you open two or more connections simultaneously (in which case the AT clause is needed to identify the active connection). To make the default connection to a remote database, use the following syntax:

EXEC SQL CONNECT :username IDENTIFIED BY :password
 USING :db-string;

You can use the AT db_name clause for multiple explicit logons, just as you would for a single explicit logon. In the following example, you connect to two nondefault databases concurrently:

EXEC SQL BEGIN DECLARE SECTION;
 username CHARACTER(10);
 password CHARACTER(10);
 db_string1 CHARACTER(20);
 db_string2 CHARACTER(20);
EXEC SQL END DECLARE SECTION;
...
set username = 'scott';
set password = 'tiger';
set db_string1 = 'New_York';
set db_string2 = 'Boston';
-- give each database connection a unique name
EXEC SQL DECLARE db_name1 DATABASE;
EXEC SQL DECLARE db_name2 DATABASE;
-- connect to the two nondefault databases
EXEC SQL CONNECT :username IDENTIFIED BY :password
 AT db_name1 USING :db_string1;
EXEC SQL CONNECT :username IDENTIFIED BY :password
 AT db_name2 USING :db_string2;

The undeclared identifiers db_name1 and db_name2 are used to name the default databases at the two nondefault nodes so that later SQL statements can refer to the databases by name.

Alternatively, you can use a host variable in the AT clause, as the following example shows:

EXEC SQL BEGIN DECLARE SECTION;
 username CHARACTER(10);
 password CHARACTER(10);
 db_name CHARACTER(10);
 db_string CHARACTER(20);
EXEC SQL END DECLARE SECTION;
...
set username = 'scott';
set password = 'tiger';
FOR EACH nondefault database
 -- get next database name and SQL*Net string
 display 'Database Name? ';
 read db_name;
 display 'SQL*Net String? ';
 read db_string;
 -- connect to the nondefault database
 EXEC SQL CONNECT :username IDENTIFIED BY :password
 AT :db_name USING :db_string;
ENDFOR;

You can also use this method to make multiple connections to the same database, as the following example shows:

set username = 'scott';
set password = 'tiger';
set db_string = 'd:newyork-nondef';
FOR EACH nondefault database
 -- get next database name
 display 'Database Name? ';
 read db_name;
 -- connect to the nondefault database
 EXEC SQL CONNECT :username IDENTIFIED BY :password
 AT :db_name USING :db_string;
ENDFOR;

You must use different database names for the connections, even if they use the same SQL*Net string.

Implicit logons are supported through the Oracle distributed database option, which does not require explicit logons. For example, a distributed query allows a single SELECT statement to access data on one or more nondefault databases.

The distributed query facility depends on database links, which assign a name to a CONNECT statement rather than to the connection itself. At run time, the embedded SELECT statement is executed by the specified Oracle Server, which connects implicitly to the nondefault database(s) to get the required data.

In the next example, you connect to a single nondefault database. First, your program executes the following statement to define a database link (database links are usually established interactively by the DBA or user):

EXEC SQL CREATE DATABASE LINK db_link
 CONNECT TO username IDENTIFIED BY password
 USING 'd:newyork-nondef';

Then, the program can query the nondefault EMP table using the database link, as follows:

EXEC SQL SELECT ENAME, JOB INTO :emp_name, :job_title
 FROM emp@db_link
 WHERE DEPTNO = :dept_number;

The database link is not related to the database name used in the AT clause of an embedded SQL statement. It simply tells Oracle where the nondefault database is located, the path to it, and the Oracle username and password to use. The database link is stored in the data dictionary until it is explicitly dropped.

In our example, the default Oracle Server logs on to the nondefault database through SQL*Net using the database link db_link. The query is submitted to the default server, but is "forwarded" to the nondefault database for execution.

To make referencing the database link easier, you can create a synonym as follows (again, this is usually done interactively):

EXEC SQL CREATE SYNONYM emp FOR emp@db_link;

Then, your program can query the nondefault EMP table, as follows:

EXEC SQL SELECT ENAME, JOB INTO :emp_name, :job_title
 FROM emp
 WHERE DEPTNO = :dept_number;

This provides location transparency for emp.

In the following example, you connect to two nondefault databases concurrently. First, you execute the following sequence of statements to define two database links and create two synonyms:

EXEC SQL CREATE DATABASE LINK db_link1
 CONNECT TO username1 IDENTIFIED BY password1
 USING 'd:newyork-nondef';
EXEC SQL CREATE DATABASE LINK db_link2
 CONNECT TO username2 IDENTIFIED BY password2
 USING 'd:chicago-nondef';
EXEC SQL CREATE SYNONYM emp FOR emp@db_link1;
EXEC SQL CREATE SYNONYM dept FOR dept@db_link2;

Then, your program can query the nondefault EMP and DEPT tables, as follows:

EXEC SQL SELECT ENAME, JOB, SAL, LOC
 FROM emp, dept
 WHERE emp.DEPTNO = dept.DEPTNO AND DEPTNO = :dept_number;

Oracle executes the query by performing a join between the nondefault EMP table at db_link1 and the nondefault DEPT table at db_link2.

You set up the LDA by issuing the OCI call

SQLLDA(lda);

where lda identifies the LDA data structure. The format of this call is language-dependent. If the CONNECT statement fails, the lda_rc field in the lda is set to 1012 to indicate the error.

A call to SQLLDA sets up an LDA for the connection used by the most recently executed SQL statement. To set up the different LDAs needed for additional connections, just call SQLLDA with a different lda after each CONNECT. In the following example, you connect to two nondefault databases concurrently:

EXEC SQL BEGIN DECLARE SECTION;
 username CHARACTER(10);
 password CHARACTER(10);
 db_string1 CHARACTER(20);
 db_string2 CHARACTER(20);
EXEC SQL END DECLARE SECTION;
lda1 INTEGER(32);
lda2 INTEGER(32);
set username = 'SCOTT';
set password = 'TIGER';
set db_string1 = 'D:NEWYORK-NONDEF1';
set db_string2 = 'D:CHICAGO-NONDEF2';
-- give each database connection a unique name
EXEC SQL DECLARE db_name1 DATABASE;
EXEC SQL DECLARE db_name2 DATABASE;
-- connect to first nondefault database
EXEC SQL CONNECT :username IDENTIFIED BY :password
 AT db_name1 USING :db_string1;
-- set up first LDA for OCI use
SQLLDA(lda1);
-- connect to second nondefault database
EXEC SQL CONNECT :username IDENTIFIED BY :password
 AT db_name2 USING :db_string2;
-- set up second LDA for OCI use
SQLLDA(lda2);

Remember, do not declare db_name1 and db_name2 in the Declare Section because they are not host variables. You use them only to name the default databases at the two nondefault nodes so that later SQL statements can refer to the databases by name.

You can use the Oracle Precompilers to develop applications that comply with the X/Open standards. However, you must meet the following requirements.

The X/Open application does not establish and maintain connections to a database. Instead, the transaction manager and the XA interface, which is supplied by Oracle, handle database connections and disconnections transparently. So, normally an X/Open-compliant application does not execute CONNECT statements.

The X/Open application must not execute statements such as COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION that affect the state of global transactions. For example, the application must not execute the COMMIT statement because the transaction manager handles commits. Also, the application must not execute SQL data definition statements such as CREATE, ALTER, and RENAME because they issue an implicit commit.

The application can execute an internal ROLLBACK statement if it detects an error that prevents further SQL operations. However, this might change in later versions of the XA interface.

If you want your X/Open application to issue OCI calls, you must use the run-time library routine SQLLD2, which sets up an LDA for a specified connection established through the XA interface. Note that OCOM, OCON, OCOF, ORLON, OLON, OLOG, and OLOGOF cannot be issued by an X/Open application.

To get XA functionality, you must link the XA library to your X/Open application object modules. For instructions, see your system-specific Oracle manuals.