Read this Oracle Tutorial Point article to know about how to manage data and what are different data types available in SQL / Oracle. Each value in Oracle Database is manipulated by a data type.
The data type value associates fixed set of properties with the value. The
value of one data type is different from another data type. The data type
defines the domain of values that every column can contain. The built-in data
types of Oracle are categorized as,
They store character data which can be alphanumeric data. The information can be,
CHAR, NCHAR, VARCHAR2, NVARCHAR2
CHAR Data type:
It represents fixed length character string. The size should be specified. If the data is less than the original given size, blank pads are applied. The default length is 1 Byte and the maximum is 2000 Bytes. The size of a character will range from one(1) Byte to four(4) Bytes depending on the database character set.
NCHAR Data type:
It is first time defined in Oracle 9i and contains only Unicode data. The columns maximum length is regulated by the National Character set definition. The largest size allowed is 2000 bytes and size has to be specified. If the data is shorter than the defined size, then the blank pads are applied.
VARCHAR2 Data type:
It define the variable length character string. The minimum size is one(1) Byte and the maximum size is 4k(4000) Bytes. It occupies only that space for which the data is provided.
NVARCHAR2 Data type:
It is first time defined in ORACLE 9i. It's defined for Unicode only date. The minimum for the scale is one(1) Byte and maximum is 4k(4000) Bytes.
Number Datatype:
It stores zero(0), positive and negative fixed and floating point numbers. The range of magnitude is,
The general declaration is,
NUMBER (p,s):
It specifies to have a decimal point anywhere from the first to the last digit or will don't have any decimal point at all. The scale value is not applicable to floating point numbers as the range of digits which will appear after the decimal point is not restricted.
Syntax:
Float --> It specifies a floating point range with binary decimal precision 38 or binary precision of 126.
Float (B) --> It specified a floating point range with binary precision b.
Long & Raw Data Types:
This data type stores variable length character string. It's used to store very lengthy text strings. The length of LONG values could also be restricted by the memory available on the computer. LONG column can be referenced in,
Date Data Type:
It's used to store date and time information. The dates can be specified as literals using the Gregorian calendar. The information revealed by date is:
It is an extension of the DATE data type. It store,
Fractional – Seconds – Precision data types are interested for storage of binary data or Byte Strings. RAW and LONG RAW are variable range data types. They are mostly used to store graphics, sounds documents etc. The ORACLE converts the RAW & LONG RAW data into Hexadecimal type. Every Hexadecimal character represents four bits of RAW data.
Large Object Datatypes (LOB):
The built in LOB data types are BLOB, CLOB, NCLOB. These data types are stored internally. The Bfile is an LOB that is stored externally. The LOB data types can store large and unstructured data like Text, Image and Video and Spatial data. The maximum size is up to 4 GB. A LOB column contains LOB locators which can refer to out-of-line or in-line LOB values. LOB’s selection basically returns the LOB locator.
BFILE Datatypes:
It enables access to binary file LOB's those are stored in the file systems outside ORACLE. A BFILE column or the attribute stores the BFILE locator. BFILE locator manages the directory alias and the filename. A Binary file LOB does not participate in transactions and aren't recoverable. The maximum size is 4 GB.
BLOB Datatype:
It stores unstructured Binary Large Objects. They're Bit streams with no Character set semantics. They're provided with full transactional support.
CLOB Datatype:
CLOB stores single byte and multiple byte character data. Both fixed width and variable width character sets are supported. They're provided with full transactional support.
NCLOB Datatype:
It stores UNICODE data using the National Character set.
ROWID Datatype:
Each and every row in the database has an address. The rows address can be queried using the pseudo column ROWID. ROWID efficiently support partitioned tables and indexes.
Example-1:
Example-2:
- CHARACTER DATA TYPES,
- ROWID DATA TYPES,
- NUMBER DATA TYPES,
- LONG AND RAW DATA TYPES,
- LARGE OBJECT DATA TYPES,
- DATETIME DATA TYPES,
They store character data which can be alphanumeric data. The information can be,
- Words,
- Free – Form Text,
- National character set,
- Database character set,
- 7 – list ASCII character set
- EBCDIC
CHAR, NCHAR, VARCHAR2, NVARCHAR2
CHAR Data type:
It represents fixed length character string. The size should be specified. If the data is less than the original given size, blank pads are applied. The default length is 1 Byte and the maximum is 2000 Bytes. The size of a character will range from one(1) Byte to four(4) Bytes depending on the database character set.
NCHAR Data type:
It is first time defined in Oracle 9i and contains only Unicode data. The columns maximum length is regulated by the National Character set definition. The largest size allowed is 2000 bytes and size has to be specified. If the data is shorter than the defined size, then the blank pads are applied.
VARCHAR2 Data type:
It define the variable length character string. The minimum size is one(1) Byte and the maximum size is 4k(4000) Bytes. It occupies only that space for which the data is provided.
NVARCHAR2 Data type:
It is first time defined in ORACLE 9i. It's defined for Unicode only date. The minimum for the scale is one(1) Byte and maximum is 4k(4000) Bytes.
Number Datatype:
It stores zero(0), positive and negative fixed and floating point numbers. The range of magnitude is,
1.0 * 10-139 to 9.9 * 10125
The general declaration is,
NUMBER (p,s):
- P --> It specifies the precision. i.e. the total number of digits (1 to 38)
- S --> It specified the scale. I.e. the number digits to the right of the decimal point. It will vary from -84 to 127.
It specifies to have a decimal point anywhere from the first to the last digit or will don't have any decimal point at all. The scale value is not applicable to floating point numbers as the range of digits which will appear after the decimal point is not restricted.
Syntax:
Float --> It specifies a floating point range with binary decimal precision 38 or binary precision of 126.
Float (B) --> It specified a floating point range with binary precision b.
- The precision can range from 1(one) to 126.
- Convert from binary to decimal precision multiply ‘b’ by 0.30103.
- Convert from decimal to binary precision multiply the decimal precision by 3.32193.
Long & Raw Data Types:
This data type stores variable length character string. It's used to store very lengthy text strings. The length of LONG values could also be restricted by the memory available on the computer. LONG column can be referenced in,
- SELECT List.
- SET clause of UPDATE statements.
- VALUES clause of INSERT statements.
- A single table can contains only one LONG column.
- Object types can't be created on LONG attributes.
- LONG columns cannot appear in WHERE clauses or in Integrity Constraints.
- Indexes can't be created on LONG Columns.
- LONG can be returned through a function, but not through a stored procedure.
- It may be declared in a PL/SQL unit but cannot be referenced in SQL.
Date Data Type:
It's used to store date and time information. The dates can be specified as literals using the Gregorian calendar. The information revealed by date is:
- Century
- Year
- Month
- Date
- Hour
- Minute
- Second
- JANUARY 1, 4712 BC to DECEMBER 31, 9999 AD.
It is an extension of the DATE data type. It store,
- Day, Month, Year
- Hour, Minute, Second
Fractional – Seconds – Precision data types are interested for storage of binary data or Byte Strings. RAW and LONG RAW are variable range data types. They are mostly used to store graphics, sounds documents etc. The ORACLE converts the RAW & LONG RAW data into Hexadecimal type. Every Hexadecimal character represents four bits of RAW data.
Large Object Datatypes (LOB):
The built in LOB data types are BLOB, CLOB, NCLOB. These data types are stored internally. The Bfile is an LOB that is stored externally. The LOB data types can store large and unstructured data like Text, Image and Video and Spatial data. The maximum size is up to 4 GB. A LOB column contains LOB locators which can refer to out-of-line or in-line LOB values. LOB’s selection basically returns the LOB locator.
BFILE Datatypes:
It enables access to binary file LOB's those are stored in the file systems outside ORACLE. A BFILE column or the attribute stores the BFILE locator. BFILE locator manages the directory alias and the filename. A Binary file LOB does not participate in transactions and aren't recoverable. The maximum size is 4 GB.
BLOB Datatype:
It stores unstructured Binary Large Objects. They're Bit streams with no Character set semantics. They're provided with full transactional support.
CLOB Datatype:
CLOB stores single byte and multiple byte character data. Both fixed width and variable width character sets are supported. They're provided with full transactional support.
NCLOB Datatype:
It stores UNICODE data using the National Character set.
ROWID Datatype:
Each and every row in the database has an address. The rows address can be queried using the pseudo column ROWID. ROWID efficiently support partitioned tables and indexes.
Example-1:
SQL> Create table STUDENTS ( SID NUMBER(8), FNAME VARCHAR2(50), LNAME VARCHAR2(50), DOB DATE, DOJ DATE, FEES NUMBER(6), GENDER VARCHAR2(1) );
Example-2:
SQL> CREATE TABLE LABSPECIFICATIONS ( PROD_ID NUMBER(6), PROD_PHOTO BLOB, PROD_GRAPHIC BFILE, PROD_DESC LONG );
Comments
Post a Comment