Oracle8 SQL Reference Release 8.0 A58225-01 |
Library | Product | Contents | Index

# 2
Elements of Oracle8 SQL

This chapter contains reference information on the basic elements of Oracle SQL.

Note: Commands and descriptions preceded by are available only if the Oracle objects option is installed on your database server.

Before using any of the commands described in Chapter 4, "Commands", you should familiarize yourself with the concepts covered in this chapter:

- Literals
- Text
- Integer
- Number
- Datatypes
- Nulls
- Pseudocolumns
- Comments
- Database Objects
- Schema Object Names and Qualifiers
- Referring to Schema Objects and Parts

## Literals

The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JACK', 'BLUE ISLAND', and '101' are all character literals; 5001 is a numeric literal. Note that character literals are enclosed in single quotation marks, which enable Oracle to distinguish them from schema object names.

Many SQL statements and functions require you to specify character and numeric literal values. You can also specify literals as part of expressions and conditions. You can specify character literals with the 'text' notation, national character literals with the N'text' notation, and numeric literals with the integer or number notation, depending on the context of the literal. The syntactic forms of these notations appear in the following sections.

## Text

Text specifies a text or character literal. You must use this notation to specify values whenever 'text' or char appear in expressions, conditions, SQL functions, and SQL commands in other parts of this reference.

The syntax of text is as follows:

text::=

where

N | specifies representation of the literal using the national character set. Text entered using this notation is translated into the national character set by Oracle when used.
c | is any member of the user's character set, except a single quotation mark (').
' ' | are two single quotation marks that begin and end text literals. To represent one single quotation mark within a literal, enter two single quotation marks.

A text literal must be enclosed in single quotation marks. This reference uses the terms text literal and character literal interchangeably.

Text literals have properties of both the CHAR and VARCHAR2 datatypes:

- Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.
- A text literal can have a maximum length of 4000 bytes.

Here are some valid text literals:

    'Hello'
    'ORACLE.dbs'
    'Jackie''s raincoat'
    '09-MAR-92'
    N'nchar literal'

For more information, see the syntax description of expr in "Expressions".

## Integer

You must use the integer notation to specify an integer whenever integer appears in expressions, conditions, SQL functions, and SQL commands described in other parts of this reference.

The syntax of integer is as follows:

integer::=

where

digit | is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

An integer can store a maximum of 38 digits of precision.

Here are some valid integers:

    7
    +255

For more information, see the syntax description of expr in "Expressions".

## Number

You must use the number notation to specify values whenever number appears in expressions, conditions, SQL functions, and SQL commands in other parts of this reference.

The syntax of number is as follows:

number::=

where

+, - | indicates a positive or negative value. If you omit the sign, a positive value is the default.
digit | is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.
e, E | indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.

A number can store a maximum of 38 digits of precision.

If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, you must specify numeric literals with 'text' notation. In such cases, Oracle automatically converts the text literal to a numeric value.

For example, if the NLS_NUMERIC_CHARACTERS parameter specifies a decimal character of comma, specify the number 5.123 as follows:

    '5,123'

For more information on this parameter, see Oracle8 Reference.

Here are some valid representations of number:

    25
    +6.34
    0.5
    25e-03
    -1

For more information, see the syntax description of expr in "Expressions".

## Datatypes

Each literal or column value manipulated by Oracle has a datatype. A value's datatype associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another. For example, you can add values of NUMBER datatype, but not values of RAW datatype.

When you create a table or cluster, you must specify an internal datatype for each of its columns. When you create a procedure or stored function, you must specify an internal datatype for each of its arguments. These datatypes define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a column assumes the column's datatype. For example, if you insert '01-JAN-92' into a DATE column, Oracle treats the '01-JAN-92' character string as a DATE value after verifying that it translates to a valid date.

Table 2-1 summarizes Oracle internal datatypes. The rest of this section describes these datatypes in detail.

Note: The Oracle precompilers recognize other datatypes in embedded SQL programs. These datatypes are called external datatypes and are associated with host variables. Do not confuse the internal datatypes with external datatypes. For information on external datatypes, including how Oracle converts between internal and external datatypes, see Pro*COBOL Precompiler Programmer's Guide, Pro*C/C++ Precompiler Programmer's Guide, and SQL*Module for Ada Programmer's Guide.

##### Table 2-1 Internal Datatype Summary

Code | Internal Datatype | Description
--- | --- | ---
1 | `VARCHAR2(`size`)` | Variable-length character string having maximum length size bytes. Maximum size is 4000, and minimum is 1. You must specify size for a VARCHAR2.
| `NVARCHAR2(`size`)` | Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
2 | `NUMBER(`p,s`)` | Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
8 | `LONG` | Character data of variable length up to 2 gigabytes, or 231 -1 bytes.
12 | `DATE` | Valid date range from January 1, 4712 BC to December 31, 4712 AD.
23 | `RAW(`size`)` | Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
24 | `LONG RAW` | Raw binary data of variable length up to 2 gigabytes.
69 | `ROWID` | Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
96 | `CHAR(`size`)` | Fixed length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.
| `NCHAR(`size`)` | Fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.
106 | `MLSLABEL` | Binary format of an operating system label. This datatype is used for backward compatibility with Trusted Oracle.
112 | `CLOB` | A character large object containing single-byte characters. Variable-width character sets are not supported. Maximum size is 4 gigabytes.
| `NCLOB` | A character large object containing fixed-width multibyte characters. Variable-width character sets are not supported. Maximum size is 4 gigabytes. Stores national character set data.
113 | `BLOB` | A binary large object. Maximum size is 4 gigabytes.
114 | `BFILE` | Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

The codes listed for the datatypes are used internally by Oracle. The datatype code of a column or object attribute is returned when you use the DUMP function.

### Character Datatypes

Character datatypes store character (alphanumeric) data-words and free-form text-in the database or national character set. They are less restrictive than other datatypes and consequently have fewer properties. For example, character columns can store all alphanumeric values, but NUMBER columns can store only numeric values.

Character data is stored in strings with byte values corresponding to one of the character sets, such as 7-bit ASCII or EBCDIC Code Page 500, specified when the database was created. Oracle supports both single-byte and multibyte character sets.

These datatypes are used for character data:

- CHAR Datatype
- NCHAR Datatype
- NVARCHAR2 Datatype
- VARCHAR2 Datatype

#### CHAR Datatype

The CHAR datatype specifies a fixed-length character string. When you create a table with a CHAR column, you supply the column length in bytes. Oracle subsequently ensures that all values stored in that column have this length. If you insert a value that is shorter than the column length, Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, Oracle returns an error.

The default length for a CHAR column is 1 character and the maximum allowed is 2000 characters. A zero-length string can be inserted into a CHAR column, but the column is blank-padded to 1 character when used in comparisons. For information on comparison semantics, see "Datatype Comparison Rules".

#### NCHAR Datatype

The NCHAR datatype specifies a fixed-length national character set character string. When you create a table with an NCHAR column, you define the column length either in characters or in bytes. You define the national character set when you create your database.

If the national character set specified is fixed width, such as JA16EUCFIXED, then you declare the NCHAR column size as the number of characters desired for the string length. If the national character set is variable width, such as JA16SJIS, you declare the column size in bytes. The following statement creates a table with one NCHAR column that can store strings up to 30 characters in length using JA16EUCFIXED as the national character set:

    CREATE TABLE tab1 (col1 NCHAR(30));

The column's maximum length is determined by the national character set definition. Width specifications of character datatype NCHAR refer to the number of characters if the national character set is fixed width and refer to the number of bytes if the national character set is variable width. The maximum column size allowed is 2000 bytes. For fixed-width, multibyte character sets, the maximum length of a column allowed is the number of characters that fit into no more than 2000 bytes.

If you insert a value that is shorter than the column length, Oracle blank-pads the value to column length. You cannot insert a CHAR value into an NCHAR column, nor can you insert an NCHAR value into a CHAR column.

The following example compares the `col1` column of `tab1` with national character set string NCHAR literal:

    SELECT * FROM tab1 WHERE col1 = N'NCHAR literal';

You cannot create an object with NCHAR attributes, but you can specify NCHAR parameters in methods.

