PL/SQL - Data Types

PL/SQL - Data Types - Large Object (LOB)

Complete Tutorial

There SCALAR, LOB, Composite and Reference data types available in PL/SQL.
CategoryDescription
ScalarSingle values with no internal components, such as a NUMBER, DATE, or BOOLEAN.
Large Object (LOB)Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.
CompositeData items that have internal components that can be accessed individually. For example, collections and records.
ReferencePointers to other data items.

Scalar Data Types

Numeric
Character
Boolean
Datetime

Numeric Data Types

PLS_INTEGER
BINARY_INTEGER
BINARY_FLOAT
BINARY_DOUBLE
NUMBER(prec, scale)
DEC(prec, scale)
DECIMAL(prec, scale)
NUMERIC(pre, secale)
DOUBLE PRECISION
FLOAT
INT
INTEGER
SMALLINT
REAL

DECLARE
   n1 INTEGER;
   n2 REAL;
   n3 DOUBLE PRECISION;
BEGIN
   null;
END;
/

Character Data Types

CHAR
VARCHAR2
RAW
NCHAR
NVARCHAR2
LONG
LONG RAW
ROWID
UROWID

Boolean Data Types

The logical values are the Boolean values TRUE and FALSE.

Datetime

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR

LOB Data Types

Large object (LOB) data types refer to large data items like text, graphic images, video clips and etc .. 
BFILE
Used to store large binary objects in operating system files outside the database.
BLOB
Used to store large binary objects in the database.
CLOB
Used to store large blocks of character data in the database.
NCLOB
Used to store large blocks of NCHAR data in the database.

User-Defined Subtypes

A sub type is a subset of another data type, which is called its base type.

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);
You can define and use your own subtypes.

DECLARE
   SUBTYPE s_name IS char(20);
   SUBTYPE s_message IS varchar2(100);
   salutation s_name;
   greetings s_message;
BEGIN
   salutation := 'Mr. Hareesh ';
   greetings := 'This website is for learning Oracle';
   dbms_output.put_line('Hello ' || salutation || greetings);
END;
/

<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial

*/