--Example 6–21 CURSOR FOR LOOP REFERENCES VIRTUAL COLUMNS
BEGIN
FOR item
IN ( SELECT first_name || ' ' || last_name AS full_name,
salary * 10 AS dream_salary
FROM employees
WHERE ROWNUM <= 5
ORDER BY dream_salary DESC, last_name ASC)
LOOP
DBMS_OUTPUT.PUT_LINE (
item.full_name || ' dreams of making ' || item.dream_salary);
END LOOP;
END;
/
--Example 6–22 Subquery IN FROM Clause OF PARENT QUERY
DECLARE
CURSOR c1
IS
SELECT t1.department_id, department_name, staff
FROM departments t1,
( SELECT department_id, COUNT (*) AS staff
FROM employees
GROUP BY department_id) t2
WHERE (t1.department_id = t2.department_id) AND staff >= 5
ORDER BY staff;
BEGIN
FOR dept IN c1
LOOP
DBMS_OUTPUT.PUT_LINE (
'Department = '
|| dept.department_name
|| ', staff = '
|| dept.staff);
END LOOP;
END;
--Example 6–23 Correlated Subquery
DECLARE
CURSOR c1
IS
SELECT department_id, last_name, salary
FROM employees t
WHERE salary > (SELECT AVG (salary)
FROM employees
WHERE t.department_id = department_id)
ORDER BY department_id, last_name;
BEGIN
FOR person IN c1
LOOP
DBMS_OUTPUT.PUT_LINE (
'Making above-average salary = ' || person.last_name);
END LOOP;
END;
/
--Example 6–24 CURSOR VARIABLE Declarations
DECLARE
TYPE empcurtyp IS REF CURSOR
RETURN employees%ROWTYPE; -- strong type
TYPE genericcurtyp IS REF CURSOR; -- weak type
cursor1 empcurtyp; -- strong cursor variable
cursor2 genericcurtyp; -- weak cursor variable
my_cursor SYS_REFCURSOR; -- weak cursor variable
TYPE deptcurtyp IS REF CURSOR
RETURN departments%ROWTYPE; -- strong type
dept_cv deptcurtyp; -- strong cursor variable
BEGIN
NULL;
END;
/
--Example 6–25 CURSOR VARIABLE WITH USER-Defined RETURN TYPE
DECLARE
TYPE EmpRecTyp IS RECORD
(
employee_id NUMBER,
last_name VARCHAR2 (25),
salary NUMBER (8, 2)
);
TYPE EmpCurTyp IS REF CURSOR
RETURN EmpRecTyp;
emp_cv EmpCurTyp;
BEGIN
NULL;
END;
/
--Example 6–26 Fetching DATA WITH CURSOR Variables
DECLARE
CV SYS_REFCURSOR; -- cursor variable
v_lastname employees.last_name%TYPE; -- variable for last_name
v_jobid employees.job_id%TYPE; -- variable for job_id
query_2 VARCHAR2 (200)
:= 'SELECT * FROM employees
WHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'')
ORDER BY job_id';
v_employees employees%ROWTYPE; -- record variable row of table
BEGIN
OPEN CV FOR
SELECT last_name, job_id
FROM employees
WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
ORDER BY last_name;
LOOP -- Fetches 2 columns into variables
FETCH CV
INTO v_lastname, v_jobid;
EXIT WHEN CV%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (RPAD (v_lastname, 25, ' ') || v_jobid);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('-------------------------------------');
OPEN CV FOR query_2;
LOOP -- Fetches entire row into the v_employees record
FETCH CV INTO v_employees;
EXIT WHEN CV%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
RPAD (v_employees.last_name, 25, ' ') || v_employees.job_id);
END LOOP;
CLOSE CV;
END;
/
--Example 6–27 Fetching FROM CURSOR VARIABLE INTO Collections
DECLARE
TYPE empcurtyp IS REF CURSOR;
TYPE namelist IS TABLE OF employees.last_name%TYPE;
TYPE sallist IS TABLE OF employees.salary%TYPE;
emp_cv empcurtyp;
names namelist;
sals sallist;
BEGIN
OPEN emp_cv FOR
SELECT last_name, salary
FROM employees
WHERE job_id = 'SA_REP'
ORDER BY salary DESC;
FETCH emp_cv
BULK COLLECT INTO names, sals;
CLOSE emp_cv;
-- loop through the names and sals collections
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (
'Name = ' || names (i) || ', salary = ' || sals (i));
END LOOP;
END;
/
--Example 6–28 VARIABLE IN CURSOR VARIABLE Query—No RESULT SET CHANGE
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
CV SYS_REFCURSOR;
BEGIN
OPEN CV FOR
SELECT salary, salary * factor
FROM employees
WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor
LOOP
FETCH CV
INTO sal, sal_multiple;
EXIT WHEN CV%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);
DBMS_OUTPUT.PUT_LINE ('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
factor := factor + 1; -- Does not affect sal_multiple
END LOOP;
CLOSE CV;
END;
/
--Example 6–29 VARIABLE IN CURSOR VARIABLE Query—Result SET CHANGE
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
CV SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);
OPEN CV FOR
SELECT salary, salary * factor
FROM employees
WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor
LOOP
FETCH CV
INTO sal, sal_multiple;
EXIT WHEN CV%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
END LOOP;
factor := factor + 1;
DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);
OPEN CV FOR
SELECT salary, salary * factor
FROM employees
WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor
LOOP
FETCH CV
INTO sal, sal_multiple;
EXIT WHEN CV%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE CV;
END;
/
--Example 6–30 Querying A Collection WITH STATIC SQL
CREATE OR REPLACE PACKAGE pkg
AUTHID DEFINER
AS
TYPE rec IS RECORD
(
f1 NUMBER,
f2 VARCHAR2 (30)
);
TYPE mytab IS TABLE OF rec
INDEX BY PLS_INTEGER;
END;
DECLARE
v1 pkg.mytab; -- collection of records
v2 pkg.rec;
c1 SYS_REFCURSOR;
BEGIN
v1 (1).f1 := 1;
v1 (1).f2 := 'one';
OPEN c1 FOR SELECT * FROM TABLE (v1);
FETCH c1 INTO v2;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE (
'Values in record are ' || v2.f1 || ' and ' || v2.f2);
END;
/
--Example 6–31 PROCEDURE TO OPEN CURSOR VARIABLE FOR One QUERY
CREATE OR REPLACE PACKAGE emp_data
AUTHID DEFINER
AS
TYPE empcurtyp IS REF CURSOR
RETURN employees%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);
END emp_data;
/
CREATE OR REPLACE PACKAGE BODY emp_data
AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp)
IS
BEGIN
OPEN emp_cv FOR SELECT * FROM employees;
END open_emp_cv;
END emp_data;
/
--Example 6–32 Opening CURSOR VARIABLE FOR Chosen QUERY (Same RETURN TYPE)
CREATE OR REPLACE PACKAGE emp_data
AUTHID DEFINER
AS
TYPE empcurtyp IS REF CURSOR
RETURN employees%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT);
END emp_data;
/
CREATE OR REPLACE PACKAGE BODY emp_data
AS
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT)
IS
BEGIN
IF choice = 1
THEN
OPEN emp_cv FOR
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;
ELSIF choice = 2
THEN
OPEN emp_cv FOR
SELECT *
FROM employees
WHERE salary > 2500;
ELSIF choice = 3
THEN
OPEN emp_cv FOR
SELECT *
FROM employees
WHERE department_id = 100;
END IF;
END;
END emp_data;
/
--Example 6–33 Opening CURSOR VARIABLE FOR Chosen QUERY (Different RETURN TYPES)
CREATE OR REPLACE PACKAGE admin_data
AUTHID DEFINER
AS
TYPE gencurtyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);
END admin_data;
/
CREATE OR REPLACE PACKAGE BODY admin_data
AS
PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT)
IS
BEGIN
IF choice = 1
THEN
OPEN generic_cv FOR SELECT * FROM employees;
ELSIF choice = 2
THEN
OPEN generic_cv FOR SELECT * FROM departments;
ELSIF choice = 3
THEN
OPEN generic_cv FOR SELECT * FROM jobs;
END IF;
END;
END admin_data;
/
--END-EXEC;
/* PL/SQL anonymous block in host environment */
BEGIN
OPEN :emp_cv FOR SELECT * FROM employees;
OPEN :dept_cv FOR SELECT * FROM departments;
OPEN :loc_cv FOR SELECT * FROM locations;
END;
/
/* PL/SQL anonymous block in host environment */
BEGIN
CLOSE :emp_cv;
CLOSE :dept_cv;
CLOSE :loc_cv;
END;
/
BEGIN
OPEN :c1 FOR SELECT 1 FROM DUAL;
OPEN :c2 FOR SELECT 1 FROM DUAL;
OPEN :c3 FOR SELECT 1 FROM DUAL;
END;
/
--Example 6–35 CURSOR Expression
DECLARE
TYPE emp_cur_typ IS REF CURSOR;
emp_cur emp_cur_typ;
dept_name departments.department_name%TYPE;
emp_name employees.last_name%TYPE;
CURSOR c1
IS
SELECT department_name,
CURSOR ( SELECT e.last_name
FROM employees e
WHERE e.department_id = d.department_id
ORDER BY e.last_name)
employees
FROM departments d
WHERE department_name LIKE 'A%'
ORDER BY department_name;
BEGIN
OPEN c1;
LOOP -- Process each row of query result set
FETCH c1
INTO dept_name, emp_cur;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Department: ' || dept_name);
LOOP -- Process each row of subquery result set
FETCH emp_cur INTO emp_name;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('-- Employee: ' || emp_name);
END LOOP;
END LOOP;
CLOSE c1;
END;
/
--Example 6–36 COMMIT STATEMENT WITH COMMENT AND WRITE Clauses
DROP TABLE accounts;
CREATE TABLE accounts
(
account_id NUMBER (6),
balance NUMBER (10, 2)
);
INSERT INTO accounts (account_id, balance)
VALUES (7715, 6350.00);
INSERT INTO accounts (account_id, balance)
VALUES (7720, 5100.50);
CREATE OR REPLACE PROCEDURE transfer (from_acct NUMBER,
to_acct NUMBER,
amount NUMBER)
AUTHID CURRENT_USER
AS
BEGIN
UPDATE accounts
SET balance = balance - amount
WHERE account_id = from_acct;
UPDATE accounts
SET balance = balance + amount
WHERE account_id = to_acct;
COMMIT WRITE IMMEDIATE NOWAIT;
END;
/
--Example 6–37 ROLLBACK STATEMENT
DROP TABLE emp_name;
CREATE TABLE emp_name
AS
SELECT employee_id, last_name FROM employees;
CREATE UNIQUE INDEX empname_ix
ON emp_name (employee_id);
DROP TABLE emp_sal;
CREATE TABLE emp_sal
AS
SELECT employee_id, salary FROM employees;
CREATE UNIQUE INDEX empsal_ix
ON emp_sal (employee_id);
DROP TABLE emp_job;
CREATE TABLE emp_job
AS
SELECT employee_id, job_id FROM employees;
CREATE UNIQUE INDEX empjobid_ix
ON emp_job (employee_id);
DECLARE
emp_id NUMBER (6);
emp_lastname VARCHAR2 (25);
emp_salary NUMBER (8, 2);
emp_jobid VARCHAR2 (10);
BEGIN
SELECT employee_id,
last_name,
salary,
job_id
INTO emp_id,
emp_lastname,
emp_salary,
emp_jobid
FROM employees
WHERE employee_id = 120;
INSERT INTO emp_name (employee_id, last_name)
VALUES (emp_id, emp_lastname);
INSERT INTO emp_sal (employee_id, salary)
VALUES (emp_id, emp_salary);
INSERT INTO emp_job (employee_id, job_id)
VALUES (emp_id, emp_jobid);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE ('Inserts were rolled back');
END;
/
--Example 6–38 SAVEPOINT AND ROLLBACK STATEMENTS
DROP TABLE emp_name;
CREATE TABLE emp_name
AS
SELECT employee_id, last_name, salary FROM employees;
CREATE UNIQUE INDEX empname_ix
ON emp_name (employee_id);
DECLARE
emp_id employees.employee_id%TYPE;
emp_lastname employees.last_name%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
SELECT employee_id, last_name, salary
INTO emp_id, emp_lastname, emp_salary
FROM employees
WHERE employee_id = 120;
UPDATE emp_name
SET salary = salary * 1.1
WHERE employee_id = emp_id;
DELETE FROM emp_name
WHERE employee_id = 130;
SAVEPOINT do_insert;
INSERT INTO emp_name (employee_id, last_name, salary)
VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
ROLLBACK TO do_insert;
DBMS_OUTPUT.PUT_LINE ('Insert was rolled back');
END;
/
--Example 6–39 Reusing SAVEPOINT WITH ROLLBACK
DROP TABLE emp_name;
CREATE TABLE emp_name
AS
SELECT employee_id, last_name, salary FROM employees;
CREATE UNIQUE INDEX empname_ix
ON emp_name (employee_id);
DECLARE
emp_id employees.employee_id%TYPE;
emp_lastname employees.last_name%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
SELECT employee_id, last_name, salary
INTO emp_id, emp_lastname, emp_salary
FROM employees
WHERE employee_id = 120;
SAVEPOINT my_savepoint;
UPDATE emp_name
SET salary = salary * 1.1
WHERE employee_id = emp_id;
DELETE FROM emp_name
WHERE employee_id = 130;
SAVEPOINT my_savepoint;
INSERT INTO emp_name (employee_id, last_name, salary)
VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
ROLLBACK TO my_savepoint;
DBMS_OUTPUT.PUT_LINE ('Transaction rolled back.');
END;
/
--Example 6–40 SET TRANSACTION STATEMENT IN READ-ONLY TRANSACTION
DECLARE
daily_order_total NUMBER (12, 2);
weekly_order_total NUMBER (12, 2);
monthly_order_total NUMBER (12, 2);
BEGIN
COMMIT; -- end previous transaction
SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';
SELECT SUM (order_total)
INTO daily_order_total
FROM orders
WHERE order_date = SYSDATE;
SELECT SUM (order_total)
INTO weekly_order_total
FROM orders
WHERE order_date = SYSDATE - 7;
SELECT SUM (order_total)
INTO monthly_order_total
FROM orders
WHERE order_date = SYSDATE - 30;
COMMIT; -- ends read-only transaction
END;
/
-- Example 6–41 FOR UPDATE Cursor in CURRENT OF Clause of UPDATE Statement
DECLARE
my_emp_id NUMBER (6);
my_job_id VARCHAR2 (10);
my_sal NUMBER (8, 2);
CURSOR c1
IS
SELECT employee_id, job_id, salary
FROM employees
FOR UPDATE;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO my_emp_id, my_job_id, my_sal;
IF my_job_id = 'SA_REP'
THEN
UPDATE employees
SET salary = salary * 1.02
WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
END;
/
--Example 6–42 SELECT FOR UPDATE STATEMENT FOR Multiple TABLES
DECLARE
CURSOR c1
IS
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND job_id = 'SA_MAN'
FOR UPDATE OF salary;
BEGIN
NULL;
END;
/
--Example 6–43 FETCH WITH FOR UPDATE CURSOR AFTER COMMIT STATEMENT
DROP TABLE emp;
CREATE TABLE emp
AS
SELECT * FROM employees;
DECLARE
CURSOR c1
IS
SELECT *
FROM emp
FOR UPDATE OF salary
ORDER BY employee_id;
emp_rec emp%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec; -- fails on second iteration
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
'emp_rec.employee_id = ' || TO_CHAR (emp_rec.employee_id));
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 105;
COMMIT; -- releases locks
END LOOP;
END;
/
--Example 6–44 Simulating CURRENT OF Clause WITH ROWID Pseudocolumn
DROP TABLE emp;
CREATE TABLE emp
AS
SELECT * FROM employees;
DECLARE
CURSOR c1
IS
SELECT last_name, job_id, ROWID FROM emp; -- no FOR UPDATE clause
my_lastname employees.last_name%TYPE;
my_jobid employees.job_id%TYPE;
my_rowid UROWID;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO my_lastname, my_jobid, my_rowid;
EXIT WHEN c1%NOTFOUND;
UPDATE emp
SET salary = salary * 1.02
WHERE ROWID = my_rowid; -- simulates WHERE CURRENT OF c1
COMMIT;
END LOOP;
CLOSE c1;
END;
/
----PRAGMA AUTONOMOUS TRANSACTION-----
--Example 6–45 Declaring Autonomous FUNCTION IN PACKAGE
CREATE OR REPLACE PACKAGE emp_actions
AUTHID DEFINER
AS -- package specification
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions
AS -- package body
-- code for function raise_salary
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
new_sal NUMBER (8, 2);
BEGIN
UPDATE employees
SET salary = salary + sal_raise
WHERE employee_id = emp_id;
COMMIT;
SELECT salary
INTO new_sal
FROM employees
WHERE employee_id = emp_id;
RETURN new_sal;
END raise_salary;
END emp_actions;
--Example 6–46 Declaring Autonomous STANDALONE PROCEDURE
CREATE OR REPLACE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER)
AUTHID DEFINER
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE employees
SET salary = salary - amount
WHERE employee_id = emp_id;
COMMIT;
END lower_salary;
--Example 6–47 Declaring Autonomous PL/SQL BLOCK
DROP TABLE emp;
CREATE TABLE emp
AS
SELECT * FROM employees;
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
emp_id NUMBER (6) := 200;
amount NUMBER (6, 2) := 200;
BEGIN
UPDATE employees
SET salary = salary - amount
WHERE employee_id = emp_id;
COMMIT;
END;
/
--Example 6–48 Autonomous TRIGGER Logs INSERT STATEMENTS
DROP TABLE emp;
CREATE TABLE emp
AS
SELECT * FROM employees;
-- Log table:
DROP TABLE LOG;
CREATE TABLE LOG
(
log_id NUMBER (6),
up_date DATE,
new_sal NUMBER (8, 2),
old_sal NUMBER (8, 2)
);
-- Autonomous trigger on emp table:
CREATE OR REPLACE TRIGGER log_sal
BEFORE UPDATE OF salary
ON emp
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO LOG (log_id,
up_date,
new_sal,
old_sal)
VALUES (:old.employee_id,
SYSDATE,
:new.salary,
:old.salary);
COMMIT;
END;
/
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 115;
COMMIT;
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 116;
ROLLBACK;
-- Show that both committed and rolled-back updates
-- add rows to log table
SELECT *
FROM LOG
WHERE log_id = 115 OR log_id = 116;
--Example 6–49 Autonomous TRIGGER Uses Native Dynamic SQL FOR DDL
DROP TABLE temp;
CREATE TABLE temp
(
temp_id NUMBER (6),
up_date DATE
);
CREATE OR REPLACE TRIGGER drop_temp_table
AFTER INSERT
ON LOG
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE temp';
COMMIT;
END;
/
-- Show how trigger works
SELECT * FROM temp;
--RESULT:
--NO ROWS selected
INSERT INTO LOG (log_id,
up_date,
new_sal,
old_sal)
VALUES (999,
SYSDATE,
5000,
4500);
--1 row created.
SELECT * FROM temp;
--Example 6–50 Invoking Autonomous FUNCTION
DROP TABLE debug_output;
CREATE TABLE debug_output (MESSAGE VARCHAR2 (200));
CREATE OR REPLACE PACKAGE debugging
AUTHID DEFINER
AS
FUNCTION log_msg (msg VARCHAR2)
RETURN VARCHAR2;
END debugging;
/
CREATE OR REPLACE PACKAGE BODY debugging
AS
FUNCTION log_msg (msg VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO debug_output (MESSAGE)
VALUES (msg);
COMMIT;
RETURN msg;
END;
END debugging;
/
-- Invoke package function from query
DECLARE
my_emp_id NUMBER (6);
my_last_name VARCHAR2 (25);
my_count NUMBER;
BEGIN
my_emp_id := 120;
SELECT debugging.log_msg (last_name)
INTO my_last_name
FROM employees
WHERE employee_id = my_emp_id;
/* Even if you roll back in this scope,
the insert into 'debug_output' remains committed,
because it is part of an autonomous transaction. */
ROLLBACK;
END;
/