#### NVARCHAR2 Datatype

The NVARCHAR2 datatype specifies a variable-length national character set character string. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters or bytes that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided it does not exceed the column's maximum length.

The column's maximum length is determined by the national character set definition. Width specifications of character datatype NVARCHAR2 refer to the number of characters if the national character set is fixed width and refer to the number of bytes if the national character set is variable width. The maximum column size allowed is 4000 bytes. For fixed-width, multibyte character sets, the maximum length of a column allowed is the number of characters that fit into no more than 4000 bytes.

The following statement creates a table with one NVARCHAR2 column of 2000 characters in length (stored as 4000 bytes, because each character takes two bytes) using JA16EUCFIXED as the national character set:

    CREATE TABLE tab1 (col1 NVARCHAR2(2000));

You cannot create an object with NVARCHAR2 attributes, but you can specify NVARCHAR2 parameters in methods.

#### VARCHAR2 Datatype

The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you can supply the maximum number of bytes of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided it does not exceed the column's maximum length. This maximum must be at least 1 byte, although the actual length of the string stored is permitted to be zero. If you try to insert a value that exceeds the specified length, Oracle returns an error.

You must specify a maximum length for a VARCHAR2 column. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics. For information on comparison semantics, see "Datatype Comparison Rules".

#### VARCHAR Datatype

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics.

### NUMBER Datatype

The NUMBER datatype stores zero, positive and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, Oracle returns an error.

Specify a fixed-point number using the following form:

    NUMBER(p,s)

where:

s | is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

Specify an integer using the following form:

`NUMBER(p)` | is a fixed-point number with precision p and scale 0. This is equivalent to NUMBER(p,0).

Specify a floating-point number using the following form:

`NUMBER` | is a floating-point number with decimal precision 38. Note that a scale value is not applicable for floating-point numbers. (See "Floating-Point Numbers" for more information.)

#### Scale and Precision

Specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, Oracle returns an error. If a value exceeds the scale, Oracle rounds it.

The following examples show how Oracle stores data using different precisions and scales.

7456123.89 | NUMBER | 7456123.89
7456123.89 | NUMBER(9) | 7456124
7456123.89 | NUMBER(9,2) | 7456123.89
7456123.89 | NUMBER(9,1) | 7456123.9
7456123.89 | NUMBER(6) | exceeds precision
7456123.89 | NUMBER(7,-2) | 7456100
7456123.89 | NUMBER(-7,2) | exceeds precision

#### Negative Scale

If the scale is negative, the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.

#### Scale Greater than Precision

You can specify a scale that is greater than precision, although it is uncommon. In this case, the precision specifies the maximum number of digits to the right of the decimal point. As with all number datatypes, if the value exceeds the precision, Oracle returns an error message. If the value exceeds the scale, Oracle rounds the value. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point. The following examples show the effects of a scale greater than precision:

Actual Data | Specified As | Stored As
.01234 | NUMBER(4,5) | .01234
.00012 | NUMBER(4,5) | .00012
.000127 | NUMBER(4,5) | .00013
.0000012 | NUMBER(2,7) | .0000012
.00000123 | NUMBER(2,7) | .0000012

#### Floating-Point Numbers

Oracle allows you to specify floating-point numbers, which can have a decimal point anywhere from the first to the last digit or can have no decimal point at all. A scale value is not applicable to floating-point numbers, because the number of digits that can appear after the decimal point is not restricted.

You can specify floating-point numbers with the form discussed in "NUMBER Datatype". Oracle also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:

`FLOAT` | specifies a floating-point number with decimal precision 38, or binary precision 126.
`FLOAT(`b`)` | specifies a floating-point number with binary precision b. The precision b can range from 1 to 126. To convert from binary to decimal precision, multiply b by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

### LONG Datatype

LONG columns store variable length character strings containing up to 2 gigabytes, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. Oracle uses LONG columns in the data dictionary to store the text of view definitions. The length of LONG values may be limited by the memory available on your computer.

You can reference LONG columns in SQL statements in these places:

- SELECT lists
- SET clauses of UPDATE statements
- VALUES clauses of INSERT statements

The use of LONG values are subject to some restrictions:

- A table cannot contain more than one LONG column.
- LONG columns cannot appear in integrity constraints (except for NULL and NOT NULL constraints).
- LONG columns cannot be indexed.
- A stored function cannot return a LONG value.
- Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

LONG columns cannot appear in certain parts of SQL statements:

- WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
- the UNIQUE clause of a SELECT statement
- the column list of a CREATE CLUSTER statement
- the CLUSTER clause of a CREATE SNAPSHOT statement
- SQL functions (such as SUBSTR or INSTR)
- expressions or conditions
- SELECT lists of queries containing GROUP BY clauses
- SELECT lists of subqueries or queries combined by set operators
- SELECT lists of CREATE TABLE ... AS SELECT statements
- SELECT lists in subqueries in INSERT statements

Triggers can use the LONG datatype in the following manner:

- A SQL statement within a trigger can insert data into a LONG column.
- If data from a LONG column can be converted to a constrained datatype (such as CHAR and VARCHAR2), a LONG column can be referenced in a SQL statement within a trigger. Note that the maximum length for these datatypes is 32K.
- Variables in triggers cannot be declared using the LONG datatype.
- :NEW and :OLD cannot be used with LONG columns.

You can use the Oracle Call Interface functions to retrieve a portion of a LONG value from the database. See Oracle Call Interface Programmer's Guide.

### DATE Datatype

The DATE datatype stores date and time information. Although date and time information can be represented in both CHAR and NUMBER datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, day, hour, minute, and second.

To specify a date value, you must convert a character or numeric value to a date value with the TO_DATE function. Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions. The default date format is specified by the initialization parameter NLS_DATE_FORMAT and is a string such as 'DD-MON-YY'. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year.

If you specify a date value without a time component, the default time is 12:00:00 am (midnight). If you specify a date value without a date, the default date is the first day of the current month.

The date function SYSDATE returns the current date and time. For information on the SYSDATE and TO_DATE functions and the default date format, see Chapter 3, "Operators, Functions, Expressions, Conditions".

#### Date Arithmetic

You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values.

Oracle provides functions for many of the common date operations. For example, the ADD_MONTHS function allows you to add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month. For more information on date functions, see "Date Functions".

Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours.

#### Using Julian Dates

A Julian date is the number of days since January 1, 4712 bc. Julian dates allow continuous dating from a common reference. You can use the date format model "J" with date functions TO_DATE and TO_CHAR to convert between Oracle DATE values and their Julian equivalents.

##### Example

This statement returns the Julian equivalent of January 1, 1997:

    SELECT TO_CHAR(TO_DATE('01-01-1997', 'MM-DD-YYYY'),'J')
        FROM DUAL;

    TO_CHAR
    --------
    2450450

For a description of the DUAL table, see "Selecting from the DUAL Table".

### RAW and LONG RAW Datatypes

The RAW and LONG RAW datatypes store data that is not to be interpreted (not explicitly converted when moving data between different systems) by Oracle. These datatypes are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data; the interpretation is dependent on the use.

