Release 2 (8.1.6)
A76989-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Basic Elements of Oracle SQL, 3 of 10
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 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, as shown in Figure 2-1.
The syntax of the Oracle built-in datatypes appears in the next diagram. Table 2-1 summarizes Oracle built-in datatypes. The rest of this section describes these datatypes as well as the various kinds of user-defined types.
|
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 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. |
built-in datatypes:
The ANSI-supported datatypes appear in the figure that follows. Table 2-2 shows the mapping of ANSI-supported datatypes to Oracle build-in datatypes.
ANSI-supported 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 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:
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".
|
Note: To ensure proper data conversion between databases with different character sets, you must ensure that CHAR data consists of well-formed strings. See Oracle8i National Language Support Guide for more information on character set support. |
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 of the database 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';
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 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 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));
The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you 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 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. 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".
|
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:
|
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:
|
|
is a fixed-point number with precision p and scale 0. This is equivalent to |
Specify a floating-point number using the following form:
|
|
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.) |
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 |
NUMBER(4,5) |
.01234 |
|
.00012 |
NUMBER(4,5) |
.00012 |
|
.000127 |
NUMBER(4,5) |
.00013 |
|
.0000012 |
NUMBER(2,7) |
.0000012 |
|
.00000123 |
NUMBER(2,7) |
.0000012 |
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:
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.
|
Note:
Oracle Corporation strongly recommends that you convert |
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 integrity constraints (except for NULL and NOT NULL constraints).
LONG columns cannot be indexed.
LONG value.
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 clauses, 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
SUBSTR or INSTR)
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
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 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.
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 time 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 "Date Format Models" and Chapter 4, "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 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 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.
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 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 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 (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 the 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.
BFILEs, the actual operating system file is not deleted.
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. (See "EMPTY_[B | C]LOB".) You can then select the empty LOB attribute and populate it using the DBMS_LOB package or some other appropriate interface.
The following example creates a table with LOB columns. (It assumes the existence of tablespace RESUMES).
CREATE TABLE person_table (name CHAR(40), resume CLOB, picture BLOB) LOB (resume) STORE AS ( TABLESPACE resumes STORAGE (INITIAL 5M NEXT 5M) );
|
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. See "CREATE DIRECTORY".
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 allows 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:
Oracle8i Application Developer's Guide - Large Objects (LOBs) and Oracle Call Interface Programmer's Guide for more information about LOBs. |
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. 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.
The CLOB datatype stores single-byte 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. 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.
The NCLOB datatype stores multibyte national character set character (NCHAR) data. 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. Note, however, that 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:
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 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, as well as information specific to extended rowids. For information on the functions available with the DBMS_ROWID package and how to use them, see Oracle8i Supplied PL/SQL Packages Reference.
The restricted form of a rowid is still supported in Oracle8i for backward compatibility, but all tables return rowids in the extended format. For information regarding compatibility and migration issues, see Oracle8i Migration.
Each row in a database has an address (as discussed in "ROWID Datatype"). 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 (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:
Oracle8i Concepts and Oracle8i Designing and Tuning for Performance for more information on the |
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-2 and Table 2-3.
Do not define columns with these 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. For information about Oracle built-in datatypes, see Oracle8i Concepts. For information about creating user-defined types, see "CREATE TYPE" and the "CREATE TYPE BODY". For information about using user-defined types, see Oracle8i Application Developer's Guide - Fundamentals.
The sections that follow describe the various categories of user-defined types.
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. REFs 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 table DEPT with column MGR whose type is a REF to type EMP_T, which has an attribute NAME:
SELECT t.mgr.name FROM dept t WHERE t.mgr 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 (see the varray_storage_clause of "CREATE TABLE").
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.
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. 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' |
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.
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-4 and Table 2-5. 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.
For a description of MAP and ORDER methods and the values they return, see "CREATE TYPE". See also Oracle8i Application Developer's Guide - Fundamentals for more information.
You cannot compare varrays and nested tables in Oracle8i.
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 automatically converts a value from one datatype to another when such a conversion makes sense. Oracle performs conversions in these cases:
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.
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;
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';
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';
In the following statement, Oracle implicitly converts the text literal 'AAAAZ8AABAAABvlAAA' to a rowid value:
SELECT ename FROM emp WHERE ROWID = 'AAAAZ8AABAAABvlAAA';
You can also explicitly specify datatype conversions using SQL conversion functions. Table 2-6 shows SQL functions that explicitly convert a value from one datatype to another.
For information on these functions, see "Conversion Functions".
|
Note:
You cannot specify |
Oracle recommends that you specify explicit conversions rather than rely on implicit or automatic conversions for these reasons:
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|