How to Create Procedures in Oracle Database 23ai PL/SQL

In Oracle Database 23ai, procedures are a core part of PL/SQL programming. They allow you to group multiple SQL and PL/SQL statements into a single reusable block of code. By encapsulating logic inside procedures, developers can improve maintainability, enforce security, and enhance performance. With Oracle Database 23ai, you also gain access to advanced features like the new JSON data type and vector data processing, which can be directly integrated into your procedures for modern data-driven applications.

This tutorial walks you step by step through creating and executing procedures, using parameters, handling exceptions, and working with advanced features such as JSON and vector operations.

Syntax of a Procedure in Oracle Database 23ai

Before diving into practical examples, it is important to understand the basic syntax of a PL/SQL procedure in Oracle Database 23ai. A procedure is defined once and can be executed multiple times with different parameters.

CREATE [OR REPLACE] PROCEDURE procedure_name 
   (parameter_name [IN | OUT | IN OUT] datatype [, ...])
IS
   -- Declare local variables here
BEGIN
   -- Executable statements
   -- Your logic goes here
EXCEPTION
   -- Exception handling (optional)
   WHEN exception_name THEN
      -- Error handling statements
END procedure_name;
/

• CREATE [OR REPLACE] PROCEDURE: Creates a new procedure or replaces an existing one with the same name.
• Parameters: You can pass values into a procedure or return values using IN, OUT, or IN OUT.
  • IN: Input parameter, read-only inside the procedure.
  • OUT: Output parameter, used to return a value to the caller.
  • IN OUT: Both input and output; the caller passes a value, and the procedure may modify and return it.
• BEGIN … END: Contains the executable logic.
• EXCEPTION: Optional block to handle errors gracefully.

Creating a Simple Procedure

When learning procedures, start with a basic example. A simple procedure can perform a single action such as updating a record. In this case, we will create a procedure that increases the salary of an employee by a given percentage. This will also help demonstrate the use of IN parameters in PL/SQL.

Create a sample table and insert data

CREATE TABLE prc_emp (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    salary        NUMBER(10,2),
    department_id NUMBER
);

INSERT INTO prc_emp VALUES (101, 'John', 'Doe', 5000, 10);
INSERT INTO prc_emp VALUES (102, 'Jane', 'Smith', 6000, 20);
INSERT INTO prc_emp VALUES (103, 'Bruce', 'Wayne', 8000, 30);

COMMIT;

Create the procedure

CREATE OR REPLACE PROCEDURE prc_raise_salary (
    p_emp_id   IN  NUMBER,
    p_percent  IN  NUMBER
) IS
BEGIN
    UPDATE prc_emp
    SET salary = salary + (salary * p_percent / 100)
    WHERE employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('Salary updated successfully.');
END;
/

• p_emp_id is an IN parameter. You pass the employee ID whose salary should be updated.
• p_percent is also an IN parameter. It tells the procedure how much percentage increase should be applied to the salary.
• The UPDATE statement recalculates the salary. For example, if salary = 8000 and p_percent = 10, the new salary will be 8000 + (8000 * 0.10) = 8800.
• DBMS_OUTPUT.PUT_LINE is used to display a confirmation message in the SQL*Plus or SQL Developer output panel.

Execute the procedure

SET SERVEROUTPUT ON;

BEGIN
   prc_raise_salary(103, 10);
END;
/

Check the updated result

SELECT employee_id, first_name, last_name, salary
FROM prc_emp
WHERE employee_id = 103;

Output:

EMPLOYEE_ID   FIRST_NAME   LAST_NAME   SALARY
-----------   ----------   ---------   -------
103           Bruce        Wayne       8800

This proves the procedure successfully updated the salary.

Creating a Procedure with IN, OUT, and IN OUT Parameters

Procedures can return values using OUT parameters and can also modify existing input values using IN OUT. This makes them flexible for real-world business logic, such as returning calculated results.

Example: Fetch Employee Salary

CREATE OR REPLACE PROCEDURE prc_get_salary (
    p_emp_id   IN  NUMBER,
    p_salary   OUT NUMBER
) IS
BEGIN
    SELECT salary
    INTO p_salary
    FROM prc_emp
    WHERE employee_id = p_emp_id;
END;
/

• p_emp_id (IN): Input parameter; the employee ID you provide.
• p_salary (OUT): Output parameter; the procedure will store the salary value in this variable.

Execute the procedure

DECLARE
   v_salary NUMBER;
BEGIN
   prc_get_salary(102, v_salary);
   DBMS_OUTPUT.PUT_LINE('Employee 102 salary = ' || v_salary);
END;
/

Output:

Employee 102 salary = 6000

This shows how to use OUT parameters to return values from a procedure. If you use IN OUT, you can pass a variable with an initial value, and the procedure will return the modified value.

Adding Exception Handling in Procedures

Errors such as missing records, invalid data, or constraint violations are common in database operations. Oracle PL/SQL provides exception handling blocks to manage these errors. Including an exception section in a procedure ensures stability and better debugging.

Example: Update Salary with Error Handling

CREATE OR REPLACE PROCEDURE prc_update_salary (
    p_emp_id   IN NUMBER,
    p_new_sal  IN NUMBER
) IS
BEGIN
    UPDATE prc_emp
    SET salary = p_new_sal
    WHERE employee_id = p_emp_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee ID not found.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Salary updated successfully for employee ' || p_emp_id);
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

