PL/SQL VARCHAR

In Oracle PL/SQL, the VARCHAR data type is used to store character strings of variable length. A VARCHAR column can store alphanumeric data and symbols, such as letters, numbers, and special characters, up to a maximum length specified during declaration.

Syntax

The syntax for declaring a VARCHAR variable or column is as follows:

DECLARE
  -- declares a VARCHAR variable with a maximum length of 50 characters
  my_var VARCHAR(50);
BEGIN
  -- code block here
END;

In the example above, my_var is a VARCHAR variable that can store up to 50 characters. The maximum length can range from 1 to 4000 bytes.

One advantage of using VARCHAR over CHAR (fixed-length character string) data type is that it takes up less storage space. Since VARCHAR only stores the characters entered plus 1 or 2 bytes for overhead, it is more space-efficient compared to CHAR, which always reserves the maximum length specified, regardless of the actual data length.

To manipulate VARCHAR data, PL/SQL provides built-in functions and operators such as CONCAT, SUBSTR, INSTR, LENGTH, and others.

Example

Here’s an example that shows how to concatenate two VARCHAR variables:

DECLARE
  first_name VARCHAR(50) := 'John';
  last_name VARCHAR(50) := 'Doe';
  full_name VARCHAR(100);
BEGIN
  full_name := CONCAT(first_name, ' ', last_name);
  DBMS_OUTPUT.PUT_LINE(full_name); -- prints "John Doe"
END;

In the example above, the CONCAT function is used to concatenate the first and last name variables with a space in between. The resulting string is stored in the full_name variable and then printed to the console using the DBMS_OUTPUT.PUT_LINE procedure.

In summary, VARCHAR is a data type used in Oracle PL/SQL to store variable-length character strings. It is space-efficient, flexible, and can be manipulated using various built-in functions and operators.