RAW is a variable-length datatype like the VARCHAR2 character datatype, except that Net8 (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, Net8 and Import/Export automatically convert CHAR, VARCHAR2, and LONG data from the database character set to the user session character set (set by the NLS_LANGUAGE parameter of the ALTER SESSION command), if the two character sets are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.

You can index RAW data, but not LONG RAW data.

### Large Object (LOB) Datatypes

Internal LOB datatypes-BLOB, CLOB, NCLOB-and external datatype BFILE, can store large and unstructured data such as text, image, video, and spatial data up to 4 gigabytes in size.

When creating a table, you can optionally specify different tablespace and storage characteristics for internal LOB columns or internal LOB object attributes from those specified for the table.

Internal LOB columns contain LOB locators that can refer to out-of-line or in-line LOB values. Selecting a LOB from a table actually returns the LOB's locator and not the entire LOB value. The DBMS_LOB package and OCI operations on LOBs are performed through these locators. For more information about these interfaces and LOBs, see Oracle8 Application Developer's Guide and Oracle Call Interface Programmer's Guide.

LOBs are similar to LONG and LONG RAW types, but differ in the following ways:

- LOBs can be attributes of a user-defined datatype (object).
- The LOB locator is stored in the table column, either with or without the actual LOB value; BLOB, NCLOB, and CLOB values can be stored in separate tablespaces and BFILE data is stored in an external file on the server.
- When you access a LOB column, the locator is returned.
- A LOB can be up to 4 gigabytes in size. BFILE maximum size is operating system dependent, but cannot exceed 4 gigabytes.
- LOBs permit efficient, random, piece-wise access to and manipulation of data.
- You can define more than one LOB column in a table.
- With the exception of NCLOB, you can define one or more LOB attributes in an object.
- You can declare LOB bind variables.
- You can select LOB columns and LOB attributes.
- You can insert a new row or update an existing row that contains one or more LOB columns and/or an object with one or more LOB attributes. (You can set the internal LOB value to NULL, empty, or replace the entire LOB with data. You can set the BFILE to NULL or so that it points to a different file.)
- You can update a LOB row/column intersection or a LOB attribute with another LOB row/column intersection or LOB attribute.
- You can delete a row containing a LOB column or LOB attribute and thereby also delete the LOB value. Note that for BFILEs, the actual operating system file is not deleted.

For more information, please refer to the discussion of LOB restrictions in Oracle8 Application Developer's Guide.

To access and populate rows of an internal LOB column, use the INSERT statement first to initialize the internal LOB value to empty. Once the row is inserted, you can select the empty LOB and populate it using the DBMS_LOB package or the OCI.

The following example creates a table with LOB columns:

    CREATE TABLE  person_table (name CHAR(40),
                                resume  CLOB,
                                picture BLOB)
      LOB (resume) STORE AS
                   ( TABLESPACE resumes
                      STORAGE (INITIAL 5M NEXT 5M) );

Use LOBs to read and write large chunks of the LOB value.

#### BFILE Datatype

The BFILE datatype enables access to binary file LOBs that are stored in file systems outside the Oracle database. A BFILE column or attribute stores a BFILE locator, which serves as a pointer to a binary file on the server's file system. The locator maintains the directory alias and the filename. See CREATE DIRECTORY.

Binary file LOBs do not participate in transactions. Rather, the underlying operating system provides file integrity and durability. The maximum file size supported is 4 gigabytes.

The database administrator must ensure that the file exists and that Oracle processes have operating system read permissions on the file.

The BFILE datatype allows read-only support of large binary files; you cannot modify a file. Oracle provides APIs to access file data. The primary interfaces that you use to access file data are the DBMS_LOB package and the OCI. For more information about LOBs, see Oracle8 Application Developer's Guide and Oracle Call Interface Programmer's Guide.

#### BLOB Datatype

The BLOB datatype stores unstructured binary large objects. BLOBs can be thought of as bitstreams with no character set semantics. BLOBs can store up to 4 gigabytes of binary data.

BLOBs have full transactional support; changes made through SQL, the DBMS_LOB package, or the OCI participate fully in the transaction. The BLOB value manipulations can be committed or rolled back. Note, however, that you cannot save a BLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.

#### CLOB Datatype

The CLOB datatype stores single-byte character large object data. Variable-width character sets are not supported. CLOBs can store up to 4 gigabytes of character data.

CLOBs have full transactional support; changes made through SQL, the OCI, or the DBMS_LOB package participate fully in the transaction. The CLOB value manipulations can be committed or rolled back. Note, however, that, you cannot save a CLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.

#### NCLOB Datatype

The NCLOB datatype stores fixed-width, multibyte national character set character (NCHAR) data. Variable-width character sets are not supported. NCLOBs can store up to 4 gigabytes of character text data.

NCLOBs have full transactional support; changes made through SQL, the DBMS_LOB package, or the OCI participate fully in the transaction. NCLOB value manipulations can be committed or rolled back. Note, however, that you cannot save a NCLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.

You cannot create an object with NCLOB attributes, but you can specify NCLOB parameters in methods.

### ROWID Datatype

Each row in the database has an address. You can examine a row's address by querying the pseudocolumn ROWID. Values of this pseudocolumn are hexadecimal strings representing the address of each row. These strings have the datatype ROWID. For more information on the ROWID pseudocolumn, see "Pseudocolumns". You can also create tables and clusters that contain actual columns having the ROWID datatype. Oracle does not guarantee that the values of such columns are valid ROWIDs.

#### Restricted ROWIDs

Oracle8 incorporates an extended format for ROWIDs to efficiently support partitioned tables and indexes and tablespace-relative data block addresses (DBAs) without ambiguity.

Character values representing ROWIDs in Oracle7 and earlier releases are as follows:

    block.row.file

where:

block | is a hexadecimal string identifying the data block of the datafile containing the row. The length of this string depends on your operating system.
row | is a four-digit hexadecimal string identifying the row in the data block. The first row of the block has a digit of 0.
file | is a hexadecimal string identifying the database file containing the row. The first datafile has the number 1. The length of this string depends on your operating system.

In Oracle8, this kind of ROWID is called a restricted ROWID.

#### Extended ROWIDs

The Oracle8 extended ROWID datatype stored in a user column includes the data in the restricted ROWID plus a data object number. The data object number is an identification number assigned to every database segment. You can retrieve the data object number from data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number.

Extended ROWIDs are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended ROWID contents. The package functions extract and provide information that would be available directly from a restricted ROWID. You can use functions from the DBMS_ROWID package as you would any built-in SQL function. Table 2-2 lists the functions and procedures in the DBMS_ROWID package. For more information on DBMS_ROWID, see Oracle8 Application Developer's Guide.

##### Table 2-2 DBMS_ROWID Functions

Function Name | Description
--- | ---
ROWID_CREATE | Create a ROWID, for testing only.
ROWID_TYPE | Returns the ROWID type: 0 is restricted, 1 is extended.
ROWID_OBJECT | Returns the object number of the extended ROWID.
ROWID_RELATIVE_FNO | Returns the file number of a ROWID.
ROWID_BLOCK_NUMBER | Returns the block number of a ROWID.
ROWID_ROW_NUMBER | Returns the row number.
ROWID_TO_ABSOLUTE_ FNO | Returns the absolute file number associated with the ROWID for a row in a specific table.
ROWID_TO_EXTENDED | Converts a ROWID from restricted format to extended.
ROWID_TO_RESTRICTED | Converts an extended ROWID to restricted format.
ROWID_VERIFY | Checks if a ROWID can be correctly extended by the ROWID_TO_EXTENDED function.

#### Compatibility and Migration

The restricted form of ROWID is still supported in Oracle8 for backward compatibility, but all tables return ROWIDs in the extended format. For information regarding compatibility and migration issues, see Oracle8 Migration.

### MLSLABEL Datatype

The MLSLABEL datatype stores the binary format of a label used on a secure operating system. This datatype is supported in Oracle8 for backward compatibility with earlier versions of Oracle servers using Trusted Oracle.

Labels are used by Trusted Oracle to mediate access to information. You can also define columns with this datatype if you are using the standard Oracle server. For more information on Trusted Oracle, including this datatype and labels, see your Trusted Oracle documentation.

### ANSI, DB2, and SQL/DS Datatypes

SQL commands that create tables and clusters can also use ANSI datatypes and datatypes from IBM's products SQL/DS and DB2. Oracle recognizes the ANSI or IBM datatype name and records it as the name of the datatype of the column, and then stores the column's data in an Oracle datatype based on the conversions shown in Table 2-3 and Table 2-4.

##### Table 2-3 ANSI Datatypes Converted to Oracle Datatypes

ANSI SQL Datatype | Oracle Datatype
CHARACTER(n) CHAR(n) | CHAR(n)
CHARACTER VARYING(n) CHAR VARYING(n) | VARCHAR(n)
NATIONAL CHARACTER(n) NATIONAL CHAR(n) NCHAR(n) | NCHAR(n)
NATIONAL CHARACTER VARYING(n) NATIONAL CHAR VARYING(n) NCHAR VARYING(n) | NVARCHAR2(n)
NUMERIC(p,s) DECIMAL(p,s)a | NUMBER(p,s)
INTEGER INT SMALLINT | NUMBER(38)
FLOAT(b)b DOUBLE PRECISIONc REALd | NUMBER
aThe NUMERIC and DECIMAL datatypes can specify only fixed-point numbers. For these datatypes, s defaults to 0. bThe FLOAT datatype is a floating-point number with a binary precision b. The default precision for this datatype is 126 binary, or 38 decimal. cThe DOUBLE PRECISION datatype is a floating-point number with binary precision 126. dThe REAL datatype is a floating-point number with a binary precision of 63, or 18 decimal.

##### Table 2-4 SQL/DS and DB2 Datatypes Converted to Oracle Datatypes

SQL/DS or DB2 Datatype | Oracle Datatype
CHARACTER(n) | CHAR(n)
VARCHAR(n) | VARCHAR(n)
LONG VARCHAR(n) | LONG
DECIMAL(p,s)a | NUMBER(p,s)
INTEGER SMALLINT | NUMBER(38)
FLOAT(b)b | NUMBER
aThe DECIMAL datatype can specify only fixed-point numbers. For this datatype, s defaults to 0. bThe FLOAT datatype is a floating-point number with a binary precision b. This default precision for this datatype is 126 binary, or 38 decimal.

Do not define columns with these SQL/DS and DB2 datatypes, because they have no corresponding Oracle datatype:

- GRAPHIC
- LONG VARGRAPHIC
- VARGRAPHIC
- TIME
- TIMESTAMP

Note that data of type TIME and TIMESTAMP can also be expressed as Oracle DATE data.

### User-Defined Types

User-defined datatypes use Oracle built-in datatypes and other user-defined datatypes as the building blocks of types that model the structure and behavior of data in applications. For information about Oracle built-in datatypes, see Oracle8 Concepts. For information about creating user-defined types, see CREATE TYPE and the CREATE TYPE BODY. For information about using user-defined types, see Oracle8 Application Developer's Guide.

#### Object Types

Object types are abstractions of the real-world entities-for example, purchase orders-that application programs deal with. An object type is a schema object with three kinds of components:

- A name, which identifies the object type uniquely within that schema
- Attributes, which are built-in types or other user-defined types. Attributes model the structure of the real-world entity.
- Methods, which are functions or procedures written in PL/SQL and stored in the database, or written in a language like C and stored externally. Methods implement operations the application can perform on the real-world entity.

#### REFs

An object identifier (OID) uniquely identifies an object and enables you to reference the object from other objects or from relational tables. A built-in datatype called REF represents such references. A REF is a container for an object identifier. REFs are pointers to objects.

When a REF value points to a nonexistent object, the REF is said to be DANGLING. DANGLING is different from being NULL. To check to see if a REF is dangling or not, use the predicate IS [NOT] DANGLING. For example, given table DEPT with column MGR whose type is a REF to type EMP_T:

    SELECT t.mgr.name
    FROM dept t
    WHERE t.mgr IS NOT DANGLING;

#### VARRAYs

An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.

The number of elements in an array is the size of the array. Oracle arrays are of variable size, which is why they are called VARRAYs. You must specify a maximum size when you declare the array.

When you declare a VARRAY, it does not allocate space. It defines a type, which you can use as

- the datatype of a column of a relational table
- an object type attribute
- a PL/SQL variable, parameter, or function return type

  Note: You cannot specify VARRAY as a column datatype in an index-organized table.

An array object may be stored in line (that is, in the same tablespace as the other data in its row) or out of line, depending on its size.

#### Nested Tables

A nested table type is used to model an unordered set of elements. The elements may be built-in types or user-defined types. You can view a nested table as a single-column table or, if the nested table is an object type, as a multicolumn table, with a column for each attribute of the object type.

A nested table definition does not allocate space. It defines a type, which you can use to declare

- columns of a relational table
- object type attributes
- PL/SQL variables, parameters, and function return values

When a nested table appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single table, which it associates with the enclosing relational or object table.

### Datatype Comparison Rules

This section describes how Oracle compares values of each datatype.

#### Number Values

A larger value is considered greater than a smaller one. All negative numbers are less than zero and all positive numbers. Thus, -1 is less than 100; -100 is less than -1.

#### Date Values

A later date is considered greater than an earlier one. For example, the date equivalent of '29-MAR-1991' is less than that of '05-JAN-1992' and '05-JAN-1992 1:35pm' is greater than '05-JAN-1992 10:09am'.

#### Character String Values

Character values are compared using one of these comparison rules:

- blank-padded comparison semantics
- nonpadded comparison semantics

The following sections explain these comparison semantics. The results of comparing two character values using different comparison semantics may be different. Table 2-5 shows the results of comparing five pairs of character values using each comparison semantic. Usually, the results of blank-padded and nonpadded comparisons are the same.The last comparison in the table illustrates the differences between the blank-padded and nonpadded comparison semantics.

Blank-Padded | Nonpadded
--- | ---
`'ab' > 'aa'` | `'ab' > 'aa'`
`'ab' > 'a '` | `'ab' > 'a '`
`'ab' > 'a'` | `'ab' > 'a'`
`'ab' = 'ab'` | `'ab' = 'ab'`
'a ' = 'a' | 'a ' > 'a'

##### Blank-Padded Comparison Semantics

If the two values have different lengths, Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function.

##### Nonpadded Comparison Semantics

Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the datatype VARCHAR2 or NVARCHAR2.

#### Single Characters

Oracle compares single characters according to their numeric values in the database character set. One character is greater than another if it has a greater numeric value than the other in the character set. Oracle considers blanks to be less than any character, which is true in most character sets.

These are some common character sets:

- 7-bit ASCII (American Standard Code for Information Interchange)
- EBCDIC Code (Extended Binary Coded Decimal Interchange Code) Page 500
- ISO 8859/1 (International Standards Organization)
- JEUC Japan Extended UNIX

Portions of the ASCII and EBCDIC character sets appear in Table 2-6 and Table 2-7. Note that uppercase and lowercase letters are not equivalent. Also, note that the numeric values for the characters of a character set may not match the linguistic sequence for a particular language.

##### Table 2-6 ASCII Character Set

Symbol | Decimal value | Symbol | Decimal value
--- | --- | --- | ---
`blank` | `32` | `;` | `59`
`!` | `33` | `<` | `60`
`"` | `34` | `=` | `61`
`#` | `35` | `>` | `62`
`$` | `36` | `?` | `63`
`%` | `37` | `@` | `64`
`&` | `38` | `A-Z` | `65-90`
`'` | `39` | `[` | `91`
`(` | `40` | `\` | `92`
`)` | `41` | `]` | `93`
`*` | `42` | `^^` | `94`
`+` | `43` | `_` | `95`
`,` | `44` | ``` | `96`
`-` | `45` | `a-z` | `97-122`
`.` | `46` | `{` | `123`
`/` | `47` | `|` | `124`
`0-9` | `48-57` | `}` | `125`
`:` | `58` | `~` | `126`

