Ads 468x60px

Thursday, July 28, 2011

Oracle/PLSQL: Data Types


Oracle/PLSQL: Data Types


The following is a list of datatypes available in Oracle.

Character Datatypes

Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
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 4000 bytes.
Maximum size of 4000 bytes.
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 Data types

Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
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(7,2) is a number that has 5 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(7,2) is a number that has 5 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(3,1) is a number that has 2 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(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer




int




smallint




real




double precision





Date/Time Data types

Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
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 number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default 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 number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default 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 number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default 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 is 2)
year precision is the number of digits in the year. (default is 2)
year precision is the number of digits in the year. (default 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 precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6)
day precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6)
day precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6)
Time period stored in days, hours, minutes, and seconds.
For example:
interval day(2) to second(6)

Large Object (LOB) Datatypes

Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
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

Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
Rowed
The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where 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
Where 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
Where 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 size is optional.







Oracle/PLSQL: Literals


A literal is the same as a constant. We'll cover three types of literals - text literals, integer literals, and number literals
Text literals are always surrounded by single quotes ('). For example:
'Hewlett Packard'
'28-MAY-03'

Integer literals can be up to 38 digits. Integer literals can be either positive numbers or negative numbers. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid integer literals:
23
+23
-23

Number literals can be up to 38 digits. Number literals can be either positive or negative numbers. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid number literals:
25
+25
-25
25e-04
25.607


Oracle/PLSQL: Declaring Variables


The syntax for declaring variables is:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

For example:
Declaring a variable:
LDescription varchar2(40);
           
Declaring a constant:
LTotal CONSTANT numeric(8,1) := 8363934.1;

Declaring a variable with an initial value (not a constant):
LType varchar2(10) := 'Example';


Oracle/PLSQL: IS NULL


In PLSQL to check if a value is null, you must use the "IS NULL" syntax.
For example,
IF Lvalue IS NULL then
    ...
END IF;
If Lvalue contains a null value, the "IF" expression will evaluate to TRUE.

You can also use "IS NULL" in an SQL statement. For example:
select * from suppliers
where supplier_name IS NULL;
This will return all records from the suppliers table where the supplier_name contains a null value.

To learn how to check for a value that is not null, click here.


Oracle/PLSQL: IS NOT NULL


In PLSQL to check if a value is not null, you must use the "IS NOT NULL" syntax.
For example,
IF Lvalue IS NOT NULL then
    ...
END IF;
If Lvalue does not contain a null value, the "IF" expression will evaluate to TRUE.

You can also use "IS NOT NULL" in an SQL statement. For example:
select * from suppliers
where supplier_name IS NOT NULL;
This will return all records from the suppliers table where the supplier_name does not contain a null value.

0 comments:

Post a Comment