• p_emp_id (IN): Employee ID whose salary you want to update.
• p_new_sal (IN): The new salary to be set.
• SQL%ROWCOUNT: Returns how many rows were affected by the last UPDATE. If zero, no employee was updated, so we raise an error.
• RAISE_APPLICATION_ERROR: Custom error handling that throws an application-defined error with a specific error number and message.

Execute the procedure

SET SERVEROUTPUT ON;

BEGIN
   prc_update_salary(105, 9000); -- Employee 105 does not exist
END;
/

Output:

Error: ORA-20001: Employee ID not found.

This ensures the application receives a meaningful error instead of silently failing.

Writing Procedures with JSON in Oracle Database 23ai

Oracle Database 23ai includes a new JSON data type that makes handling semi-structured data much faster and easier. You can store JSON documents directly in a column and access them in procedures without conversion.

Create a table and procedure with JSON

CREATE TABLE prc_customers (
    customer_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_data JSON
);

INSERT INTO prc_customers (customer_data) VALUES (
    JSON('{"name": "Alice", "email": "alice@example.com", "loyalty": "Gold"}')
);

INSERT INTO prc_customers (customer_data) VALUES (
    JSON('{"name": "Bob", "email": "bob@example.com", "loyalty": "Silver"}')
);

COMMIT;

CREATE OR REPLACE PROCEDURE prc_get_customer_email (
    p_customer_id IN  NUMBER,
    p_email       OUT VARCHAR2
) IS
BEGIN
    SELECT c.customer_data.email.string()
    INTO p_email
    FROM prc_customers c
    WHERE c.customer_id = p_customer_id;
END;
/

• p_customer_id (IN): Identifies the row in the JSON-enabled table.
• p_email (OUT): Returns the email extracted from the JSON column.
• c.customer_data.email.string(): A 23ai JSON field accessor that retrieves the string value of the email property directly.

Execute the procedure

DECLARE
   v_email VARCHAR2(100);
BEGIN
   prc_get_customer_email(1, v_email);
   DBMS_OUTPUT.PUT_LINE('Customer email = ' || v_email);
END;
/

Output:

Customer email = alice@example.com

This demonstrates how procedures can interact with JSON data seamlessly using Oracle 23ai’s native JSON features.

Writing Procedures with Vector Data in Oracle Database 23ai

Oracle Database 23ai introduces vector data types for AI-driven applications. You can use PL/SQL procedures to store embeddings and perform similarity searches directly inside the database, removing the need for external vector databases.

Create a table with vector data

CREATE TABLE prc_documents (
    doc_id    NUMBER PRIMARY KEY,
    content   CLOB,
    embedding VECTOR(4)  -- 4-dimensional vector for demonstration
);

INSERT INTO prc_documents VALUES (
    1,
    'Oracle Database 23ai introduces vector search for AI-driven applications.',
    TO_VECTOR('[0.12, 0.93, -0.44, 0.58]')
);

INSERT INTO prc_documents VALUES (
    2,
    'This tutorial explains how to write procedures in Oracle 23ai using PL/SQL.',
    TO_VECTOR('[0.11, 0.89, -0.40, 0.60]')
);

COMMIT;

Create a procedure for vector similarity search

CREATE OR REPLACE PROCEDURE prc_search_similar_docs (
    p_query_vec IN VECTOR,
    p_top_n     IN NUMBER
) IS
BEGIN
    FOR rec IN (
        SELECT doc_id, content, embedding <-> p_query_vec AS distance
        FROM prc_documents
        ORDER BY embedding <-> p_query_vec
        FETCH FIRST p_top_n ROWS ONLY
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Doc ID: ' || rec.doc_id ||
                             ' | Distance: ' || rec.distance ||
                             ' | Content: ' || DBMS_LOB.SUBSTR(rec.content, 60));
    END LOOP;
END;
/

• p_query_vec (IN): The input vector, usually generated by an AI/ML model.
• p_top_n (IN): The number of most similar documents to return.
• embedding <-> p_query_vec: Oracle 23ai operator for vector similarity (Euclidean distance).
• DBMS_LOB.SUBSTR: Extracts part of the CLOB text for display.

Execute the procedure

SET SERVEROUTPUT ON;

DECLARE
   v_query_vec VECTOR(4) := TO_VECTOR('[0.10, 0.90, -0.42, 0.59]');
BEGIN
   prc_search_similar_docs(v_query_vec, 2);
END;
/

Sample Output:

Doc ID: 2 | Distance: 0.01 | Content: This tutorial explains how to write procedures in Oracle 23ai using PL/SQL.
Doc ID: 1 | Distance: 0.03 | Content: Oracle Database 23ai introduces vector search for AI-driven applications.

This example shows how you can leverage Oracle Database 23ai to build AI-powered solutions by embedding vector operations into PL/SQL procedures.

Managing Procedures

After creating procedures, you may need to modify, recompile, or remove them.

-- Recompile a procedure after changes in dependent objects
ALTER PROCEDURE prc_update_salary COMPILE;

-- Drop a procedure if it is no longer needed
DROP PROCEDURE prc_get_salary;

Managing procedures carefully ensures your database remains clean and free from unused objects. Using version control for procedure definitions is a good practice in enterprise environments.

Conclusion

Procedures in Oracle Database 23ai provide a structured way to implement reusable, secure, and efficient business logic inside the database. By understanding how to define procedures, use input and output parameters, handle exceptions, and integrate modern features like JSON data types and vector search, you can build powerful applications that take advantage of Oracle’s latest capabilities.