Notes

-- 1. PACKAGE zajecia_pkg
CREATE OR REPLACE PACKAGE zajecia_pkg AS
-- Funkcje
FUNCTION get_job_title(p_job_id VARCHAR2) RETURN VARCHAR2;
FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER;
FUNCTION get_area_code(p_phone_number VARCHAR2) RETURN VARCHAR2;
FUNCTION format_string(p_input VARCHAR2) RETURN VARCHAR2;
FUNCTION pesel_to_date(p_pesel VARCHAR2) RETURN DATE;
FUNCTION get_country_stats(p_country_name VARCHAR2) RETURN VARCHAR2;
FUNCTION generuj_identyfikator(
p_nazwisko IN VARCHAR2,
p_telefon IN VARCHAR2,
p_imie IN VARCHAR2
) RETURN VARCHAR2;

-- Procedury
PROCEDURE add_job(
p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE
);
PROCEDURE update_job_title(
p_job_id IN jobs.job_id%TYPE,
p_new_title IN jobs.job_title%TYPE
);
PROCEDURE delete_job(
p_job_id IN jobs.job_id%TYPE
);
PROCEDURE get_employee_info(
p_employee_id IN employees.employee_id%TYPE,
o_salary OUT employees.salary%TYPE,
o_last_name OUT employees.last_name%TYPE
);
PROCEDURE insert_employee(
p_first_name IN employees.first_name%TYPE DEFAULT 'tomasz',
p_last_name IN employees.last_name%TYPE DEFAULT 'szamot',
p_email IN employees.email%TYPE DEFAULT '[email protected]',
p_phone_number IN employees.phone_number%TYPE DEFAULT NULL,
p_hire_date IN employees.hire_date%TYPE DEFAULT SYSDATE,
p_job_id IN employees.job_id%TYPE DEFAULT 'IT_PROG',
p_salary IN employees.salary%TYPE DEFAULT 10000,
p_commission_pct IN employees.commission_pct%TYPE DEFAULT NULL,
p_manager_id IN employees.manager_id%TYPE DEFAULT NULL,
p_department_id IN employees.department_id%TYPE DEFAULT NULL
);
END zajecia_pkg;
/
CREATE OR REPLACE PACKAGE BODY zajecia_pkg AS

-- 1. get_job_title
FUNCTION get_job_title(p_job_id VARCHAR2) RETURN VARCHAR2 IS
v_job_title jobs.job_title%TYPE;
BEGIN
SELECT job_title
INTO v_job_title
FROM jobs
WHERE job_id = p_job_id;
RETURN v_job_title;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001, 'No job found with id: ' || p_job_id);
END;

-- 2. get_annual_salary
FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER IS
v_salary employees.salary%TYPE;
v_commission employees.commission_pct%TYPE;
v_annual NUMBER;
BEGIN
SELECT salary, NVL(commission_pct,0)
INTO v_salary, v_commission
FROM employees
WHERE employee_id = p_emp_id;
v_annual := (v_salary * 12) + (v_salary * v_commission * 12);
RETURN v_annual;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20002, 'No employee with id: ' || p_emp_id);
END;

-- 3. get_area_code
FUNCTION get_area_code(p_phone_number VARCHAR2) RETURN VARCHAR2 IS
v_code VARCHAR2(20);
BEGIN
v_code := REGEXP_SUBSTR(p_phone_number, '+d+');
IF v_code IS NULL THEN
RETURN '(brak kodu)';
ELSE
RETURN '(' || v_code || ')';
END IF;
END;

-- 4. format_string
FUNCTION format_string(p_input VARCHAR2) RETURN VARCHAR2 IS
v_len PLS_INTEGER := LENGTH(p_input);
v_res VARCHAR2(4000);
BEGIN
IF v_len = 0 THEN
RETURN '';
ELSIF v_len = 1 THEN
RETURN UPPER(p_input);
ELSIF v_len = 2 THEN
RETURN UPPER(p_input);
ELSE
v_res := UPPER(SUBSTR(p_input,1,1))
|| LOWER(SUBSTR(p_input,2,v_len-2))
|| UPPER(SUBSTR(p_input,v_len,1));
RETURN v_res;
END IF;
END;

