INTERVAL DAY TO SECOND-Datatypes
The syntax for the INTERVAL DAY TO SECOND type is straightforward:
INTERVAL DAY(n) TO SECOND(m)
where N is an optional number of digits to support for the day component and varies from zero to nine, with a default of two. M is the number of digits to preserve in the fractional part of the seconds field and varies from zero to nine, with a default of six. Once again, the function I prefer to use to create instances of this INTERVAL type is NUMTODSINTERVAL:
SQL> select numtodsinterval( 10, ‘day’ )+ numtodsinterval( 2, ‘hour’ )+ numtodsinterval( 3, ‘minute’ )+ numtodsinterval( 2.3312, ‘second’ ) fromdual;NUMTODSINTERVAL(10,’DAY’)+NUMTODSINTERVAL(2,’HOUR’)+NUMTODSINTERVAL(3,’MINU
or simply
SQL> select numtodsinterval( 1086400+23600+360+2.3312, ‘second’ ) from dual;NUMTODSINTERVAL(1086400+23600+360+2.3312,’SECOND’)
using the fact that there are 86,400 seconds in a day, 3600 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:
SQL> select to_dsinterval( ’10 02:03:02.3312′ ) from dual; TO_DSINTERVAL(‘1002:03:02.3312’)
or just use an INTERVAL literal in SQL itself:
SQL> select interval ’10 02:03:02.3312′ day to second from dual;INTERVAL’1002:03:02.3312’DAYTOSECOND
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 store 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.
When discussing LOBs, I’ll break the preceding list into two pieces: LOBs stored in the database, or internal LOBs, which include CLOB, BLOB, and NCLOB, and LOBs stored outside of the database, or the BFILE type. I will not discuss CLOB, BLOB, or NCLOB independently, since from a storage and option perspective they are the same. It is just that a CLOB and NCLOB support textual information and a BLOB does not. But the options we specify for them—the CHUNK size, RETENTION, and so on—and the considerations are the same, regardless of the base type. Since BFILEs are significantly different, I’ll discuss them separately.