2 Cursors
The single most important capability of PL/SQL is its ability to execute SQL statements and store the results—either into individual variables or into cursors that hold multiple rows—within procedures, functions, and other Oracle program units. A cursor is essentially a local, in-memory result set: a collection of rows and columns returned by a SQL query that the PL/SQL program can iterate through, examine, and manipulate.
In real systems, it is common for a cursor to represent hundreds, thousands, or even millions of rows. The practical limit is largely determined by available memory. What makes cursors powerful is that they are defined by SQL itself—the most expressive and efficient tool available for filtering, shaping, and combining relational data. You can think of a cursor as a dynamic spreadsheet: each row contains meaningful columns of data that can be examined, transformed, or used to produce new rows elsewhere in the process.
When I begin writing a PL/SQL module that will operate on a set of data, the first step is almost always crafting the SQL query that defines the cursor. In many of my projects, this query can be substantial—sometimes hundreds of lines long—because the real world is complex. Caseload lists, care-management members and their demographics, product catalogs, claim histories, or operational snapshots often require joins across numerous tables and must satisfy intricate business rules. SQL is uniquely suited to expressing those rules clearly and efficiently.
In practice, much of the effort in building high-quality PL/SQL comes down to prototyping the SQL that identifies the exact dataset the algorithm will operate on. Often the purpose of the PL/SQL program is to merge this extracted data into a new table, transform it, or enrich it with additional logic. For this reason, I frequently wrap the core SQL query inside a view. This allows me to run the query independently in a SQL prompt, experiment with filters, and verify correctness long before embedding it into PL/SQL. It also ensures the version of the query executed in the PL/SQL code and the version tested directly in SQL are always identical.
This approach cannot be overstated. Many times in my career I have inherited legacy PL/SQL systems where the SQL was deeply embedded in procedural code, making it extremely difficult to extract, visualize, or debug. Manually reconstructing a multi-hundred-line query—especially one filled with nested subqueries and conditional logic—is both error-prone and time-consuming. Using a view greatly simplifies this: it isolates the data-shaping logic, exposes it for direct inspection, and enables a far clearer understanding of what the algorithm will actually process.
In the examples that follow, I will introduce a simple PERSON view, based on a table with columns such as person_id, first_name, last_name, birth_date, and birth_year, along with typical housekeeping fields for security and documentation. This view will serve as the foundation for many of the cursor and PL/SQL techniques demonstrated throughout the chapter.
The table and matched view would be defined as example 2.2 and 2.3 in the appendex.
With the creation of the view we now have two objects stored in the database, the actual table and a predetermined way to access its data: the view. This is important because the next step will be to create the cursor. There are many types of cursors. I have outlined several in the appendix in sections 2.3.and 2.6. In short these are so at this stage I will show one of the simplest versions (Example 5). I want to emphasize that I very rarely use this format unless I am just quickly moving through the data without making any changes. Maybe just printing it out or looking for something.
All cursors exist within either a package, procedure, function, object, or anonymous program body. For this example, we will use the anonymous program body because it does not create an object of its own and thus is not usually viewed as anything other than a SQL statement that is run in the database. A more common type of cursor and the one most encountered is the fetch cursor. This cursor is a very basic cursor which while common has several issues and again I don’t recommend its use. The code snipit below shows the format. A complete example is in the appendix in section 2.5
Traditional cursor
OPEN c_person;
LOOP
--- Fetch each row into variables
FETCH c_person INTO v_person_id, v_last_name;
--- Exit loop when no more --rows
EXIT WHEN c_person%NOTFOUND;
END LOOP;
CLOSE c_person;
This is the common format of an Oracle cursor as PL/SQL was first developed in the early days. In many shops it is still wildly used. The main reason it is so common is that at one point it was the only cursor variant available since Collections really did not become viable until Oracle 9i in 2001.
A better type of cursor is a collection who’s outline is shown below.
Simplified code example of a collection
OPEN persons_cur;
LOOP
FETCH persons_cur
BULK COLLECT INTO l_persons
EXIT WHEN l_persons.COUNT = 0;
FOR i IN 1 .. l_persons.COUNT LOOP
--concatinate the line and write to the buffer
DBMS_OUTPUT.PUT_LINE(
l_persons(i).first_name || ' ' ||
l_persons(i).last_name || ', ' ||
l_persons(i).age
);
END LOOP;
END LOOP;
CLOSE persons_cur;
The full code is in the appendix. Section 2.6.
There are many things which are superior about a collection. Collections have significantly better performance, are easier to use and allow cursor data to be edited in real time within the cursor and they can be run many times if needed to traverse the same data during calculations. Collections are also indexed so rows can be accessed out of order. In this simple example the performance increase is not measurable but with a larger complex cursor with a 100k of rows it can be significant. This is because there is no context switching (PL/SQL calling SQL to do line by line fetches of rows of data) in the collection implementation. This is also why PL/SQL is faster than say python or C++ implementations since all the data being processed is addressed directly in the database PGA memory rather than being transferred over the network to a completely different engine. The SQL which defines the cursor is bulk loaded into a memory array and there can be changed by simple assignments without committing the changes each loop or even switching between the SQL and PL/SQL Oracle processes (called context switching). When the cursor is finished the FORALL bulk DML can be used to do mass update/insert or deletes significantly faster than traditional methods used in older cursors and especially externally run processes. Finally, the LIMIT clause allows balancing the size of work being done to enable much more efficient processing than possible with older cursors. Limit will be discussed in more detail in section 2.7. The advantages of the collection technology are such that I would not recommend ever using traditional cursers.
Simplified code example of a collection with limit
OPEN persons_cur;
LOOP
FETCH persons_cur
BULK COLLECT INTO l_persons
LIMIT 100;
EXIT WHEN l_persons.COUNT = 0;
--work on the data and set each value in memory
FOR i IN 1 .. l_persons.COUNT LOOP
l_birth_years.EXTEND;
l_birth_years(i) :=<<get years>>
END LOOP;
--Get the data out of the collection and into a table
-- fast.
FORALL i IN 1 .. l_persons.COUNT
UPDATE person
SET birth_year = l_birth_years(i)
WHERE person_id = l_persons(i).person_id;
COMMIT;
END LOOP;
CLOSE persons_cur;
The full code is in the appendix. Section 2.6.
shows the limit clause which complicates the cursor syntax but allows the collection to address any size collection by limiting how many rows are processed at any single time. I have had numerous instances in my career when the run-time for one of these types of collections could take an entire evening and running the database out of memory was frowned upon by all.
Unfortunately, with capability also comes some overhead. Using a collection on a potentially large set of data requires that the programmer be aware of 3 parameter settings set by the DBA.
These are
---How to get the settings for the above parameters
SELECT name, value, display_value, isdefault, issys_modifiable, ismodified
FROM v$parameter WHERE name IN ('pga_aggregate_target', 'pga_aggregate_limit', 'workarea_size_policy');
Example 9 – Collection DBA settings
There is also the need to determine what the PGA memory size is which can be done using the following SQL.
SELECT name, ROUND(value / 1024 / 1024, 2) AS value_mb
FROM v$pgastat
WHERE name IN (
'total PGA inuse',
'total PGA allocated',
'maximum PGA allocated',
'aggregate PGA target parameter',
'aggregate PGA auto target'
);
Columns of interest:
total PGA inuse: Memory actively used by sessions.
total PGA allocated: Total memory allocated (might be more than in use).
maximum PGA allocated: Peak value allocated.
aggregate PGA target parameter: The configured target (PGA_AGGREGATE_TARGET).
aggregate PGA auto target: Portion of PGA Oracle automatically manages.
Example 10 – How much PGA memory is being used
Collections exceptions. Oracle is very good at providing an exception process for handling errors. In many cases this just means that certain types of errors will bubble up an exception if something goes wrong. Collections are a bit different. They also have exceptions which trigger immediately but they also have a process to capture exceptions as they loop through the data, they are processing. Example 11 shows the types of exceptions which are available and details about the “save exceptions” clause.
The SAVE EXCEPTIONS clause allows the FORALL loop to continue processing remaining rows even if some iterations fail. After the loop finishes, you can catch and inspect all the exceptions.
Example: FORALL with SAVE EXCEPTIONS
DECLARE
Type person_rec is persons%rowtype; --- Define a record type matching the people table
TYPE person_tab IS TABLE OF person_rec; --- Define a collection of the record type
--- people person_tab := person_tab();
dml_errors EXCEPTION; --- Exception for bulk errors
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
people.EXTEND(4); --- Populate the collection
people(1) := person_rec(1, 'Alice', 30);
people(2) := person_rec(2, 'Bob', NULL); --- suppose age can't be NULL
people(3) := person_rec(1, 'Charlie', 25); --- duplicate ID (will cause error)
people(4) := person_rec(3, 'Diana', 28);
-- Perform bulk insert
FORALL i IN people.FIRST .. people.LAST SAVE EXCEPTIONS
INSERT INTO people (person_id, name, age)
VALUES (people(i).person_id, people(i).name, people(i).age);
EXCEPTION
WHEN dml_errors THEN
DBMS_OUTPUT.PUT_LINE('DML Errors Count: ' || SQL%BULK_EXCEPTIONS.COUNT);
FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Error #' || i ||
' - Index: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
', Code: ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE ||
', Message: ' || SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
What is SQL%BULK_EXCEPTIONS?
In the context of a FORALL ... SAVE EXCEPTIONS block, SQL%BULK_EXCEPTIONS is a special predefined PL/SQL collection provided by Oracle to hold details about each DML error that occurred during the FORALL.
SQL%BULK_EXCEPTIONS is a PL/SQL associative array (aka an "index-by table") that is automatically populated by Oracle only when you use FORALL ... SAVE EXCEPTIONS.
Each element in this array is a record with the following fields:
Fields (Columns) of SQL%BULK_EXCEPTIONS:
Example 11 – Collection save exceptions clause
Robert Diekmann's Dev Demo Platform