PL/SQL Strings

In this tutorial we will learn about Strings in PL/SQL covering various types of strings, syntax of declaring a string variable and then using it in a PL/SQL code block.

Just like in any other programming language, string in PL/SQL is a combination of characters which can be numbers, special characters, alphabets or all together.

PL/SQL offers us the liberty of creating variable length strings where it is optional to provide the size of the string.

In PL/SQL there are basically three different types of strings, they are:

1. Strings with fixed length: When we define a fixed length string we must specify the size of the string while declaring it. Once we declare a fixed length string the variable occupies the memory space equal to the length of the string filling the memory space with empty spaces.
2. Strings with variable length: In case of variable length string, the string can take a length upto a maximum of 32,767 characters. When a variable length string is declared initially no space is provided to it.
3. CLOB (character large object): This is a type of variable length string which can take a size of upto 128 terabytes.

PL/SQL: Syntax of String Declaration

A basic literal is also a string when enclosed in a quotation marks, for example:

'This is a string literal'

To include a single quote in the string text, you will have to use two single quotes. For example, 'You think it' 's your choice'.

For declaring a string variable, we have to specify the datatype and length value which defines the maximum length of the string.

We can use datatypes like CHAR, VARCHAR2, CLOB etc, where each of these is used to define a different type of string.

First let's see how to declare a variable length string variable. We use VARCHAR2 datatype for it.

name VARCHAR2(10)

Here, name is the name of the variable, VARCHAR2 is the datatype and 10 is the maximum length that the string can take.

Now, let's see how can we declare a fixed length string variable. We use CHAR datatype for this. While declaring this type of string, you may or may not provide the size/length of the string because if you do not provide it, Oracle Database automatically uses a maximum length required. For example,

approved CHAR(1) := 'Y'
approved CHAR := 1

Let's take an example of PL/SQL code where we will be using multiple strings to understand the concept better.

PL/SQL String usage Example

In the code example below we will be using all 3 types of strings.

DECLARE 
   	s_name VARCHAR2(20); 
   	s_age NUMBER(2); 
   	s_address CLOB; 
   	s_gender CHAR(1); 
BEGIN 
   	s_name := 'Sherlock Holmes'; 
   	s_age := 21; 
   	s_address := '221B, Baker Street, London'; 
   	s_gender := 'M';
   	IF s_gender = 'M' THEN 
      	dbms_output.put_line(s_name || ' is a student at Studytonight'); 
      	dbms_output.put_line('He is ' || s_age || ' years old.'); 
      	dbms_output.put_line('He lives at ' || s_address); 
    ELSE
    	dbms_output.put_line(s_name || ' is a student at Studytonight'); 
      	dbms_output.put_line('She is ' || s_age || ' years old.'); 
      	dbms_output.put_line('She lives at ' || s_address);
   END IF; 
END; 
/

Sherlock Holmes is a student at Studytonight He is 21 years old. He lives at 221B, Baker Street, London

PL/SQL: Useful String Function

You can find the list and usage examples of all the string functions in the official documentation at Oracle's website here: String Functions