PL/SQL datatypes in Oracle

Table of contents:
[IMAGE] Define PL/SQL and understand PL/SQL basics
[IMAGE] PL/SQL datatypes in Oracle
[IMAGE] PL/SQL functions and triggers in Oracle
[IMAGE] 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 ...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Using Oracle PL/SQL Oracle tutorial library: SearchOracle.com's learning guides SELECT statement syntax and examples Oracle PL/SQL tutorial PL/SQL functions and triggers in Oracle Stored procedures in PL/SQL How to concatenate rows into a single CLOB in PL/SQL How to open a ref cursor in a PL/SQL procedure Working with substitution variables and using EXECUTE IMMEDIATE in PL/SQL Oracle's free SQL Developer adds database migration tool Confused about Oracle certification exams Oracle and SQL Oracle tutorial library: SearchOracle.com's learning guides Can I specify Oracle column order in my database table? Review: Oracle's 11g R2 database has some good and bad SELECT statement syntax and examples Oracle PL/SQL tutorial PL/SQL functions and triggers in Oracle Stored procedures in PL/SQL Do I need a license for SQL Developer Data Modeler in Oracle? Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary PL/SQL  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.