Table of contents: Define PL/SQL and understand PL/SQL basics PL/SQL datatypes in Oracle PL/SQL functions and triggers...
in Oracle Stored procedures in PL/SQL
|PL/SQL datatypes in Oracle|
PL/SQL datatypes, as noted in Oracle's PL/SQL User's Guide and Reference manual, include numbers, characters, large objects, boolean, date, time and intervals. Every constant, variable, and parameter has a datatype, which specifies a storage format, constraints, and valid range of values. PL/SQL provides a variety of predefined datatypes. Here are some of the most common ones:
BLOB: The BLOB is one of the LOB (large object) datatypes that lets you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size, according to Oracle's PL/SQL User's Guide and Reference manual.
The BLOB definition from Whatis.com defines a BLOB (binary large object), pronounced BLAHB and sometimes spelled in all lower case, as a large file, typically an image or sound file, that must be handled (for example, uploaded, downloaded, or stored in a database) in a special way because of its size.
CLOB: The CLOB is also one of the LOB (large object) datatypes that lets you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size.
CHAR: According to Oracle's PL/SQL User's Guide and Reference manual, CHAR is a character type that lets you store alphanumeric data, represent words and text, and manipulate character strings. The CHAR datatype is used to store fixed-length character data.
DATE: DATE datatypes store fixed-length datetimes, which include the time of day in seconds since midnight. The date portion defaults to the first day of the current month; the time portion defaults to midnight. The date function SYSDATE returns the current date and time.
LONG and LONG RAW: The LONG datatype is used to store variable-length character strings, and the maximum size of a LONG value is 32760 bytes. The LONG RAW datatype is used to store binary data or byte strings. LONG RAW data is like LONG data, except that LONG RAW data is not interpreted by PL/SQL, according to Oracle's PL/SQL User's Guide and Reference manual.
VARCHAR2: It is recommended to use VARCHAR2 rather than VARCHAR in Oracle. VARCHAR2 is used to store variable-length character data. VARCHAR 2 takes a required parameter that specifies a maximum size up to 32767 bytes. Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use, according to Oracle's PL/SQL User's Guide and Reference manual.