When you write PL/SQL programs in Oracle Database 23ai, you often need storage for values that change during program execution and values that should never change. Variables allow you to store and modify data, while constants preserve values that remain fixed throughout the block or program.
Oracle Database 23ai fully supports these concepts and enhances their use by adding native support for BOOLEAN in SQL, better handling of JSON and VECTOR types, and consistent rules for initialization and null values.
In this Oracle tutorial, you will learn everything about declaring and using variables and constants, their rules, best practices, and how to work with them in Oracle 23ai.
What are variables in PL/SQL
A variable is a named memory location used to hold data that can be changed while your program runs. You can assign new values, use the variable in expressions, and pass it to functions or procedures.
DECLARE
v_name VARCHAR2(50);
v_salary NUMBER(10,2);
BEGIN
v_name := 'Sophia';
v_salary := 8500;
DBMS_OUTPUT.PUT_LINE('Employee ' || v_name || ' has salary ' || v_salary);
END;
/
Output
Employee Sophia has salary 8500
This shows how a variable can store and display information inside a block.
What are constants in PL/SQL
A constant behaves like a variable but once assigned a value, it cannot be changed. You declare it with the CONSTANT keyword and you must give it a value at declaration.
DECLARE
c_pi CONSTANT NUMBER(5,2) := 3.14;
v_radius NUMBER := 5;
v_area NUMBER;
BEGIN
v_area := c_pi * (v_radius * v_radius);
DBMS_OUTPUT.PUT_LINE('Circle area: ' || v_area);
END;
/
Output
Circle area: 78.5
Constants are ideal for fixed values like mathematical figures, standard rates, or codes that should not be altered.
Using NOT NULL in declarations
Oracle PL/SQL allows you to mark a variable or constant as NOT NULL. This means it cannot hold a null value. In Oracle 23ai, the rule remains strict: any scalar declared as NOT NULL must be given an initial value right away.
DECLARE
v_count NUMBER NOT NULL := 0; -- must be initialized
BEGIN
v_count := v_count + 1;
DBMS_OUTPUT.PUT_LINE('Count is: ' || v_count);
END;
/
If you attempt to declare v_count NUMBER NOT NULL; without an initial value, the compiler will throw an error. This ensures that NOT NULL variables are always safe to use.
Null values and empty strings
An important rule in PL/SQL is that empty strings are treated as NULL. If you assign '' to a variable, it becomes NULL. Comparisons with NULL never return TRUE or FALSE but instead return NULL. You must use IS NULL or IS NOT NULL to check.
DECLARE
v_text VARCHAR2(20) := '';
BEGIN
IF v_text IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Variable is NULL because empty strings count as NULL');
END IF;
END;
/
This behavior is different from many other languages, so it is important to remember when writing PL/SQL code.
Initial values and when they apply
You can initialize variables and constants using := or DEFAULT. The timing of initialization depends on where the declaration occurs:
- In a block or procedure: the initial value is assigned every time the block starts running.
- In a package specification: the initial value is assigned once per session.
DECLARE
v_counter NUMBER DEFAULT 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('Counter starts with: ' || v_counter);
END;
/
This makes your program predictable, because you know when the values reset.
Using %TYPE for safe declarations
The %TYPE attribute lets a variable or constant take its type from a table column or another variable. This ensures that your declarations automatically match changes in the database schema.
DECLARE
v_emp_name employees.first_name%TYPE;
v_bonus employees.commission_pct%TYPE := 0.05;
BEGIN
SELECT first_name INTO v_emp_name
FROM employees
WHERE employee_id = 1;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name || ', Bonus %: ' || v_bonus);
END;
/
If the size of first_name changes in the table, your PL/SQL code will still work without modification.
Inheritance rules of %TYPE
When %TYPE references a column, only the type and size are inherited, not constraints like NOT NULL. When %TYPE references another variable, it also inherits constraints such as NOT NULL, but it does not inherit the initial value.
DECLARE
base_var VARCHAR2(20) NOT NULL := 'Base';
child_var base_var%TYPE := 'Child'; -- must be initialized, because NOT NULL was inherited
BEGIN
DBMS_OUTPUT.PUT_LINE('Child value: ' || child_var);
END;
/
This behavior makes %TYPE reliable but requires awareness when using it with constraints.
Using %ROWTYPE for full rows
Sometimes you need to handle all columns of a row. %ROWTYPE creates a record structure with fields that match the columns.
DECLARE
v_emp employees%ROWTYPE;
BEGIN
SELECT * INTO v_emp
FROM employees
WHERE employee_id = 2;
DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp.first_name || ' has salary ' || v_emp.salary);
END;
/
This saves effort and avoids mistakes when working with many columns.
BOOLEAN support in Oracle Database 23ai
Before Oracle 23ai, BOOLEAN was allowed only inside PL/SQL. Now you can declare BOOLEAN columns in tables and use them in queries. This bridges the gap between SQL and PL/SQL.
DROP TABLE tasks PURGE;
CREATE TABLE tasks (
task_id NUMBER PRIMARY KEY,
task_name VARCHAR2(50),
completed BOOLEAN
);
INSERT INTO tasks VALUES (1,'Write Tutorial', TRUE);
INSERT INTO tasks VALUES (2,'Review Draft', FALSE);
COMMIT;
DECLARE
v_status BOOLEAN;
BEGIN
SELECT completed INTO v_status
FROM tasks
WHERE task_id = 1;
IF v_status THEN
DBMS_OUTPUT.PUT_LINE('Task completed');
ELSE
DBMS_OUTPUT.PUT_LINE('Task not completed');
END IF;
END;
/
With this change, Oracle Database makes logical data easier to handle.
Variables with JSON type
Oracle 23ai supports JSON as a true data type. You can declare JSON variables, assign values, and query parts of the document.
DECLARE
v_product JSON;
v_ram VARCHAR2(20);
BEGIN
v_product := JSON('{"name":"Laptop","specs":{"ram":"16GB"}}');
v_ram := JSON_VALUE(v_product,'$.specs.ram');
DBMS_OUTPUT.PUT_LINE('RAM: ' || v_ram);
END;
/
This integration is much faster and easier than working with JSON as plain text.
Variables with VECTOR type
Oracle 23ai introduces VECTOR type for AI embeddings. You can declare VECTOR variables in PL/SQL and use them for similarity queries.
DECLARE
v_search documents.embedding%TYPE := VECTOR('[0.25,0.60,0.75]');
v_distance BINARY_DOUBLE;
BEGIN
SELECT VECTOR_DISTANCE(embedding, v_search, COSINE)
INTO v_distance
FROM documents
WHERE doc_id = 1;
DBMS_OUTPUT.PUT_LINE('Similarity score: ' || TO_CHAR(v_distance));
END;
/
This feature makes it possible to integrate AI-powered search directly inside PL/SQL.
Best practices
Declare constants for values that never change to avoid mistakes. Use %TYPE and %ROWTYPE to keep your declarations safe when the schema changes. Always provide initial values for NOT NULL declarations. Remember that empty strings become NULL in PL/SQL. Take advantage of Oracle 23ai features such as BOOLEAN in SQL, JSON variables, and VECTOR types for modern workloads.
Conclusion
Variables and constants are fundamental to PL/SQL. They let you store temporary values and fixed values safely. Oracle Database 23ai keeps the traditional rules while adding modern features like BOOLEAN in SQL, native JSON, and VECTOR support. By mastering variables and constants and understanding the new enhancements, you can write PL/SQL programs that are reliable, flexible, and ready for both traditional applications and AI-driven workloads.
Note: All examples in this tutorial were tested on Oracle Database 23ai using Oracle SQL Developer version 24
