Data type in Oracle.

For more details on data type in oracle follow the link: < https://youtu.be/L-HuN9Qi8sM >

CREATE TABLE syntax :

CREATE TABLE table_name
(
column1 datatype (width) [ NULL | NOT NULL ],
column2 datatype (width) [ NULL | NOT NULL ],
column_n datatype (width) [ NULL | NOT NULL ],
[ CONSTRAINT [constraint_name] constraint_type (column_name), ]
[ CONSTRAINT [constraint_name] constraint_type (column_name), ]
);

There are 2 properties of DATATYPE of a column, i.e. TYPE and SIZE.

  •  TYPE: It is the property of a column / variable that describes which kind of information that variable is going to have.
  •  SIZE : It is the property of column / variable that describes the amount of space it occupy to keep the information.

Types of information:

ALPHABET         : A-Z , a-z 
NUMBER              : 0-9 (with precision and scale)

DATE / Temporal  : any Date and time (Hours, Minutes, Seconds, Mili-seconds, Timestamp, Timezone etc)


Alphabet + Number => Alphanumeric Data  
                                 => String / CHARACTER Datatype Category
Number                    =>  Numeric Datatype Category
Date                         => Date Datatype Category

1. CHARACTER Datatype:

CHAR, VARCHAR, NCHAR: 

  • CHAR is fixed length datatype and VARCHAR is Variable length datatype to store character data. i.e. A-Z , a-z , 0-9, keyboard characters etc.
  • The default size is 1 character and it can store maximum up to 2000 bytes.
  • Example : EName, EmpID, PassportNo, SSN, etc.
    • EName CHAR(10) := ‘TOM’;              Wastage of 7 space after the string.
    • EName VARCHAR(10) := ‘TOM’;        Spaces can be re-use which left  after the string
  • NCHAR additionally handles NLS(National Language Support).
  • Oracle supports a reliable Unicode datatype through NCHAR , NVARCHAR2 , and NCLOB 


VARCHAR2, NVARCHAR2: 

  • These are Variable length datatype.
  • VARCHAR2 handles alphanumeric character string whereas NVARCHAR2 handles alphanumeric character string with NLS(National Language Support).
  • The default size is 1 character and it can store maximum up to 4000 bytes.

Few more character datatypes:
LONG:  Variable length string.  (Maximum size: 2 GB – 1)               Only one LONG column is allowed per table.RAW:    Variable length binary string (Maximum size 2000 bytes)LONG RAW: Variable length binary string (Maximum size 2GB)

2. NUMERIC Datatype:

NUMBER:

  • It stores Numeric values and performs numeric calculations.
  • NUMBER,   NUMBER(n),   NUMBER(p,s)
  • It stores Numbers up to 38 digits of precision.
    • SeqNo NUMBER;                     1, 123, 12345678
    • EmpID NUMBER(4);                 1, 123, 1234
    • Sal NUMBER(7,2);                     23456.78 , 123.45 
  • 1234567 can be a type of NUMBER, NUMBER(7), NUMBER(7,0)
  • It can store both integer and floating point numbers


NUMERIC(p,s)

FLOAT:     

  • Decimal Points allowed
    • Ex:  EmpSal FLOAT;   —  FLOAT(7)       

DEC(p,s), DECIMAL(p,s) , REAL, DOUBLE PRECISION

INTEGER:   

  • Decimal Points are not allowed
    • Ex:  SSN INTEGER;       

INT, SMALLINT


3. DATE Datatype:

DATE

  • It stores DATE(Date, Month, Year) and Time(Hour, Minute, Second, AM/PM) and performs calculations with such data.
  • Default DATE format in Oracle is “DD-MON-YY”
  • Based on “Gregorian calendar” where the date ranges from “JAN 1 4712 BC” to “DEC 31 9999 AD”
    • doj DATE;    “18-MAR-2010 12:30:00 PM”

TIMESTAMP

  • It can store all parameters as DATE datatype and additionally it can have “Fraction of seconds” and TIMESTAMP WITH TIMEZONE / TIMESTAMP WITHOUT TIMEZONE.
  • Range from 0-9 digits, the default size is 6.

4. LOB Datatype:

LOB: “Large Object” data.It can store pictures, motion pictures, Textfiles etc.CLOB: “Character Large Object” is used to store structured information like a text file with a specific file format.BLOB: “Binary Large Object” is used to store Un-structured information like Image, JPEG files, MPEG files etc.
NCLOB : It supports all the character set supported by CLOB and additionally it handles NLS(National Language Support).
BFILE: “Binary File” is used to store the pointer to a specific file / Just store the location of a file.

Maximum size of LOB: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)

5. ROWID and UROWID Datatype:

ROWID and UROWID(optional size)  Datatype: 

  • It stores fixed length Binary data.
  • BBBBBBB.RRRR.FFFFF  combination of Block-Row-Database File
  • It can be of 2 type i.e. Physical and Logical ROWID

1 thought on “Data type in Oracle.”

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn