Appendix A: Cursors and Logging Examples
A.1 Example person table definition
CREATE TABLE person (
person_id NUMBER GENERATED ALWAYS AS IDENTITY
CONSTRAINT person_pk PRIMARY KEY,
first_name VARCHAR2(30),
last_name VARCHAR2(50),
birth_dt DATE,
birth_year VARCHAR2(4),
tax_year varchar2(4),
created_dt DATE DEFAULT SYSDATE,
created_by VARCHAR2(100)
DEFAULT SYSCONTEXT('USERENV','SESSION_USER'),
mod_by VARCHAR2(100)
DEFAULT ON NULL SYSCONTEXT('USERENV','SESSION_USER'),
mod_dt DATE DEFAULT SYSDATE
) COMPRESS;
COMMENT ON TABLE person IS
'PERSON: a simple table with enforced primary key and change tracking';
COMMENT ON COLUMN person.person_id IS
'Enforced primary key generated by identity column';
COMMENT ON COLUMN person.created_by IS
'User who created the row, captured automatically';
COMMENT ON COLUMN person.mod_by IS
'User who last modified the row, captured automatically';
A.2 One possible SQL to query this table would be below
SELECT
FIRST_NAME, LAST_NAME, BIRTH_DATE, SYSDATE – BIRTHDATE AS AGE;
A.3 The view would be as shown and it is needed to define the cursor next
-- The v_persons view provides a simplified logical
-- representation of person data, including a derived
-- age column. Using a view allows PL/SQL code and SQL
-- tools to reference consistent query logic without
-- repeating the underlying SELECT statement.
CREATE OR REPLACE VIEW v_persons AS
SELECT first_name,
last_name,
birth_dt,
tax_birth_year,
TRUNC(MONTHS_BETWEEN(SYSDATE, birth_dt) / 12)
AS age
FROM person;
A.4 creating a cursor on the view v_persons (defined above in 2.3)
DECLARE
-- Cursor selecting first name, last name, and age
-- from the v_persons view. The cursor stores the
-- query definition; data is retrieved later when
-- the FOR loop iterates through each result row.
CURSOR persons_list IS
SELECT first_name, last_name, tax_birth_year , age
FROM v_persons;
BEGIN
-- Implicit cursor loop. Oracle automatically:
-- * opens the cursor
-- * fetches each row
-- * detects end of result set
-- * closes the cursor
-- This removes the need for explicit OPEN, FETCH,
-- and CLOSE statements.
FOR row IN persons_list LOOP
-- DBMS_OUTPUT.PUT_LINE writes formatted text to
-- the SQL*Plus or SQL Developer output buffer.
-- This is useful during debugging or teaching
-- examples but should not be used as a primary
-- logging strategy in production systems.
DBMS_OUTPUT.PUT_LINE(
'Name: ' || row.first_name ||
' ' || row.last_name ||
', Age: ' || row.age
);
END LOOP;
END;
A.5 old style cursors
DECLARE
-- Explicit cursor selecting person_id and last_name
-- from the PERSON table. This cursor is opened,
-- fetched, and closed manually, demonstrating
-- full cursor control for teaching purposes.
CURSOR c_person IS
SELECT person_id, last_name
FROM person;
-- Variable to store fetched person_id from the
-- current cursor row.
v_person_id person.person_id%TYPE;
-- Variable to store fetched last_name from the
-- current cursor row.
v_last_name person.last_name%TYPE;
BEGIN
-- Open the cursor to begin reading data. This step
-- allocates resources and prepares Oracle to
-- fetch rows sequentially.
OPEN c_person;
LOOP
-- Fetch the next row into local variables.
-- If no rows remain, NOTFOUND becomes TRUE.
FETCH c_person
INTO v_person_id, v_last_name;
-- Exit the loop once the cursor has no more
-- rows to return.
EXIT WHEN c_person%NOTFOUND;
-- Print values to the output buffer. This is
-- useful for debugging or demonstration but
-- should not be used as a production logging
-- method.
DBMS_OUTPUT.PUT_LINE(
'Person ID: ' || v_person_id ||
', Last Name: ' || v_last_name
);
END LOOP;
-- Close the cursor after finishing. This releases
-- all resources associated with the cursor.
CLOSE c_person;
END;
A.6 Better style cursor, bulk collect
DECLARE
-- Record type based on the row structure of the
-- v_persons view. Each record will contain all
-- columns returned by the view.
TYPE persons_rec_t IS v_persons%ROWTYPE;
-- Collection type representing a table (array) of
-- v_persons records. Used to hold batches of rows
-- fetched with BULK COLLECT.
TYPE persons_tab_t IS TABLE OF persons_rec_t;
-- Collection variable for storing fetched batches
-- of rows from the cursor.
l_persons persons_tab_t;
-- Variable used to store the calculated birth year
-- derived from the birth_dt column.
v_birth_year VARCHAR2(4);
-- Cursor that selects all rows and columns from the
-- v_persons view. BULK COLLECT will fetch batches
-- of rows into a PL/SQL collection.
CURSOR persons_cur IS
SELECT *
FROM v_persons;
BEGIN
-- Open the cursor to begin fetching rows.
OPEN persons_cur;
LOOP
-- BULK COLLECT retrieves up to 100 rows at once
-- and stores them in the l_persons collection.
-- This reduces context switching between SQL
-- and PL/SQL and improves performance.
FETCH persons_cur
BULK COLLECT INTO l_persons
LIMIT 100;
-- Exit the loop when no rows remain. BULK
-- COLLECT returns an empty collection when
-- the cursor is exhausted.
EXIT WHEN l_persons.COUNT = 0;
-- Loop through each element of the collection.
-- The index i refers to an individual row from
-- the cursor batch.
FOR i IN 1 .. l_persons.COUNT LOOP
-- Convert birth_dt to a four-digit year and
-- store it for printing.
v_birth_year := TO_CHAR(
l_persons(i).birth_dt,
'YYYY'
);
-- Output details for the current record.
-- Used for debugging or instructional
-- purposes, not production logging.
DBMS_OUTPUT.PUT_LINE(
'FIRST_NAME: ' || l_persons(i).first_name ||
', LAST_NAME: ' || l_persons(i).last_name ||
', BIRTH_DT: ' ||
TO_CHAR(l_persons(i).birth_dt, 'YYYY-MM-DD') ||
', AGE (days): ' ||
TO_CHAR(l_persons(i).age) ||
', BIRTH_YEAR: ' || v_birth_year
);
END LOOP;
END LOOP;
-- Close the cursor and release resources.
CLOSE persons_cur;
END;
A.7 Better style cursor, bulk collect with Limit and FORALL
This example demonstrates how to combine BULK COLLECT and FORALL to efficiently update many rows in a table, and it also shows that the same batch of data can be reused for more than one bulk operation. Rows are fetched in batches of up to 100 from the v_persons view into a PL/SQL collection. For each batch, the code computes two derived values: birth_year and tax_birth_year. Both are calculated in memory from the birth_dt column.
The birth_year value is stored in one collection and written to the birth_year column in the person table using a FORALL update. The tax_birth_year value is stored in a second parallel collection and written to a separate tax_birth_year column using a second FORALL. This illustrates that once a batch of rows has been fetched into a collection, you can perform multiple bulk updates driven by the same collection without refetching the data.
This pattern is common in ETL and data transformation code, where a single source dataset may feed multiple derived columns or even multiple target tables. The key idea is to fetch once, transform in memory, and then apply one or more set-based FORALL operations for maximum performance.
Note: This example assumes the person table has an additional
column tax_birth_year VARCHAR2(4) added for demonstration.
DECLARE
-- Collection type representing rows returned by the
-- v_persons view. Each element contains one full
-- record of person data.
TYPE persons_tab_t IS TABLE OF v_persons%ROWTYPE;
-- Collection to hold batches of rows fetched from
-- the cursor.
l_persons persons_tab_t;
-- Collection type used to store computed birth_year
-- values for each fetched row.
TYPE birth_years_t IS TABLE OF VARCHAR2(4);
-- Collection type used to store computed tax birth
-- year values for each fetched row.
TYPE tax_birth_years_t IS TABLE OF VARCHAR2(4);
-- Parallel collection holding derived birth_year
-- values.
l_birth_years birth_years_t;
-- Parallel collection holding derived tax_birth_year
-- values. This might represent a fiscal or tax
-- year derived from the birth date.
l_tax_birth_years tax_birth_years_t;
-- Cursor selecting only rows with valid birth_dt
-- values from the v_persons view.
CURSOR persons_cur IS
SELECT *
FROM v_persons
WHERE birth_dt IS NOT NULL;
BEGIN
-- Open the cursor to begin batch retrieval.
OPEN persons_cur;
LOOP
-- Fetch a batch of up to 100 rows into the
-- l_persons collection. BULK COLLECT reduces
-- context switching between SQL and PL/SQL.
FETCH persons_cur
BULK COLLECT INTO l_persons
LIMIT 100;
-- Exit the loop when no rows remain.
EXIT WHEN l_persons.COUNT = 0;
-- Initialize the collections used to store the
-- derived birth_year and tax_birth_year values.
l_birth_years := birth_years_t();
l_tax_birth_years := tax_birth_years_t();
-- Compute both birth_year and tax_birth_year for
-- each fetched row. Here tax_birth_year is shown
-- as an example using a simple formula; in a real
-- system this might be a business rule.
FOR i IN 1 .. l_persons.COUNT LOOP
l_birth_years.EXTEND;
l_tax_birth_years.EXTEND;
-- Standard calendar birth year.
l_birth_years(i) :=
TO_CHAR(l_persons(i).birth_dt, 'YYYY');
-- Example tax birth year rule: shift six months
-- forward before taking the year. This is only
-- for demonstration of a derived formula.
l_tax_birth_years(i) :=
TO_CHAR(
ADD_MONTHS(l_persons(i).birth_dt, 6),
'YYYY'
);
END LOOP;
-- First FORALL: update the birth_year column using
-- values from the l_birth_years collection.
FORALL i IN 1 .. l_persons.COUNT
UPDATE person
SET birth_year = l_birth_years(i)
WHERE person_id = l_persons(i).person_id;
-- Second FORALL: reuse the same driver collection
-- l_persons and update the tax_birth_year column
-- using values from l_tax_birth_years. This shows
-- that the same batch can drive multiple bulk
-- operations without refetching the data.
FORALL i IN 1 .. l_persons.COUNT
UPDATE person
SET tax_birth_year = l_tax_birth_years(i)
WHERE person_id = l_persons(i).person_id;
-- Commit each batch to control undo usage and to
-- make incremental progress on large data sets.
COMMIT;
END LOOP;
-- Close the cursor after all batches are processed.
CLOSE persons_cur;
END;
A.8 How to monitor memory consumption with cursors
There are 3 primary values which show memory consumption:
---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
A.9 How to monitor memory consumption using PGA memory size
When using BULK COLLECT, every fetched row is stored in the session’s Program Global Area (PGA), which is the private memory allocated to your PL/SQL process. Large collections can consume significant PGA, especially when rows contain many columns or when multiple collections are used at once. The LIMIT clause provides direct control over how much data is fetched into memory at a time. A smaller LIMIT reduces PGA consumption but increases the number of fetch cycles; a larger LIMIT improves performance by reducing context switches but consumes more PGA. Choosing an appropriate LIMIT value—commonly between 100 and 1,000 rows—balances performance and memory usage, ensuring that batch processing remains efficient without risking excessive PGA growth or hitting session memory limits.
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
When tuning the LIMIT value, you can use a simple query against PGA statistics to see how much memory your session is consuming during bulk operations. For example, a query on V$PGASTAT that reports total PGA allocated and maximum PGA allocated before and during the BULK COLLECT loop will show how each batch size affects PGA usage. If you test your code with different LIMIT values (for example, 100, 500, 1,000) and observe that higher LIMITs push the allocated PGA close to your session or system targets, that’s a sign the LIMIT is too large. Conversely, if PGA remains comfortably below your thresholds and performance improves, you can safely increase the LIMIT. In practice, you choose a LIMIT that keeps the PGA growth from this SQL within an acceptable range while still reducing the number of fetch cycles enough to gain good performance.
A.10 Collection exceptions across multiple rows
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 have exceptions which trigger immediately but they also have a process to capture exceptions as they loop through the data, since that is what they are mostly used for. Example 2.10 shows the types of exceptions which are available and details how to use the “save exceptions” clause.
A.11 cursor exceptions, don’t kill me now, report later
This example demonstrates how to use FORALL with the SAVE EXCEPTIONS clause to perform a bulk insert while still capturing row-level errors. A PL/SQL collection is populated with several test records, some of which intentionally violate table constraints—for example, a NULL value in a NOT NULL column and a duplicate primary key. The FORALL statement attempts to insert all rows in one bulk operation, and when individual rows fail, Oracle records the errors in the SQL%BULK_EXCEPTIONS array instead of stopping the entire operation. After the bulk insert completes, the exception handler reports how many rows failed as well as the index, Oracle error code, and message for each failed record. This pattern allows high-performance bulk DML while still providing detailed diagnostics for invalid data.
DECLARE
-- Record type matching the PEOPLE table
TYPE person_rec IS people%ROWTYPE;
-- Collection type of person_rec records used for
-- BULK operations.
TYPE person_tab IS TABLE OF person_rec;
-- Collection instance to hold sample rows.
people person_tab := person_tab();
-- Exception to capture DML errors raised during
-- SAVE EXCEPTIONS in FORALL.
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
-- Populate the collection with test rows. These are
-- designed to cause constraint violations so that
-- SQL%BULK_EXCEPTIONS can be demonstrated.
people.EXTEND(4);
people(1) := person_rec(1, 'Alice', 30);
people(2) := person_rec(2, 'Bob', NULL);
-- age is NULL here; assume AGE is defined as NOT NULL
-- in the PEOPLE table, so this row will fail.
people(3) := person_rec(1, 'Charlie', 25);
-- duplicate ID 1; assume PERSON_ID is unique or a
-- primary key, so this row will fail.
people(4) := person_rec(3, 'Diana', 28);
-- Perform BULK INSERT with SAVE EXCEPTIONS so that
-- valid rows are inserted and invalid rows are
-- reported via SQL%BULK_EXCEPTIONS.
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
-- Report how many DML errors occurred during the
-- FORALL operation.
DBMS_OUTPUT.PUT_LINE(
'DML Errors Count: ' ||
SQL%BULK_EXCEPTIONS.COUNT
);
-- Loop through each error and display the index of
-- the failing element and the Oracle error code.
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;
A.12 Log Manager Code
A.12.1 EVENT_LOG_OBJ Type
This object type defines the in-memory structure for a single log entry. It mirrors the columns in the EVENT_LOG table so that you can project table rows into EVENT_LOG_OBJ instances, or later use this type in pipelined functions or APIs. Each attribute captures basic metadata about the event: when it occurred, which application/module wrote it, what kind of event it was, where in the code it happened, and any error or extended diagnostic details.
CREATE OR REPLACE TYPE event_log_obj AS OBJECT (
log_id NUMBER, -- primary key
log_ts TIMESTAMP, -- event timestamp
application_name VARCHAR2(100), -- calling app
module_name VARCHAR2(100), -- module/job name
log_level VARCHAR2(20), -- INFO, ERROR, ASSERT, TEST
status VARCHAR2(20), -- START, END, OK, ERROR, PASS, FAIL
checkpoint_message VARCHAR2(4000), -- code checkpoint
message VARCHAR2(4000), -- event text
error_code NUMBER, -- SQLCODE or custom error
extra_info VARCHAR2(4000) -- JSON/params/etc.
);
/
A.12.2 EVENT_LOG Table
The EVENT_LOG table is the central repository for all logging events—normal checkpoints, errors, assertions, and test results. It is intentionally kept in sync with EVENT_LOG_OBJ so the two can be used together. Each row represents one event, with enough information to reconstruct what happened, where, and why, without impacting the caller’s transaction.
CREATE TABLE event_log (
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT event_log_pk PRIMARY KEY,
log_ts TIMESTAMP DEFAULT SYSTIMESTAMP,
application_name VARCHAR2(100),
module_name VARCHAR2(100),
log_level VARCHAR2(20),
status VARCHAR2(20),
checkpoint_message VARCHAR2(4000),
message VARCHAR2(4000),
error_code NUMBER,
extra_info VARCHAR2(4000)
);
A.12.3 LOG_MANAGER Package Specification
The LOG_MANAGER package specification defines the public API for the logging system. It separates runtime checkpoints, error logging, and test assertions into a small set of procedures that any PL/SQL code can call. At runtime you typically use CHECKPOINT_START, CHECKPOINT_END, and LOG_ERROR, while ASSERT_CHECKPOINT, TEST_START, TEST_ASSERT_EQUAL, and TEST_ASSERT_TRUE support richer diagnostics and test-driven development patterns.
CREATE OR REPLACE PACKAGE log_manager AS
PROCEDURE checkpoint_start( -- mark start of a unit of work
p_application IN VARCHAR2, -- calling app name
p_module IN VARCHAR2 -- module/job name
);
PROCEDURE checkpoint_end( -- mark end of a unit of work
p_status IN VARCHAR2 -- OK, ERROR, WARN, etc.
);
PROCEDURE log_error( -- log error with last checkpoint
p_error_code IN NUMBER, -- SQLCODE or custom code
p_checkpoint_message IN VARCHAR2 -- code position marker
);
PROCEDURE assert_checkpoint( -- non-fatal assertion checkpoint
p_checkpoint_message IN VARCHAR2, -- code position / description
p_extra_info IN VARCHAR2 DEFAULT NULL -- optional details
);
PROCEDURE test_start( -- mark start of a test case
p_test_name IN VARCHAR2 -- logical test name
);
PROCEDURE test_assert_equal( -- PASS/FAIL comparison
p_test_name IN VARCHAR2, -- test name
p_expected IN VARCHAR2, -- expected value
p_actual IN VARCHAR2, -- actual value
p_message IN VARCHAR2 DEFAULT NULL-- extra context
);
PROCEDURE test_assert_true( -- Boolean assertion
p_test_name IN VARCHAR2, -- test name
p_condition IN BOOLEAN, -- condition to check
p_message IN VARCHAR2 DEFAULT NULL-- extra context
);
END log_manager;
/
A.12.4 LOG_MANAGER Package Body
The package body implements the logging behavior. All writes go through the helper procedure WRITE_EVENT, which runs in an autonomous transaction so logging never interferes with the caller’s commit or rollback. The global variables remember the current application, module, and last checkpoint. Runtime code can record start/end markers and errors, while the test procedures record assertions as structured events in EVENT_LOG.
CREATE OR REPLACE PACKAGE BODY log_manager AS
g_application_name VARCHAR2(100); -- current application
g_module_name VARCHAR2(100); -- current module/job
g_checkpoint_message VARCHAR2(4000); -- last checkpoint marker
PROCEDURE write_event( -- internal helper: single insert
p_log_level IN VARCHAR2, -- INFO, ERROR, ASSERT, TEST
p_status IN VARCHAR2, -- START, END, OK, ERROR, PASS...
p_checkpoint_message IN VARCHAR2, -- code position marker
p_message IN VARCHAR2, -- event text
p_error_code IN NUMBER, -- error code if any
p_extra_info IN VARCHAR2 -- optional extra detail
) IS
PRAGMA AUTONOMOUS_TRANSACTION; -- log in its own transaction
BEGIN
INSERT INTO event_log(
log_ts,
application_name,
module_name,
log_level,
status,
checkpoint_message,
message,
error_code,
extra_info
) VALUES(
SYSTIMESTAMP,
g_application_name,
g_module_name,
p_log_level,
p_status,
p_checkpoint_message,
p_message,
p_error_code,
p_extra_info
);
COMMIT; -- commit only the log row
EXCEPTION
WHEN OTHERS THEN
NULL; -- logging must never raise errors
END write_event;
PROCEDURE checkpoint_start(
p_application IN VARCHAR2,
p_module IN VARCHAR2
) IS
BEGIN
g_application_name := p_application;
g_module_name := p_module;
g_checkpoint_message := NULL;
write_event('INFO','START',NULL,'checkpoint_start',NULL,NULL);
END checkpoint_start;
PROCEDURE checkpoint_end(
p_status IN VARCHAR2
) IS
BEGIN
write_event('INFO',p_status,g_checkpoint_message,'checkpoint_end',NULL,NULL);
END checkpoint_end;
PROCEDURE log_error(
p_error_code IN NUMBER,
p_checkpoint_message IN VARCHAR2
) IS
BEGIN
g_checkpoint_message := p_checkpoint_message; -- remember last position
write_event('ERROR','ERROR',g_checkpoint_message,'log_error',p_error_code,NULL);
END log_error;
PROCEDURE assert_checkpoint(
p_checkpoint_message IN VARCHAR2,
p_extra_info IN VARCHAR2 DEFAULT NULL
) IS
BEGIN
g_checkpoint_message := p_checkpoint_message;
write_event('ASSERT','CHECK',p_checkpoint_message,'assert_checkpoint',NULL,p_extra_info);
END assert_checkpoint;
PROCEDURE test_start(
p_test_name IN VARCHAR2
) IS
BEGIN
write_event('TEST','START',p_test_name,'test_start',NULL,NULL);
END test_start;
PROCEDURE test_assert_equal(
p_test_name IN VARCHAR2,
p_expected IN VARCHAR2,
p_actual IN VARCHAR2,
p_message IN VARCHAR2 DEFAULT NULL
) IS
l_status VARCHAR2(10); -- PASS or FAIL
l_msg VARCHAR2(4000); -- formatted assertion text
BEGIN
l_status := CASE WHEN p_expected = p_actual THEN 'PASS' ELSE 'FAIL' END;
l_msg := NVL(p_message,'ASSERT_EQUAL')||' (expected='||p_expected||', actual='||p_actual||')';
write_event('TEST',l_status,p_test_name,l_msg,NULL,NULL);
-- Optional strict mode:
-- IF l_status = 'FAIL' THEN
-- RAISE_APPLICATION_ERROR(-20001,'Test '||p_test_name||' failed: '||l_msg);
-- END IF;
END test_assert_equal;
PROCEDURE test_assert_true(
p_test_name IN VARCHAR2,
p_condition IN BOOLEAN,
p_message IN VARCHAR2 DEFAULT NULL
) IS
BEGIN
IF p_condition THEN
test_assert_equal(p_test_name,'TRUE','TRUE',NVL(p_message,'ASSERT_TRUE'));
ELSE
test_assert_equal(p_test_name,'TRUE','FALSE',NVL(p_message,'ASSERT_TRUE'));
END IF;
END test_assert_true;
END log_manager;
Robert Diekmann's Dev Demo Platform