Datatypes - Expert Oracle Database Architecture - page 558

or simply

EODA@ORA12CR1> select numtodsinterval( 10*86400+2*3600+3*60+2.3312, 'second' ) from dual;
NUMTODSINTERVAL(10*86400+2*3600+3*60+2.3312,'SECOND')
---------------------------------------------------------------------------
+000000010 02:03:02.331200000

using the fact that there are 86,400 seconds in a day, 3,600 seconds in an hour, and so on. Alternatively, as before, we
can use the TO_DSINTERVAL function to convert a string into a DAY TO SECOND interval

EODA@ORA12CR1> select to_dsinterval( '10 02:03:02.3312' ) from dual;
TO_DSINTERVAL('1002:03:02.3312')
---------------------------------------------------------------------------
+000000010 02:03:02.331200000

or just using an INTERVAL literal in SQL itself

EODA@ORA12CR1> select interval '10 02:03:02.3312' day to second from dual;
INTERVAL'1002:03:02.3312'DAYTOSECOND
---------------------------------------------------------------------------
+10 02:03:02.331200

LOB Types
LOBs , or large objects , are the source of much confusion, in my experience. They are a misunderstood datatype, both
in how they are implemented and how best to use them. This section provides an overview of how LOBs are stored
physically and the considerations you must take into account when using a LOB type. They have many optional
settings, and getting the right mix for your application is crucial.
There are four types of LOBs supported in Oracle:

•

CLOB : A character LOB. This type is used to store large amounts of textual information, such
as XML or just plain text. This datatype is subject to character set translation—that is, the
characters in this field will be converted from the database's character set to the client's
character set upon retrieval, and from the client's character set to the database's character set
upon modification.

•

NCLOB : Another type of character LOB. The character set of the data stored in this column is
the national character set of the database, not the default character set of the database.

•

BLOB : A binary LOB. This type is used to stored large amounts of binary information, such as
word processing documents, images, and anything else you can imagine. It is not subject to
character set translation. Whatever bits and bytes the application writes into a BLOB are what is
returned by the BLOB .

•

BFILE : A binary file LOB. This is more of a pointer than a database-stored entity. The only
thing stored in the database with a BFILE is a pointer to a file in the operating system.
The file is maintained outside of the database and is not really part of the database at all.
A BFILE provides read-only access to the contents of the file.