-- 5. pesel_to_date
FUNCTION pesel_to_date(p_pesel VARCHAR2) RETURN DATE IS
v_year NUMBER := TO_NUMBER(SUBSTR(p_pesel,1,2));
v_month NUMBER := TO_NUMBER(SUBSTR(p_pesel,3,2));
v_day NUMBER := TO_NUMBER(SUBSTR(p_pesel,5,2));
v_full_year NUMBER;
BEGIN
IF v_month BETWEEN 1 AND 12 THEN
v_full_year := 1900 + v_year;
ELSIF v_month BETWEEN 21 AND 32 THEN
v_full_year := 2000 + v_year; v_month := v_month - 20;
ELSIF v_month BETWEEN 41 AND 52 THEN
v_full_year := 2100 + v_year; v_month := v_month - 40;
ELSIF v_month BETWEEN 61 AND 72 THEN
v_full_year := 2200 + v_year; v_month := v_month - 60;
ELSIF v_month BETWEEN 81 AND 92 THEN
v_full_year := 1800 + v_year; v_month := v_month - 80;
ELSE
RAISE_APPLICATION_ERROR(-20003,'Wrong month in PESEL: '||v_month);
END IF;
RETURN TO_DATE(v_full_year||'-'
||LPAD(v_month,2,'0')||'-'
||LPAD(v_day,2,'0'),
'YYYY-MM-DD');
END;

-- 6. get_country_stats
FUNCTION get_country_stats(p_country_name VARCHAR2) RETURN VARCHAR2 IS
v_country_id countries.country_id%TYPE;
v_dept_cnt NUMBER;
v_emp_cnt NUMBER;
BEGIN
SELECT country_id INTO v_country_id
FROM countries
WHERE country_name = p_country_name;
SELECT COUNT(*) INTO v_dept_cnt
FROM departments d
JOIN locations l ON d.location_id = l.location_id
WHERE l.country_id = v_country_id;
SELECT COUNT(*) INTO v_emp_cnt
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.country_id = v_country_id;
RETURN 'Employees: '||v_emp_cnt||', Departments: '||v_dept_cnt;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20004,'Country not found: '||p_country_name);
END;

-- 7. generuj_identyfikator
FUNCTION generuj_identyfikator(
p_nazwisko IN VARCHAR2,
p_telefon IN VARCHAR2,
p_imie IN VARCHAR2
) RETURN VARCHAR2 IS
v_naz VARCHAR2(3) := UPPER(SUBSTR(p_nazwisko,1,3));
v_tel VARCHAR2(4) := SUBSTR(p_telefon,-4);
v_init VARCHAR2(1) := UPPER(SUBSTR(p_imie,1,1));
BEGIN
RETURN v_naz||v_tel||v_init;
END;

-- Procedury CRUD na tabeli JOBS / EMPLOYEES
PROCEDURE add_job(
p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE
) IS
BEGIN
INSERT INTO jobs(job_id, job_title)
VALUES(p_job_id, p_job_title);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Dodano JOB: '||p_job_id);
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Err: '||SQLERRM);
END;

PROCEDURE update_job_title(
p_job_id IN jobs.job_id%TYPE,
p_new_title IN jobs.job_title%TYPE
) IS
e_no_row_updated EXCEPTION;
BEGIN
UPDATE jobs SET job_title = p_new_title
WHERE job_id = p_job_id;
IF SQL%ROWCOUNT=0 THEN RAISE e_no_row_updated; END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Zakt JOB: '||p_job_id);
EXCEPTION
WHEN e_no_row_updated THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Brak JOB_ID: '||p_job_id);
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Err: '||SQLERRM);
END;

PROCEDURE delete_job(p_job_id IN jobs.job_id%TYPE) IS
e_no_row_deleted EXCEPTION;
BEGIN
DELETE FROM jobs WHERE job_id = p_job_id;
IF SQL%ROWCOUNT=0 THEN RAISE e_no_row_deleted; END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Usunięto JOB: '||p_job_id);
EXCEPTION
WHEN e_no_row_deleted THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Brak JOB_ID: '||p_job_id);
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Err: '||SQLERRM);
END;

