Well in Oracle you can not do this on [long] fields:
>From the Oracle 8i reference material
LONG columns cannot be referenced by SQL functions (such as SUBSTR or
INSTR). length() is a SQL function
---- more information about datatype [long] ----
Oracle8 Application Developer's Guide
Release 8.0
A58241-01
Library
Product
Contents
Index
----------------------------------------------------------------------------
----
5
Selecting a Datatype
This chapter discusses how to use Oracle built-in datatypes in applications.
Topics include:
Oracle Built-In Datatypes
ROWIDs and the ROWID Datatype
Trusted Oracle MLSLABEL Datatype
ANSI/ISO, DB2, and SQL/DS Datatypes
Data Conversion
See Also:
For information about user-defined datatypes, refer to Oracle8 Concepts and
to Chapter 7, "User-Defined Datatypes - An Extended Example" in this manual.
Oracle Built-In Datatypes
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:
character datatypes
CHAR
NCHAR
VARCHAR2 and VARCHAR
NVARCHAR2
CLOB
NCLOB
LONG
NUMBER datatype
DATE datatype
binary datatypes
BLOB
BFILE
RAW
LONG RAW
Another datatype, ROWID, is used for values in the ROWID pseudocolumn, which
represents the unique address of each row in a table.
See Also:
Figure 5-2 summarizes the information about each Oracle built-in datatype.
See Oracle8 Concepts for general descriptions of these datatypes, and see
Chapter 6, "Large Objects (LOBs)" in this Guide for information about the
LOB datatypes.
Table 5-1 Summary of Oracle Built-In Datatypes
Datatype Description Column Length and Default
CHAR (size)
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.
VARCHAR2 (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.
NCHAR(size)
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.
NVARCHAR2 (size)
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.
CLOB
Single-byte character data.
Up to 2^32 - 1 bytes, or 4 gigabytes.
NCLOB
Single-byte or fixed-length multibyte national character set (NCHAR) data.
Up to 2^32 - 1 bytes, or 4 gigabytes.
LONG
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.
NUMBER (p, s)
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.
DATE
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 DD-MON-YY) specified by NLS_DATE_FORMAT parameter.
BLOB
Unstructured binary data.
Up to 2^32 - 1 bytes, or 4 gigabytes.
BFILE
Binary data stored in an external file.
Up to 2^32 - 1 bytes, or 4 gigabytes.
RAW (size)
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.
LONG RAW
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.
ROWID
Binary data representing row addresses.
Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each
row in the table.
MLSLABEL
Trusted Oracle datatype.
See the Trusted Oracle documentation.
Using Character Datatypes
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.
See Also:
Chapter 6, "Large Objects (LOBs)")
The LONG datatype stores variable-length character strings containing up to
two gigabytes, but with many restrictions.
See Also:
"Restrictions on LONG and LONG RAW Data" on page 5-10
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:
Space Usage
To store data more efficiently, use the 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.
Comparison Semantics
Use the CHAR datatype when you require ANSI compatibility in comparison
semantics, that is, when trailing blanks are not important in string
comparisons. Use the VARCHAR2 when trailing blanks are important in string
comparisons.
Future Compatibility
The 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.
Column Lengths for Single-Byte and Multibyte Character Sets
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, the number of bytes and
the number of characters in a column is the same. If it is multibyte, 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:
Oracle8 Reference for information about National Language Support features
of Oracle and support for different character encoding schemes.
Comparison Semantics
Oracle compares CHAR and NCHAR values using blank-padded comparison
semantics. If two values have different lengths, 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, 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, you must store these values in CHAR columns.
See Also:
For more information on comparison semantics for these datatypes, see the
Oracle8 SQL Reference.
Using the NUMBER Datatype
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,
the column stores values as given. Table 5-2 shows examples of how data
would be stored using different scale factors.
Table 5-2 How Scale Factors Affect Numeric Data Storage
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
See Also:
For information about the internal format for the NUMBER datatype, see
Oracle8 Concepts.
Using the DATE Datatype
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 the Oracle Call Interface
Programmer's Guide for a complete description of the Oracle internal date
format.
Date Format
For input and output of dates, the standard Oracle default date format is
DD-MON-YY, as in:
'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, as in:
TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')
----------------------------------------------------------------------------
----
Note:
Oracle Julian dates might not be compatible with Julian dates generated by
other date algorithms. For information about Julian dates, see Oracle8
Concepts.
----------------------------------------------------------------------------
----
If the date format DD-MON-YY is used, 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, use a different
format mask, as shown above.
Time Format
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 (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 function SYSDATE to return the
system date and time. The FIXED_DATE initialization parameter allows you to
set SYSDATE to a constant; this can be useful for testing.
Centuries and the Year 2000
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.
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. Application programmers should therefore review and test their 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. This can help applications make the conversion to the new
century easily.
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 expression:
DECODE (TO_CHAR (userdate, 'YY'),
'00', TO_CHAR (userdate - 366, 'CC'),
TO_CHAR (userdate, 'CC'))
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
so compute a date in the preceding year (whose Oracle century is the desired
true century). Otherwise, use the Oracle century.
See Also:
For more information about date format codes, see Oracle8 SQL Reference.
Using the LONG Datatype
----------------------------------------------------------------------------
----
Note:
The LONG datatype is provided for backward compatibility with existing
applications. For new applications, you should use the CLOB and NCLOB
datatypes for large amounts of character data. See Chapter 6, "Large Objects
(LOBs)" for information about the CLOB and NCLOB datatypes.
----------------------------------------------------------------------------
----
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.
Restrictions on LONG and LONG RAW Data
Although LONG (and LONG RAW; see below) columns have many uses, their use
has some restrictions:
Only one 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...).
A variable or argument of a PL/SQL program unit cannot be declared using the
LONG datatype.
Variables in database triggers cannot be declared using the LONG or LONG RAW
datatypes.
References to :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.
Note:
If you design tables containing LONG or LONG RAW data, you should place each
LONG or LONG RAW column in a table separate from any other data associated
with it, rather than storing the LONG or LONG RAW column and its associated
data together in the same table. You can then relate the two tables with a
referential integrity constraint. This design allows SQL statements that
access only the associated data to avoid reading through LONG or LONG RAW
data.
Example of LONG Datatype
To store information on magazine articles, including the texts of each
article, create two tables:
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, you can issue this
statement that queries the ARTICLE_HEADER table:
SELECT first_author
FROM 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, Oracle would have to read through
the text to perform this query.
Using RAW and LONG RAW Datatypes
----------------------------------------------------------------------------
----
Note:
The RAW and LONG RAW datatypes are provided for backward compatibility with
existing applications. For new applications, you should use the BLOB and
BFILE datatypes for large amounts of binary data. See Chapter 6, "Large
Objects (LOBs)" for information about the BLOB and BFILE datatypes.
----------------------------------------------------------------------------
----
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. For more
information about restrictions on LONG RAW data, see "Restrictions on LONG
and LONG RAW Data" on page 5-10.
ROWIDs and the ROWID Datatype
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:
Oracle8 Concepts for general information about the ROWID pseudocolumn and
the ROWID datatype.
Extended ROWID Format
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:
data object (segment) identifier
datafile identifier
block identifier
row identifier
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, the query
SELECT DATA_OBJECT_ID FROM DBA_OBJECTS
WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMP';
returns the data object identifier for the EMP table in the SCOTT schema.
"The DBMS_ROWID Package" on page 10-81 describes other ways to get the data
object identifier, using the DBMS_ROWID package functions.
Different Forms of the ROWID
Oracle documentation uses the term ROWID in different ways, depending on
context. These uses are explained in this section.
Internal ROWID
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.
ROWID Pseudocolumn
Each table and nonjoined view has a pseudocolumn called ROWID. Statements
such as
CREATE TABLE T1 (col1 ROWID);
INSERT INTO T1 SELECT ROWID FROM EMP WHERE empno = 7499;
return the ROWID pseudocolumn of the row of the EMP table that satisfies the
query, and insert it into the T1 table.
External Character ROWID
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:
OOOOOO: The data object number identifies the database segment (AAAA8m in
the example). Schema objects in the same segment, such as a cluster of
tables, have the same data object number.
FFF: The datafile that contains the row (file AAL in the example). File
numbers are unique within a database.
BBBBBB: The data block that contains the row (block AAAAQk in the example).
Block numbers are relative to their datafile, not tablespace. Therefore, two
rows with identical block numbers could reside in two different datafiles of
the same tablespace.
RRR: The row in the block (row AAA in the example).
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.
See Also:
"The DBMS_ROWID Package" on page 10-81.
.
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.
External Binary 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 ROWIDs. 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;
}
ROWID Migration and Compatibility Issues
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 ROWIDs.
See Also:
Oracle8 Administrator's Guide.
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:
FThe description of "ROWID_TO_EXTENDED Function" on page 10-88 has more
information, as has Oracle8 Migration.
Accessing an Oracle7 Database from an Oracle8 Client
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:
selecting a ROWID and using the obtained value in a WHERE clause
WHERE CURRENT OF cursor operations
storing ROWIDs in user columns of ROWID or CHAR type
interpreting ROWIDs using the hexadecimal encoding (not recommended, use the
DBMS_ROWID functions)
Accessing an Oracle8 Database from an Oracle7 Client
Oracle8 returns ROWIDs in the extended format. This means that you can only:
select a ROWID and use it in a WHERE clause
use WHERE CURRENT OF cursor operations
store ROWIDs in user columns of CHAR(18) datatype
Import and Export
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 MLSLABEL Datatype
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 MLSLABEL
datatype, the ROWLABEL column, and Trusted Oracle.
----------------------------------------------------------------------------
----
ANSI/ISO, DB2, and SQL/DS Datatypes
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 5-3.
The ANSI/ISO datatypes NUMERIC, DECIMAL, and DEC can specify only
fixed-point numbers. For these datatypes, s defaults to 0.
Table 5-3 ANSI Datatype Conversions to Oracle Datatypes
ANSI SQL Datatype Oracle Datatype
CHARACTER (n), CHAR (n) CHAR (n)
NUMERIC (p,s), DECIMAL (p,s), DEC (p,s) NUMBER (p,s)
INTEGER, INT, SMALLINT NUMBER (38)
FLOAT (p) FLOAT (p)
REAL FLOAT (63)
DOUBLE PRECISION FLOAT (126)
CHARACTER VARYING(n), CHAR VARYING(n) VARCHAR2 (n)
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 5-4 shows the DB2 and SQL/DS conversions.
Table 5-4 SQL/DS, DB2 Datatype Conversions to Oracle Datatypes
DB2 or SQL/DS Datatype Oracle Datatype
CHARACTER (n) CHAR (n)
VARCHAR (n) VARCHAR2 (n)
LONG VARCHAR LONG
DECIMAL (p,s) NUMBER (p,s)
INTEGER, SMALLINT NUMBER (38)
FLOAT (p) FLOAT (p)
DATE DATE
Data Conversion
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:
TO_NUMBER()
TO_CHAR()
TO_DATE()
HEXTORAW()
RAWTOHEX()
ROWIDTOCHAR()
CHARTOROWID()
Implicit datatype conversions work according to the rules explained below.
See Also:
If you are using Trusted Oracle, see "Data Conversion for Trusted Oracle" on
page 5-22 for information about data conversions and the MLSLABEL datatype.
I
Rule 1: Assignments
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:
var1 CHAR(5);
CREATE TABLE table1 (col1 NUMBER);
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 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 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 WHERE col1 = 30;
Rule 2: Expression Evaluation
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:
VARCHAR2 or CHAR to NUMBER
VARCHAR2 or CHAR to DATE
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:
Simple expressions, such as
comm + '500'
Boolean expressions, such as
bonus > sal / '10'
Function and procedure calls, such as
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, 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.
Data Conversion for Trusted Oracle
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
TO_LABEL and TO_CHAR functions to convert label formats.
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Prev
Next
<<<Additional text truncated.>>>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