Symbol | Decimal value | Symbol | Decimal value
--- | --- | --- | ---
`blank` | `64` | `%` | `108`
`¢` | `74` | `_` | `109`
`.` | `75` | `>` | `110`
`<` | `76` | `?` | `111`
`(` | `77` | `:` | `122`
`+` | `78` | `#` | `123`
`|` | `79` | `@` | `124`
`&` | `80` | `'` | `125`
`!` | `90` | `=` | `126`
`$` | `91` | `"` | `127`
`*` | `92` | `a-i` | `129-137`
`)` | `93` | `j-r` | `145-153`
`;` | `94` | `s-z` | `162-169`
`ÿ` | `95` | `A-I` | `193-201`
`-` | `96` | `J-R` | `209-217`
`/` | `97` | `S-Z` | `226-233`

#### Object Values

Object values are compared using one of two comparison functions: MAP and ORDER. Both functions compare object type instances, but they are quite different from one another. These functions must be specified as part of the object type.

A MAP function takes a single object as an argument and returns a scalar value. To compare two objects, the MAP method is applied to each of the objects individually. Then the results are compared. MAP is like a hash function that maps object types into scalars.

An ORDER function takes two objects (object1 and object2, for example) and simply compares one object value to the other. ORDER returns the result +1 if object1 is greater than object2, 0 if equal to, or -1 if less than. Order methods cannot return a NULL value.

