Cursors in PL/SQL are used to handle query results that return more than one row. By default, Oracle manages cursors internally when you use simple SELECT INTO statements—these are called implicit cursors. However, when you need more control over query execution, row-by-row fetching, or want to handle multiple records in a structured way, you should use explicit cursors.
In Oracle Database 23ai, explicit cursors work as they always have, but with modern enhancements like native JSON support and vector operations, they can now be applied to new use cases. This Oracle tutorial explains how to declare, open, fetch, and close explicit cursors, how to use them with parameters, and how to apply them with JSON and vector data introduced in Oracle Database 23ai.
Syntax of an Explicit Cursor in PL/SQL
An explicit cursor allows developers to fetch one row at a time or process rows in loops. Unlike implicit cursors, explicit cursors must be explicitly declared and controlled.
-- Step 1: Declare the cursor CURSOR cursor_name IS SELECT_statement; -- Step 2: Open the cursor OPEN cursor_name; -- Step 3: Fetch rows from the cursor FETCH cursor_name INTO variable_list; -- Step 4: Close the cursor CLOSE cursor_name;
- DECLARE CURSOR: Defines the cursor and assigns it a
SELECTstatement. - OPEN: Executes the SQL query and makes the result set available.
- FETCH: Retrieves one row at a time from the result set into variables.
- CLOSE: Releases memory and resources.
Explicit cursors are helpful when you need row-by-row processing rather than handling sets of data all at once.
Creating a Simple Explicit Cursor
Explicit cursors are often used when you want to process multiple rows one at a time. For example, suppose we want to display employee names and salaries from a table.
Create a sample table
CREATE TABLE cur_emp (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER(10,2)
);
INSERT INTO cur_emp VALUES (401, 'Peter', 'Parker', 5000);
INSERT INTO cur_emp VALUES (402, 'Bruce', 'Banner', 6500);
INSERT INTO cur_emp VALUES (403, 'Carol', 'Danvers', 8000);
COMMIT;
Use an explicit cursor
SET SERVEROUTPUT ON;
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary
FROM cur_emp;
v_fname VARCHAR2(50);
v_lname VARCHAR2(50);
v_salary NUMBER;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_fname, v_lname, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_fname || ' ' || v_lname || ' | Salary = ' || v_salary);
END LOOP;
CLOSE emp_cursor;
END;
/
Output:
Employee: Peter Parker | Salary = 5000 Employee: Bruce Banner | Salary = 6500 Employee: Carol Danvers | Salary = 8000
Here, the explicit cursor emp_cursor is declared for a SELECT query. Each record is fetched one at a time using a loop until %NOTFOUND becomes true.
Using Explicit Cursors with Parameters
Explicit cursors can also accept parameters, making them reusable for different conditions. For example, you can fetch employees based on a minimum salary.
SET SERVEROUTPUT ON;
DECLARE
CURSOR emp_cursor (p_min_salary NUMBER) IS
SELECT first_name, last_name, salary
FROM cur_emp
WHERE salary >= p_min_salary;
v_fname VARCHAR2(50);
v_lname VARCHAR2(50);
v_salary NUMBER;
BEGIN
OPEN emp_cursor(6000);
LOOP
FETCH emp_cursor INTO v_fname, v_lname, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_fname || ' ' || v_lname || ' | Salary = ' || v_salary);
END LOOP;
CLOSE emp_cursor;
END;
/
Output:
Employee: Bruce Banner | Salary = 6500 Employee: Carol Danvers | Salary = 8000
The cursor now acts like a parameterized query. By passing a different value (for example OPEN emp_cursor(7000)), you can reuse the cursor for different filters.
Using Cursor Attributes
Explicit cursors provide attributes that help in managing and monitoring cursor operations:
%FOUND→ ReturnsTRUEif the last fetch returned a row.%NOTFOUND→ ReturnsTRUEif the last fetch did not return a row.%ROWCOUNT→ Returns the number of rows fetched so far.%ISOPEN→ ReturnsTRUEif the cursor is open.
Example:
SET SERVEROUTPUT ON;
DECLARE
CURSOR emp_cursor IS
SELECT first_name, salary FROM cur_emp;
v_name VARCHAR2(50);
v_salary NUMBER;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Row ' || emp_cursor%ROWCOUNT ||
' | ' || v_name || ' | Salary = ' || v_salary);
END LOOP;
CLOSE emp_cursor;
END;
/
Output:
Row 1 | Peter | Salary = 5000 Row 2 | Bruce | Salary = 6500 Row 3 | Carol | Salary = 8000
This makes it easy to keep track of how many rows have been processed.
Using Explicit Cursors with JSON in Oracle Database 23ai
With Oracle Database 23ai, explicit cursors can also process JSON data stored in native JSON columns. You can iterate over JSON records just like relational rows.
Create a JSON table
CREATE TABLE cur_customers (
customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_data JSON
);
INSERT INTO cur_customers (customer_data) VALUES (
JSON('{"name": "Clark Kent", "email": "clark@dailyplanet.com", "tier": "Gold"}')
);
INSERT INTO cur_customers (customer_data) VALUES (
JSON('{"name": "Lois Lane", "email": "lois@dailyplanet.com", "tier": "Silver"}')
);
COMMIT;
Cursor over JSON data
SET SERVEROUTPUT ON;
DECLARE
CURSOR cust_cursor IS
SELECT JSON_VALUE(customer_data, '$.name') AS name,
JSON_VALUE(customer_data, '$.email') AS email,
JSON_VALUE(customer_data, '$.tier') AS tier
FROM cur_customers;
v_name VARCHAR2(100);
v_email VARCHAR2(100);
v_tier VARCHAR2(50);
BEGIN
OPEN cust_cursor;
LOOP
FETCH cust_cursor INTO v_name, v_email, v_tier;
EXIT WHEN cust_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Customer: ' || v_name ||
' | Email: ' || v_email ||
' | Tier: ' || v_tier);
END LOOP;
CLOSE cust_cursor;
END;
/
Output:
Customer: Clark Kent | Email: clark@dailyplanet.com | Tier: Gold
Customer: Lois Lane | Email: lois@dailyplanet.com | Tier: Silver
This shows how explicit cursors can be extended to process semi-structured JSON content directly using Oracle 23ai’s JSON accessors.
Using Explicit Cursors with Vector Data in Oracle Database 23ai
Oracle Database 23ai adds vector data types for AI applications. Explicit cursors can be used to fetch vector data and iterate over similarity search results.
Create a vector table
CREATE TABLE cur_docs (
doc_id NUMBER PRIMARY KEY,
content CLOB,
embedding VECTOR(4)
);
INSERT INTO cur_docs VALUES (
1,
'Oracle 23ai introduces AI vector search.',
TO_VECTOR('[0.11, 0.95, -0.40, 0.61]')
);
INSERT INTO cur_docs VALUES (
2,
'Explicit cursors in PL/SQL are useful for row-by-row processing.',
TO_VECTOR('[0.12, 0.93, -0.41, 0.63]')
);
COMMIT;
Cursor with vector similarity search
SET SERVEROUTPUT ON;
DECLARE
CURSOR doc_cursor IS
SELECT doc_id, content, embedding <-> TO_VECTOR('[0.12, 0.94, -0.40, 0.62]') AS distance
FROM cur_docs
ORDER BY embedding <-> TO_VECTOR('[0.12, 0.94, -0.40, 0.62]')
FETCH FIRST 2 ROWS ONLY;
v_id NUMBER;
v_text CLOB;
v_dist BINARY_DOUBLE;
BEGIN
OPEN doc_cursor;
LOOP
FETCH doc_cursor INTO v_id, v_text, v_dist;
EXIT WHEN doc_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Doc ID: ' || v_id || ' | Distance: ' || v_dist ||
' | Content: ' || DBMS_LOB.SUBSTR(v_text, 60));
END LOOP;
CLOSE doc_cursor;
END;
/
Sample Output:
Doc ID: 2 | Distance: 0.01 | Content: Explicit cursors in PL/SQL are useful for row-by-row processing. Doc ID: 1 | Distance: 0.02 | Content: Oracle 23ai introduces AI vector search.
This example shows how explicit cursors can be applied to modern AI-driven queries, iterating over vector similarity results.
Best Practices for Using Explicit Cursors
To make explicit cursors efficient and maintainable in Oracle Database 23ai:
- Always close cursors to free resources.
- Use parameterized cursors for reusability.
- Take advantage of cursor attributes (
%ROWCOUNT,%FOUND) for better control. - Avoid row-by-row cursor processing for bulk operations—use bulk collect when possible.
- Use explicit cursors when you need fine-grained control, not just simple queries.
Conclusion
Explicit cursors in PL/SQL allow developers to manage query results row by row, offering full control over fetching, looping, and processing. In Oracle Database 23ai, cursors become even more useful as they can be applied not just to relational data but also to JSON and vector data for AI-driven applications. By mastering explicit cursors, you gain a powerful tool for writing structured, reusable, and modern PL/SQL programs.
