ORACLE

ORACLE Data Type

DATA TYPE:

একটি Table তৈরি করার সময় কলাম সমুহ কোন ধরনের DATA ধারন করবে তা নির্ধারন করে দিতে হয়। নিচে Data Type সমুহের একটি তালিকা প্রদান করা হলঃ

Data Type কে কয়েকটি ভাগে বিভক্ত করা যায়ঃ

  1. Character Data Type
  2. Numeric Data Type
  3. Date/Time Data Type
  4. Large Object Data Type
  5. ROWID Data Type

Character Data types

The following are the Character Data types in Oracle SQL & PLSQL:

Data Type Syntax Oracle 9i Oracle 10g Oracle 11g Explanation
char(size) Maximum size of 2000 bytes. Maximum size of 2000 bytes. Maximum size of 2000 bytes. Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size) Maximum size of 2000 bytes. Maximum size of 2000 bytes. Maximum size of 2000 bytes. Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size) Maximum size of 4000 bytes. Maximum size of 4000 bytes. Maximum size of 4000 bytes. Where size is the number of characters to store. Variable-length NLS string.
varchar2(size) Maximum size of 4000 bytes.

Maximum size of 32KB in PLSQL.

 Maximum size of 4000 bytes.

Maximum size of 32KB in PLSQL.

 Maximum size of 4000 bytes.

Maximum size of 32KB in PLSQL.

 Where size is the number of characters to store. Variable-length string.
long Maximum size of 2GB. Maximum size of 2GB. Maximum size of 2GB. Variable-length strings. (backward compatible)
raw Maximum size of 2000 bytes. Maximum size of 2000 bytes. Maximum size of 2000 bytes. Variable-length binary strings
long raw Maximum size of 2GB. Maximum size of 2GB. Maximum size of 2GB. Variable-length binary strings. (backward compatible)

Numeric Datatypes

The following are the Numeric Datatypes in Oracle SQL & PLSQL:

Data Type Syntax Oracle 9i Oracle 10g Oracle 11g Explanation
number(p,s) Precision can range from 1 to 38.
Scale can range from -84 to 127.		 Precision can range from 1 to 38.
Scale can range from -84 to 127.		 Precision can range from 1 to 38.
Scale can range from -84 to 127.		 Where p is the precision and s is the scale.

For example, number(8,2) is a number that has 6 digits before the decimal and 2 digits after the decimal.
numeric(p,s) Precision can range from 1 to 38. Precision can range from 1 to 38. Precision can range from 1 to 38. Where p is the precision and s is the scale.

For example, numeric(8,2) is a number that has 6 digits before the decimal and 2 digits after the decimal.
float
dec(p,s) Precision can range from 1 to 38. Precision can range from 1 to 38. Precision can range from 1 to 38. Where p is the precision and s is the scale.

For example, dec(4,1) is a number that has 3 digits before the decimal and 1 digit after the decimal.
decimal(p,s) Precision can range from 1 to 38. Precision can range from 1 to 38. Precision can range from 1 to 38. Where p is the precision and s is the scale.

For example, decimal(4,1) is a number that has 3 digits before the decimal and 1 digit after the decimal.
integer
int
smallint
real
double precision

Date/Time Datatypes

The following are the Date/Time Datatypes in Oracle SQL & PLSQL:

Data Type Syntax Oracle 9i Oracle 10g Oracle 11g Explanation
date A date between Jan 1, 4712 BC and Dec 31, 9999 AD. A date between Jan 1, 4712 BC and Dec 31, 9999 AD. A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision) fractional seconds precision must be a value between 0 and 9. (default value is 6) fractional seconds precision must be a value between 0 and 9. (default value is 6) fractional seconds precision must be a value between 0 and 9. (default value is 6) Includes year, month, day, hour, minute, and seconds.

For example:
timestamp(6)
timestamp (fractional seconds precision) with time zone fractional seconds precision must be a value between 0 and 9. (default value is 6) fractional seconds precision must be a value between 0 and 9. (default value is 6) fractional seconds precision must be a value between 0 and 9. (default value is 6) Includes year, month, day, hour, minute, and seconds; with a time zone displacement value.

For example:
timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone fractional seconds precision must be a value between 0 and 9. (default value is 6) fractional seconds precision must be a value between 0 and 9. (default value is 6) fractional seconds precision must be a value between 0 and 9. (default value is 6) Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone.

For example:
timestamp(4) with local time zone
interval year
(year precision)
to month		 year precision is the number of digits in the year. (default value is 2) year precision is the number of digits in the year. (default value is 2) year precision is the number of digits in the year. (default value is 2) Time period stored in years and months.

For example:
interval year(4) to month
interval day
(day precision)
to second (fractional seconds precision)		 day precisionmust be a value between 0 and 9. (default is 2)

fractional seconds precision must be a value between 0 and 9. (default value is 6)

 day precisionmust be a value between 0 and 9. (default is 2)

fractional seconds precision must be a value between 0 and 9. (default value is 6)

 day precisionmust be a value between 0 and 9. (default is 2)

fractional seconds precision must be a value between 0 and 9. (default value is 6)

 Time period stored in days, hours, minutes, and seconds.

For example:
interval day(2) to second(6)

Large Object (LOB) Datatypes

The following are the LOB Datatypes in Oracle SQL & PLSQL:

Data Type Syntax Oracle 9i Oracle 10g Oracle 11g Explanation
bfile Maximum file size of 4GB. Maximum file size of 232-1 bytes. Maximum file size of 264-1 bytes. File locators that point to a binary file on the server file system (outside the database).
blob Store up to 4GB of binary data. Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). Stores unstructured binary large objects.
clob Store up to 4GB of character data. Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data. Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data. Stores single-byte and multi-byte character data.
nclob Store up to 4GB of character text data. Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data. Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data. Stores unicode data.

Rowid Datatypes

The following are the Rowid Datatypes in Oracle SQL & PLSQL:

Data Type Syntax Oracle 9i Oracle 10g Oracle 11g Explanation
rowid The format of the rowid is: BBBBBBB.RRRR.FFFFF

There BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

 The format of the rowid is: BBBBBBB.RRRR.FFFFF

There BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

 The format of the rowid is: BBBBBBB.RRRR.FFFFF

There BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

 Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size) Universal rowid.

Where sizeis optional.

 

Comments

CONTACT US

Send Message on Whats App
Cell: +880-1717539855

We are social:

Visitor Counter

110936
Visit Today : 71
Visit Yesterday : 88
This Month : 1150
This Year : 25365