Use MAP if you are performing extensive sorting or hash join operations on object instances. MAP is applied once to map the objects to scalar values and then the scalars are used during sorting and merging. A MAP method is more efficient than an ORDER method, which must invoke the method for each object comparison. You must use a MAP method for hash joins and cannot use an ORDER method because the hash mechanism hashes on the object value.

An object specification can contain only one comparison method, which must be a function. You can define either MAP method or ORDER method in a type specification, but not both.

No comparison method needs to be specified to determine the equality of two object types.

See CREATE TYPE and the Oracle8 Application Developer's Guide for more information.

#### VARRAYs and Nested Tables

You cannot compare VARRAYs and nested tables in Oracle8.

### Data Conversion

Generally an expression cannot contain values of different datatypes. For example, an expression cannot multiply 5 by 10 and then add 'JAMES'. However, Oracle supports both implicit and explicit conversion of values from one datatype to another.

#### Implicit Data Conversion

Oracle automatically converts a value from one datatype to another when such a conversion makes sense. Oracle performs conversions in these cases:

- When an INSERT or UPDATE statement assigns a value of one datatype to a column of another, Oracle converts the value to the datatype of the column.
- When you use a SQL function or operator with an argument with a datatype other than the one it accepts, Oracle converts the argument to the accepted datatype.
- When you use a comparison operator on values of different datatypes, Oracle converts one of the expressions to the datatype of the other.

##### Example 1

The text literal '10' has datatype CHAR. Oracle implicitly converts it to the NUMBER datatype if it appears in a numeric expression as in the following statement:

    SELECT sal + '10'
        FROM emp;

##### Example 2

When a condition compares a character value and a NUMBER value, Oracle implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value. In the following statement, Oracle implicitly converts '7936' to 7936:

    SELECT ename
        FROM emp
        WHERE empno = '7936';

##### Example 3

In the following statement, Oracle implicitly converts '12-MAR-1993' to a DATE value using the default date format 'DD-MON-YYYY':

    SELECT ename
        FROM emp
        WHERE hiredate = '12-MAR-1993';

##### Example 4

In the following statement, Oracle implicitly converts the text literal 'AAAAZ8AABAAABvlAAA' to a ROWID value:

    SELECT ename
        FROM emp
        WHERE ROWID = 'AAAAZ8AABAAABvlAAA';

#### Explicit Data Conversion

You can also explicitly specify datatype conversions using SQL conversion functions. Table 2-8 shows SQL functions that explicitly convert a value from one datatype to another.

##### Table 2-8 SQL Functions for Datatype Conversion

TO: FROM: | CHAR | NUMBER | DATE | RAW | ROWID
--- | --- | --- | --- | --- | ---
CHAR | `-` | `TO_NUMBER` | `TO_DATE` | `HEXTORAW` | `CHARTOROWID`
NUMBER | `TO_CHAR` | `-` | `TO_DATE` `(number,'J')` | `` | ``
DATE | `TO_CHAR` | `TO_CHAR` `(date,'J')` | `-` | `` | ``
RAW | `RAWTOHEX` | `` | `` | `-` | ``
ROWID | `ROWIDTOCHAR` | `` | `` | `` | `-`

For information on these functions, see "Conversion Functions".

Note: Table 2-8 does not show conversions from LONG and LONG RAW values because it is impossible to specify LONG and LONG RAW values in cases in which Oracle can perform implicit datatype conversion. For example, LONG and LONG RAW values cannot appear in expressions with functions or operators. For information on the limitations on LONG and LONG RAW datatypes, see "LONG Datatype".

#### Implicit vs. Explicit Data Conversion

Oracle recommends that you specify explicit conversions rather than rely on implicit or automatic conversions for these reasons:

- SQL statements are easier to understand when you use explicit datatype conversions functions.
- Automatic datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.
- Implicit conversion depends on the context in which it occurs and may not work the same way in every case.
- Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

## Nulls

If a column in a row has no value, then column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.

Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as NULLs.) Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.

### Nulls in SQL Functions

All scalar functions (except NVL and TRANSLATE) return null when given a null argument. The NVL function can be used to return a value when a null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null.

Most group functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.

### Nulls with Comparison Operators

To test for nulls, use only the comparison operators IS NULL and IS NOT NULL. If you use any other operator with nulls and the result depends on the value of the null, the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, note that Oracle considers two nulls to be equal when evaluating a DECODE expression. For information on the DECODE syntax, see "Expressions".

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.

### Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Table 2-9 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

##### Table 2-9 Conditions Containing Nulls

If A is: | Condition | Evaluates to:
--- | --- | ---
10 | a IS NULL | FALSE
10 | a IS NOT NULL | TRUE
NULL | a IS NULL | TRUE
NULL | a IS NOT NULL | FALSE
10 | a = NULL | UNKNOWN
10 | a != NULL | UNKNOWN
NULL | a = NULL | UNKNOWN
NULL | a != NULL | UNKNOWN
NULL | a = 10 | UNKNOWN
NULL | a != 10 | UNKNOWN

For the truth tables showing the results of logical expressions containing nulls, see Table 3-6, as well as Table 3-7 and Table 3-8.

## Pseudocolumns

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

- CURRVAL and NEXTVAL
- LEVEL
- ROWID
- ROWNUM

### CURRVAL and NEXTVAL

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:

CURRVAL | returns the current value of a sequence.
NEXTVAL | increments the sequence and returns the next value.

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

    sequence.CURRVAL
    sequence.NEXTVAL

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

    schema.sequence.CURRVAL
    schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

    schema.sequence.CURRVAL@dblink
    schema.sequence.NEXTVAL@dblink

For more information on referring to database links, see "Referring to Objects in Remote Databases".

If you are using Trusted Oracle in DBMS MAC mode, you can refer to a sequence only if your DBMS label dominates the sequence's creation label or if one of these criteria is satisfied:

- If the sequence's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.
- If the sequence's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

If you are using Trusted Oracle in OS MAC mode, you cannot refer to a sequence with a lower creation label than your DBMS label.

#### Where to Use Sequence Values

You can use CURRVAL and NEXTVAL in these places:

- the SELECT list of a SELECT statement that is not contained in a subquery, snapshot, or view
- the SELECT list of a subquery in an INSERT statement
- the VALUES clause of an INSERT statement
- the SET clause of an UPDATE statement

You cannot use CURRVAL and NEXTVAL in these places:

- a subquery in a DELETE, SELECT, or UPDATE statement
- a view's query or snapshot's query
- a SELECT statement with the DISTINCT operator
- a SELECT statement with a GROUP BY or ORDER BY clause
- a SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
- the WHERE clause of a SELECT statement
- DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
- the condition of a CHECK constraint

Also, within a single SQL statement that uses CURVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

#### How to Use Sequence Values

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last reference to NEXTVAL. Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

Within a single SQL statement, Oracle will increment the sequence only once. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.

A sequence can be accessed by many users concurrently with no waiting or locking. For information on sequences, see CREATE SEQUENCE.

##### Example 1

This example selects the current value of the employee sequence:

    SELECT empseq.currval
        FROM DUAL;

##### Example 2

This example increments the employee sequence and uses its value for a new employee inserted into the employee table:

    INSERT INTO emp
        VALUES (empseq.nextval, 'LEWIS', 'CLERK',
                 7902, SYSDATE, 1200, NULL, 20);

##### Example 3

This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:

    INSERT INTO master_order(orderno, customer, orderdate)
        VALUES (orderseq.nextval, 'Al''s Auto Shop', SYSDATE);

    INSERT INTO detail_order (orderno, part, quantity)
        VALUES (orderseq.currval, 'SPARKPLUG', 4);

    INSERT INTO detail_order (orderno, part, quantity)
        VALUES (orderseq.currval, 'FUEL PUMP', 1);

    INSERT INTO detail_order (orderno, part, quantity)
        VALUES (orderseq.currval, 'TAILPIPE', 2);

