Datatypes in Oracle
 

A datatype is a classification of a particular type of information or data. Each value manipulated by Oracle has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another.

Numeric

The NUMBER data type is used to store zero, negative, positive, fixed, and floating point numbers with up to 38 digits of precision. Numbers range between 1.0x10 -130 and 1.0x10 126.

Numbers can be defined in following ways:

• NUMBER(p,s)

  where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point). The scale can range between -84 to 127.

• NUMBER (p)

  This is a fixed-point number with a scale of zero and a precision of p.

• FLOAT[(p)]

  Oracle supports the ANSI data type FLOAT. Therefore, specifying a floating-point number you can use FLOAT data type instead of NUMBER data type.
  p is the binary precision that can range from 1 to 126. If p is not specified the default value is binary 126. To convert from binary to decimal precision, multiply p by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

Date

Instead of storing date and time information in a character or numeric format, a separate data type was created. For each DATE data type, the following information is stored:

• Century
• Year
• Month
• Day
• Hour
• Minute
• Second

You can easily retrieve the current date and time by using the function SYSDATE. Date arithmetic is possible using number constants or other dates. Only addition and subtraction are supported. For example, SYSDATE + 7 will return one week from today. Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD-MON-YY, where DD is the day of the month (the first day of the month is 01), MON is the abbreviated month name, and YY is a two-digit year designation. If you do not specify a time, the default time is 12:00:00 a.m. If only the time component is captured, the default date will be the first day of the current month.

• TIMESTAMP[(fractional_seconds_precision)]

The TIMESTAMP data type is an extension of the DATE data type. For each TIMESTAMP value, Oracle stores the following information: year, month, day, hour, minute, second and fraction of second. fractional_seconds_precision optionally specifies the number of digits in the fractional part of second and can be a number in the range 0 to 9. The default is 6. The TIMESTAMP data type is available in Oracle 9i Release 1 (9.0.1) or later.

Character

1. The CHAR data type is used where fixed-length fields are necessary. Any length up to 255 characters can be specified. The default length is 1. When data is entered, any space left over will be filled with blanks. All alpha-numeric characters are allowed.
2. The VARCHAR2 is used for variable-length fields. A length component must be supplied when you use this data type. The maximum length is 2000 characters. All alpha-numeric characters are allowed.
3. The LONG data type is used to store large amounts of variable-length text. Any length up to 2 GB can be specified. Be aware that there are some restrictions to using this data type, such as:
   • Only one column per table can be defined as LONG.
   • A LONG column cannot be indexed.
   • A LONG column cannot be passed as an argument to a procedure.
   • A function cannot be used to return a LONG column.
   • A LONG column cannot be used in where, order by, group by, or connect by clauses.
4. NCHAR[(n)]
   Fixed-length character data of length n characters or bytes, depending on the national character set. The maximum length is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. The default value is 1.
5. NVARCHAR2(n)
   Variable-length character data having maximum length n characters or bytes, depending on the national character set. The maximum length is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify the maximum length for NVARCHAR2 columns.

Binary

Two data types, RAW and LONGRAW, are available for storing binary type data such as digitized sound and images. These data types take on similar characteristics as the VARCHAR2 and LONG data types already mentioned. Use the RAW data type to store binary data up to 2000 characters and use the LONGRAW data type to store binary data up to 2 GB. Oracle only stores and retrieves binary data; no string manipulations are allowed. Data is retrieved as hexadecimal character values.

Others

BLOB, CLOB, NCLOB and BFILE

The built-in LOB data types BLOB, CLOB and NCLOB (stored internally), and BFILE (stored externally), can store large and unstructured data such as text, images and spatial data up to 4 gigabytes in size.

BLOB

The BLOB data type stores binary large objects. BLOB can store up to 4 gigabytes of binary data.

CLOB

The CBLOB data type stores character large objects. CLOB can store up to 4 gigabytes of character data.

NCLOB

The NCBLOB data type stores character large objects in multibyte national character set. NCLOB can store up to 4 gigabytes of character data.

BFILE

The BFILE data type enables access to binary file LOBs that are stored in file systems outside the Oracle database. A BFILE column stores a locator, which serves as a pointer to a binary file on the server's file system. The maximum file size supported is 4 gigabytes.

ROWID and UROWID

ROWID

The ROWID data type is used to store physical address of each row in the database. This data type is primarily for values returned by the ROWID pseudocolumn.

UROWID[(n)]

The UROWID data type is used to store the logical addresses of index-organized and foreign tables.
n is the size of a UROWID column. The range of n is 1 to 4000. The default value is 4000.
This data type is available in Oracle 9i Release 1 (9.0.1) or later.

Re: Datatypes in Oracle
 

Which things do you think difference between Oracle and SqlServer

Re: Datatypes in Oracle
 

Is it oracle or SQL server is most commonly used.

Re: Datatypes in Oracle
 

The Basic datatypes of oracle are:
varchar2/nvarchar2
number
long
rowid
date
raw
longraw
char/nchar

Re: Datatypes in Oracle
 

what is the difference in oracle and sql server

Re: Datatypes in Oracle
 

Another datatype is TIMESTAMP which is basically an upgrade of DATE to include fractional seconds and time zone info. There are a couple of interesting variations on TIMESTAMP which are TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.

TIMESTAMP can contain a value like '01-JAN-2003 04:56:07.890123'
TIMESTAMP WITH TIME ZONE adds a time zone to that, e.g. '01-JAN-2003 04:56:07.890123 GMT'

TIMESTAMP WITH LOCAL TIME ZONE is an interesting one. Timestamp values are converted to reflect the time in different parts of the world. So if I store '01-JAN-2003 04:56:07.890123 GMT' in a French database (GMT+1), it will be stored as '01-JAN-2003 05:56:07.890123' because France is 1 hour ahead of the UK. If this value is subsequently accessed by a computer in Israel which is GMT+2 it will be returned as '01-JAN-2003 06:56:07.890123'. This reflects the fact that '01-JAN-2003 04:56:07.890123 GMT' is equal to '01-JAN-2003 06:56:07.890123' in Israel.

For more brain numbing stuff have a look at the INTERVAL types in the SQL reference.

Re: Datatypes in Oracle
 

A little more about TIMESTAMP datatype

TIMESTAMP can be used in any of the following ways:

1. datetimestamp (fractional seconds precision)

fractional seconds precision must be a number between 0 and 9 (default is 6), eaxample, timestamp(6)

2. timestamp (fractional seconds precision) with time zone
Includes year, month, day, hour, minute, and seconds with a time zone displacement value. Example timestamp(9) with time zone

3. timestamp (fractional seconds precision) with local time zone
Here time zone is expressed as the session time zone For example: timestamp(6) with local time zone

4. interval year (year precision) to month
Here, Time period stored in years and months. For example: interval year(6) to month

5. interval day (day precision) to second (fractional seconds precision)
Time period stored in days, hours, minutes, and seconds with a fractional seconds precision. For example: interval day(2) to second(6)

Re: Datatypes in Oracle
 

You have described the data types very well. Thanks for your tutorial.

Re: Datatypes in Oracle
 

The differences is given below:-
1.Transaction control:- In Oracle EVERYTHING is a transaction and it is not permanent until you COMMIT. In SQL Server, there is (by default) no transaction control.
2.MVCC. In SQL Server and Oracle is different. SQL Server will allow dirty reads, and writes can block reads in MS SQL,unlike Oracle where read consistency is default and unbendable.
Thanks