Most of time we have
ASCII as character set . Where 7 bits are used to represent a character.
Unicode(Character
set ) UTF 8(Encoding) -- Is the one that can be used for storing chinese,
English.. And is a multi byte dataset. Specifying the length in character is
better in case of multibyte character sets.
Unicode(Character
set) -- Allows you to store data in any language in oracle. There are two data sets 1) the default one of
the data base and 2) The column can have unicode character set. This is made
possible by Nchar and Nvarchar.So
suppose you are trying to save data in oracle using Hindi, French ,
chinese use Nchar,Nvarchar,Nclob data types
A Short note on Character set and Encoding
A character set is a list of characters with
unique numbers (these numbers are sometimes referred to as "code
points"). For example, in the Unicode character set, the number for A is
41. The other character set is ASCII
An Encoding on the other hand, is an algorithm
that translates a list of numbers to binary so it can be stored on disk. For
example UTF-8 would translate the number sequence 1, 2, 3, 4 like this:
Char --- Data type can have the length
specified in either in Bytes or in Characters .
Char ( 2 char) or
Char ( 2 bytes) . But for Ascii it does not make much difference as 7 bits are
used to represent a character . The default whether a byte or char is taken
depends on the database NLS_Length_Semantics
Char is a fixed
length data types and rest is padded by blank
Varchar does not pad blanks and only uses space
depending on the number of characters.
For all purposes use Varchar2 .. Varchar is like the old one
Long --- Its there for backward compatiblity
and for all development use LOB ( that is CLOB in case of characters)
BLOB and CLOB and NCLOB -- Clob is used for
Character Large object , as the name suggest its allows you to store ASCI character data (or character set of your
DB) data . The NCLOB allows you to save unicode data irrespective of the character
set of the database. The BLOB is used to store binary data. When we say that
the data is Binary it means the data will not be interpreted by oracle to
resemble any character from a character set.
Numeric Data types
when you specify a
data type as number without scale and precision then the precision is taken as
zero that is , It cant store decimal points .
Important point is
you can store numbers of any length the only restriction is on the precision
and maximum precision is 38
RAW and Long RAW -- They are used for Binary
data that is not to be interpreted . Nowadays Long RAW is only there for
backward compatibility and its better to use BLOB
Date and timestamp
Point to remember
are Both date and Timestamp both contain time part. The precision of time noted by Date is upto
seconds and that noted down by timestamp is upto milli seconds. If you are doing any comparision using dates in the
where clause be sure to do them in the
exact format specified by your NLS setting otherwise you need to use to_date
Things not there in Oracle …..FLOAT,
DECIMAL,BYTE, SMALL INT, INT All these things are not there in
Oracle. The NUMBER is the all that is there . It covers everything in oracle.
So even if you write int while creating a table it is taken as number in the
create statement. Oracle allows you to specify int while creating a table
Very good article on the topic
