How to Use Cursors in Oracle Database 23ai PL/SQL

In Oracle Database 23ai, PL/SQL is not limited to single-row queries. Many times you need to process multiple rows one by one—for example, when iterating through employees, orders, or JSON data. This is where cursors become essential. A cursor acts like a pointer to the result set of a query, allowing you to fetch rows sequentially.

In this Oracle tutorial, you will learn how to use cursors in PL/SQL with clear examples, complete with data preparation, and we will also explore new Oracle Database 23ai features like JSON handling and vectors.

What is a Cursor in PL/SQL?

A cursor is a mechanism that enables you to retrieve multiple rows from a query and process them individually. Think of it as a handle to a query result set. There are two main types:

• Implicit cursors – Created automatically when you run a SELECT INTO, INSERT, UPDATE, or DELETE statement.
• Explicit cursors – Declared by developers to control the process of fetching multiple rows.

Cursors are especially useful in loops, where each row from the result set can be processed in sequence.

Preparing Sample Data

Before writing cursor examples, let us create a simple table and insert some data that we can use throughout the tutorial. This makes our examples reproducible and easy to test.

-- Create sample table
CREATE TABLE employees (
   employee_id   NUMBER PRIMARY KEY,
   first_name    VARCHAR2(50),
   salary        NUMBER,
   department_id NUMBER
);

-- Insert sample records
INSERT INTO employees VALUES (101, 'Neena', 17000, 90);
INSERT INTO employees VALUES (102, 'Lex',   14000, 90);
INSERT INTO employees VALUES (103, 'Bruce',  9000, 60);
INSERT INTO employees VALUES (104, 'Diana',  8000, 60);
INSERT INTO employees VALUES (105, 'Alice',  6000, 80);
INSERT INTO employees VALUES (106, 'Bob',    4000, 80);

COMMIT;

We also create a table for vector examples:

-- Create demo vector table
CREATE TABLE demo_vec (
  id  NUMBER PRIMARY KEY,
  emb VECTOR(3)
);

-- Insert vector data
INSERT INTO demo_vec VALUES (1, TO_VECTOR('[0.12,0.45,0.78]'));
INSERT INTO demo_vec VALUES (2, TO_VECTOR('[0.10,0.40,0.80]'));
INSERT INTO demo_vec VALUES (3, TO_VECTOR('[0.90,0.10,0.05]'));
COMMIT;

Now we are ready to use this data in cursor examples.

Example 1: Using an Implicit Cursor

This example shows how Oracle automatically uses an implicit cursor when you select a single row.

SET SERVEROUTPUT ON;

DECLARE
   v_name   employees.first_name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   SELECT first_name, salary
   INTO   v_name, v_salary
   FROM   employees
   WHERE  employee_id = 101;

   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ' | Salary: ' || v_salary);

   -- implicit cursor attributes
   DBMS_OUTPUT.PUT_LINE('Rows Processed: ' || SQL%ROWCOUNT);
   DBMS_OUTPUT.PUT_LINE('Was data found? ' || CASE WHEN SQL%FOUND THEN 'Yes' ELSE 'No' END);
END;
/

Result:

Employee: Neena | Salary: 17000  
Rows Processed: 1  
Was data found? Yes  

Example 2: Explicit Cursor Basics

This example shows how to declare, open, fetch, and close an explicit cursor.

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c_emp IS
      SELECT employee_id, first_name, salary
      FROM   employees
      WHERE  department_id = 60;

   v_id     employees.employee_id%TYPE;
   v_name   employees.first_name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   OPEN c_emp;
   LOOP
      FETCH c_emp INTO v_id, v_name, v_salary;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('ID: '||v_id||', Name: '||v_name||', Salary: '||v_salary);
   END LOOP;
   CLOSE c_emp;
END;
/

Result:

ID: 103, Name: Bruce, Salary: 9000  
ID: 104, Name: Diana, Salary: 8000  

Example 3: Cursor FOR LOOP

This example demonstrates a cursor FOR LOOP, which simplifies cursor management because Oracle handles opening, fetching, and closing automatically.

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c_sales IS
      SELECT employee_id, salary
      FROM   employees
      WHERE  salary > 10000;
