| Programmer's Guide to the Oracle Precompilers Release 1.8 Part Number A42525-1 |
|
|
View PDF |
| Programmer's Guide to the Oracle Precompilers Release 1.8 Part Number A42525-1 |
|
|
View PDF |
Passing data between Oracle and your application program requires host variables, datatype conversions, event handling, and access to Oracle. This chapter shows you how to meet these requirements. You learn the embedded SQL commands that declare variables, declare communication areas, and connect to an Oracle database. You also learn about the Oracle datatypes, National Language Support (NLS), data conversion, and how to take advantage of datatype equivalencing. The final two sections show you how to embed OCI calls in your program and how to develop X/Open applications.
You must declare all program variables to be used in SQL statements (that is, all host variables) in the Declare Section. If you use an undeclared host variable in a SQL statement, the precompiler issues an error message. For a complete listing of error messages see Oracle7 Server Messages.
The Declare Section begins with the statement
EXEC SQL BEGIN DECLARE SECTION;
and ends with the statement
EXEC SQL END DECLARE SECTION;
Note: In COBOL, the statement terminator is END-EXEC. In FORTRAN, it is a carriage return.
Between these two statements only the following items are allowed:
Multiple Declare Sections are allowed per precompiled unit. Furthermore, a host program can contain several independently precompiled units.
In the following example, you declare four host variables for use later in your program. (Remember, many program examples are written in a pseudocode, which is described vi in the Preface.)
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" ![[*]](jump.gif)
.
The INCLUDE statement lets you copy files into your host program. It is similar to the COBOL COPY command. An example follows:
-- copy in the SQLCA file EXEC SQL INCLUDE SQLCA;
When you precompile your program, each EXEC SQL INCLUDE statement is replaced by a copy of the file named in the statement.
You can INCLUDE any file. If a file contains embedded SQL, you must INCLUDE it because only INCLUDEd files are precompiled. If you do not specify a file extension, the precompiler assumes the default extension for source files, which is language-dependent (see your host-language supplement to this Guide).
You can set a directory path for INCLUDEd files by specifying the precompiler option
INCLUDE=<path>
where path defaults to the current directory. (In this context, a directory is an index of file locations.)
The precompiler searches first in the current directory, then in the directory specified by INCLUDE, and finally in a directory for standard INCLUDE files. So, you need not specify a directory path for standard files such as the SQLCA and ORACA. You must still use INCLUDE to specify a directory path for nonstandard files unless they are stored in the current directory.
If your operating system is case-sensitive (like UNIX for example), be sure to specify the same upper/lower case filename under which the file is stored. The syntax for specifying a directory path is system-specific. Check your system-specific Oracle manuals.
The SQLCA is a data structure that provides for diagnostic checking and event handling. At run time, the SQLCA holds status information passed to your program by Oracle. After executing a SQL statement, Oracle sets SQLCA variables to indicate the outcome, as illustrated in Figure 3 - 1.
Text description of the illustration image006.gif. Figure 3 - 1. Updating the SQLCA
Thus, you can check to see if an INSERT, UPDATE, or DELETE statement succeeded and if so, how many rows were affected. Or, if the statement failed, you can get more information about what happened.
When MODE={ANSI13|ORACLE}, you must declare the SQLCA by hardcoding it or by copying it into your program with the INCLUDE statement. The section "Using the SQL Communications Area" shows you how to declare and use the SQLCA.
Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores data in database columns. Oracle also uses internal datatypes to represent database pseudocolumns. An external datatype specifies how data is stored in a host variable.
At precompile time, each host variable in the Declare Section is associated with an external datatype code. At run time, the datatype code of every host variable used in a SQL statement is passed to Oracle. Oracle uses the codes to convert between internal and external datatypes.
Note: You can override default datatype conversions by using dynamic SQL Method 4 or datatype equivalencing. For information about dynamic SQL Method 4, see "Using Method 4" . For information about datatype equivalencing, see "Datatype Equivalencing" .
shows the internal datatypes that Oracle uses for database columns and pseudocolumns.
| Name | Code | Description |
|---|---|---|
| CHAR | 96 | <= 255-byte, fixed-length string |
| DATE | 12 | 7-byte, fixed-length date/time value |
| LONG | 8 | <= 2147483647-byte, variable-length string |
| LONG RAW | 24 | <= 2147483647-byte, variable-length binary data |
| MLSLABEL | 105 | <= 5-byte, variable-length binary label |
| NUMBER | 2 | fixed or floating point number |
| RAW | 23 | <= 255-byte, variable-length binary data |
| ROWID | 11 | fixed-length binary value |
| VARCHAR2 | 1 | <= 2000-byte, variable-length string |
These internal datatypes can be quite different from host-language datatypes. For example, the NUMBER datatype was designed for portability, precision (no rounding error), and correct collating. No host language has an equivalent datatype.
Brief descriptions of the internal datatypes follow. For more information, see the Oracle7 Server SQL Reference.
You use the CHAR datatype to store fixed-length character data. How the data is represented internally depends on the database character set. The CHAR datatype takes an optional parameter that lets you specify a maximum width up to 255 bytes. The syntax follows:
CHAR[(maximum_width)]
You cannot use a constant or variable to specify the maximum width; you must use an integer literal. If you do not specify the maximum width, it defaults to 1. Remember, you specify the maximum width of a CHAR(n) column in bytes, not characters. So, if a CHAR(n) column stores multi-byte (2-byte) characters, its maximum width is less than n/2 characters.
You use the DATE datatype to store dates and times in 7-byte, fixed-length fields. The date portion defaults to the first day of the current month; the time portion defaults to midnight.
Internally, DATEs are stored in a binary format. When converting a DATE column value to a character string in your program, Oracle uses the default format mask for your session. If you need other date/time information such as the date in Julian days, use the TO_CHAR function with a format mask. Always convert DATE column values to and from character strings using (external) character datatypes such as VARCHAR2 or STRING.
You use the LONG datatype to store variable-length character strings. LONG columns can store text, arrays of characters, or even short documents. The LONG datatype is like the VARCHAR2 datatype, except the maximum width of a LONG column is 2147483647 bytes or two gigabytes.
You can use LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, function calls, or SQL clauses such as WHERE, GROUP BY, and CONNECT BY. Only one LONG column is allowed per database table and that column cannot be indexed.
You use the LONG RAW datatype to store variable-length binary data or byte strings. The maximum width of a LONG RAW column is 2147483647 bytes or two gigabytes.
LONG RAW data is like LONG data, except that Oracle assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another. The restrictions that apply to LONG data also apply to LONG RAW data.
With Trusted Oracle7, you use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle7 uses labels to control access to data. For more information, see the Trusted Oracle7 Server Administrator's Guide.
You can use the MLSLABEL datatype to define a database column. However, with standard Oracle, such columns can store only nulls. With Trusted Oracle7, you can insert any valid operating system label into a column of type MLSLABEL. If the label is in text format, Trusted Oracle7 converts it to a binary value automatically. The text string can be up to 255 bytes long. However, the internal length of an MLSLABEL value is between 2 and 5 bytes.
With Trusted Oracle7, you can also select values from a MLSLABEL column into a character variable. Trusted Oracle7 converts the internal binary value to a VARCHAR2 value automatically.
You use the NUMBER datatype to store fixed or floating point numbers of virtually any size. You can specify precision, which is the total number of digits, and scale, which determines where rounding occurs.
The maximum precision of a NUMBER value is 38; the magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127. For example, a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000). A scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46).
When you specify precision and scale, Oracle does extra integrity checks before storing the data. If a value exceeds the precision, Oracle issues an error message; if a value exceeds the scale, Oracle rounds the value.
You use the RAW datatype to store binary data or byte strings (a sequence of graphics characters, for example). RAW data is not interpreted by Oracle.
The RAW datatype takes a required parameter that lets you specify a maximum width up to 255 bytes. The syntax follows:
RAW(maximum_width)
You cannot use a constant or variable to specify the maximum width; you must use an integer literal.
RAW data is like CHAR data, except that Oracle assumes nothing about the meaning of RAW data and does no character set conversions (from 7-bit ASCII to EBCDIC Code Page 500 for example) when you transmit RAW data from one system to another.
Internally, every table in an Oracle database has a pseudocolumn named ROWID, which stores binary values called rowids. ROWIDs uniquely identify rows and provide the fastest way to access particular rows.
You use the VARCHAR2 datatype to store variable-length character strings. How the strings are represented internally depends on the database character set, which might be 7-bit ASCII or EBCDIC Code Page 500 for example.
The maximum width of a VARCHAR2 database column is 2000 bytes. To define a VARCHAR2 column, you use the syntax
VARCHAR2(maximum_width)
where maximum_width is an integer literal in the range 1 .. 2000.
You specify the maximum width of a VARCHAR2(n) column in bytes, not characters. So, if a VARCHAR2(n) column stores multi-byte (2-byte) characters, its maximum width is less than n/2 characters.
SQL recognizes the pseudocolumns in Table 3 - 2, which return specific data items:
| Pseudocolumn | Internal Datatype |
|---|---|
| CURRVAL | NUMBER |
| LEVEL | NUMBER |
| NEXTVAL | NUMBER |
| ROWID | ROWID |
| ROWLABEL | MLSLABEL |
| ROWNUM | NUMBER |
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:
| Function | Internal Datatype |
|---|---|
| SYSDATE | DATE |
| UID | NUMBER |
| USER | VARCHAR2 |
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 Oracle7 Server SQL 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.
SQL also recognizes the special column ROWLABEL, which Trusted Oracle7 creates for every database table. Like other columns, ROWLABEL can be referenced in SQL statements. However, with standard Oracle, ROWLABEL returns a null. With Trusted Oracle7, ROWLABEL returns the operating system label for a row.
A common use of ROWLABEL is to filter query results. For example, the following statement counts only those rows with a security level higher than "unclassified":
EXEC SQL SELECT COUNT(*) INTO :head_count FROM EMP
WHERE ROWLABEL > 'UNCLASSIFIED';
For more information about the ROWLABEL column, see the Trusted Oracle7 Server Administrator's Guide.
As Table 3 - 4 shows, the external datatypes include all the internal datatypes plus several datatypes found in other supported host languages. For example, the STRING external datatype refers to a C null-terminated string, and the DECIMAL datatype refers to COBOL packed decimals. You use the datatype names in datatype equivalencing, and you use the datatype codes in dynamic SQL Method 4.
| Name | Code | Description |
|---|---|---|
| CHAR | 1 96 | <= 65535-byte, variable-length character string (1)<=65535-byte, fixed-length character string (1) |
| CHARF | 96 | <= 65535-byte, fixed-length character string |
| CHARZ | 97 | <= 65535-byte, fixed-length, null-terminated string (2) |
| DATE | 12 | 7-byte, fixed-length date/time value |
| DECIMAL | 7 | COBOL packed decimal |
| DISPLAY | 91 | COBOL numeric character string |
| FLOAT | 4 | 4-byte or 8-byte floating-point number |
| INTEGER | 3 | 2-byte or 4-byte signed integer |
| LONG | 8 | <= 2147483647-byte, fixed-length string |
| LONG RAW | 24 | <= 217483647-byte, fixed-length binary data (3) |
| LONG VARCHAR | 94 | <= 217483643-byte, variable-length string (3) |
| LONG VARRAW | 95 | <= 217483643-byte, variable-length binary data |
| MLSLABEL | 106 | 2..5-byte, variable-length binary data |
| NUMBER | 2 | integer or floating-point number |
| RAW | 23 | <= 65535-byte, fixed-length binary data (2) |
| ROWID | 11 | (typically) 13-byte, fixed-length binary value |
| STRING | 5 | <= 65535-byte, variable-length, null-terminated character string (2) |
| UNSIGNED | 68 | 2-byte or 4-byte unsigned integer |
| VARCHAR | 9 | <= 65533-byte, variable-length character string (3) |
| VARCHAR2 | 1 | <= 65535-byte, variable-length character string (2) |
| VARNUM | 6 | variable-length binary number |
| VARRAW | 15 | <= 65533-byte, variable-length binary data (3) |
Notes:
Brief descriptions of the external datatypes follow. For more information, see your host language supplement.
CHAR behavior depends on the settings of the options DBMS and MODE. See the table .
When MODE=ANSI, Oracle assigns the CHAR datatype to all character host variables. You use the CHAR datatype to store fixed-length character strings. On most platforms, the maximum length of a CHAR value is 65535 (64K) bytes. See Table 6 - 4 for more information about the relationship between the DBMS and MODE options.
On Input. Oracle reads the number of bytes specified for the input host variable, does not strip trailing blanks, then stores the input value in the target database column.
If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value is all-blank, Oracle treats it like a character value.
On Output. Oracle returns the number of bytes specified for the output host variable, blank-padding if necessary, then assigns the output value to the target host variable. If a null is returned, Oracle fills the host variable with blanks.
If the output value is longer than the declared length of the host variable, Oracle truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle sets it to the original length of the output value.
You use the CHARZ datatype to store fixed-length, null-terminated character strings. On most platforms, the maximum length of a CHARZ value is 65,535 bytes. You should not need this external type in Pro*COBOL or Pro*FORTRAN.
On input, the CHARZ and STRING datatypes work the same way. You must null-terminate the input value. The null terminator serves only to delimit the string; it is not part of the data.
On output, the CHARZ and CHAR datatypes work the same way. Oracle appends a null terminator to the output value, which is also blank-padded if necessary.
You use the DATE datatype to store dates and times in 7-byte, fixed-length fields. As Table 3 - 5 shows, the century, year, month, day, hour (in 24-hour format), minute, and second are stored in that order from left to right.
| Byte | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
|---|---|---|---|---|---|---|---|
| Meaning | Century | Year | Month | Day | Hour | Minute | Second |
| Example 17-OCT-1994 at 1:23:12 PM | 119 | 194 | 10 | 17 | 14 | 24 | 13 |
The century and year bytes are in excess-100 notation. The hour, minute, and second are in excess-1 notation. Dates before the Common Era (B.C.E.) are less than 100. The epoch is January 1, 4712 B.C.E. For this date, the century byte is 53 and the year byte is 88. The hour byte ranges from 1 to 24. The minute and second bytes range from 1 to 60. The time defaults to midnight (1, 1, 1).
With Pro*COBOL, you use the DECIMAL datatype to store packed decimal numbers for calculation. In COBOL, the host variable must be a signed COMP-3 field with an implied decimal point. If significant digits are lost during data conversion, Oracle fills the host variable with asterisks.
With Pro*COBOL, you use the DISPLAY datatype to store numeric character data. The DISPLAY datatype refers to a COBOL "DISPLAY SIGN LEADING SEPARATE" number, which typically requires n + 1 bytes of storage for PIC S9(n), and n + d + 1 bytes of storage for PIC S9(n)V9(d).
You use the FLOAT datatype to store numbers that have a fractional part or that exceed the capacity of the INTEGER datatype. The number is represented using the floating-point format of your computer and typically requires 4 or 8 bytes of storage. You must specify a length for input and output host variables.
Oracle can represent numbers with greater precision than floating point implementations because the internal format of Oracle numbers is decimal.
Note: In SQL statements, when comparing FLOAT values, use the SQL function ROUND because FLOAT stores binary (not decimal) numbers; so, fractions do not convert exactly.
You use the INTEGER datatype to store numbers that have no fractional part. An integer is a signed, 2- or 4-byte binary number. The order of the bytes in a word is system-dependent. You must specify a length for input and output host variables. On output, if the column value is a floating point number, Oracle truncates the fractional part.
You use the LONG datatype to store fixed-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 2147483647 bytes (two gigabytes).
You use the LONG RAW datatype to store fixed-length, binary data or byte strings. The maximum length of a LONG RAW value is 2147483647 bytes (two gigabytes).
LONG RAW data is like LONG data, except that Oracle assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another.
You use the LONG VARCHAR datatype to store variable-length character strings. LONG VARCHAR variables have a 4-byte length field followed by a string field. The maximum length of the string field is 2147483643 bytes. In an EXEC SQL VAR statement, do not include the 4-byte length field.
You use the LONG VARRAW datatype to store binary data or byte strings. LONG VARRAW variables have a 4-byte length field followed by a data field. The maximum length of the data field is 2147483643 bytes. In an EXEC SQL VAR statement, do not include the 4-byte length field.
You use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle7 uses labels to control access to data. For more information, see the Trusted Oracle7 Server Administrator's Guide.
You can use the MLSLABEL datatype to define a column. However, with standard Oracle, such columns can store nulls only. With Trusted Oracle7, you can insert any valid operating system label into a column of type MLSLABEL.
On Input. Trusted Oracle7 translates the input value into a binary label, which must be a valid operating system label. If the label is invalid, Trusted Oracle7 issues an error message. If the label is valid, Trusted Oracle7 stores it in the target database column.
On Output. Trusted Oracle7 converts the binary label to a character string, which can be of type CHAR, CHARZ, STRING, VARCHAR, or VARCHAR2.
You use the NUMBER datatype to store fixed or floating point Oracle numbers. You can specify precision and scale. The maximum precision of a NUMBER value is 38; the magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127.
NUMBER values are stored in variable-length format, starting with an exponent byte and followed by up to 20 mantissa bytes. The high-order bit of the exponent byte is a sign bit, which is set for positive numbers. The low-order 7 bits represent the exponent, which is a base-100 digit with an offset of 65.
Each mantissa byte is a base-100 digit in the range 1 .. 100. For positive numbers, 1 is added to the digit. For negative numbers, the digit is subtracted from 101, and, unless there are 20 mantissa bytes, a byte containing 102 is appended to the data bytes. Each mantissa byte can represent two decimal digits. The mantissa is normalized and leading zeros are not stored. You can use up to 20 data bytes for the mantissa but only 19 are guaranteed accurate. The 19 bytes, each representing a base-100 digit, allow a maximum precision of 38 digits.
On output, the host variable contains the number as represented internally by Oracle. To accommodate the largest possible number, the output host variable must be 21 bytes long. Only the bytes used to represent the number are returned. Oracle does not blank-pad or null-terminate the output value. If you need to know the length of the returned value, use the VARNUM datatype instead.
Normally, there is little reason to use this datatype.
You use the RAW datatype to store fixed-length binary data or byte strings. On most platforms, the maximum length of a RAW value is 65535 bytes.
RAW data is like CHAR data, except that Oracle assumes nothing about the meaning of RAW data and does no character set conversions when you transmit RAW data from one system to another.
You use the ROWID datatype to store binary rowids in (typically 13-byte) fixed-length fields. The field size is port-specific. So, check your system-specific Oracle manuals.
You can use VARCHAR2 host variables to store rowids in a readable format. When you select or fetch a rowid into a VARCHAR2 host variable, Oracle converts the binary value to an 18-byte character string and returns it in the format
BBBBBBBB.RRRR.FFFF
where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file. These numbers are hexadecimal. For example, the rowid
0000000E.000A.0007
points to the 11th row in the 15th block in the 7th database file.
Typically, you fetch a rowid into a VARCHAR2 host variable, then compare the host variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement. That way, you can identify the latest row fetched by a cursor. For an example, see "Mimicking CURRENT OF" .
Note: If you need full portability or your application communicates with a non-Oracle database via Transparent Gateway, specify a maximum length of 256 (not 18) bytes when declaring the VARCHAR2 host variable. If your application communicates with a non-Oracle data source via Oracle Open Gateway, specify a maximum length of 256 bytes. Though you can assume nothing about its contents, the host variable will behave normally in SQL statements.
The STRING datatype is like the VARCHAR2 datatype, except that a STRING value is always null-terminated.
On Input. Oracle uses the specified length to limit the scan for a null terminator. If a null terminator is not found, Oracle generates an error. If you do not specify a length, Oracle assumes the maximum length, which is 65535 on most platforms.
The minimum length of a STRING value is 2 bytes. If the first character is a null terminator and the specified length is 2, Oracle inserts a null unless the column is defined as NOT NULL. An all-blank or null-terminated value is stored intact.
On Output. Oracle appends a null byte to the last character returned. If the string length exceeds the specified length, Oracle truncates the output value and appends a null byte.
You use the UNSIGNED datatype to store unsigned integers. An unsigned integer is a binary number of 2 or 4 bytes. The order of the bytes in a word is system-dependent. You must specify a length for input and output host variables. On output, if the column value is a floating point number, Oracle truncates the fractional part. You should not need this external type in Pro*COBOL or Pro*FORTRAN.
You use the VARCHAR datatype to store variable-length character strings. VARCHAR variables have a 2-byte length field followed by a <= 65533-byte string field. However, for VARCHAR array elements, the maximum length of the string field is 65530 bytes. When you specify the length of a VARCHAR variable, be sure to include 2 bytes for the length field. For longer strings, use the LONG VARCHAR datatype. In an EXEC SQL VAR statement, do not include the 2-byte length field.
When MODE=ORACLE, Oracle assigns the VARCHAR2 datatype to all character host variables. You use the VARCHAR2 datatype to store variable-length character strings. On most platforms, the maximum length of a VARCHAR2 value is 65535 bytes.
You specify the maximum length of a VARCHAR2(n) value in bytes, not characters. So, if a VARCHAR2(n) variable stores multi-byte characters, its maximum length is less than n characters.
On Input. Oracle reads the number of bytes specified for the input host variable, strips any trailing blanks, then stores the input value in the target database column. Be careful. An uninitialized host variable can contain nulls. So, always blank-pad a character input host variable to its declared length. (COBOL PIC X(n) and FORTRAN CHARACTER*n variables do this automatically.)
If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value is all-blank, Oracle treats it like a null.
Oracle can convert a character value to a NUMBER column value if the character value represents a valid number. Otherwise, Oracle generates an error.
On Output. Oracle returns the number of bytes specified for the output host variable, blank-padding if necessary, then assigns the output value to the target host variable. If a null is returned, Oracle fills the host variable with blanks.
If the output value is longer than the declared length of the host variable, Oracle truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle sets it to the original length of the output value.
Oracle can convert NUMBER column values to character values. The length of the character host variable determines precision. If the host variable is too short for the number, scientific notation is used. For example, if you select the column value abcdefg89 into a host variable of length 6, Oracle returns the value "1.2E08" to the host variable.
The VARNUM datatype is like the NUMBER datatype, except that the first byte of a VARNUM variable stores the length of the value.
On input, you must set the first byte of the host variable to the length of the value. On output, the host variable contains the length followed by the number as represented internally by Oracle. To accommodate the largest possible number, the host variable must be 22 bytes long. After selecting a column value into a VARNUM host variable, you can check the first byte to get the length of the value.
You use the VARRAW datatype to store variable-length binary data or byte strings. The VARRAW datatype is like the RAW datatype, except that VARRAW variables have a 2-byte length field followed by a <=65533-byte data field. For longer strings, use the LONG VARRAW datatype. In an EXEC SQL VAR statement, do not include the 2-byte length field. To get the length of a VARRAW variable, simply refer to its length field.
At precompile time, an external datatype is assigned to each host variable in the Declare Section. For example, the precompiler assigns the INTEGER external datatype to integer host variables. At run time, the datatype code of every host variable used in a SQL statement is passed to Oracle. Oracle uses the codes to convert between internal and external datatypes.
Before assigning a selected column (or pseudocolumn) value to an output host variable, if necessary, Oracle converts the internal datatype of the column to the datatype of the host variable. Likewise, before assigning or comparing the value of an input host variable to a database column, if necessary, Oracle converts the external datatype of the host variable to the internal datatype of the column.
However, the datatype of the host variable must be compatible with that of the database column. It is your responsibility to make sure that values are convertible. For example, if you try to convert the string value "YESTERDAY" to a DATE column value, you get an error.
Conversions between internal and external datatypes follow the usual data conversion rules. For instance, you can convert a CHAR value of "1234" to a 2-byte integer. But, you cannot convert a CHAR value of "65543" (number too large) or "10F" (number not decimal) to a 2-byte integer. Likewise, you cannot convert a string value that contains alphabetic characters to a NUMBER value.
Number conversion follows the conventions specified by National Language Support (NLS) parameters in the Oracle initialization file. For example, your system might be configured to recognize a comma (,) instead of a period (.) as the decimal character. For more information about NLS, see the Oracle7 Server Application Developer's Guide.
Table 3 - 6 shows the supported conversions between internal and external datatypes.
| Internal | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| External | CHAR | DATE | LONG | LONG RAW | MLSLABEL | NUMBER | RAW | ROWID | VARCHAR2 |
| CHAR | I/O | I/O (2) | I/O | I (3) | I/O (7) | I/O | I/O (3) | I/O (1) | I/O |
| CHARF | I/O | I/O (2) | I/O | I (3) | I/O (7) | I/O | I/O (3) | I/O (1) | I/O |
| CHARZ | I/O | I/O (2) | I/O | I (3) | I/O (7) | I/O | I/O (3) | I/O (1) | I/O |
| DATE | I/O | I/O | I | I/O | |||||
| DECIMAL | I/O (4) | I | I/O | I/O (4) | |||||
| DISPLAY | I/O (4) | I | I/O | I/O (4) | |||||
| FLOAT | I/O (4) | I | I/O | I/O (4) | |||||
| INTEGER | I/O (4) | I | I/O | I/O (4) | |||||
| LONG | I/O | I/O (2) | I/O | I (3,5) | I/O (7) | I/O | I/O (3) | I/O (1) | I/O |
| LONG RAW | O (6) | I (5,6) | I/O | I/O | O (6) | ||||
| LONG VARCHAR | I/O | I/O (2) | I/O | I (3,5) | I/O (7) | I/O | I/O (3) | I/O (1) | I/O |
| LONG VARRAW | I/O (6) | I (5,6) | I/O | I/O | I/O (6) | ||||
| MLSLABEL | I/O (8) | I/O (8) | I/O | I/O (8) | |||||
| NUMBER | I/O (4) | I | I/O | I/O (4) | |||||
| RAW | I/O (6) | I (5,6) | I/O | I/O | I/O (6) | ||||
| ROWID | I | I | I/O | I | |||||
| STRING | I/O | I/O (2) | I/O | I (3,5) | I/O (7) | I/O | I/O (3) | I/O (1) | I/O |
| UNSIGNED | I/O (4) | I | I/O | I/O (4) | |||||
| VARCHAR | I/O | I/O (2) | I/O | I (3,5) | I/O (7) | I/O | I/O (3) | I/O (1) | I/O |
| VARCHAR2 | I/O | I/O (2) | I/O | I (3) | I/O (7) | I/O | I/O (3) | I/O (1) | I/O |
| VARNUM | I/O (4) | I | I/O | I/O (4) | |||||
| VARRAW | I/O (6) | I (5,6) | I/O | I/O | I/O (6) | ||||
| Notes: Legend: 1. On input, host string must be in Oracle 'BBBBBBBB.RRRR.FFFF' format. I = input only On output, column value is returned in same format. O = output only
2. On input, host string must be the default DATE character format. I/O = input or output On output, column value is returned in same format
3. On input, host string must be in hex format. On output, column value is returned in same format.
4. On output, column value must represent a valid number.
5. On input, length must be less than or equal to 2000.
6. On input, column value is stored in hex format. On output, column value must be in hex format.
7. On input, host string must be a valid OS label in text format. On output, column value is returned in same format.
8. On input, host string must be a valid OS label in raw format. On output, column value is returned in same format. | |||||||||
When you select a DATE column value into a character host variable, Oracle must convert the internal binary value to an external character value. So, Oracle implicitly calls the SQL function TO_CHAR, which returns a character string in the default date format. The default is set by the Oracle initialization parameter NLS_DATE_FORMAT. To get other information such as the time or Julian date, you must explicitly call TO_CHAR with a format mask.
A conversion is also necessary when you insert a character host value into a DATE column. Oracle implicitly calls the SQL function TO_DATE, which expects the default date format. To insert dates in other formats, you must explicitly call TO_DATE with a format mask.
When you select a RAW or LONG RAW column value into a character host variable, Oracle must convert the internal binary value to an external character value. In this case, Oracle returns each binary byte of RAW or LONG RAW data as a pair of characters. Each character represents the hexadecimal equivalent of a nibble (half a byte). For example, Oracle returns the binary byte 11111111 as the pair of characters "FF". The SQL function RAWTOHEX performs the same conversion.
A conversion is also necessary when you insert a character host value into a RAW or LONG RAW column. Each pair of characters in the host variable must represent the hexadecimal equivalent of a binary byte. If a character does not represent the hexadecimal value of a nibble, Oracle issues the following error message:
ORA-01465: invalid hex number
Every program variable used in a SQL statement must be declared as a host variable. You declare a host variable in the Declare Section according to the rules of the host language. Normal scoping rules apply. Host variable names can be any length, but only the first 31 characters are significant. For ANSI/ISO compliance, a host variable name must be <= 18 characters long, begin with a letter, and not contain consecutive or trailing underscores.
The external datatype of a host variable and the internal datatype of its source or target database column need not be the same, but they must be compatible. Table 3 - 6 shows the compatible datatypes between which Oracle converts automatically when necessary.
The Oracle Precompilers support most built-in host language datatypes. For a list of supported datatypes, see your host-language supplement. User-defined datatypes are not supported. Datatype equivalencing is discussed in the next section.
Although references to a user-defined structure are not allowed, the Pro*COBOL Precompiler lets you reference individual elements of the structure as if they were host variables. You can use such references wherever host variables are allowed.
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 "Using Host Variables" .
You can use the VARCHAR pseudotype to declare variable-length character strings. (A pseudotype is a datatype not native to your host language.) Recall that VARCHAR variables have a 2-byte length field followed by a string field. For example, the Pro*COBOL Precompiler expands the VARCHAR declaration
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ENAME PIC X(20) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
into the following COBOL group item with array and length members:
01 ENAME.
05 ENAME-LEN PIC S9(4) COMP.
05 ENAME-ARR PIC X(20).
To get the length of a VARCHAR, you simply refer to its length field. You need not use a string function or character-counting algorithm.
For more information about VARCHARs, see your host-language supplement to this Guide.
The following guidelines apply to declaring and referencing host variables. A host variable must be
A host variable must not be
A host variable can be
You can associate every host variable with an optional indicator variable. An indicator variable must be defined in the Declare Section as a 2-byte integer and, in SQL statements, must be prefixed with a colon and must directly follows its host variable unless you use the keyword INDICATOR.
To improve readability, you can precede any indicator variable with the optional keyword INDICATOR. You must still prefix the indicator variable with a colon. The correct syntax is
:<host_variable> INDICATOR :<indicator_variable>
which is equivalent to
:<host_variable>:<indicator_variable>
You can use both forms of expression in your host program.
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 below, 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 "Using Indicator Variables" .
The following guidelines apply to declaring and referencing indicator variables. An indicator variable must be
An indicator variable must not be
Datatype equivalencing lets you customize the way Oracle interprets input data and the way Oracle formats output data. On a variable-by- variable basis, you can equivalence supported host language datatypes to the Oracle external datatypes.
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 NLS 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.
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. On the other hand, 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.
| External Datatype | Length | Precision | Scale | Default Length |
|---|---|---|---|---|
| CHAR | optional | n/a | n/a | declared length of variable |
| CHARZ | optional | n/a | n/a | declared length of variable |
| DATE | n/a | n/a | n/a | 7 bytes |
| DECIMAL | n/a | required | required | none |
| DISPLAY | n/a | required | required | none |
| FLOAT | optional (4 or 8) | n/a | n/a | declared length of variable |
| INTEGER | optional (1, 2, or 4) | n/a | n/a | declared length of variable |
| LONG | optional | n/a | n/a | declared length of variable |
| LONG RAW | optional | n/a | n/a | declared length of variable |
| LONG VARCHAR | required (note 1) | n/a | n/a | none |
| LONG VARRAW | required (note 1) | n/a | n/a | none |
| MLSLABEL | required | n/a | n/a | none |
| NUMBER | n/a | n/a | n/a | not available |
| STRING | optional | n/a | n/a | declared length of variable |
| RAW | optional | n/a | n/a | declared length of variable |
| ROWID | n/a | n/a | n/a | 13 bytes (see note 2) |
| UNSIGNED | optional (1, 2, or 4) | n/a | n/a | declared length of variable |
| VARCHAR | required | n/a | n/a | none |
| VARCHAR2 | optional | n/a | n/a | declared length of variable |
| VARNUM | n/a | n/a | n/a | 22 bytes |
| VARRAW | optional | n/a | n/a | none |
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 VAR 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.
| VARNUM Value | ||||
|---|---|---|---|---|
| Decimal Value | Length Byte | Exponent Byte | Mantissa Bytes | Terminator Byte |
| 0 | 1 | 128 | n/a | n/a |
| 5 | 2 | 193 | 6 | n/a |
| -5 | 3 | 62 | 96 | 102 |
| 2767 | 3 | 194 | 28, 68 | n/a |
| -2767 | 4 | 61 | 74, 34 | 102 |
| 100000 | 2 | 195 | 11 | n/a |
| abcdefg | 5 | 196 | 2, 24, 46, 68 | n/a |
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.
Although the widely-used 7- or 8-bit ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, some Asian languages, such as Japanese, contain thousands of characters. These languages require 16 bits (two bytes) to represent each character. How does Oracle deal with such dissimilar languages?
Oracle provides National Language Support (NLS), which lets you process single-byte and multi-byte character data and convert between character sets. It also lets your applications run in different language environments. With NLS, number and date formats adapt automatically to the language conventions specified for a user session. Thus, NLS allows users around the world to interact with Oracle in their native languages.
You control the operation of language-dependent features by specifying various NLS parameters. You can set default parameter values in the Oracle initialization file. Table 3 - 9 shows what each NLS parameter specifies.
| NLS Parameter | Specifies ... |
|---|---|
| NLS_LANGUAGE | language-dependent conventions |
| NLS_TERRITORY | territory-dependent conventions |
| NLS_DATE_FORMAT | date format |
| NLS_DATE_LANGUAGE | language for day and month names |
| NLS_NUMERIC_CHARACTERS | decimal character and group separator |
| NLS_CURRENCY | local currency symbol |
| NLS_ISO_CURRENCY | ISO currency symbol |
| NLS_SORT | sort sequence |
The main parameters are NLS_LANGUAGE and NLS_TERRITORY. NLS_LANGUAGE specifies the default values for language-dependent features, which include
NLS_TERRITORY specifies the default values for territory-dependent features, which include
You can control the operation of language-dependent NLS features for a user session by specifying the parameter NLS_LANG as follows
NLS_LANG = <language>_<territory>.<character set>
where language specifies the value of NLS_LANGUAGE for the user session, territory specifies the value of NLS_TERRITORY, and character set specifies the encoding scheme used for the terminal. An encoding scheme (usually called a character set or code page) is a range of numeric codes that corresponds to the set of characters a terminal can display. It also includes codes that control communication with the terminal.
You define NLS_LANG as an environment variable (or the equivalent on your system). For example, on UNIX using the C shell, you might define NLS_LANG as follows:
setenv NLS_LANG French_France.WE8ISO8859P1
To change the values of NLS parameters during a session, you use the ALTER SESSION statement as follows:
ALTER SESSION SET <nls_parameter> = <value>
The Oracle Precompilers fully support all the NLS features that allow your applications to process multilingual data stored in an Oracle database. For example, you can declare foreign-language character variables and pass them to string functions such as INSTRB, LENGTHB, and SUBSTRB. These functions have the same syntax as the INSTR, LENGTH, and SUBSTR functions, respectively, but operate on a per-byte basis rather than a per-character basis.
You can use the functions NLS_INITCAP, NLS_LOWER, and NLS_UPPER to handle special instances of case conversion. And, you can use the function NLSSORT to specify WHERE-clause comparisons based on linguistic rather than binary ordering. You can even pass NLS parameters to the TO_CHAR, TO_DATE, and TO_NUMBER functions. For more information about NLS, see the Oracle7 Server Application Developer's Guide.
The Pro*COBOL Precompiler extends support for multi-byte NLS character sets through
The current release (Oracle7 Server 7.3 with Pro*COBOL 1.8) supports multi-byte strings through the precompiler runtime library, SQLLIB.
A multi-byte NLS character string in an embedded SQL statement consists of a character literal that identifies the string as a multi-byte 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 multi-byte character string, since the N character literal preceding the string "Kuroda" identifies it as a multi-byte string.
Since dynamic SQL statements are not processed at precompile time, and since the Oracle7 Server, Release 7.3 does not itself process multi-byte NLS strings, you cannot embed multi-byte NLS strings in dynamic SQL statements.
Columns storing multi-byte NLS 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 multi-byte character data. Variables declared using the PIC N clause are recognized as string variables of double-byte characters.
For more information about these options, see Chapter 6.
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 multi-byte NLS characters. A runtime error is generated if data with an odd number of bytes is FETCHed from a single-byte column into a multi-byte NLS (PIC N) host variable.
No Host Variable Equivalencing. Multi-byte NLS character variables cannot be equivalenced using an EXEC SQL VAR statement.
No Dynamic SQL. Dynamic SQL is not available for NLS multi-byte character string host variables in Pro*COBOL.
When a Pro*COBOL character variable is defined as a multi-byte NLS variable, the following blank padding and blank stripping rules apply, depending on the external datatype of the variable. See the section "External Datatypes'' and in the Pro*COBOL Supplement to the Oracle Precompilers Guide.
CHARF. This is the default character type when a multi-byte 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 NLS data, since they can only be specified using dynamic SQL or datatype equivalencing, neither of which is supported for NLS data.
You can use indicator variables with multi-byte NLS 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 "Using Indicator Variables" .
Your host program must log on to Oracle before querying or manipulating data. To log on, simply use the following CONNECT statement:
EXEC SQL CONNECT :username IDENTIFIED BY :password;
Or, you can use the statement
EXEC SQL CONNECT :usr_pwd;
where usr_pwd contains username/password (include the slash).
The CONNECT statement must be the first executable SQL statement in the program. Only declarative SQL statements and host language code can logically precede the CONNECT statement.
To supply the Oracle username and password separately, you must define two host variables in the Declare Section as character strings. If you supply a userid containing both username and password, only one host variable is needed.
Make sure to set the username and password variables before the CONNECT is executed, or it will fail. You can hardcode the values into your program or have the program prompt for them, as follows:
EXEC SQL BEGIN DECLARE SECTION;
username CHARACTER(20);
password CHARACTER(20);
EXEC SQL END DECLARE SECTION;
display 'Username? ';
read username;
display 'Password? ':
read password;
-- handle processing errors
EXEC SQL WHENEVER SQLERROR DO sql_error;
-- connect to local database
EXEC SQL CONNECT :username IDENTIFIED BY :password
...
ROUTINE sql_error
BEGIN
...
exit program with an error;
END sql_error;
Some of the examples in this and the following section might use SQL*Net V1 connection strings to show network protocols and server names. To connect using a SQL*Net V2 driver, substitute a service name, as defined in your tnsnames.ora configuration file or in Oracle Names, in place of the SQL*Net V1 connect string.
If you are using Oracle Names, the name server obtains the service name from the network definition database.
See Understanding SQL*Net for more information about SQL*Net Version 2.
You can log on to Oracle automatically with the userid:
<prefix><username>
where prefix is the value of the Oracle initialization parameter OS_AUTHENT_PREFIX (the default value is OPS$) and username is your operating system user or task name. For example, if the prefix is OPS$, your user name is TBARNES, and OPS$TBARNES is a valid Oracle userid, you log on to Oracle as user OPS$TBARNES.
To take advantage of the automatic logon feature, you simply pass a slash (/) character to the precompiler, as follows:
EXEC SQL BEGIN DECLARE SECTION;
oracleid CHARACTER(1);
EXEC SQL END DECLARE SECTION;
...
set oracleid = '/';
EXEC SQL CONNECT :oracleid;
This automatically connects you as user OPS$username. For example, if your operating system username is RHILL, and OPS$RHILL is a valid Oracle username, connecting with a slash (/) automatically logs you on to Oracle as user OPS$RHILL.
You can also pass a character string to the precompiler. However, the string cannot contain trailing blanks. For example, the following CONNECT statement will fail:
EXEC SQL BEGIN DECLARE SECTION;
oracleid CHARACTER(5);
EXEC SQL END DECLARE SECTION;
...
set oracleid = '/ ';
EXEC SQL CONNECT :oracleid;
To increase compatibility with other precompilers, the Oracle Precompilers let your program log on to the default database without using the CONNECT statement. Simply specify the precompiler option AUTO_CONNECT on the command line.
Assume that the default value of OS_AUTHENT_PREFIX is OPS$, your username is TBARNES, and OPS$TBARNES is a valid Oracle userid. When AUTO_CONNECT=YES, as soon as the precompiler encounters an executable SQL statement, your program logs on to Oracle automatically with the userid OPS$TBARNES.
When AUTO_CONNECT=NO (the default), you must use the CONNECT statement to log on to Oracle.
The Oracle Precompilers support distributed processing via SQL*Net. Your application can concurrently access any combination of local and remote databases or make multiple connections to the same database. In Figure 3 - 2, an application program communicates with one local and three remote Oracle databases. ORA2, ORA3, and ORA4 are simply logical names used in CONNECT statements.
Text description of the illustration image007.gif. Figure 3 - 2. Connecting via SQL*Net
By eliminating the boundaries in a network between different machines and operating systems, SQL*Net provides a distributed processing environment for Oracle tools. This section shows you how the Oracle Precompilers support distributed processing via SQL*Net. You learn how your application can
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.US.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 non-default database at any local or remote node. SQL statements without an AT clause are executed against the default connection. Conversely, a non-default connection is made by a CONNECT statement that has an AT clause. A SQL statement with an AT clause is executed against the non-default 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 non-default 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 non-default database
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT db_name USING :db_string;
The identifiers in this example serve the following purposes:
The USING clause specifies the network, machine, 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 non-default 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 non-default 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 allows you to 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 non-default connection. An example follows:
EXEC SQL AT :db_name EXECUTE IMMEDIATE :slq_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 non-default 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 slq_stmt STATEMENT; EXEC SQL PREPARE slq_stmt FROM :sql_string; EXEC SQL EXECUTE slq_stmt;
The following example shows Method 3:
EXEC SQL AT :db_name DECLARE slq_stmt STATEMENT; EXEC SQL PREPARE slq_stmt FROM :sql_string; EXEC SQL DECLARE emp_cursor CURSOR FOR slq_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 non-default 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 non-default 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 non-default 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 non-default 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 non-default 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 non-default database
-- get next database name
display 'Database Name? ';
read db_name;
-- connect to the non-default 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 non-default 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 non-default database(s) to get the required data.
In the next example, you connect to a single non-default 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 non-default 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 non-default database is located, the path to it, and what 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 non-default database via SQL*Net using the database link db_link. The query is submitted to the default server, but is "forwarded" to the non-default 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 non-default 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 non-default 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 non-default 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 non-default EMP table at db_link1 and the non-default DEPT table at db_link2.
The Oracle Precompilers let you embed OCI calls in your host program. Just take the following steps:
That way, the Oracle Precompiler and the OCI "know" that they are working together. However, there is no sharing of Oracle cursors.
You need not worry about declaring the OCI Host Data Area (HDA) because the Oracle runtime library manages connections and maintains the HDA for you.
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. See the Programmer's Guide to the Oracle Call Interface. 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 non-default 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 non-default 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 non-default 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 non-default nodes so that later SQL statements can refer to the databases by name.
X/Open applications run in a distributed transaction processing (DTP) environment. In an abstract model, an X/Open application calls on resource managers (RMs) to provide a variety of services. For example, a database resource manager provides access to data in a database. Resource managers interact with a transaction manager (TM), which controls all transactions for the application.
Figure 3 - 3 shows one way that components of the DTP model can interact to provide efficient access to data in an Oracle database. The DTP model specifies the XA interface between resource managers and the transaction manager. Oracle supplies an XA-compliant library, which you must link to your X/Open application. Also, you must specify the native interface between your application program and the resource managers.
Text description of the illustration image008.gif. Figure 3 - 3. Hypothetical DTP Model
The DTP model that specifies how a transaction manager and resource managers interact with an application program is described in the X/Open guide Distributed Transaction Processing Reference Model and related publications, which you can obtain by writing to
For instructions on using the XA interface, see your Transaction Processing (TP) Monitor user's guide.
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 runtime library routine SQLLD2, which sets up an LDA for a specified connection established through the XA interface. For a description of the SQLLD2 call, see the Programmer's Guide to the Oracle Call 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.
|
 Copyright © 1996, 2001 Oracle Corporation All Rights Reserved. |
|