PROCEDURE get_employee_info(
p_employee_id IN employees.employee_id%TYPE,
o_salary OUT employees.salary%TYPE,
o_last_name OUT employees.last_name%TYPE
) IS
BEGIN
SELECT salary, last_name
INTO o_salary, o_last_name
FROM employees
WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE('Pobrano dane prac.: '||p_employee_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
o_salary := NULL;
o_last_name := NULL;
DBMS_OUTPUT.PUT_LINE('Brak pracownika: '||p_employee_id);
WHEN OTHERS THEN
o_salary := NULL;
o_last_name := NULL;
DBMS_OUTPUT.PUT_LINE('Err: '||SQLERRM);
END;

PROCEDURE insert_employee(
p_first_name IN employees.first_name%TYPE,
p_last_name IN employees.last_name%TYPE,
p_email IN employees.email%TYPE,
p_phone_number IN employees.phone_number%TYPE,
p_hire_date IN employees.hire_date%TYPE,
p_job_id IN employees.job_id%TYPE,
p_salary IN employees.salary%TYPE,
p_commission_pct IN employees.commission_pct%TYPE,
p_manager_id IN employees.manager_id%TYPE,
p_department_id IN employees.department_id%TYPE
) IS
e_salary_high EXCEPTION;
BEGIN
IF p_salary > 20000 THEN RAISE e_salary_high; END IF;
INSERT INTO employees(
employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id
) VALUES(
employees_seq.NEXTVAL,
p_first_name, p_last_name, p_email, p_phone_number,
p_hire_date, p_job_id, p_salary, p_commission_pct, p_manager_id, p_department_id
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Dodano pracownika');
EXCEPTION
WHEN e_salary_high THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Err: salary > 20000');
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Err: '||SQLERRM);
END;

END zajecia_pkg;
/

SELECT zajecia_pkg.get_job_title('AC_MGR') AS job_title FROM dual;

-- 1.2. get_annual_salary
SELECT zajecia_pkg.get_annual_salary(120) AS annual_salary FROM dual;

-- 1.3. get_area_code
SELECT zajecia_pkg.get_area_code('+48 333123123') AS area_code FROM dual;

-- 1.4. format_string
SELECT zajecia_pkg.format_string('SEBAStiAn') AS formatted FROM dual;

-- 1.5. pesel_to_date
SELECT zajecia_pkg.pesel_to_date('03210812444') AS birth_date FROM dual;

-- 1.6. get_country_stats
SELECT zajecia_pkg.get_country_stats('Mexico') AS stats_mexico FROM dual;

-- 1.7. generuj_identyfikator
SELECT zajecia_pkg.generuj_identyfikator('Kowalski','123456789','Jan') AS iden FROM dual;

SET SERVEROUTPUT ON;
BEGIN
-- 2.1. add_job
zajecia_pkg.add_job('J1000','Specjalista PL/SQL');

-- 2.2. update_job_title
zajecia_pkg.update_job_title('J1000','Senior Specjalista');

-- 2.3. delete_job
zajecia_pkg.delete_job('J1000');
END;
/

-- 2.4. get_employee_info (OUT parameters)
DECLARE
v_sal employees.salary%TYPE;
v_name employees.last_name%TYPE;
BEGIN
zajecia_pkg.get_employee_info(100, v_sal, v_name);
DBMS_OUTPUT.PUT_LINE('Salary='||NVL(TO_CHAR(v_sal),'NULL')||', LastName='||NVL(v_name,'NULL'));
END;
/

-- 2.5. insert_employee
BEGIN
zajecia_pkg.insert_employee;
zajecia_pkg.insert_employee(
p_first_name => 'Anna',
p_last_name => 'Nowak',
p_email => '[email protected]',
p_phone_number => '+48 600700800',
p_hire_date => TO_DATE('2025-05-10','YYYY-MM-DD'),
p_job_id => 'IT_PROG',
p_salary => 15000,
p_commission_pct => NULL,
p_manager_id => 100,
p_department_id => 60
);
END;
/

-- 2. PACKAGE regions_pkg
CREATE OR REPLACE PACKAGE regions_pkg AS
PROCEDURE create_region(
p_region_id IN regions.region_id%TYPE,
p_region_name IN regions.region_name%TYPE
);

FUNCTION get_region_by_id(
p_region_id IN regions.region_id%TYPE
) RETURN regions%ROWTYPE;

FUNCTION get_regions_by_name(
p_pattern IN VARCHAR2
) RETURN SYS_REFCURSOR;

FUNCTION get_all_regions RETURN SYS_REFCURSOR;

PROCEDURE update_region_name(
p_region_id IN regions.region_id%TYPE,
p_new_name IN regions.region_name%TYPE
);

PROCEDURE delete_region(
p_region_id IN regions.region_id%TYPE
);
END regions_pkg;
/
CREATE OR REPLACE PACKAGE BODY regions_pkg AS

PROCEDURE create_region(
p_region_id IN regions.region_id%TYPE,
p_region_name IN regions.region_name%TYPE
) IS
BEGIN
INSERT INTO regions(region_id, region_name)
VALUES(p_region_id, p_region_name);
COMMIT;
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20010,'Region o ID='||p_region_id||' już istnieje');
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;

FUNCTION get_region_by_id(
p_region_id IN regions.region_id%TYPE
) RETURN regions%ROWTYPE IS
v_region regions%ROWTYPE;
BEGIN
SELECT * INTO v_region
FROM regions
WHERE region_id = p_region_id;
RETURN v_region;
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20011,'Brak regionu o ID='||p_region_id);
END;

