PL/SQL Data Types
Oracle PL/SQL has a variety of data types that can be used to declare variables, constants, and record fields.
Oracle PL/SQL includes a variety of data types that can be used to store and manipulate different types of data within the database. The most commonly used data types in Oracle PL/SQL include:
NUMBER used to store numeric values. Can be specified with or without precision and scale.
VARCHAR2 used to store variable-length character strings. Can specify a maximum size up to 4000 characters.
CHAR used to store fixed-length character strings. Can specify a maximum size up to 2000 characters.
DATE used to store date and time values.
BOOLEAN used to store true or false values.
BLOB used to store binary large objects with a maximum size of 4 GB.
CLOB used to store character large objects with a maximum size of 4 GB.
LONG used to store variable-length character strings with a maximum size of 2 GB.
RAW used to store binary data with a maximum size of 2000 bytes.
BFILE used to store binary files.
ROWID used to store row addresses.
REF CURSOR used to store a cursor reference.
RECORD used to store an entire row of data from a table or view.
TABLE used to store an indexed collection of data, similar to an array.
These data types can be used in various parts of a PL/SQL program, such as in variable declarations, function and procedure arguments, and SQL statements.
The NUMBER data type is the most widely used data type in Oracle Database. This data type can be used to store both integer and floating-point numbers, and it also supports various mathematical operations.
Table with datatypes
| BINARY_INTEGER | CLOB | DECIMAL |
| DOUBLE PRECISION | FLOAT | INT |
| INTEGER | NATURAL | NATURALN |
| NUMBER | NUMERIC | PLS_INTEGER |
| POSITIVE | POSITIVEN | REAL |
| SIGNTYPE | SMALLINT | CHAR |
| CHARACTER | LONG | LONG RAW |
| NCHAR | NVARCHAR2 | RAW |
| ROWID | STRING | UROWID |
| VARCHAR | VARCHAR2 | BOOLEAN |
| DATE | INTERVAL DAY TO SECOND | INTERVAL YEAR TO MONTH |
| TIMESTAMP | TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMP WITH TIME ZONE |
It’s important to choose the appropriate data type for your specific needs, as it can have an impact on the performance and functionality of your PL/SQL program.