Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5 A68003-01 |
|
This chapter discusses how to use Oracle built-in datatypes in applications. Topics include:
For information about user-defined datatypes, refer to Oracle8i Concepts and to Chapter 16, "User-Defined Datatypes" in this manual.
See Also:
A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle to treat values of one datatype differently from values of another datatype. For example, Oracle can add values of NUMBER
datatype, but not values of RAW
datatype.
Oracle supplies the following built-in datatypes:
Another datatype, ROWID
, is used for values in the ROWID
pseudocolumn, which represents the unique address of each row in a table.
See Also:
See Oracle Call Interface Programmer's Guide for general descriptions of these datatypes, and see Oracle8i Application Developer's Guide - Large Objects (LOBs) for information about the |
Table 4-1 summarizes the information about each Oracle built-in datatype.
Datatype | Description | Column Length and Default |
|
Fixed-length character data of length size bytes |
Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (one-byte or multibyte) before setting size. |
|
Variable-length character data |
Variable for each row, up to 4000 bytes per row: Consider the character set (one-byte or multibyte) before setting size: A maximum size must be specified. |
|
Fixed-length character data of length size characters or bytes, depending on the national character set |
Fixed for every row in the table (with trailing blanks). Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 2000 bytes per row. Default is 1 character or 1 byte, depending on the character set. |
|
Variable-length character data of length size characters or bytes, depending on national character set: A maximum size must be specified |
Variable for each row. Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 4000 bytes per row. Default is 1 character or 1 byte, depending on the character set. |
|
Single-byte character data |
Up to 2^32 - 1 bytes, or 4 gigabytes. |
|
Single-byte or fixed-length multibyte national character set ( |
Up to 2^32 - 1 bytes, or 4 gigabytes. |
|
Variable-length character data |
Variable for each row in the table, up to 2^31 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility. |
|
Variable-length numeric data.: Maximum precision p and/or scale s is 38 |
Variable for each row. The maximum space required for a given column is 21 bytes per row. |
|
Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E. |
Fixed at 7 bytes for each row in the table. Default format is a string (such as |
|
Unstructured binary data |
Up to 2^32 - 1 bytes, or 4 gigabytes. |
|
Binary data stored in an external file |
Up to 2^32 - 1 bytes, or 4 gigabytes. |
|
Variable-length raw binary data |
Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility. |
|
Variable-length raw binary data |
Variable for each row in the table, up to 2^31 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility. |
|
Binary data representing row addresses |
Fixed at 10 bytes (extended |
|
Trusted Oracle datatype |
See the Trusted Oracle documentation. |
Use the character datatypes to store alphanumeric data.
CHAR
and NCHAR
datatypes store fixed-length character strings.
VARCHAR2
and NVARCHAR2
datatypes store variable-length character strings. (The VARCHAR
datatype is synonymous with the VARCHAR2
datatype.)
CLOB
and NCLOB
datatypes store single-byte and multibyte character strings of up to four gigabytes.
LONG
datatype stores variable-length character strings containing up to two gigabytes, but with many restrictions.
This datatype is provided for backward compatibility with existing applications; in general, new applications should use CLOB
and NCLOB
datatypes to store large amounts of character data.
When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:
VARCHAR2
datatype. The CHAR
datatype blank-pads and stores trailing blanks up to a fixed column length for all column values, while the VARCHAR2
datatype does not blank-pad or store trailing blanks for column values.
CHAR
datatype when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the VARCHAR2
when trailing blanks are important in string comparisons.
CHAR
and VARCHAR2
datatypes are and will always be fully supported. At this time, the VARCHAR
datatype automatically corresponds to the VARCHAR2
datatype and is reserved for future use.
CHAR
, VARCHAR2
, and LONG
data is automatically converted from the database character set to the character set defined for the user session by the NLS_LANGUAGE
parameter, where these are different.
The lengths of CHAR
and VARCHAR2
columns are specified in bytes rather than characters, and are constrained as such. The lengths of NCHAR
and NVARCHAR2
columns are specified either in bytes or in characters, depending on the national character set being used.
When using a multibyte database character encoding scheme, consider carefully the space required for tables with character columns. If the database character encoding scheme is single-byte, then the number of bytes and the number of characters in a column is the same. If it is multibyte, then there generally is no such correspondence. A character might consist of one or more bytes depending upon the specific multibyte encoding scheme, and whether shift-in/shift-out control codes are present.
See Also:
Oracle8i Reference for information about National Language Support features of Oracle and support for different character encoding schemes. |
Oracle compares CHAR
and NCHAR
values using blank-padded comparison semantics. If two values have different lengths, then Oracle adds blanks at the end of the shorter value, until the two values are the same length. 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. Two values that differ only in the number of trailing blanks are considered equal.
Oracle compares VARCHAR2
and NVARCHAR2
values using non-padded comparison semantics. Two values are considered equal only if they have the same characters and are of equal length. Oracle compares the values character-by-character up to the first character that differs. The value with the greater character in that position is considered greater.
Because Oracle blank-pads values stored in CHAR
columns but not in VARCHAR2
columns, a value stored in a VARCHAR2
column may take up less space than if it were stored in a CHAR
column. For this reason, a full table scan on a large table containing VARCHAR2
columns may read fewer data blocks than a full table scan on a table containing the same data stored in CHAR
columns. If your application often performs full table scans on large tables containing character data, then you might be able to improve performance by storing this data in VARCHAR2
columns rather than in CHAR
columns.
However, performance is not the only factor to consider when deciding which of these datatypes to use. Oracle uses different semantics to compare values of each datatype. You might choose one datatype over the other if your application is sensitive to the differences between these semantics. For example, if you want Oracle to ignore trailing blanks when comparing character values, then you must store these values in CHAR
columns.
See Also:
For more information on comparison semantics for these datatypes, see the Oracle8i Reference. |
Use the NUMBER
datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10^-130 to 9.99...x10^125, as well as zero, in a NUMBER
column.
For numeric columns you can specify the column as a floating-point number:
Column_name NUMBER
Or, you can specify a precision (total number of digits) and scale (number of digits to right of decimal point):
Column_name NUMBER (<precision>, <scale>)
Although not required, specifying the precision and scale for numeric fields provides extra integrity checking on input. If a precision is not specified, then the column stores values as given. Table 3-2 shows examples of how data would be stored using different scale factors.
Input Data | Stored As | Specified As |
---|---|---|
7,456,123.89 | NUMBER | 7456123.89 |
7,456,123.89 | NUMBER (9) | 7456124 |
7,456,123.89 | NUMBER (9,2) | 7456123.89 |
7,456,123.89 | NUMBER (9,1) | 7456123.9 |
7,456,123.89 | NUMBER (6) | (not accepted, exceeds precision) |
7,456,123.89 | NUMBER (7, -2) | 7456100 |
Use the DATE
datatype to store point-in-time values (dates and times) in a table. The DATE
datatype stores the century, year, month, day, hours, minutes, and seconds.
Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.
See Also:
See the Oracle Call Interface Programmer's Guide for a complete description of the Oracle internal date format. |
For input and output of dates, the standard Oracle default date format is DD-MON-YY
. For example:
'13-NOV-92'
To change this default date format on an instance-wide basis, use the NLS_DATE_FORMAT
parameter. To change the format during a session, use the ALTER
SESSION
statement. To enter dates that are not in the current default date format, use the TO_DATE
function with a format mask. For example:
TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')
See Also:
Oracle Julian dates might not be compatible with Julian dates generated by other date algorithms. For information about Julian dates, see Oracle8i Concepts. |
If the date format DD-MON-YY
is used, then YY
indicates the year in the 20th century (for example, 31-DEC-92 is December 31, 1992). If you want to indicate years in any century other than the 20th century, then use a different format mask, as shown above.
Time is stored in 24-hour format#HH:MM:SS
. By default, the time in a date field is 12:00:00 A.M. (midnight) if no time portion is entered. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, use the TO_DATE
function with a format mask indicating the time portion, as in:
INSERT INTO Birthdays_tab (bname, bday) VALUES('ANNIE',TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.'));To compare dates that have time data, use the SQL function
TRUNC
if you want to ignore the time component. Use the SQL functionSYSDATE
to return the system date and time. TheFIXED_DATE
initialization parameter allows you to setSYSDATE
to a constant; this can be useful for testing.Establishing Year 2000 Compliance
An application must satisfy the following criteria to meet the requirements for Year 2000 (Y2K) compliance:
These criteria are a superset of the Year 2000 conformance requirements set out by the British Standards Institute in DISC PD-2000-1 A Definition of Year 2000 Conformity Requirements.
You can warrant your application as Y2K compliant only if you have validated its conformance at all three of the following system levels:
The Oracle Server is Year 2000 compliant. No operational problems are expected with the Oracle Server, networking and system management products. Oracle's Development Organization has conducted tests of various Year 2000 operational scenarios to verify that there is no impact to users at the turn of the century. These scenarios included tests of replication, point-in-time recovery, distributed transactions. System management and networking features across time zones / datelines / centuries have also been tested.
Please note that Oracle's Year 2000 product compliance does not eliminate the need for you to test your own applications. Most importantly, your application software has to be tested on the Oracle Server to ensure that operations having to do with the year 2000 perform as promised. This test is critical even if the application software is certified to be Year 2000 compliant because there are no universal protocol definitions that can guarantee conformance without such testing.
Oracle stores year data with the century information. For example, the Oracle database stores 1996 or 2001, and not just 96 or 01. The DATE
datatype always stores a four-digit year internally, and all other dates stored internally in the database have four digit years. Oracle utilities such as import, export, and recovery also deal properly with four-digit years.
Applications that use the Oracle RDBMS (Oracle7 and Oracle8 Server) and exploit the DATE
data type (for date and/or date with time values) need have no concerns about their stored data when the year 2000 approaches. The Oracle7 and Oracle8 Server DATE data type stores date and time data to a precision that includes a four digit year and a time component down to seconds (typically `YYYY:MM:DD:HH24:MI:SS')
However, some applications might be written with an assumption about the year (such as assuming that everything is 19xx). The application might hand over a two-digit year to the database, and the procedures that Oracle uses for determining the century could be different from what the programmer expects (see "Programming Hints and Tips"). For this reason, you should review and test your code with regard to the Year 2000.
The RR date format element of the TO_DATE
and TO_CHAR
functions allows a database site to default the century to different values depending on the two-digit year, so that years 50 to 99 default to 19xx and years 00 to 49 default to 20xx. Therefore, regardless of the current century at the time the data is entered, the 'RR' format will ensure that the year stored in the database is as follows:
The `RR' date format is available for inserting and updating DATE
data in the database. It is not required for retrieval or query of data already stored in the database as Oracle has always stored the YEAR
component of a date in its four-digit form.
Here is an example of the RR usage:
INSERT INTO emp (empno, deptno,hiredate) VALUES
(9999, 20, TO_DATE('01-jan-03', 'DD-MON-RR'));
INSERT INTO emp (empno, deptno,hiredate) VALUES
(8888, 20, TO_DATE('01-jan-67', 'DD-MON-RR'));
SELECT empno, deptno,
TO_CHAR(hiredate, 'DD-MON-YYYY') hiredate
FROM
emp;
This produces the following data:
EMPNO DEPTNO HIREDATE
---------- ---------- -----------------
8888 20 01-JAN-1967
9999 20 01-JAN-2003
The CC date format element of the TO_CHAR
function sets the century value to one greater than the first two digits of a four-digit year (for example, '20' from '1900'). For years that are a multiple of 100, this is not the true century. Strictly speaking, the century of '1900' is not the twentieth century (which began in 1901) but rather the nineteenth century.
The following workaround computes the correct century for any Common Era (CE, formerly known as AD) date. If userdate is a CE date for which you want the true century, use the following expression:
SELECT DECODE (TO_CHAR (Hiredate, 'YY'),'00', TO_CHAR (Hiredate - 366, 'CC'), TO_CHAR (Hiredate, 'CC')) FROM Emp_tab;
This expression works as follows: Get the last two digits of the year. If it is '00', then it is a year in which the Oracle century is one year too large, and compute a date in the preceding year (whose Oracle century is the desired true century). Otherwise, use the Oracle century.
Where applications store date values in CHAR
or VARCHAR2
datatypes, and the century information is not maintained, you will need to modify the application to include routines which ensure that such dates are treated appropriately when affected by the change in century. You can do this by changing the strings to maintain century information or, with certain constraints, by using the 'RR' date format when interpreting the string as a date.
If you are creating a new application, or if you are modifying an application to ensure that dates stored as character strings are Year 2000 compliant, we advise that you convert dates to use the Oracle DATE
data type. If this is not feasible, store the dates in a form which is language and format independent, and which handles full years. For example, utilize `SYYYY/MM/DD' plus the time element as `HH24:MI:SS' if necessary. Note that dates stored in this form must be converted to the correct external format whenever they are displayed or received from users or other programs.
The format 'SYYYY/MM/DD HH24:MI:SS' has the following advantages:
The following views will enable you to verify what your settings are:
V$NLS_DATABASE_PARAMETERS
-- shows instance wide NLS parameters whether explicitly declared in the INIT
.ORA
or defaulting.
NLS_SESSION_PARAMETERS
-- shows current session values which may have been been changed by means of ALTER
SESSION
A format model is a character that describes the format of DATE
or NUMBER
data stored in a character string. You may use the format model as an argument of the TO_CHAR
or TO_DATE
function for one of the following:
Please note that the format does not change the internal representation of the value in the database.
You may set the date format in your environment or as the default for the entire database. If you set this in your environment it will override the setting in the initialization parameter.
INIT
.ORA
to include the following
NLS_DATE_FORMAT = DD-MON-RR
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR'
In this section we describe some common programming problems around Y2K compliance. These problems may seem to derive from incorrect Year 2000 processing by the database engine, but on closer inspection are seen to arise from incorrect use of Oracle technology.
Your application may have defined the year of a date using a column of CHAR
(2) or NUMBER
(2) in order to save disk space. This can lead to unpredictable results when 20xx dates are mixed with 19xx dates. To resolve this, modify your application to use the full 4-digit year.
You application may be designed to store a 4-digit year, but the code may allow for the incorrect storage of 2-digit year rows with the 4-digit year rows. This will lead to unpredictable results for queries by date if the date columns contains dates earlier than 1900. To deal with this problem, have your application check for rows which contain dates earlier than 1900, and then adjust for this.
Examine your applications to determine if it processes dates prior to 1950 or later than 2049, and store the year as 2-digits. If both conditions are met, your application should not use the 'RR' format but should instead expand the 2 digit year `YY ` into a for digit year `YYYY', and store the 4 digit number in the database.
The following unusual error helps illuminate the interaction between NLS_DATE_FORMAT
and the Oracle 'RR'
format mask. The following is a syntactically correct statement but contains a logical flaw:
SELECT TO_CHAR(TO_DATE(LAST_DAY(`01-FEB-00'),'DD-MON-RR'),'MM/DD/RRRR') FROM DUAL;
The above query will return 02/28/2000. This is consistent with the defined behavior of the `RR' format element. However, since the year 2000 is a leap year, this is incorrect.
The problems is that the operation is using the default NLS_DATE_FORMAT
, which is 'DD-MON-YY'. If the NLS_DATE_FORMAT
is changed to 'DD-MON-RR', then the same select returns 02/29/2000, which is the correct value.
Let us evaluate the query as the Oracle Server engine does. The first function processed is the innermost function, LAST_DAY
. Because NLS_DATE_FORMAT
is YY, this correctly returns 2/28, because it is using the year 1900 to evaluate the expression. The value 2/28 is then returned to the next outer function. So, the TO_DATE
and TO_CHAR
functions format the value 02/28/00 using the 'RR' format mask and display the result as 02/28/2000.
If SELECT
LAST_DAY
('01-FEB-00'
) FROM
DUAL
is issued, the result will change depending on the NLS_DATE_FORMAT
. With 'YY', the LAST_DAY
returned is 28-Feb-00 because the year is interpreted as 1900. With 'RR', the LAST_DAY
returned is 29-Feb-00 because the year is interpreted as 2000. The year 1900 is not a leap year whereas the year 2000 is a leap year.
Note:
The |
The LONG
datatype can store variable-length character data containing up to two gigabytes of information. The length of LONG
values might be limited by the memory available on your computer.
You can use columns defined as LONG
in SELECT
lists, SET
clauses of UPDATE
statements, and VALUES
clauses of INSERT
statements. LONG
columns have many of the characteristics of VARCHAR2
columns.
Although LONG
(and LONG
RAW
; see below) columns have many uses, their use has some restrictions:
LONG
column is allowed per table.
LONG
columns cannot be indexed.
LONG
columns cannot appear in integrity constraints.
LONG
columns cannot be used in WHERE,
GROUP
BY,
ORDER
BY
, or CONNECT
BY
clauses or with the DISTINCT
operator in SELECT
statements.
LONG
columns cannot be referenced by SQL functions (such as SUBSTR
or INSTR
).
LONG
columns cannot be used in the SELECT
list of a subquery or queries combined by set operators (UNION,
UNION
ALL,
INTERSECT,
or MINUS
).
LONG
columns cannot be used in SQL expressions.
LONG
columns cannot be referenced when creating a table with a query (CREATE
TABLE
... AS
SELECT
...) or when inserting into a table or view with a query (INSERT
INTO
... SELECT
...).
LONG
datatype.
LONG
or LONG
RAW
datatypes.
NEW
and :OLD
in database triggers cannot be used with LONG
or LONG
RAW
columns.
LONG
and LONG
RAW
columns cannot be used in distributed SQL statements.
LONG
and LONG
RAW
columns cannot be replicated.
To store information on magazine articles, including the texts of each article, create two tables. For example:
CREATE TABLE Article_header(Id NUMBER PRIMARY KEY, Title VARCHAR2(200), First_author VARCHAR2(30), Journal VARCHAR2(50), Pub_date DATE);CREATE TABLE article_text(Id NUMBER REFERENCES Article_header, Text LONG);
The ARTICLE_TEXT
table stores only the text of each article. The ARTICLE_HEADER
table stores all other information about the article, including the title, first author, and journal and date of publication. The two tables are related by the referential integrity constraint on the ID
column of each table.
This design allows SQL statements to query data other than the text of an article without reading through the text. If you want to select all first authors published in Nature magazine during July 1991, then you can issue this statement that queries the ARTICLE_HEADER
table:
SELECT First_authorFROM Article_header WHERE Journal = 'NATURE'AND TO_CHAR(Pub_date, 'MM YYYY') = '07 1991';
If the text of each article were stored in the same table with the first author, publication, and publication date, then Oracle would need to read through the text to perform this query.
See Also:
See Oracle8i Application Developer's Guide - Large Objects (LOBs) for information about the |
The RAW
and LONG
RAW
datatypes store data that is not to be interpreted by Oracle (that is, not to be converted when moving data between different systems). These datatypes are intended for binary data and byte strings. For example, LONG
RAW
can be used to store graphics, sound, documents, and arrays of binary data; the interpretation is dependent on the use.
Net8 and the Export and Import utilities do not perform character conversion when transmitting RAW
or LONG
RAW
data. When Oracle automatically converts RAW
or LONG
RAW
data to and from CHAR
data (as is the case when entering RAW
data as a literal in an INSERT
statement), the data is represented as one hexadecimal character representing the bit pattern for every four bits of RAW
data. For example, one byte of RAW
data with bits 11001011 is displayed and entered as 'CB'.
LONG
RAW
data cannot be indexed, but RAW
data can be indexed.
See Also:
For more information about restrictions on |
Every row in a nonclustered table of an Oracle database is assigned a unique ROWID
that corresponds to the physical address of a row's row piece (initial row piece if the row is chained among multiple row pieces). In the case of clustered tables, rows in different tables that are in the same data block can have the same ROWID
.
Each table in an Oracle database internally has a pseudocolumn named ROWID
.
See Also:
Oracle8i Concepts for general information about the |
The Oracle Server uses an extended ROWID
format, which supports features such as table partitions, index partitions, and clusters.
The extended ROWID
includes the following information:
The data object identifier is an identification number that Oracle assigns to schema objects in the database, such as nonpartitioned tables or partitions. For example:
SELECT DATA_OBJECT_ID FROM ALL_OBJECTS WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMP_TAB';
This query returns the data object identifier for the EMP_TAB
table in the SCOTT
schema.
See Also:
Oracle8i Supplied Packages Reference for information about other ways to get the data object identifier, using the |
Oracle documentation uses the term ROWID
in different ways, depending on context. These uses are explained in this section.
The internal ROWID
format is an internal structure which holds information that the server code needs to access a row. The restricted internal ROWID
is 6 bytes on most platforms; the extended ROWID
is 10 bytes on these platforms.
Each table and nonjoined view has a pseudocolumn called ROWID
. For example:
CREATE TABLE T_tab (col1 Rowid);INSERT INTO T_tab SELECT Rowid FROM Emp_tab WHERE Empno = 7499;
This command returns the ROWID
pseudocolumn of the row of the EMP_TAB
table that satisfies the query, and inserts it into the T1
table.
The extended ROWID
pseudocolumn is returned to the client in the form of an 18-character string (for example, "AAAA8mAALAAAAQkAAA"), which represents a base 64 encoding of the components of the extended ROWID
in a four-piece format, OOOOOOFFFBBBBBBRRR:
There is no need to decode the external ROWID
; you can use the functions in the DBMS_ROWID
package to obtain the individual components of the extended ROWID
.
The restricted ROWID
pseudocolumn is returned to the client in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID
.
Some client applications use a binary form of the ROWID
. For example, OCI and some precompiler applications can map the ROWID
to a 3GL structure on bind or define calls. The size of the binary ROWID
is the same for extended and restricted ROWID
s. The information for the extended ROWID
is included in an unused field of the restricted ROWID
structure.
The format of the extended binary ROWID
, expressed as a C struct, is:
struct riddef {ub4 ridobjnum; /* data obj#--this field is unused in restricted ROWIDs */ ub2 ridfilenum; ub1 filler; ub4 ridblocknum; ub2 ridslotnum;}
For backward compatibility, the restricted form of the ROWID
is still supported. These ROWIDs
exist in massive amounts of Oracle7 data, and the extended form of the ROWID
is required only in global indexes on partitioned tables. New tables always get extended ROWID
s.
It is possible for an Oracle7 client to access an Oracle8 database. Similarly, an Oracle8 client can access an Oracle7 Server. A client in this sense can include a remote database accessing a server using database links, as well as a client 3GL or 4GL application accessing a server.
See Also:
There is more information on the |
The ROWID
values that are returned are always restricted ROWIDs
. Also, Oracle8 uses restricted ROWIDs
when returning a ROWID
value to an Oracle7 or earlier server.
The following ROWID
functionality works when accessing an Oracle7 Server:
ROWID
and using the obtained value in a WHERE
clause
WHERE
CURRENT
OF
cursor operations
ROWID
s in user columns of ROWID
or CHAR
type
ROWID
s using the hexadecimal encoding (not recommended, use the DBMS_ROWID
functions)
Oracle8 returns ROWID
s in the extended format. This means that you can only:
ROWID
and use it in a WHERE
clause
WHERE CURRENT OF
cursor operations
ROWID
s in user columns of CHAR(18)
datatype
It is not possible for an Oracle7 client to import an Oracle8 table that has a ROWID
column (not the ROWID
pseudocolumn), if any row of the table contains an extended ROWID
value.
Trusted Oracle provides the MLSLABEL
datatype, which stores Trusted Oracle's internal representation of labels generated by multilevel secure (MLS) operating systems. Trusted Oracle uses labels to control database access.
You can define a column using the MLSLABEL
datatype for compatibility with Trusted Oracle applications, but the only valid value for the column in Oracle8 is NULL
.
When you create a table in Trusted Oracle, a column called ROWLABEL
is automatically appended to the table. This column contains a label of the MLSLABEL
datatype for every row in the table.
See Also:
Trusted Oracle documentation for more information about the |
You can define columns of tables in an Oracle database using ANSI/ISO, DB2, and SQL/DS datatypes. Oracle internally converts such datatypes to Oracle datatypes.
The ANSI datatype conversions to Oracle datatypes are shown in Table 4-3. The ANSI/ISO datatypes NUMERIC
, DECIMAL
, and DEC
can specify only fixed-point numbers. For these datatypes, s defaults to 0.
The IBM products SQL/DS, and DB2 datatypes TIME
, TIMESTAMP
, GRAPHIC
, VARGRAPHIC
, and LONG
VARGRAPHIC
have no corresponding Oracle datatype and cannot be used. The TIME
and TIMESTAMP
datatypes are subcomponents of the Oracle datatype DATE
.
Table 4-4 shows the DB2 and SQL/DS conversions.
DB2 or SQL/DS Datatype | Oracle Datatype |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
In some cases, Oracle allows data of one datatype where it expects data of a different datatype. Generally, an expression cannot contain values with different datatypes. However, Oracle can use the following functions to automatically convert data to the expected datatype:
Implicit datatype conversions work according to the rules explained below.
See Also:
If you are using Trusted Oracle, see "Data Conversion for Trusted Oracle" for information about data conversions and the |
For assignments, Oracle can automatically convert the following:
VARCHAR2
or CHAR
to NUMBER
NUMBER
to VARCHAR2
VARCHAR2
or CHAR
to DATE
DATE
to VARCHAR2
VARCHAR2
or CHAR
to ROWID
ROWID
to VARCHAR2
VARCHAR2
or CHAR
to MLSLABEL
MLSLABEL
to VARCHAR2
VARCHAR2
or CHAR
to HEX
HEX
to VARCHAR2
The assignment succeeds if Oracle can convert the datatype of the value used in the assignment to that of the assignment's target.
For the examples in the following list, assume a package with a public variable and a table declared as in the following statements:
variable := expression
The datatype of expression must be either the same as, or convertible to, the datatype of variable. For example, Oracle automatically converts the data provided in the following assignment within the body of a stored procedure:
VAR1 := 0;
INSERT
INTO
table
VALUES
(expression1
, expression2
, ...)
The datatypes of expression1, expression2, and so on, must be either the same as, or convertible to, the datatypes of the corresponding columns in table. For example, Oracle automatically converts the data provided in the following INSERT
statement for TABLE1
(see table definition above):
INSERT INTO Table1_tab VALUES ('19');
UPDATE
table SET column = expression
The datatype of expression must be either the same as, or convertible to, the datatype of column. For example, Oracle automatically converts the data provided in the following UPDATE
statement issued against TABLE1
:
UPDATE Table1_tab SET col1 = '30';
SELECT
column INTO
variable FROM
table
The datatype of column must be either the same as, or convertible to, the datatype of variable. For example, Oracle automatically converts data selected from the table before assigning it to the variable in the following statement:
SELECT Col1 INTO Var1 FROM Table1_tab WHERE Col1 = 30;
For expression evaluation, Oracle can automatically perform the same conversions as for assignments. An expression is converted to a type based on its context. For example, operands to arithmetic operators are converted to NUMBER
and operands to string functions are converted to VARCHAR2
.
Oracle can automatically convert the following:
Character to NUMBER
conversions succeed only if the character string represents a valid number. Character to DATE
conversions succeed only if the character string satisfies the session default format, which is specified by the initialization parameter NLS_DATE_FORMAT
.
Some common types of expressions follow:
Comm + '500'
Bonus > Sal / '10'
MOD (Counter, '2')
WHERE
clause conditions, such as:
WHERE Hiredate = TO_DATE('1997-01-01','yyyy-mm-dd')
WHERE
clause conditions, such as:
WHERE Rowid = 'AAAAaoAATAAAADAAA'
In general, Oracle uses the rule for expression evaluation when a datatype conversion is needed in places not covered by the rule for assignment conversions.
In assignments of the form:
variable := expression
Oracle first evaluates expression using the conversions covered by Rule 2; expression can be as simple or complex as desired. If it succeeds, then the evaluation of expression results in a single value and datatype. Then, Oracle tries to assign this value to the assignment's target using Rule 1.
In Trusted Oracle, labels are stored internally as compact binary structures. Trusted Oracle provides the TO_LABEL
function that enables you to convert a label from its internal binary format to an external character format. To convert a label from character format to binary format in Trusted Oracle, you use the TO_CHAR
function.
The TO_LABEL
function is provided for compatibility with Trusted Oracle applications. It returns the NULL
value in Oracle8.
See Also:
The Trusted Oracle documentation has more information about using the |