Oracle supports Unicode data through NCHAR, NVARCHAR2, and NCLOB datatypes. (in Version 8 and above)
Unicode character data can be stored in a database even if the database character set is not Unicode. The exact character set used by NCHAR/NVARCHAR2 can vary depending on the setting of the national character set when the database is created.
The datatypes CLOB and NCLOB can store up to 4 gigabytes of character data or national char. set data. LOB datatypes replace the depracated LONG datatype.
The BFILE datatype stores unstructured binary data in operating-system files
outside the database.
A BFILE column or attribute stores a file locator that points to an external
file containing the data.
For input and output of dates, the standard Oracle default date format is DD-MON-YY, ('13-NOV-92')
You can change this default date format for an instance with the parameter NLS_DATE_FORMAT.
You can also change it during a user session with the ALTER SESSION statement.
To account for time zone differences, use the datatype 'TIMESTAMP WITH LOCAL TIME ZONE' or 'TIMESTAMP WITH TIME ZONE'
'TIMESTAMP WITH TIME ZONE'
Includes a time zone region name or time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC.
'TIMESTAMP WITH LOCAL TIME ZONE'
Data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle Database returns it in the users' local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC. In other words, this assumes all application users are in the same Time Zone.
Example:SQL>
create table timetest (
col1 timestamp,
col2 timestamp with time zone,
col3 timestamp with local time zone);insert into timetest values (sysdate,sysdate,sysdate);
commit;select * from timetest;
alter session set time_zone='America/New_York';
select * from timetest;In most cases, you should use 'TIMESTAMP WITH TIME ZONE' this data type allows an application to store dates/times across time zones e.g. for international event booking.
See the SQL Reference manual for full details about this syntax.
A null is the absence of a value. Nulls indicate missing or unknown data. A null should not be used to imply any other value, such as zero.
A column allows nulls unless a NOT NULL or PRIMARY KEY integrity constraint has been defined for the column.
Nulls columns generally require 1 byte to store the length of the column (zero).
Trailing nulls (at the end of a row) require no storage because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, no information is stored for those columns.To identify nulls in SQL, use the IS NULL predicate.
Use the SQL function NVL to convert nulls to non-null values.
A bitmap index will include any NULLs, a binary tree index won’t.
Try to place columns that frequently contain nulls last so that rows take less space.
Note, the benefits of this are lost if the table includes a LONG column (so use BLOB instead)
You can assign a default value to a column so that when a new row is inserted and a value for the column is omitted a value is supplied automatically. If a default value is not explicitly defined for a column, then the default for the column is implicitly set to NULL.
create table ACCOUNTS(
AC_ID_PK number primary key,
AC_COUNTRY_ID number default 44,
AC_CREATED date default sysdate,
)
"The estimated cost of the National ID scheme is £3.1bn, Chris Pond
MP, has put benefit fraud related to false ID at £50m” ~ The
Register
Related
Full list of Datatypes
Database field sizes - Some popular best practices on length and data type for common database fields.