### LEVEL

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any nonroot node. A parent node is any node that has children. A leaf node is any node without children. Figure 2-1 shows the nodes of an inverted tree with their LEVEL values.

#### Figure 2-1 Hierarchical Tree

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses. For more information on using the LEVEL pseudocolumn, see SELECT.

### ROWID

For each row in the database, the ROWID pseudocolumn returns a row's address. Oracle8 ROWID values contain information necessary to locate a row:

- the data object number of the object
- which data block in the datafile
- which row in the data block (first row is 0)
- which datafile (first file is 1). The file number is relative to the tablespace.

Usually, a ROWID value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same ROWID.

Values of the ROWID pseudocolumn have the datatype ROWID. For information on the ROWID datatype, see "ROWID Datatype".

ROWID values have several important uses:

- They are the fastest way to access a single row.
- They can show you how a table's rows are stored.
- They are unique identifiers for rows in a table.

You should not use ROWID as a table's primary key. If you delete and reinsert a row with the Import and Export utilities, for example, its ROWID may change. If you delete a row, Oracle may reassign its ROWID to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clauses of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

##### Example

This statement selects the address of all rows that contain data for employees in department 20:

    SELECT ROWID, ename
        FROM emp
        WHERE deptno = 20;

    ROWID              ENAME
    ------------------ ----------
    AAAAfSAABAAAClaAAA SMITH
    AAAAfSAABAAAClaAAD JONES
    AAAAfSAABAAAClaAAH SCOTT
    AAAAfSAABAAAClaAAK ADAMS
    AAAAfSAABAAAClaAAM FORD

### ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

    SELECT *
        FROM emp
        WHERE ROWNUM < 10;

Note that conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

    SELECT * FROM emp
        WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

    UPDATE tabx
        SET col1 = ROWNUM;

Oracle assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index, so the ROWNUMs may be different than they would be without the ORDER BY clause.

Note: Using ROWNUM in a query can affect view optimization. For more information, see Oracle8 Concepts.

## Comments

You can associate comments with SQL statements and schema objects.

### Comments Within SQL Statements

Comments within SQL statements do not affect the statement execution, but they may make your application easier for you to read and maintain. You may want to include a comment in a statement that describes the statement's purpose within your application.

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using either of these means:

- Begin the comment with /*. Proceed with the text of the comment. This text can span multiple lines. End the comment with */. The opening and terminating characters need not be separated from the text by a space or a line break.
- Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

Note: You cannot use these styles of comments between SQL statements in a SQL script. Use the Server Manager or SQL*Plus REMARK command for this purpose. For information on these commands, see Oracle Server Manager User's Guide or SQL*Plus User's Guide and Reference.

##### Example

These statements contain many comments:

    SELECT ename, sal + NVL(comm, 0), job, loc
    /* Select all employees whose compensation is
    greater than that of Jones.*/
      FROM emp, dept
           /*The DEPT table is used to get the department name.*/
      WHERE emp.deptno = dept.deptno
        AND sal + NVL(comm,0) >   /* Subquery:       */
       (SELECT sal + NLV(comm,0)
                                  /* total compensation is sal + comm */
          FROM emp
          WHERE ename = 'JONES')

    SELECT ename,                    -- select the name
        sal + NVL(comm, 0),          -- total compensation
        job,                         -- job
        loc                          -- and city containing the office
      FROM emp,                      -- of all employees
           dept
      WHERE emp.deptno = dept.deptno
        AND sal + NVL(comm, 0) >     -- whose compensation
                                     -- is greater than
          (SELECT sal + NVL(comm,0)  -- the compensation
        FROM emp
        WHERE ename = 'JONES')       -- of Jones.

### Comments on Schema Objects

You can associate a comment with a table, view, snapshot, or column using the COMMENT command described in Chapter 4, "Commands". Comments associated with schema objects are stored in the data dictionary.

### Hints

You can use comments in a SQL statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses these hints to choose an execution plan for the statement.

A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The syntax below shows the syntax for hints contained in both styles of comments that Oracle supports within a statement block.

    {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

    {DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:

DELETE INSERT SELECT UPDATE | is a DELETE, INSERT, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can appear only after these keywords.
+ | is a plus sign that causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter (no space is permitted).
hint | is one of the hints discussed in this section and in Oracle8 Tuning. The space between the plus sign and the hint is optional. If the comment contains multiple hints, each pair of hints must be separated by at least one space.
text | is other commenting text that can be interspersed with the hints.

Table 2-10 lists hint syntax and descriptions. For more information on hints, see Oracle8 Tuning, Oracle8 Parallel Server Concepts and Administration, and Oracle8 Concepts.

##### Table 2-10 Hint Syntax and Descriptions

Hint Syntax | Description
--- | ---
Optimization Approaches and Goals
`/*+ ALL_ROWS */` | explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption)
`/*+ CHOOSE */` | causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement
`/*+ FIRST_ROWS */` | explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row)
`/*+ RULE */` | explicitly chooses rule-based optimization for a statement block
Access Methods
`/*+ AND_EQUAL(table index) */` | explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes
`/*+ CLUSTER(table) */` | explicitly chooses a cluster scan to access the specified table
`/*+ FULL(table) */` | explicitly chooses a full table scan for the specified table
`/*+ HASH(table) */` | explicitly chooses a hash scan to access the specified table
`/*+ HASH_AJ(table) */` | transforms a NOT IN subquery into a hash antijoin to access the specified table
/*+ HASH_SJ (table) */ | transforms a NOT IN subquery into a hash anti-join to access the specified table
`/*+ INDEX(table index) */` | explicitly chooses an index scan for the specified table
`/*+ INDEX_ASC(table index) */` | explicitly chooses an ascending-range index scan for the specified table
`/*+ INDEX_COMBINE(table index) */` | If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate. If particular indexes are given as arguments, the optimizer tries to use some Boolean combination of those particular bitmap indexes.
`/*+ INDEX_DESC(table index) */` | explicitly chooses a descending-range index scan for the specified table
`/*+ INDEX_FFS(table index) */` | causes a fast full index scan to be performed rather than a full table scan
`/*+ MERGE_AJ (table) */` | transforms a NOT IN subquery into a merge anti-join to access the specified table
/*+ MERGE_SJ (table) */ | transforms a correlated EXISTS subquery into a merge semi-join to access the specified table
`/*+ ROWID(table) */` | explicitly chooses a table scan by ROWID for the specified table
`/*+ USE_CONCAT */` | forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator
Join Orders
`/*+ ORDERED */` | causes Oracle to join tables in the order in which they appear in the FROM clause
`/*+ STAR */` | forces the large table to be joined last using a nested-loops join on the index
Join Operations
/*+ DRIVING_SITE (table) */ | forces query execution to be done at a different site from that selected by Oracle
`/*+ USE_HASH (table) */` | causes Oracle to join each specified table with another row source with a hash join
`/*+ USE_MERGE (table) */` | causes Oracle to join each specified table with another row source with a sort-merge join
`/*+ USE_NL (table) */` | causes Oracle to join each specified table to another row source with a nested-loops join using the specified table as the inner table
Parallel Execution
`/*+ APPEND */` `/*+ NOAPPEND */` | specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.
`/*+ NOPARALLEL(table) */` | disables parallel scanning of a table, even if the table was created with a PARALLEL clause
`/*+ PARALLEL(table, instances) */` | allows you to specify the desired number of concurrent slave processes that can be used for the operation. DELETE, INSERT, and UPDATE operations are considered for parallelization only if the session is in a PARALLEL DML enabled mode. (Use ALTER SESSION PARALLEL DML to enter this mode.)
`/*+ PARALLEL_INDEX` | allows you to parallelize fast full index scan for partitioned and nonpartitioned indexes that have the PARALLEL attribute
/*+ NOPARALLEL_INDEX */ | overrides a PARALLEL attribute setting on an index
Other Hints |
`/*+ CACHE */` | specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed
`/*+ NOCACHE */` | specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed
/*+ MERGE (table) */ | causes Oracle to evaluate complex views or subqueries before the surrounding query
`/*+ NO_MERGE (table) */` | causes Oracle not to merge mergeable views
/*+ PUSH_JOIN_PRED (table) */ | causes the optimizer to evaluate, on a cost basis, whether or not to push individual join predicates into the view
/*+ NO_PUSH_JOIN_PRED (table) */ | Prevents pushing of a join predicate into the view
`/*+ PUSH_SUBQ */` | causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan
/*+ STAR_TRANSFORMATION */ | makes the optimizer use the best plan in which the transformation has been used.

