# Oracle/PLSQL: Data Types

## Character Datatypes

Data TypeSyntax | 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 is the number of characters to store. Fixed-length strings. Space padded.size |

nchar(size) | Maximum size of 2000 bytes. | Maximum size of 2000 bytes. | Maximum size of 2000 bytes. | Where is the number of characters to store. Fixed-length NLS string Space padded.size |

nvarchar2(size) | Maximum size of 4000 bytes. | Maximum size of 4000 bytes. | Maximum size of 4000 bytes. | Where is the number of characters to store. Variable-length NLS string.size |

varchar2(size) | Maximum size of 4000 bytes. | Maximum size of 4000 bytes. | Maximum size of 4000 bytes. | Where is the number of characters to store. Variable-length string.size |

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 TypeSyntax | 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 For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal. is the precision and p is the scale. s |

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 For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal. is the precision and p is the scale. s |

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 For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal. is the precision and p is the scale. s |

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 For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal. is the precision and p is the scale. s |

integer | ||||

int | ||||

smallint | ||||

real | ||||

double precision |

## Date/Time Data types

Data TypeSyntax | 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) | 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)fractional seconds precision | Includes year, month, day, hour, minute, and seconds. For example: timestamp(6) |

timestamp ( fractional seconds precision) with time zone | 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)fractional seconds precision | 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 | 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)fractional seconds precision | 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 | 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)year precision | Time period stored in years and months. For example:interval year(4) to month |

interval day ( day precision)to second ( fractional seconds precision) | must be a number between 0 and 9. (default is 2) day 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 2) day 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 2) day precision must be a number between 0 and 9. (default is 6)fractional seconds precision | Time period stored in days, hours, minutes, and seconds. For example:interval day(2) to second(6) |

## Large Object (LOB) Datatypes

Data TypeSyntax | Oracle 9i | Oracle 10g | Oracle 11g | Explanation(if applicable) |

bfile | Maximum file size of 4GB. | Maximum file size of 2 ^{32}-1 bytes. | Maximum file size of 2 ^{64}-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 TypeSyntax | 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 is optional.size |

# Oracle/PLSQL: 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

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

For example:

Declaring a variable:LDescription varchar2(40);

LTotal CONSTANT numeric(8,1) := 8363934.1;

LType varchar2(10) := 'Example';

# Oracle/PLSQL: IS NULL

For example,

IF Lvalue IS NULL then

...

END IF;

If

*Lvalue*contains a null value, the "IF" expression will evaluate to TRUE.

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.

**not**null, click here.

# Oracle/PLSQL: IS NOT NULL

**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.

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.