PL/SQL datatypes in Oracle

Learn about PL/SQL datatypes in Oracle, and learn the definitions of BLOB, CLOB, DATE, VACHAR2 and more in this PL/SQL tutorial for beginners and experts.

In this section, learn about PL/SQL datatypes in Oracle, and learn the definitions of BLOB, CLOB, DATE, VACHAR2, LONG and LONG RAW and more.

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.

Learn how to find a BLOB file size and convert LONGRAW to BLOB.

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.

Learn how to view data stored in a CLOB, search in CLOB columns and concatenate rows into a single CLOB in PL/SQL.

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.

Learn how to write Oracle scripts for dates, days and holidays , write coding for a unique calendar yearin Oracle PL/SQL and transfer an Oracle table on date change.

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.

Learn how to load LONG RAW data, convert LONGRAW to BLOB and get the number of bytes in a LONG RAW column.

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.

This was first published in August 2009

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close