Home » PL/SQL

Strings in PL/SQL - 1 (Types of string with Example)

In this article, we are going to learn about the strings and its type in PL/SQL, we will learn about fixed length string, variable length string and character large object (CLOBs).
Submitted by Yash Kumar, on October 18, 2017

In PL/SQL, strings are basically sequence of characters; it provides fixed length strings as well as dynamic strings (variable length) string and a variety of function for easy manipulations.

Types of Strings in PL/SQL

There are three types of strings on PL/SQL:

1. Fixed length string
2. Variable length string
3. Character large objects (CLOBs)

1) Fixed length strings

In these types of strings, we the coders specify the length of Strings. If the length of string entered by user is less than specified length, then the remaining places are right padded by the spaces. These strings are made by the char and nchar data types.

2) Variable length strings

These types of Strings are dynamic in nature. If the user entered the String having length 10, then it will occupy space for 10 characters. Its maximum length is 4000.These strings are made by the varchar2 and nvarchar2 data types.

3) Character large object (CLOBs)

These are variable length strings which can store a large amount of data up to 128 terabytes. These are used to store text files. And these strings are made by using clob data type.

Example: Fixed and Variable length strings

Note: nchar and nvarchar2 are character data types, which are used to store unicodes. Here n stands for "National character set".

declare
name varchar2(15); --declaration of variable length string
grade char(1);  --declaration of fixed length string
begin
name:='mitali sajnani';
case name
	when 'yash kumar' then grade:='C';
	when 'mitali sajnani' then grade:='B';
	when 'khushi sharma' then grade:='A';
	when 'udit kumar gupta' then grade:='D';
	else  grade:='E';  
end case;
dbms_output.put_line('Grade obtained by the student '||name||' is: '||grade);
end;

Output

Grade obtained by the student mitali sajnani is: B
Statement processed.
0.00 seconds

Its output is easy to understand. Important thing is that char data type is storing fixed length string. If we declare char(10), then it will always take 10 bytes of memory irrespective of length of string entered by the user. While in case of varchar2(10), it will consume memory accordingly with the length of string entered. But maximum length will be 10.