FUNCTION get_regions_by_name(
p_pattern IN VARCHAR2
) RETURN SYS_REFCURSOR IS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR
SELECT *
FROM regions
WHERE LOWER(region_name) LIKE LOWER(p_pattern);
RETURN c;
END;

FUNCTION get_all_regions RETURN SYS_REFCURSOR IS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT * FROM regions ORDER BY region_id;
RETURN c;
END;

PROCEDURE update_region_name(
p_region_id IN regions.region_id%TYPE,
p_new_name IN regions.region_name%TYPE
) IS
BEGIN
UPDATE regions
SET region_name = p_new_name
WHERE region_id = p_region_id;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20012,'Nie znaleziono regionu o ID='||p_region_id);
END IF;
COMMIT;
END;

PROCEDURE delete_region(
p_region_id IN regions.region_id%TYPE
) IS
BEGIN
DELETE FROM regions
WHERE region_id = p_region_id;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20013,'Nie znaleziono regionu o ID='||p_region_id);
END IF;
COMMIT;
END;

END regions_pkg;
/

SET SERVEROUTPUT ON;
DECLARE
rec regions%ROWTYPE;
c SYS_REFCURSOR;
v_id regions.region_id%TYPE;
v_name regions.region_name%TYPE;
BEGIN
-- 3.1. create_region
regions_pkg.create_region(10,'Antarktyda');

-- 3.2. get_region_by_id
rec := regions_pkg.get_region_by_id(10);
DBMS_OUTPUT.PUT_LINE('Region[10]='||rec.region_name);

-- 3.3. get_regions_by_name (pattern)
c := regions_pkg.get_regions_by_name('%a%');
LOOP
FETCH c INTO v_id, v_name;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Pat['||v_id||']='||v_name);
END LOOP;
CLOSE c;

-- 3.4. get_all_regions
c := regions_pkg.get_all_regions;
DBMS_OUTPUT.PUT_LINE('Wszystkie regiony:');
LOOP
FETCH c INTO v_id, v_name;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id||' -> '||v_name);
END LOOP;
CLOSE c;

-- 3.5. update_region_name
regions_pkg.update_region_name(10,'Nowa Antarktyda');
DBMS_OUTPUT.PUT_LINE('Zaktualizowano region 10');

-- 3.6. delete_region
regions_pkg.delete_region(10);
DBMS_OUTPUT.PUT_LINE('Usunięto region 10');

END;
/

CREATE TABLE region_audit (
audit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
region_id NUMBER,
action_name VARCHAR2(30),
err_code NUMBER,
err_msg VARCHAR2(4000),
log_date DATE DEFAULT SYSDATE
);

ALTER TABLE regions
ADD CONSTRAINT regions_name_uk UNIQUE(region_name);

CREATE OR REPLACE PACKAGE regions_pkg AS
e_name_exists EXCEPTION;
e_delete_with_countries EXCEPTION;

PROCEDURE log_error(
p_region_id IN regions.region_id%TYPE,
p_action IN VARCHAR2,
p_err_code IN NUMBER,
p_err_msg IN VARCHAR2
);

PROCEDURE create_region(
p_region_id IN regions.region_id%TYPE,
p_region_name IN regions.region_name%TYPE
);
FUNCTION get_region_by_id(
p_region_id IN regions.region_id%TYPE
) RETURN regions%ROWTYPE;
FUNCTION get_regions_by_name(
p_pattern IN VARCHAR2
) RETURN SYS_REFCURSOR;
FUNCTION get_all_regions RETURN SYS_REFCURSOR;
PROCEDURE update_region_name(
p_region_id IN regions.region_id%TYPE,
p_new_name IN regions.region_name%TYPE
);
PROCEDURE delete_region(
p_region_id IN regions.region_id%TYPE
);
END regions_pkg;
/

CREATE OR REPLACE PACKAGE BODY regions_pkg AS
PROCEDURE log_error(
p_region_id IN regions.region_id%TYPE,
p_action IN VARCHAR2,
p_err_code IN NUMBER,
p_err_msg IN VARCHAR2
) IS
BEGIN
INSERT INTO region_audit(region_id, action_name, err_code, err_msg)
VALUES(p_region_id, p_action, p_err_code, p_err_msg);
COMMIT;
EXCEPTION WHEN OTHERS THEN NULL;
END log_error;

