PL/SQL VARCHAR2

The PL/SQL VARCHAR2 data type in Oracle database is used to store character strings of variable length.

Syntax

VARCHAR2(size)

where size is the number of characters that the string can contain.

Examples

The maximum length of a VARCHAR2 data type used in a table column is 4000 characters.

create table test_table(
id number,
comments varchar2(4000)
);

In the example above, if you try to assign a size that is longer than 4000 characters to varchar2 column, an error will occur.

The maximum length of a PL/SQL VARCHAR2 data type used in a anonymous block is 32767 characters.

declare
v_string varchar2(32767):='Test characters';
begin
DBMS_OUTPUT.PUT_LINE('v_string = '||v_string);
end;

declare
v_string varchar2(100);
begin
v_string:='This is a character string';
if v_string is not null then
DBMS_OUTPUT.PUT_LINE('v_string = '||v_string);
end if;
end;

In the example above, the variable v_string is declared as a VARCHAR2 data type with a size of 100 characters. The string ‘This is a character string’ is then assigned to the variable v_string.

Note that if you try to assign a string that is longer than 100 characters to the v_string variable, an error will occur.

You can also use the PL/SQL VARCHAR2 data type to store values of other data types, such as numbers or dates. However, you need to convert the value to a string first before storing it in a VARCHAR2 variable.

For example:

DECLARE 
my_var VARCHAR2(250); 
my_num NUMBER := 100; 
my_date DATE := SYSDATE; 
BEGIN 
my_var := TO_CHAR(my_num); 
DBMS_OUTPUT.PUT_LINE('1. my_var = '||my_var);

my_var := TO_CHAR(my_date, 'DD-MM-YYYY'); 
DBMS_OUTPUT.PUT_LINE('2. my_var = '||my_var);

my_var := my_num; 
DBMS_OUTPUT.PUT_LINE('3. my_var = '||my_var);

my_var := my_date; 
DBMS_OUTPUT.PUT_LINE('4. my_var = '||my_var);
END; 

In the example above, the my_var variable is first used to store the number 100. The number is converted to a string before it is stored in the my_var variable.

The second assignment to the my_var variable converts the sysdate value to a string in the format DD-MM-YYYY and stores it in the my_var variable.

When declaring a VARCHAR2 variable, it is a good idea to specify the maximum size of the string that the variable can store. This helps to prevent errors from occurring if a string that is too long is assigned to the variable.

It is also a good idea to use the TO_CHAR function when converting values of other data types to strings before storing them in a VARCHAR2 variable. This helps to ensure that the value is converted to a string in the correct format.