Sage-Code Laboratory

PL/SQL Data Types

PL/SQL is designed to manipulate data elements. It is a strongly typed language with explicit types. The SQL types and PL/SQL types are a little bit different. Let's explore these types.

String Type

There are 3 types of strings in PL/SQL:

  1. CHAR: Right padded, fixed-length strings;
  2. VARCHAR2: Variable length strings, maximum 32767 code points;
  3. CLOB: Character large objects, up to 128 terabytes.

Numeric Type

SQL has only one numeric data type: NUMBER. This data type can be stored in the database. PL/SQL has several other numeric data types designed to improve computation performance.

Date Type

There are three datatypes you can use to work with dates and times:

Boolean Type

You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL. Only logic operations are allowed on BOOLEAN variables. Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable.

Note: You cannot insert the values TRUE and FALSE into a database column. Also, you cannot select or fetch column values into a Boolean variable. Database SQL do not support Boolean type.

Data Elements

For working with data types you need two things: First you must declare variable, constant or parameter using a data type. Second you can give a value to these elements using an assignment.
Constants and input parameters are immutable. Variables and output parameters can be modified using modification operators. Next I will post examples with comments:

Declaring variables:

    v_message varchar2(30); -- variable length string with maxim capacity 30
    c_code char(10); -- fixed length string
    n_index number(5,2); -- number with two decimals
    d_today date := SYSDATE; -- a date with initial value: today
    v_message := 'hello world'; -- alter a variable
    DBMS_OUTPTUT.PUT_LINE(v_message); -- print a message

In the example above we declare several variables and use only one. That is, we can define variables that can be used later in the code but when we compile this code we may receive some warnings for variables that are not used.


In PL/SQL expressions have data type. Expressions of same data type can be combined in larger expressions. Logical expressions can be used in conditional statements: {IF, WHILE, CASE}.

Different data types can be converted using functions to make two data types compatible. For this we will explain more in next chapter about data processing.


-- example of expressions
    m varchar2(10);
    a pls_integer;
    b boolean;
    -- string expression
    m := 'hello' || 'world';
    -- numeric expression
    a := a + 1;
    -- logical expression
    if b = False then
    dbms_output.put_line('initial value for boolean is False');
    end if;

Data Operators

First operator we already use is ":=". This is assignment operator. It can be used to setup the initial value for a variable or reset an existing variable to take a new value. The old value is lost and is replaced by the new value using an expression or a literal.

Expressions are based on variables, data literals, operators and function calls. Operators are specific to a particular data type. For example operator "||" can be used to concatenate two strings while operator "+" can be used for addition between two numbers:

Modification operators

Operator Description Example
:= Assign/reset operator str := ‘test'
|| Concatenation of two strings str := ‘test ‘||'concatenation'
+ Addition of two numbers sum := 4 + 8;
- Subtraction of two numbers diff := 8 - 4;
* Multiplication of two numbers prod := 4 * 8;
/ Division of two numbers div := 8 / 4;
** Exponent operator bool := (2 ** 3 = 8); — true

Relation operators

Next operators have a Boolean result. The result can be used in conditional statements.
given: a = 1, b = 2

Operator Description Example
= Is equal a = b; — False
<> Not equal: alternatives: (!=, ~=) a != b; — True
< Less than a < b; — True
> Greater than a > b; — False
>= Greater than or equal to 2 >= b; — True
<= Less than or equal to a <= b; — True

Logical operators

Next operators are keyword based operators for Boolean expressions.
given: a = True, b = False

Operator Description Example Result
= Is equal a = b False
<> Not equal: alternatives: (!=, ~=) a != b True
< Less than a < b True
> Greater than a > b False
>= Greater than or equal to 2 >= b True
<= Less than or equal to a <= b True

Read next: Packages