PL/SQL Data Types

By | May 25, 2015
In the previous tutorial, you learnt about how to write and run Hello World program in PL/SQL programming language. In this tutorial you will learn about various data types used in PL/SQL.

PL/SQL Data Types

Every programming language has built-in data types which are used for declaring variables and many other related tasks. Variable is nothing but the name of the memory space. Every variable has a data type which specifies the storage format, set of specified values, range of the variable and many other such factors. 

Variables have to be defined in the declaration section of a PL/SQL program. However, its not mandatory to include a declaration section and variable declaration in a PL/SQL program if its not required.

The Data Types used in PL/SQL are as follows:

PL/SQL Data Types
Image Source

1. Scalar: These data types don’t include any internal components. It includes data types such as NUMBER, DATE, BOOLEAN, etc.

2. Large Objects (LOB): This type of data type stores objects that are relatively large in size and stored separately from other data types such as text, graphic images, video clips, sound, etc.

3. Composite: These type of data types have internal components that can be accessed individually. It includes records and collections. 

4. Reference: As the name sounds, it includes pointers that refer to the location of the other data items.

Now, I have listed down some of the sub data types frequently used in PL/SQL

1. Numeric: Numeric values on which arithmetic operations are performed. It includes sub types such as number, decimal, real, float, etc.

2. Character: Character values on which character operations such as strings are performed. It includes sub types such as char, varchar, varchar2, nvarchar2, etc.

3. Data and Time: This data type is used to store fixed data type which displays and saves time and date values. The default data format saved into the database might be ‘DD-MM-YY’.  However, you can change and alter the position of the terms accordingly.

4. Boolean: These include logical values on which logical operations are performed. The logical values are the Boolean values TRUE and FALSE and the value NULL. But, SQL has no data type equivalent to BOOLEAN. It cannot be used in SQL Statements, built in SQL functions such as To_char, PL/SQL function invoked from DQL commands.

5. Number: Syntax: Number(Precision, Scale). Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0.

6. Float: ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits).

7. Integer: ANSI and IBM specific integer type with maximum precision of 38 decimal digits 

8. Real: Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits).

9. Varchar2: Variable-length character string with maximum size of 32,767 bytes. 

10. Rowid: Physical row identifier, the address of a row in an ordinary table.

11. Bfile: Used to store large binary objects in operating system files outside the database               System-dependent. Cannot exceed 4GB.

12. Blob: Used to store large binary objects in the database. Memory Capacity: 8 to 128 TB. 

13. Clob: Used to store large blocks of character data in the database. Memory Capacity: 8 to 128 TB.

14. Nclob: Used to store large blocks of NCHAR data in the database. Memory Capacity: 8 to 128 TB.

Note: A variable in PL/SQL program code can be assigned a NULL value i.e., ‘’. However, it cannot be equated with any other variable or even itself.

Variable Declaration

Variable-Name DataType(Precision/Dimension)

Emp_name varchar2(30)

Here, Emp_name is the variable name and varchar2 is the data type with the 30 as the precision or the dimension. It is allowed to assign values during declaration of a variable in the declaration block. Setting the precision as 30 allows us to assign a string value of not more than 30 characters for that particular variable.

There are many more PL/SQL data types used in PL/SQL programming language. Since, this tutorial is especially for beginners, we just want you to first grasp the basic understanding and then jump into deeper details. These mentioned are sufficient for you to get started into PL/SQL programming. However, we shall mention new data types at regular intervals later.

If you find anything incorrect In above tutorial then please mention it by commenting below. You can also ask your queries.

Leave a Reply

Your email address will not be published. Required fields are marked *