Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Basic Elements of Oracle SQL, 2 of 10
Each 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 a datatype for each of its columns. When you create a procedure or stored function, you must specify a 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-98' into a DATE
column, Oracle treats the '01-JAN-98' character string as a DATE
value after verifying that it translates to a valid date.
Oracle provides a number of built-in datatypes as well as several categories for user-defined types. The syntax of Oracle datatypes appears in the diagrams that follow. The text of this section is divided into the following sections:
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 built-in and user-defined datatypes with external datatypes. For information on external datatypes, including how Oracle converts between them and built-in or user-defined datatypes, see Pro*COBOL Precompiler Programmer's Guide, Pro*C/C++ Precompiler Programmer's Guide, and SQL*Module for Ada Programmer's Guide.
Note:
datatypes::=
datatypes
Oracle_built_in_datatypes::=
oracle_built_in_datatypes
character_datatypes::=
character_datatypes
number_datatypes::=
number_datatypes
long_and_raw_datatypes::=
long_and_raw_datatypes
datetime_datatypes::=
datetime_datatypes
large_object_datatypes::=
large_object_datatypes
rowid_datatypes::=
rowid_datatypes
The ANSI-supported datatypes appear in the figure that follows. Table 2-3 shows the mapping of ANSI-supported datatypes to Oracle built-in datatypes.
ANSI_supported_datatypes::=
ansi_supported_datatypes
Oracle_supplied_types::=
oracle_supplied_types
any_types::=
any_types
XML_types::=
xml_types
spatial_type::=
spatial_type
media_types::=
media_types
Table 2-1 summarizes Oracle built-in datatypes.
Character datatypes store character (alphanumeric) data, which are words and free-form text, in the database character set 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, specified when the database was created. Oracle supports both single-byte and multibyte character sets.
These datatypes are used for character data:
The CHAR
datatype specifies a fixed-length character string. Oracle subsequently ensures that all values stored in that column have the length specified by size
. 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 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10)
column, but the string is blank-padded to 10 bytes before it is stored.
When you create a table with a CHAR
column, by default you supply the column length in bytes. The BYTE
qualifier is the same as the default. If you use the CHAR
qualifier, for example CHAR
(10
CHAR
), you supply the column length in characters. A character is technically a codepoint of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. The BYTE
and CHAR
qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS
parameter, which has a default of byte semantics.
Note:
To ensure proper data conversion between databases with different character sets, you must ensure that |
Beginning with Oracle9i, the NCHAR
datatype is redefined to be a Unicode-only datatype. When you create a table with an NCHAR
column, you define the column length in characters. You define the national character set when you create your database.
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. The maximum column size allowed is 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 translated_description from product_descriptions WHERE translated_name = N'LCD Monitor 11/PM';
See Also:
Oracle9i Globalization and National Language Support Guide for information on Unicode datatype support |
Beginning with Oracle9i, the NVARCHAR2
datatype is redefined to be a Unicode-only datatype. When you create a table with an NVARCHAR2
column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value 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. The maximum column size allowed is 4000 bytes.
The VARCHAR2
datatype specifies a variable-length character string. When you create a VARCHAR2
column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length. 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. This maximum must be at least 1 byte, although the actual length of the string stored is permitted to be zero. Oracle treats zero-length strings as nulls. You can use the CHAR
qualifier, for example VARCHAR2
(10
CHAR
), to give the maximum length in characters instead of bytes. A character is technically a codepoint of the database character set. CHAR
and BYTE
qualifiers override the setting of the NLS_LENGTH_SEMANTICS
parameter, which has a default of bytes. The maximum length of VARCHAR2
data is 4000 bytes. Oracle compares VARCHAR2
values using nonpadded comparison semantics.
Note:
To ensure proper data conversion between databases with different character sets, you must ensure that |
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.
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:
p
is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
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)
This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0)
.
Specify a floating-point number using the following form:
NUMBER
The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.
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.
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.
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 |
|
.01234 |
.00012 |
|
.00012 |
.000127 |
|
.00013 |
.0000012 |
|
.0000012 |
.00000123 |
|
.0000012 |
Oracle lets you 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. An exponent may optionally be used following the number to increase the range (e.g. 1.777 e-20). 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 range of values discussed in "NUMBER Datatype". The format is defined in "Number Literals". 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
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. 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:
The use of LONG
values is subject to some restrictions:
LONG
column.
LONG
attribute.
LONG
columns cannot appear in WHERE
clauses or in integrity constraints (except that they can appear in NULL
and NOT
NULL
constraints).
LONG
columns cannot be indexed.
LONG
value.
LONG
datatype. However, you cannot then call the program unit from SQL.
LONG
columns, updated tables, and locked tables must be located on the same database.
LONG
and LONG
RAW
columns cannot be used in distributed SQL statements and cannot be replicated.
LONG
and LOB columns, you cannot bind more than 4000 bytes of data to both the LONG
and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG
or the LOB column.
LONG
columns cannot be stored in a tablespace with automatic segment-space management.
LONG
columns cannot appear in certain parts of SQL statements:
GROUP
BY
clauses, ORDER
BY
clauses, or CONNECT
BY
clauses or with the DISTINCT
operator in SELECT
statements
UNIQUE
operator of a SELECT
statement
CREATE
CLUSTER
statement
CLUSTER
clause of a CREATE
MATERIALIZED
VIEW
statement
SELECT
lists of queries containing GROUP
BY
clauses
SELECT
lists of subqueries or queries combined by the UNION
, INTERSECT
, or MINUS
set operators
SELECT
lists of CREATE
TABLE
... AS
SELECT
statements
ALTER
TABLE
... MOVE
statements
SELECT
lists in subqueries in INSERT
statements
Triggers can use the LONG
datatype in the following manner:
LONG
column.
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.
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.
The datetime datatypes are DATE
, TIMESTAMP
, TIMESTAMP
WITH
TIME
ZONE
and TIMESTAMP
WITH
LOCAL
TIME
ZONE
. Values of datetime datatypes are sometimes called "datetimes". The interval datatypes are INTERVAL
YEAR
TO
MONTH
and INTERVAL
DAY
TO
SECOND
. Values of interval datatypes are sometimes called "intervals".
Both datetimes and intervals are made up of fields. The values of these fields determine the value of the datatype. The table that follows lists the datetime fields and their possible values for datetimes and intervals.
The DATE
datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE
datatype has special associated properties. For each DATE
value, Oracle stores the following information: century, year, month, date, hour, minute, and second.
You can specify a date value as a literal, or you can convert a character or numeric value to a date value with the TO_DATE
function. To specify a date as a literal, you must use the Gregorian calendar. You can specify an ANSI date literal, as shown in this example:
DATE '1998-12-25'
The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD'). Alternatively you can specify an Oracle date literal, as in the following example:
TO_DATE('98-DEC-25:17:30','YY-MON-DD:HH24:MI')
The default date format for an Oracle date literal is specified by the initialization parameter NLS_DATE_FORMAT
. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation.
Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions.
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.
Oracle DATE
columns always contain both the date and time fields. If your queries use a date format without a time portion, you must ensure that the time fields in the DATE
column are set to zero (that is, midnight). Otherwise, Oracle may not return the query results you expect. Here are some examples that assume a table my_table
with a number column row_num
and a DATE
column datecol
:
INSERT INTO my_table VALUES (1, SYSDATE); INSERT INTO my_table VALUES (2, TRUNC(SYSDATE)); SELECT * FROM my_table; ROW_NUM DATECOL ---------- --------- 1 04-OCT-00 2 04-OCT-00 SELECT * FROM my_table WHERE datecol = TO_DATE('04-OCT-00','DD-MON-YY'); ROW_NUM DATECOL ---------- --------- 2 04-OCT-00
If you know that the time fields of your DATE
column are set to zero, then you can query your DATE
column as shown in the second example above, or by using the DATE
literal:
SELECT * FROM my_table WHERE datecol = DATE '2000-10-04';
However, if the DATE
column contains nonzero time fields, then you must filter out the time fields in the query to get the correct result. For example:
SELECT * FROM my_table WHERE TRUNC(datecol) = DATE'2000-10-04';
Oracle applies the TRUNC
function to each row in the query, so performance is better if you ensure the zero value of the time fields in your data. To ensure that the time fields are set to zero, use one of the following methods during inserts and updates:
TO_DATE
function to mask out the time fields:
INSERT INTO my_table VALUES (3, TO_DATE('4-APR-2000','DD-MON-YYYY'));
DATE
literal:
INSERT INTO my_table VALUES (4, '04-OCT-00');
TRUNC
function:
INSERT INTO my_table VALUES (5, TRUNC(SYSDATE));
The date function SYSDATE
returns the current system date and time. The function CURRENT_DATE
returns the current session date. For information on SYSDATE
, the TO_*
datetime functions, and the default date format, see Chapter 6, "Functions".
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 sample table employees
from SYSDATE
returns the number of days since each employee was hired. You cannot multiply or divide DATE
values.
Oracle provides functions for many common date operations. For example, the ADD_MONTHS
function lets you 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.
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.
See Also:
|
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.
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".
The TIMESTAMP
datatype is an extension of the DATE
datatype. It stores the year, month, and day of the DATE
datatype, plus hour, minute, and second values. Specify the TIMESTAMP
datatype as follows:
TIMESTAMP [ (fractional_seconds_precision)]
where fractional_seconds_precision
optionally specifies the number of digits in the fractional part of the SECOND
datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP
as a literal as follows:
TIMESTAMP'1997-01-31 09:26:50.124'
TIMESTAMP
WITH
TIME
ZONE
is a variant of TIMESTAMP
that includes a time zone displacement in its value. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). Specify the TIMESTAMP
WITH
TIME
ZONE
datatype as follows:
TIMESTAMP [ (fractional_seconds_precision) ] WITH TIME ZONE
where fractional_seconds_precision
optionally specifies the number of digits in the fractional part of the SECOND
datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP
WITH
TIME
ZONE
as a literal as follows:
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
Two TIMESTAMP
WITH
TIME
ZONE
values are considered identical if they represent the same instant in UTC, regardless of the TIME
ZONE
offsets stored in the data. For example,
TIMESTAMP '1999-04-15 8:00:00 -8:00'
is the same as
TIMESTAMP '1999-04-15 11:00:00 -5:00'
That is, 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time.
You can replace the UTC offset with the TZR
(time zone region) format element. For example, the following example has the same value as the preceding example:
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
To eliminate the ambiguity of boundary cases when the daylight savings time switches, use both the TZR
and a corresponding TZD
format element. The following example ensures that the preceding example will return a daylight savings time value:
TIMESTAMP '1999-10-31 01:30:00 US/Pacific PDT'
If you do not add the TZD
format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME
session parameter set to TRUE
. If that parameter is set to FALSE
, then Oracle interprets the ambiguous datetime as standard time.
See Also:
|
TIMESTAMP
WITH
LOCAL
TIME
ZONE
is another variant of TIMESTAMP
that includes a time zone displacement in its value. It differs from TIMESTAMP
WITH
TIME
ZONE
in that data stored in the database is normalized to the database time zone, and the time zone displacement is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). Specify the TIMESTAMP
WITH
LOCAL
TIME
ZONE
datatype as follows:
TIMESTAMP [ (fractional_seconds_precision) ] WITH LOCAL TIME ZONE
where fractional_seconds_precision
optionally specifies the number of digits in the fractional part of the SECOND
datetime field and can be a number in the range 0 to 9. The default is 6.
There is no literal for TIMESTAMP
WITH
LOCAL
TIME
ZONE
.
INTERVAL
YEAR
TO
MONTH
stores a period of time using the YEAR
and MONTH
datetime fields. Specify INTERVAL
YEAR
TO
MONTH
as follows:
INTERVAL YEAR [(year_precision)] TO MONTH
where year_precision
is the number of digits in the YEAR
datetime field. The default value of year_precision
is 2.
INTERVAL
DAY
TO
SECOND
stores a period of time in terms of days, hours, minutes, and seconds. Specify this datatype as follows:
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
where
day_precision
is the number of digits in the DAY
datetime field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision
is the number of digits in the fractional part of the SECOND
datetime field. Accepted values are 0 to 9. The default is 6.
Oracle lets you derive datetime and interval value expressions. Datetime value expressions yield values of datetime datatype. Interval value expressions yield values of interval datatype. Table 2-2 lists the operators that you can use in these expressions.
Oracle performs all timestamp arithmetic in UTC time. For TIMESTAMP
WITH
LOCAL
TIME
ZONE
, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP
WITH
TIME
ZONE
, the datetime value is always in UTC, so no conversion is necessary.
Oracle automatically determines, for any given time zone region, whether daylight savings is in effect and returns local time values based accordingly. The datetime value is sufficient for Oracle to determine whether daylight savings time is in effect for a given region in all cases except boundary cases. A boundary case occurs during the period when daylight savings goes into or comes out of effect. For example, in the US-Pacific region, when daylight savings goes into effect, the time changes from 2:00 a.m. to 3:00 a.m. The one hour interval between 2 and 3 a.m. does not exist. When daylight savings goes out of effect, the time changes from 2:00 a.m. back to 1:00 a.m., and the one-hour interval between 1 and 2 a.m. is repeated.
To resolve these boundary cases, Oracle uses the TZR
and TZD
format elements, as described in Table 2-12. TZR
represents the time zone region in datetime input strings. Examples are 'Australia/North
', 'UTC
', and 'Singapore
'. TZD
represents an abbreviated form of the time zone region with daylight savings information. Examples are 'PST
' for US/Pacific standard time and 'PDT
' for US/Pacific daylight time. To see a listing of valid values for the TZR
and TZD
format elements, query the TZNAME
and TZABBREV
columns of the V$TIMEZONE_NAMES
dynamic performance view.
See Also:
|
The following example shows how to declare some datetime and interval datatypes.
CREATE TABLE my_table ( start_time TIMESTAMP, duration_1 INTERVAL DAY (6) TO SECOND (5), duration_2 INTERVAL YEAR TO MONTH);
The start_time
column is of type TIMESTAMP
. The implicit fractional seconds precision of TIMESTAMP
is 6.
The duration_1
column is of type INTERVAL
DAY
TO
SECOND
. The maximum number of digits in field DAY
is 6 and the maximum number of digits in the fractional second is 5. (The maximum number of digits in all other datetime fields is 2.)
The duration_2
column is of type INTERVAL
YEAR
TO
MONTH
. The maximum number of digits of the value in each field (YEAR
and MONTH
) is 2.
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, for which the interpretation is dependent on the use.
Note:
Oracle Corporation strongly recommends that you convert |
RAW
is a variable-length datatype like VARCHAR2
, except that Oracle Net (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, Oracle Net and Import/Export automatically convert CHAR
, VARCHAR2
, and LONG
data from the database character set to the user session character set (which you can set with the NLS_LANGUAGE
parameter of the ALTER
SESSION
statement), 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'.
The built-in LOB datatypes BLOB
, CLOB
, and NCLOB
(stored internally) and BFILE
(stored externally), 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 LOB columns or LOB object attributes from those specified for the table.
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 Oracle Call Interface (OCI) operations on LOBs are performed through these locators.
LOBs are similar to LONG
and LONG
RAW
types, but differ in the following ways:
BLOB
, NCLOB
, and CLOB
values can be stored in separate tablespaces. BFILE
data is stored in an external file on the server.
BFILE
maximum size is operating system dependent, but cannot exceed 4 gigabytes.
NCLOB
, you can define one or more LOB attributes in an object.
NULL
, empty, or replace the entire LOB with data. You can set the BFILE
to NULL
or make it point to a different file.)
You can access and populate rows of an internal LOB column (a LOB column stored in the database) simply by issuing an INSERT
or UPDATE
statement. However, to access and populate a LOB attribute that is part of an object type, you must first initialize the LOB attribute using the EMPTY_CLOB
or EMPTY_BLOB
function. You can then select the empty LOB attribute and populate it using the DBMS_LOB
package or some other appropriate interface.
LOB columns are subject to the following restrictions:
SELECT
or WHERE
clauses of queries or in functions of the DBMS_LOB
package.
The following syntax is not supported for LOBs:
SELECT lobcol FROM table1@remote_site; INSERT INTO lobtable SELECT type1.lobattr FROM table1@remote_ site; SELECT DBMS_LOB.getlength(lobcol) FROM table1@remote_site;
However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns:
CREATE TABLE t AS SELECT * FROM table1@remote_site; INSERT INTO t SELECT * FROM table1@remote_site; UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site); INSERT INTO table1@remote_site ... UPDATE table1@remote_site ... DELETE table1@remote_site ...
For the first three types of statement, which contain subqueries, only standalone LOB columns are allowed in the select list. SQL functions or DBMS_LOB
APIs on LOBs are not supported. For example, the following statement is supported:
CREATE TABLE AS SELECT clob_col FROM tab@dbs2;
However, the following statement is not supported:
CREATE TABLE AS SELECT dbms_lob.substr(clob_col) from tab@dbs2;
ORDER
BY
clause of a query, or in the GROUP
BY
clause of a query or in an aggregate function.
SELECT
... DISTINCT
or SELECT
... UNIQUE
statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT
... DISTINCT
statement or in a query that uses the UNION
or MINUS
set operator if the column's object type has a MAP
or ORDER
function defined on it.
NCLOB
as an attribute of an object type when creating a table. However, you can specify NCLOB
parameters in methods.
ANALYZE
... COMPUTE
or ANALYZE
... ESTIMATE
statements.
AUTO
segment-managed tablespaces.
BEFORE
ROW
DML trigger, you can read the :old
value of the LOB, but you cannot read the :new
value. However, for AFTER
ROW
and INSTEAD
OF
DML triggers, you can read both the :new
and :old
values.
UPDATE
DML trigger on a LOB column.
Oracle9i Data Cartridge Developer's Guide for more information about defining triggers on domain indexes
See Also:
INSERT
or UPDATE
operation, you can bind data of any size to a LOB column, but you cannot bind data to a LOB attribute of an object type. In an INSERT
... AS
SELECT
operation, you can bind up to 4000 bytes of data to LOB columns.
"Keywords and Parameters" section of individual SQL statements in Oracle9i SQL Reference for additional semantics for the use of LOBs
See Also:
LONG
and LOB columns, you cannot bind more than 4000 bytes of data to both the LONG
and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG
or the LOB column.
Notes:
CACHE
READS
setting for LOBs. If you have such LOBs and you downgrade to an earlier release, Oracle generates a warning and converts the LOBs from CACHE
READS
to CACHE
LOGGING
. You can subsequently alter the LOBs to either NOCACHE
LOGGING
or NOCACHE
NOLOGGING
. For more information see Oracle9i Application Developer's Guide - Large Objects (LOBs)
DBMS_LOB
routines to change the value of a LOB column or the LOB attribute of an object type column, Oracle does not fire the DML trigger.
See Also:
EMPTY_BLOB, EMPTY_CLOB
The following example shows how the sample table pm.print_media
was created. (This example assumes the existence of the textdoc_tab
object table, which is nested table in the print_media
table.)
CREATE TABLE print_media ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ , press_release LONG ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;
See Also:
|
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.
You can change the filename and path of a BFILE
without affecting the base table by using the BFILENAME
function.
Binary file LOBs do not participate in transactions and are not recoverable. 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 enables read-only support of large binary files. You cannot modify or replicate such 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.
See Also:
|
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. BLOB
value manipulations can be committed and rolled back. However, 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.
The CLOB
datatype stores single-byte and multibyte character data. Both fixed-width and variable-width character sets are supported, and both use the CHAR
database character set. CLOBs
can store up to 4 gigabytes of character data.
CLOBs
have full transactional support. Changes made through SQL, the DBMS_LOB
package, or the OCI participate fully in the transaction. CLOB
value manipulations can be committed and rolled back. However, 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.
The NCLOB
datatype stores Unicode data using the national character set. Both fixed-width and variable-width character sets are 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 and rolled back. However, you cannot save an NCLOB
locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.
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
. 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.
Beginning with Oracle8, Oracle SQL incorporated 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 called restricted rowids. Their format is 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.
The 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 the 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 stored as base 64 values that can contain the characters A-Z, a-z, 0-9, as well as the plus sign (+) and forward slash (/). 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 as well as information specific to extended rowids.
See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for information on the functions available with the |
The restricted form of a rowid is still supported in Oracle9i for backward compatibility, but all tables return rowids in the extended format.
Each row in a database has an address. However, the rows of some tables have addresses that are not physical or permanent or were not generated by Oracle. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Rowids of foreign tables (such as DB2 tables accessed through a gateway) are not standard Oracle rowids.
Oracle uses "universal rowids" (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids. Both types of urowid are stored in the ROWID
pseudocolumn (as are the physical rowids of heap-organized tables).
Oracle creates logical rowids based on a table's primary key. The logical rowids do not change as long as the primary key does not change. The ROWID
pseudocolumn of an index-organized table has a datatype of UROWID
. You can access this pseudocolumn as you would the ROWID
pseudocolumn of a heap-organized table (that is, using the SELECT
ROWID
statement). If you wish to
store the rowids of an index-organized table, you can define a column of type UROWID
for the table and retrieve the value of the ROWID
pseudocolumn into that column.
See Also:
|
SQL statements 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.
Do not define columns with the following SQL/DS and DB2 datatypes, because they have no corresponding Oracle datatype:
Note that data of type TIME
and TIMESTAMP
can also be expressed as Oracle DATE
data.
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. You can create user-defined types in two ways:
The sections that follow describe the various categories of user-defined types.
See Also:
|
Object types are abstractions of the real-world entities, such as purchase orders, that application programs deal with. An object type is a schema object with three kinds of components:
An object identifier (OID) uniquely identifies an object and enables you to reference the object from other objects or from relational tables. A datatype category called REF
represents such references. A REF
is a container for an object identifier. REF
s are pointers to objects.
When a REF
value points to a nonexistent object, the REF
is said to be "dangling". A dangling REF
is different from a null REF
. To determine whether a REF
is dangling or not, use the predicate IS
[NOT
] DANGLING
. For example, given object view oc_orders
in the sample schema oe
, the column customer_ref
is of type REF
to type customer_typ
, which has an attribute cust_email
:
SELECT o.customer_ref.cust_email FROM oc_orders o WHERE o.customer_ref IS NOT DANGLING;
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:
Oracle normally stores an array object either in line (that is, as part of the row data) or out of line (in a LOB), depending on its size. However, if you specify separate storage characteristics for a varray, Oracle will store it out of line, regardless of its size.
A nested table type models 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:
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.
Oracle Corporation provides SQL-based interfaces for defining new types when the built-in or ANSI-supported types are not sufficient. The behavior for these types can be implemented in C/C++, Java, or PL/ SQL. Oracle automatically provides the low-level infrastructure services needed for input-output, heterogeneous client-side access for new datatypes, and optimizations for data transfers between the application and the database.
These interfaces can be used to build user-defined (or object) types, and are also used by Oracle to create some commonly useful datatypes. Several such datatypes are supplied with the server, and they serve both broad horizontal application areas (for example, the "Any" types) and specific vertical ones (for example, the spatial type).
The Oracle-supplied types are listed in the sections that follow, along with cross-references to the documentation of their implementation and use.
The "Any" types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These datatypes let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. These types have OCI and PL/SQL interfaces for construction and access.
This type can contain a type description of any named SQL type or unnamed transient type.
This type contains an instance of a given type, with data, plus a description of the type. AnyData
can be used as a table column datatype and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types.
This type contains a description of a given type plus a set of data instances of that type. AnyDataSet
can be used as a procedure parameter datatype where such flexibility is needed. The values of the data instances can be of SQL built-in types as well as user-defined types.
See Also:
Oracle Call Interface Programmer's Guide, PL/SQL User's Guide and Reference, and Oracle9i Supplied PL/SQL Packages and Types Reference for the implementation of these types and guidelines for using them |
Extensible Markup Language (XML) is a standard format developed by the World Wide Web Consortium (W3C) a for representing structured and unstructured data on the Web. Universal Resource Identifiers (URIs) identify resources such as Web pages anywhere on the Web. Oracle provides types to handle XML and URI data, as well as a class of URIs called DBUri-REF
s to access data stored within the database itself. It also provides a new set of types to store and access both external and internal URIs from within the database.
This Oracle-supplied type can be used to store and query XML data in the database. SYS.XMLType
has member functions you can use to access, extract, and query the XML data using XPath expressions. XPath is another standard developed by the W3C committee to traverse XML documents. Oracle XMLType
functions support a subset of the W3C XPath expressions. Oracle also provides a set of SQL functions (including SYS_XMLGEN
and SYS_XMLAGG
) and PL/SQL packages (including DBMS_XMLGEN
) to create XMLType
values from existing relational or object relational data.
SYS.XMLType
is a system-defined type, so you can use it as an argument of a function or as the datatype of a table or view column. When you create an SYS.XMLType
column in a table, Oracle internally uses a CLOB
to store the actual XML data associated with this column. As is true for all CLOB
data, you can make updates only to the entire XML document. You can create an Oracle Text index or other function-based index on an SYS.XMLType
column.
Oracle supplies a family of URI types--SYS.UriType
, SYS.DBUriType
, and SYS.HttpUriType
--which are related by an inheritance hierarchy. SYS.UriType
is an object type and the others are subtypes of SYS.UriType
.
SYS.HttpUriType
to store URLs to external web pages or to files. It accesses these files using the HTTP (Hypertext Transfer Protocol) protocol.
SYS.DBUriType
can be used to store DBUri-REF
s, which reference data inside the database. Since SYS.UriType
is the supertype, you can create columns of this type and store SYS.DBUriType
or SYS.HttpUriType
type instances in this column. Doing so lets you reference data stored inside or outside the database and access the data consistently.
DBUri-REF
s use an XPath-like representation to reference data inside the database. If you imagine the database as a XML tree, then you would see the tables, rows, and columns as elements in the XML document. For instance, the sample human resources user hr
would see the following XML tree:
<HR> <EMPLOYEES> <ROW> <EMPLOYEE_ID>205</EMPLOYEE_ID> <LAST_NAME>Higgins</LAST_NAME> <SALARY>12000</SALARY> .. <!-- other columns --> </ROW> ... <!-- other rows --> </EMPLOYEES> <!-- other tables..--> </HR> <!-- other user schemas on which you have some privilege on..-->
The DBUri-REF
is simply an XPath expression over this virtual XML document. So to reference the SALARY
value in the EMPLOYEES
table for the employee with employee number 205, we can write a DBUri-REF
as,
/HR/EMPLOYEES/ROW[EMPLOYEE_ID=205]/SALARY
Using this model, you can reference data stored in CLOB
columns or other columns and expose them as URLs to the external world. Oracle provides a standard URI servlet that can interpret such URLs. You can install and run this servlet under the Oracle Servlet engine.
SYS.UriFactoryType
is a factory type, which is a type that can create and return other object types. When given a URL string, SYS.UriFactoryType
can create instances of the various subtypes of the UriTypes
. It analyzes the URL string, identifies the type of URL (HTTP, DBUri
, and so on) and creates an instance of the subtype.
See Also:
|
The object-relational implementation of Oracle Spatial consists of a set of object data types, an index method type, and operators on these types.
The geometric description of a spatial object is stored in a single row, in a single column of object type SDO_GEOMETRY
in a user-defined table. Any table that has a column of type SDO_GEOMETRY
must have another column, or set of columns, that defines a unique primary key for that table. Tables of this sort are sometimes referred to as geometry tables.
See Also:
Oracle Spatial User's Guide and Reference for information on the implementation of this type and guidelines for using it |
Oracle interMedia uses object types, similar to Java or C++ classes, to describe multimedia data. An instance of these object types consists of attributes, including metadata and the media data, and methods. The Oracle interMedia types are:
The ORDAudio
object type supports the storage and management of audio data.
The ORDImage
object type supports the storage and management of image data.
The ORDVideo
object type supports the storage and management of video data.
See Also:
Oracle interMedia User's Guide and Reference for information on the implementation of these types and guidelines for using them |
This section describes how Oracle compares values of each datatype.
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.
A later date is considered greater than an earlier one. For example, the date equivalent of '29-MAR-1997' is less than that of '05-JAN-1998' and '05-JAN-1998 1:35pm' is greater than '05-JAN-1998 10:09am'.
Character values are compared using one of these comparison rules:
The following sections explain these 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.
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
.
The results of comparing two character values using different comparison semantics may vary. The table below 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 |
---|---|
|
|
|
|
|
|
|
|
'a ' = 'a' |
'a ' > 'a' |
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:
Portions of the ASCII and EBCDIC character sets appear in Table 2-5 and Table 2-6. 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.
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.
See Also:
|
You cannot compare varrays and nested tables in Oracle9i.
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.
Oracle recommends that you specify explicit conversions rather than rely on implicit or automatic conversions, for these reasons:
VARCHAR2
value may return an unexpected year depending on the value of the NLS_DATE_FORMAT
parameter.
Oracle automatically converts a value from one datatype to another when such a conversion makes sense. Table 2-7 is a matrix of Oracle implicit conversions. The table shows all possible conversions, without regard to the direction of the conversion or the context in which it is made. The rules governing these details follow the table.
The following rules govern the direction in which Oracle makes implicit datatype conversions:
INSERT
and UPDATE
operations, Oracle converts the value to the datatype of the affected column.
SELECT
FROM
operations, Oracle converts the data from the column to the type of the target variable.
NUMBER
value, Oracle converts the character data to NUMBER
.
DATE
value, Oracle converts the character data to DATE
.
CHAR
or NCHAR
.
CHAR
/VARCHAR2
and NCHAR
/NVARCHAR2
, Oracle converts to a number.
CHAR
/VARCHAR2
and NCHAR
/NVARCHAR2
types may entail different character sets. The default direction of conversion in such cases is from the database character set to the national character set. Table 2-8 shows the direction of implicit conversions between different character types.
CLOB
s as parameters, and Oracle performs implicit conversions between CLOB
and CHAR
types. Therefore, functions that are not yet enabled for CLOB
s can accept CLOB
s through implicit conversion. In such cases, Oracle converts the CLOB
s to CHAR
or VARCHAR2
before the function is invoked. If the CLOB
is larger than 4000 bytes, Oracle converts only the first 4000 bytes to CHAR
.
Table 2-8 Conversion Direction of Different Character Types
TO -- FROM | CHAR | VARCHAR2 | NCHAR | NVARCHAR2 |
---|---|---|---|---|
CHAR |
|
|
|
|
VARCHAR2 |
|
|
|
|
NCHAR |
|
|
|
|
NVARCHAR2 |
|
|
|
|
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 salary + '10' FROM employees;
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 '200' to 200:
SELECT last_name FROM employees WHERE employee_id = '200';
In the following statement, Oracle implicitly converts '03-MAR-97
' to a DATE
value using the default date format 'DD-MON-YY
':
SELECT last_name FROM employees WHERE hire_date = '03-MAR-97';
In the following statement, Oracle implicitly converts the text literal 'AAAFYmAAFAAAAFGAAH' to a rowid value:
SELECT last_name FROM employees WHERE ROWID = 'AAAFYmAAFAAAAFGAAH';
You can also explicitly specify datatype conversions using SQL conversion functions. The following table shows SQL functions that explicitly convert a value from one datatype to another.
Note:
You cannot specify |
See Also:
"Conversion Functions" of the SQL Reference for details on all of the explicit conversion functions |
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|