Data Types in Oracle

What are Data Types in Oracle:

In Oracle data types are used to define and store types of data in a table for particular columns. Each data type has its own characteristics and is used to store the specific type of data to the columns in a table.  

Following is a list of data types in Oracle used in SQL as well as PL/SQL:

1. Numeric Data Type:

Numeric data type is used to store the numeric data in a table for a column.

Number:

This data type is used to store fixed point or floating point numeric data to the table.

Example:

Rollno number(4),

ID number(4,2),

It will store the rollno of a student upto 4 digits fixed size. 

If size of the Number data type is not mentioned then it will be considered as Number(38), and it is called floating-point or variable precision. 

In another example ID is defined with a number data type with fixed size upto 4 digits and 2 digits at the right of the decimal point. 

Integer:

The Integer data type represents the whole number with no fractional point.

For example,

Rollno Integer,

It will store the whole numbers to the Rollno column. But when you display the structure of the table whose data type is Integer it will show its type as Number(38). It takes by default the Number data type.

Float:

For storing floating point data you can use numbers as well as Float keywords. It represents floating numbers with binary precision. 

For example,

P_price float,

It helps to store the price of a product in floating point. By default it shows float(126). 

2. Character Data Types:

It is used to store character data or string data in a table for a particular column. 

CHAR:

CHAR represents a fixed length character string to a column. 

For example,

Name char(20),

Here name is stored with fixed length 20 characters length. If for example the name is ‘Rohan’ it is only 4 character length then the database engine will fill the remaining 16 characters with padded white space. This is not good practice in case you have not fixed length or consistent data for your column.

Gender char,

In this example Gender is defined with char data type where size of char data type is not mentioned. In this case it will take only a single character for a column gender to store data.

VARCHAR2:

VARCHAR2 helps to store variable length string to a column with maximum length upto 4000 bytes.

For example,

Name varchar2(20),

In the above example Name column is defined with its size 20 characters long.

VARCHAR data type is used in older versions of Oracle. After the Oracle 9i version the VARCHAR data type is replaced with the VARCHAR2. 

3. Date and Time Data Type:

Date and Time data type is used to store the date and time stamp for a column in a table. 

Date:

The Date data type is used to store the date and time information for the column in a table. 

For example,

DOB date,

SQL> create table dates

  2  (d_info date);

Table created.

SQL> desc dates

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 D_INFO                                             DATE

SQL> insert into dates

  2  values(sysdate);

1 row created.

SQL> select * from dates;

D_INFO

---------

30-JUL-23

SQL> insert into dates

  2  values('31-July-23');

1 row created.

SQL> select * from dates;

D_INFO

---------

30-JUL-23

31-JUL-23

TIMESTAMP:

The Timestamp data type is an extension of the data type. The format for specifying the Timestamp data type in Oracle is ‘YYYY-MM-DD HH24:MI:SS.FF’ 

SQL> alter table dates

  2  add O_Time Timestamp;

Table altered.

SQL> desc dates;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 D_INFO                                             DATE

 O_TIME                                             TIMESTAMP(6)

SQL> INSERT INTO dates (o_time)

  2  VALUES (TO_TIMESTAMP('2023-07-31 08:50:00', 'YYYY-MM-DD HH24:MI:SS'));

4. Boolean Data Type:

The Boolean data type is used to store True or False values in a table for a particular column. For example, a candidate ismarried TRUE or False.

In Oracle there is no directly Boolean data type like in other databases, however to represent Boolean type it is represented with 1 and 0 where 1 is representing True and the number 0 represents False and Number data type is used for this purpose. 

To create the table ;ike Boolean attributes we can create the table with number data type by applying check constraint 1 or 0 to represent True or False. 

For Example,

SQL> create table candidate

  2  (married number check(married in(1,0))

  3  );

Table created.

Here the table is created with check constraints on the married column. In operator is used to represent two values in 1 and 0. 

SQL> desc candidate;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 MARRIED                                            NUMBER

SQL> insert into candidate

  2  values(0);

1 row created.

SQL> select * from candidate;

   MARRIED

----------

         0