## Database Objects

### Schema Objects

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

- clusters
- database links
- database triggers
- external procedure libraries
- index-only tables
- indexes
- packages
- sequences
- stored functions
- stored procedures
- synonyms
- tables
- views

In addition, the following schema objects are available if you are using Oracle's distributed functionality:

- snapshots
- snapshot logs

In addition, the following schema objects are available if the objects option is installed on your database server:

- object tables
- object types
- object views

### Nonschema Objects

Other types of objects are also stored in the database and can be created and manipulated with SQL but are not contained in a schema:

- directories
- profiles
- roles
- rollback segments
- tablespaces
- users

In this reference, each type of object is briefly defined in Chapter 4, "Commands", in the section describing the command that creates the database object. These commands begin with the keyword CREATE. For example, for the definition of a cluster, see CREATE CLUSTER. For an overview of database objects, see Oracle8 Concepts.

You must provide names for most types of schema objects when you create them. These names must follow the rules listed in the following sections.

### Parts of Schema Objects

Some schema objects are made up of parts that you must name, such as:

- columns in a table or view
- index and table partitions
- integrity constraints on a table
- packaged procedures, packaged stored functions, and other objects stored within a package

#### Partitioned Tables and Indexes

Tables and indexes can be partitioned. When partitioned, these schema objects consist of a number of parts called partitions, all of which have the same logical attributes. For example, all partitions in a table share the same column and constraint definitions, and all partitions in an index share the same index columns.

#### Partition-Extended Table Names

Partitions can be used as tables. This capability provides you with a shortcut for performing some partition-level operations that would otherwise require using a WHERE clause.

To use a partition as a table, create a view by selecting data from a single partition, and then use the view as a table. The advantage of this method is that you can build partition-level access control mechanisms by granting (or revoking) privileges on these views to (or from) other users or roles.

Partition-level bulk operations, such as deleting all of the rows from a partition, restrict the operation to one partition. These types of operations are easily expressed with the partition-extended table name syntax. Trying to phrase the same operation with a WHERE clause predicate can be cumbersome, especially when the range partitioning key uses more than one column.

Table specification syntax has been extended for the following DML statements to allow an optional partition specification for nonremote partitioned tables:

- DELETE
- INSERT
- LOCK TABLE
- SELECT
- UPDATE

  Note: For application portability and ANSI syntax compliance, Oracle strongly recommends that you use views to insulate applications from this Oracle proprietary extension.

Currently, the use of partition-extended table names has the following restrictions:

- No remote tables: A partition-extended table name cannot contain a database link (dblink) or a synonym that translates to a table with a dblink. To use remote partitions, create a view at the remote site that uses the partition-extended table name syntax and then refer to the remote view.
- No direct PL/SQL support: A SQL statement using the partition-extended table name syntax cannot be used in a PL/SQL block, although it can be used through dynamic SQL by using the DBMS_SQL package. To refer to a partition within a PL/SQL block, use views that in turn use the partition-extended table name syntax.
- No synonyms: A partition extension must be specified with a base table. You cannot use synonyms, views, or any other objects.

##### Syntax

The basic syntax for using partition-extended table names is:

    [schema.]{table | view} [@dblink | PARTITION (partition_name)]

##### Example

In the following statement, SALES is a partitioned table with partition JAN97. You can create a view of the single partition JAN97, and then use it as if it were a table. This example deletes rows from the partition.

    CREATE VIEW sales_jan97 AS
         SELECT * FROM sales PARTITION (jan97);
    DELETE FROM sales_jan97 WHERE amount < 0;

## Schema Object Names and Qualifiers

This section provides:

- rules for naming schema objects and schema object location qualifiers
- guidelines for naming schema objects and qualifiers

### Schema Object Naming Rules

The following rules apply when naming schema objects:

1. Names must be from 1 to 30 characters long with these exceptions:
   - Names of databases are limited to 8 characters.
   - Names of database links can be as long as 128 characters.
2. Names cannot contain quotation marks.
3. Names are not case sensitive.
4. A name must begin with an alphabetic character from your database character set unless surrounded by double quotation marks.
5. Names can contain only alphanumeric characters from your database character set and the characters _, $, and #. Names of database links can also contain periods (.) and at signs (@). Oracle strongly discourages you from using $ and #.

   If your database character set contains multibyte characters, Oracle recommends that each name for a user or a role contain at least one single-byte character.

   Note: You cannot use special characters from European or Asian character sets in a database name, global database name, or database link names. For example, the umlaut is not allowed.

6. A name cannot be an Oracle reserved word. Appendix C, "Oracle Reserved Words and Keywords", lists all Oracle reserved words.

   Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. For a list of a product's reserved words, see the manual for the specific product, such as PL/SQL User's Guide and Reference.

7. Do not use the word DUAL as a name for an object or part. DUAL is the name of a dummy table.
8. The Oracle SQL language contains other keywords that have special meanings. Because these keywords are not reserved, you can also use them as names for objects and object parts. However, using them as names may make your SQL statements more difficult to read.

   Appendix C, "Oracle Reserved Words and Keywords" lists Oracle keywords.

9. Within a namespace, no two objects can have the same name.

   Figure 2-2 shows the namespaces for schema objects; each box is a namespace. Tables and views are in the same namespace; therefore, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces; therefore, a table and an index in the same schema can have the same name.

   Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

   #### Figure 2-2 Namespaces for Schema Objects

   Figure 2-3 shows the namespaces for nonschema objects. Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.

   #### Figure 2-3 Namespaces for Nonschema Objects

10. Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.
11. Procedures or functions contained in the same package can have the same name, provided that their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.
12. A name can be enclosed in double quotation marks. Such names can contain any combination of characters, including spaces, ignoring rules 3 through 7 in this list. This exception is allowed for portability, but Oracle recommends that you do not break rules 3 through 7.

    If you give a schema object a name enclosed in double quotation marks, you must use double quotation marks whenever you refer to the object.

    Enclosing a name in double quotes allows it to

    - contain spaces
    - be case sensitive
    - begin with a character other than an alphabetic character, such as a numeric character
    - contain characters other than alphanumeric characters and _, $, and #
    - be a reserved word

    By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:

        emp
        "emp"
        "Emp"
        "EMP "

    Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:

        emp
        EMP
        "EMP"

    If you give a user or password a quoted name, the name cannot contain lowercase letters.

    Database link names cannot be quoted.

### Schema Object Naming Examples

The following examples are valid schema object names:

    ename
    horse
    scott.hiredate
    "EVEN THIS & THAT!"
    a_very_long_and_valid_name

Although column aliases, table aliases, usernames, and passwords are not objects or parts of objects, they must also follow these naming rules with these exceptions:

- Column aliases and table aliases exist only for the execution of a single SQL statement and are not stored in the database, so rule 12 does not apply to them.
- Passwords do not have namespaces, so rule 9 does not apply to apply to them.
- Do not use quotation marks to make usernames and passwords case sensitive. For additional rules for naming users and passwords, see CREATE USER.

### Schema Object Naming Guidelines

Here are several helpful guidelines for naming objects and their parts:

- Use full, descriptive, pronounceable names (or well-known abbreviations).
- Use consistent naming rules.
- Use the same name to describe the same entity or attribute across tables.

When naming objects, balance the objective of keeping names short and easy to use with the objective of making names as descriptive as possible. When in doubt, choose the more descriptive name, because the objects in the database may be used by many people over a period of time. Your counterpart ten years from now may have difficulty understanding a database with a name like PMDD instead of PAYMENT_DUE_DATE.

Using consistent naming rules helps users understand the part that each table plays in your application. One such rule might be to begin the names of all tables belonging to the FINANCE application with FIN_.

Use the same names to describe the same things across tables. For example, the department number columns of the sample EMP and DEPT tables are both named DEPTNO.

## Referring to Schema Objects and Parts

This section tells you how to refer to schema objects and their parts in the context of a SQL statement. This section shows you:

- the general syntax for referring to an object
- how Oracle resolves a reference to an object
- how to refer to objects in schemas other than your own
- how to refer to objects in remote databases

The following diagram shows the general syntax for referring to an object or a part:

where:

object | is the name of the object.
schema | is the schema containing the object. The schema qualifier allows you to refer to an object in a schema other than your own. Note that you must be granted privileges to refer to objects in other schemas. If you omit schema, Oracle assumes that you are referring to an object in your own schema.
| Only schema objects can be qualified with schema. Schema objects are shown in Figure 2-2. Nonschema objects, shown in Figure 2-3, cannot be qualified with schema because they are not schema objects. (An exception is public synonyms, which can optionally be qualified with "PUBLIC". The quotation marks are required.)
part | is a part of the object. This identifier allows you to refer to a part of a schema object, such as a column or a partition of a table. Note that not all types of objects have parts.
dblink | applies only when you are using Oracle's distributed functionality. This is the name of the database containing the object. The dblink qualifier allows you to refer to an object in a database other than your local database. If you omit dblink, Oracle assumes that you are referring to an object in your local database. Note that not all SQL statements allow you to access objects on remote databases.

You can include spaces around the periods separating the components of the reference to the object, but it is conventional to omit them.

### How Oracle Resolves Schema Object References

When you refer to an object in a SQL statement, Oracle considers the context of the SQL statement and locates the object in the appropriate namespace. After locating the object, Oracle performs the statement's operation on the object. If the named object cannot be found in the appropriate namespace, Oracle returns an error message.

The following example illustrates how Oracle resolves references to objects within SQL statements. Consider this statement that adds a row of data to a table identified by the name DEPT:

    INSERT INTO dept
       VALUES (50, 'SUPPORT', 'PARIS');

Based on the context of the statement, Oracle determines that DEPT can be:

- a table in your own schema
- a view in your own schema
- a private synonym for a table or view
- a public synonym

Oracle always attempts to resolve an object reference within the namespaces in your own schema before considering namespaces outside your schema. In this example, Oracle attempts to resolve the name DEPT as follows:

1. First, Oracle attempts to locate the object in the namespace in your own schema containing tables, views, and private synonyms. If the object is a private synonym, Oracle locates the object for which the synonym stands. This object could be in your own schema, another schema, or on another database. The object could also be another synonym, in which case Oracle locates the object for which this synonym stands.
2. If the object is in the namespace, Oracle attempts to perform the statement on the object. In this example, Oracle attempts to add the row of data to DEPT. If the object is not of the correct type for the statement, Oracle returns an error message. In this example, DEPT must be a table, view, or a private synonym resolving to a table or view. If DEPT is a sequence, Oracle returns an error message.
3. If the object is not in any namespace searched in thus far, Oracle searches the namespace containing public synonyms (see Figure 2-3). If the object is in that namespace, Oracle attempts to perform the statement on it. If the object is not of the correct type for the statement, Oracle returns an error message. In this example, if DEPT is a public synonym for a sequence, Oracle returns an error message.

### Referring to Objects in Other Schemas

To refer to objects in schemas other than your own, prefix the object name with the schema name:

    schema.object

For example, this statement drops the EMP table in the schema SCOTT:

    DROP TABLE scott.emp

### Referring to Objects in Remote Databases

To refer to objects in databases other than your local database, follow the object name with the name of the database link to that database. A database link is a schema object that causes Oracle to connect to a remote database to access an object there. This section tells you:

- how to create database links
- how to use database links in your SQL statements

#### Creating Database Links

You create a database link with the CREATE DATABASE LINK command described in Chapter 4, "Commands". The command allows you to specify this information about the database link:

- the name of the database link
- the database connect string to access the remote database
- the username and password to connect to the remote database

Oracle stores this information in the data dictionary.

##### Database Link Names

When you create a database link, you must specify its name. The name of a database link can be as long as 128 bytes and can contain periods (.) and the "at" sign (@). In these ways, database link names are different from names of other types of objects.

The name that you give to a database link must correspond to the name of the database to which the database link refers and the location of that database in the hierarchy of database names. The following syntax diagram shows the form of the name of a database link:

dblink::=

where:

database | specifies the name of the remote database to which the database link connects. The name of the remote database is specified by its initialization parameter DB_NAME.
domain | specifies the domain of the remote database to which the database link connects. If you omit the domains from the name of a database link, Oracle expands the name by qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary.
connect_descriptor | allows you to further qualify a database link. Using connect descriptors, you can create multiple database links to the same database. For example, you can use connect descriptors to create multiple database links to different instances of the Oracle Parallel Server that access the same database.

The combination database.domain is sometimes called the "service name". For more information, see Net8 Administrator's Guide.

##### Username and Password

Oracle uses the username and password to connect to the remote database. The username and password for a database link are optional.

##### Database Connect String

The database connect string is the specification used by Net8 to access the remote database. For information on writing database connect strings, see the Net8 documentation for your specific network protocol. The database string for a database link is optional.

#### Referring to Database Links

Database links are available only if you are using Oracle's distributed functionality. When you issue a SQL statement that contains a database link, you can specify the database link name in one of these forms:

complete | is the complete database link name as stored in the data dictionary, including the database, domain, and optional connect_descriptor components.
partial | is the database and optional connect_descriptor components, but not the domain component.

Oracle performs these tasks before connecting to the remote database:

1. If the database link name specified in the statement is partial, Oracle expands the name to contain the domain of the local database as found in the global database name stored in the data dictionary. (You can see the current global database name in the GLOBAL_NAME data dictionary view.)
2. Oracle first searches for a private database link in your own schema with the same name as the database link in the statement; then, if necessary, it searches for a public database link with the same name.
   - Oracle always determines the username and password from the first matching database link (either private or public). If the first matching database link has an associated username and password, Oracle uses it. If it does not have an associated username and password, Oracle uses your current username and password.
   - If the first matching database link has an associated database string, Oracle uses it. If not, Oracle searches for the next matching (public) database link. If no matching database link is found, or if no matching link has an associated database string, Oracle returns an error message.
3. Oracle uses the database string to access the remote database. After accessing the remote database, Oracle verifies that both of these conditions are true:
   - The name of the remote database (specified by its initialization parameter DB_NAME) matches the database component of the database link name.
   - The domain (specified by the initialization parameter DB_DOMAIN) of the remote database matches the domain component of the database link name.

   If both of these conditions are true, Oracle proceeds with the connection, using the username and password chosen in Step 2. If not, Oracle returns an error message.

4. If the connection using the database string, username, and password is successful, Oracle attempts to access the specified object on the remote database using the rules for resolving object references and referring to objects in other schemas discussed earlier in this section.

You can enable and disable Oracle resolution of names for remote objects using the initialization parameter GLOBAL_NAMES or the GLOBAL_NAMES parameter of the ALTER SYSTEM or ALTER SESSION command.

For more information on remote name resolution, see Oracle8 Distributed Database Systems.

### Referencing Object Type Attributes and Methods

To reference object type attributes or methods in a SQL statement, you must fully qualify the reference with a table alias. For example, consider the following example:

    CREATE TYPE person AS OBJECT
       (ssno VARCHAR(20),
        name VARCHAR (10));

    CREATE TABLE emptab (pinfo person);

In a SQL statement, reference to the SSNO attribute must be fully qualified using a table alias, as illustrated below:

    SELECT e.pinfo.ssno FROM emptab e;

    UPDATE emptab e SET e.pinfo.ssno = '510129980'
       WHERE e.pinfo.name = 'Mike';

To reference an object type's member method that does not accept any arguments, you must provide "empty" parentheses. For example, assume that AGE is a method in the person type that does not take any arguments. In order to call this method in a SQL statement, you must provide empty parentheses as shows in this example:

    SELECT e.pinfo.age() FROM emptab e
       WHERE e.pinfo.name = 'Mike';

For more information, see the sections on user-defined datatypes in Oracle8 Concepts.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index