2. Oracle's Built-in Data Types

2. Oracle's Built-in Data Types

I.Q. 7. what is mean Data type in SQL ? And explain its different types.

A data type is a unit of data storage in a software system.

Some of Oracle's built-in data types are:

Data Types

1.     Number

2.    Char

3.    Varchar ( or ) Varchar2

4.    Date

5.    Time Stamp

6.    Long

7.    Raw

8.    Long Raw

9.    Lob ( Clob, Blob, Bfile & NCLob )

1)   Number Data Type :

It allows only numeric values

Maximum size is 38 digits

Syntax :

X     Number ( P, (S));

P  => It allow show many digits to store

S  => Size

Ex : X   Number (5,2)

      2)   Character Data type:

1.     Char

2.    Varchar and Varchar2

3.    Long

a)   Char:

It allows alphanumeric characters ( Numbers + Characters )

Maximum size is 2000 Bytes/ Characters

Syntax :  X     Char( S );

b)   Varchar ( or ) Varchar2:

It allows alphanumeric characters

Max size 4000 Bytes/ Characters

Memory allocation is dynamic

Syntax :X     Varchar2( S );

      3)   Date:

It is used to store date values

Max size is 7 Bytes

Syntax :X      Date;

Timestamp:

It is used to store date along with fraction of seconds.

Syntax :X       Timestamp;

      4)   Long:

It is used to store information

Max size is 2 GB.

Only once we have to use in entire table.

Syntax : X       Long;

      5)   Raw:

It is used to store images

Max size is 2000 Bytes.

Syntax :X       Raw;

      6)   Longraw:

It is used to store information as well as images.

Max size is 2GB

Syntax :X      Longraw;

      7)   Lob:

a)Clob :

It is used to store huge information

Max size is 4 GB

Syntax :X       Clob;

b)Blob :

It is used to store images but in the form of binary format.

Max size is 4 GB

Syntax :X       Blob;

c)Bfile :

It is used to store the files.

Max size is 4 GB.

Syntax :X       Bfile;

d)NCLob:

It is used to store multiple languages ( Unicode Format )

I.Q. 8 What is difference between char, Varchar and Varchar2 ?

Char	Varchar	Varchar2
Fixed length data type Maximum size 2000 byte String values will be space/blank padded before stored on disk. CHAR is faster than VARCHAR If this type is used to store variable length strings, it will waste a lot of disk space.	Variable length data type Maximum size 2000 byte VARCHAR is going to be replaced by VARCHAR2 in next version.	Variable length data type Maximum size 4000 byte

I.Q. 9 What is Boolean Data type?

A BOOLEAN data type enables you to represent logical values. In code, BOOLEAN values are represented by values for "no" and "yes" (in any combination of uppercase and lowercase characters). You can represent BOOLEAN values, using:

·         YES, TRUE, ON

·         NO, FALSE, OFF