BEGIN
   FOR rec IN c_sales LOOP
      DBMS_OUTPUT.PUT_LINE('Employee '||rec.employee_id||' earns '||rec.salary);
   END LOOP;
END;
/

Result:

Employee 101 earns 17000  
Employee 102 earns 14000  

Example 4: Parameterized Cursor

This example shows how to pass parameters to cursors, making them reusable for different conditions.

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c_dept (p_deptno NUMBER) IS
      SELECT employee_id, first_name, salary
      FROM   employees
      WHERE  department_id = p_deptno;

BEGIN
   FOR rec IN c_dept(80) LOOP
      DBMS_OUTPUT.PUT_LINE('Dept 80 -> '||rec.first_name||' earns '||rec.salary);
   END LOOP;

   FOR rec IN c_dept(60) LOOP
      DBMS_OUTPUT.PUT_LINE('Dept 60 -> '||rec.first_name||' earns '||rec.salary);
   END LOOP;
END;
/

Result:

Dept 80 -> Alice earns 6000  
Dept 80 -> Bob earns 4000  
Dept 60 -> Bruce earns 9000  
Dept 60 -> Diana earns 8000  

Example 5: Cursor with JSON Data in Oracle 23ai

This example demonstrates how to iterate through JSON data with JSON_TABLE inside a cursor loop.

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c_json IS
      SELECT jt.id, jt.name, jt.status
      FROM JSON_TABLE(
             '{"employees":[
                 {"id":201,"name":"John","status":"active"},
                 {"id":202,"name":"Emma","status":"inactive"}
               ]}',
             '$.employees[*]'
             COLUMNS (
               id     NUMBER        PATH '$.id',
               name   VARCHAR2(50)  PATH '$.name',
               status VARCHAR2(20)  PATH '$.status'
             )
          ) jt;
BEGIN
   FOR rec IN c_json LOOP
      DBMS_OUTPUT.PUT_LINE('ID='||rec.id||', Name='||rec.name||', Status='||rec.status);
   END LOOP;
END;
/

Result:

ID=201, Name=John, Status=active  
ID=202, Name=Emma, Status=inactive  

This shows how cursors can process JSON data structures row by row in Oracle 23ai.

Example 6: Cursor with Vector Similarity in Oracle 23ai

With Oracle 23ai’s new vector data type, you can use cursors to process similarity scores across rows.

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c_vec IS
      SELECT id,
             1 / (1 + VECTOR_DISTANCE(emb, TO_VECTOR('[0.12,0.45,0.80]'))) AS similarity
      FROM   demo_vec;
BEGIN
   FOR rec IN c_vec LOOP
      IF rec.similarity > 0.85 THEN
         DBMS_OUTPUT.PUT_LINE('ID '||rec.id||' -> Highly Similar ('||rec.similarity||')');
      ELSIF rec.similarity > 0.70 THEN
         DBMS_OUTPUT.PUT_LINE('ID '||rec.id||' -> Moderately Similar ('||rec.similarity||')');
      ELSE
         DBMS_OUTPUT.PUT_LINE('ID '||rec.id||' -> Low Similarity ('||rec.similarity||')');
      END IF;
   END LOOP;
END;
/

Result (example):

ID 1 -> Highly Similar (0.9259)  
ID 2 -> Highly Similar (0.9523)  
ID 3 -> Low Similarity (0.5257)  

This example demonstrates how cursors can integrate with vector operations in Oracle 23ai, making it easier to process AI-driven similarity results.

Common Mistakes with Cursors

Cursors are powerful but must be used carefully. Forgetting to close an explicit cursor can consume unnecessary resources. Using SELECT INTO when multiple rows are returned will trigger ORA-01422: exact fetch returns more than requested number of rows. Overusing row-by-row cursor loops instead of set-based SQL can cause performance bottlenecks. It is recommended to use cursors when necessary, but prefer SQL operations for bulk data processing.

Conclusion

Cursors in Oracle Database 23ai PL/SQL are essential for processing multiple rows efficiently. In this tutorial, you learned about implicit cursors, explicit cursors, cursor FOR loops, parameterized cursors, JSON integration, and vector similarity examples with data preparation. By practicing these examples, you will be able to implement row-by-row logic where SQL alone is not sufficient while also taking advantage of Oracle 23ai’s advanced JSON and vector capabilities.