PROCEDURE create_region(
p_region_id IN regions.region_id%TYPE,
p_region_name IN regions.region_name%TYPE
) IS v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM regions WHERE LOWER(region_name)=LOWER(p_region_name);
IF v_count>0 THEN RAISE e_name_exists; END IF;
INSERT INTO regions(region_id,region_name) VALUES(p_region_id,p_region_name);
COMMIT;
EXCEPTION
WHEN e_name_exists THEN
log_error(p_region_id,'CREATE',SQLCODE,'Region name exists: '||p_region_name);
RAISE_APPLICATION_ERROR(-20020,'Region o nazwie '||p_region_name||' już istnieje.');
WHEN DUP_VAL_ON_INDEX THEN
log_error(p_region_id,'CREATE',SQLCODE,SQLERRM);
RAISE_APPLICATION_ERROR(-20021,'Duplikat klucza: '||SQLERRM);
WHEN OTHERS THEN
log_error(p_region_id,'CREATE',SQLCODE,SQLERRM); ROLLBACK; RAISE;
END create_region;

FUNCTION get_region_by_id(p_region_id IN regions.region_id%TYPE)
RETURN regions%ROWTYPE IS v_region regions%ROWTYPE;
BEGIN
SELECT * INTO v_region FROM regions WHERE region_id=p_region_id;
RETURN v_region;
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20011,'Brak regionu o ID='||p_region_id);
END get_region_by_id;

FUNCTION get_regions_by_name(p_pattern IN VARCHAR2) RETURN SYS_REFCURSOR IS c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT * FROM regions WHERE LOWER(region_name) LIKE LOWER(p_pattern);
RETURN c;
END get_regions_by_name;

FUNCTION get_all_regions RETURN SYS_REFCURSOR IS c SYS_REFCURSOR;
BEGIN OPEN c FOR SELECT * FROM regions ORDER BY region_id; RETURN c; END;

PROCEDURE update_region_name(p_region_id IN regions.region_id%TYPE,
p_new_name IN regions.region_name%TYPE) IS
BEGIN
UPDATE regions SET region_name=p_new_name WHERE region_id=p_region_id;
IF SQL%ROWCOUNT=0 THEN RAISE_APPLICATION_ERROR(-20012,'Nie znaleziono regionu o ID='||p_region_id); END IF;
COMMIT;
EXCEPTION WHEN OTHERS THEN log_error(p_region_id,'UPDATE',SQLCODE,SQLERRM); ROLLBACK; RAISE;
END update_region_name;

PROCEDURE delete_region(p_region_id IN regions.region_id%TYPE) IS v_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_cnt FROM countries WHERE region_id=p_region_id;
IF v_cnt>0 THEN RAISE e_delete_with_countries; END IF;
DELETE FROM regions WHERE region_id=p_region_id;
IF SQL%ROWCOUNT=0 THEN RAISE_APPLICATION_ERROR(-20013,'Nie znaleziono regionu o ID='||p_region_id); END IF;
COMMIT;
EXCEPTION
WHEN e_delete_with_countries THEN
log_error(p_region_id,'DELETE',SQLCODE,'Posiada przypisane kraje');
RAISE_APPLICATION_ERROR(-20022,'Nie można usunąć regionu, posiada przypisane kraje.');
WHEN OTHERS THEN log_error(p_region_id,'DELETE',SQLCODE,SQLERRM); ROLLBACK; RAISE;
END delete_region;
END regions_pkg;
/

SET SERVEROUTPUT ON;
BEGIN
-- 4.1.
regions_pkg.create_region(99, 'TestRegion');
DBMS_OUTPUT.PUT_LINE('Utworzono region 99.');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Błąd CREATE: ' || SQLERRM);
END;
/

BEGIN
-- 4.2
regions_pkg.create_region(100, 'TestRegion');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Oczekiwany błąd DUPLICATE NAME: ' || SQLERRM);
END;
/

BEGIN
-- 4.3.
regions_pkg.delete_region(1);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Oczekiwany błąd DELETE WITH COUNTRIES: ' || SQLERRM);
END;
/

BEGIN
-- 4.4.
regions_pkg.delete_region(99);
DBMS_OUTPUT.PUT_LINE('Usunięto region 99.');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Błąd DELETE: ' || SQLERRM);
END;
/

-- 4.5.
SELECT audit_id, region_id, action_name, err_code, err_msg, log_date
FROM region_audit
ORDER BY log_date DESC;

/