Saturday, June 09, 2007

ORACLE Datatypes Basics

1) CHAR
-> Char contain alphanumeric data.
-> Length of Char datatype is between 1 and 255 characters.
-> If you don't specify a length, a char column stores a single character by default.
-> Char datatypes stores data in the Fixed-Length Format.
-> Be aware, when using this data type, that spaces are padded to the right of the value to supplement the total allocated length of the column.
-> It can be declared as char(size) where size is the total length allowed for a value in a column defined as char.

2) DATE
-> Date datatype is used to store all date and Time information.
-> Oracle always allocates a Fixed - 7 bytes for a DATE column.
-> Oracle uses the default format of DD-MON-YY for entering and displaying.
-> Following Include as a Part of DATE are century, year, month, day, hour, minute and second.
-> It enables you to store dates in range of January 1, 4712 B.C. To December 31, 4712 A.D.
-> To view System Date and time we can use the SQL function called SYSDATE.

3) LONG
-> It can store alphanumeric strings.
-> It stores the data in Variable-Length Format.
-> Length of Long rise upto 2 giga bytes.
-> Long is used to store more than 2000 characters in a column.
-> You cannot use any of Oracle's built-in functions or operator with LONG column.
-> You can think of a LONG column as a large container into which you can store or retrieve data but not manipulate it.
Limitations of LONG data type are as under
i> Only one column in a table can have LONG datatype, which should not contain unique or primary key constraint.
ii> Column cannot be indexed.
iii> Procedures or stored procedures cannot accept LONG datatype as argument.

4) NUMBER
-> It can store Numeric data such as Zero, Positive or Negative, Fixed or Floating point data.
-> It accepts Positive and negative integers and real number and has from 1 to 38 digits of precision.
-> Scale which refers to number of digits to the right of the decimal point, which varies between (-84) to 127.
-> Format of declaring NUMBER datatype is NUMBER( Precision, Scale)
example:-
1) NUMBER(5) - It can store numeric data upto 5 digits i.e. 99999
2) NUMBER(8,2) - It can store numeric data of 6 Integers and 2 for decimal.
i.e. 999999.99

5) VARCHAR2
-> It stores Alphanumeric data values.
-> The field must be between 1 and 2000 characters long.
-> VARCHAR2 supports Oracle built-in functions and operators.
-> It supports a variable length character string.
-> Format of declaring VARCHAR2 IS VARCHAR2( size ).

6) VARCHAR
Same as Varchar2. Always use Varchar2 instead of varchar as it may not be supported in future.

7) BLOB (Binary Large OBjects)
-> Blob include documents, graphics, sound, video, actually any type of binary file you can think of.
-> A binary large object with a limit of 4GB in length.
-> When you want to associate a BLOB with a 'NORMAL' row, two choices are available to you.
i> Store the BLOB in an operating system file(such as an MS-DOS file) and stores the directory and filename in the associated table.
ii> Store the BLOB itself in the LONG RAW column.

8) CLOB (Character Large OBject)
-> A character Large object with a limit of 4GB in length.

9) BOOLEAN
-> Boolean variables are used in PL/SQL control structure such as IF-then-ELSE and LOOP statements. A Boolean value can hold true, false or NULL only.

10) LONG RAW
-> It can store binary data upto 2GB.
-> LONG RAW datatype is designed for BLOB storage. You can't use any of the built-in functions with a LONG RAW column.

11) RAW
-> It can store binary data upto 255 bytes.
-> Because of this storing restriction, a RAW column is less useful than a LONG RAW column.

12) ROWID
-> Hexadecimal string representing the address of a row in a table.

13) INTEGER
-> Specifies size of an INTEGER(n) digits wide.

14) BINARY INTEGER
-> The number type is stored in a decimal format which is optimized for accuracy and store efficiency. This datatype is used to store signed integer values, which range from TM2147483647 to +2147483647. It is stored in a 2's complement binary format. Loop Counter are often of type BINARY INTEGER.

Related Links

No comments:

Most Recent Post

Community Updates

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape