PU Chapter 02 Creating Procedures Scripts

Example (pu_02_01)

CREATE OR REPLACE PROCEDURE pu_01_01

(p_id IN employees.employee_id%TYPE)
IS
BEGIN
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = p_id;
END pu_02_01;

EXECUTE pu_02_01 (108)

--------------------------------------------------------------------------------------
Example (pu_02_02)

CREATE OR REPLACE PROCEDURE query_emp

(p_id IN employees.employee_id%TYPE,
p_name OUT employees.last_name%TYPE,
p_salary OUT employees.salary%TYPE,
p_comm OUT employees.commission_pct%TYPE)
IS
BEGIN
SELECT last_name, salary, commission_pct
INTO p_name, p_salary, p_comm
FROM employees
WHERE employee_id = p_id;
END query_emp;

VARIABLE g_name VARCHAR2(25)

VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER
EXECUTE query_emp(103, :g_name, :g_sal, :g_comm)
PRINT g_name
--------------------------------------------------------------------------------------
Example (pu_02_03)

CREATE OR REPLACE PROCEDURE format_phone

(p_phone_no IN OUT VARCHAR2)
IS
BEGIN
p_phone_no := '(' || SUBSTR(p_phone_no,1,3) ||
')' || SUBSTR(p_phone_no,4,3) ||
'-' || SUBSTR(p_phone_no,7);
END format_phone;

VARIABLE g_phone_no VARCHAR2(15)

BEGIN
:g_phone_no := '8006330575';
END;

PRINT g_phone_no
EXECUTE format_phone (:g_phone_no)
PRINT g_phone_no
--------------------------------------------------------------------------------------
Example (pu_02_04)

CREATE OR REPLACE PROCEDURE add_dept

(p_name IN departments.department_name%TYPE
DEFAULT 'unknown',
p_loc IN departments.location_id%TYPE
DEFAULT 1700)
IS
BEGIN
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;

BEGIN
add_dept;
add_dept ('TRAINING', 2500);
add_dept ( p_loc => 2400, p_name =>'EDUCATION');
add_dept ( p_loc => 1200) ;
END;
--------------------------------------------------------------------------------------
SELECT object_name
FROM user_objects
WHERE object_type = 'PROCEDURE'

SELECT text
FROM user_source
WHERE name='ADD_DEPT' and type='PROCEDURE'
ORDER BY line;
--------------------------------------------------------------------------------------
 PU Chapter 03 Creating Functions Scripts
Example (pu_03_01)
CREATE OR REPLACE FUNCTION get_sal
(p_id IN employees.employee_id%TYPE)
RETURN NUMBER
IS
v_salary employees.salary%TYPE :=0;
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = p_id;
RETURN v_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN (0);
END get_sal;

VARIABLE g_salary NUMBER

BEGIN
:g_salary := get_sal(102);
END;
/
PRINT g_salary

SET SERVEROUTPUT ON
EXECUTE DBMS_OUTPUT.PUT_LINE(get_sal(103))
--------------------------------------------------------------------------------------
Example (pu_03_02)
CREATE OR REPLACE FUNCTION tax
(p_value IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (p_value * 0.08);
END tax;

SELECT employee_id, last_name, salary, tax(salary)

FROM employees
WHERE tax(salary)>(SELECT MAX(tax(salary))
FROM employees WHERE department_id = 50)
ORDER BY tax(salary) DESC;

--------------------------------------------------------------------------------------
Example (pu_03_03)

CREATE OR REPLACE FUNCTION dml_call_sql(p_sal NUMBER)

RETURN NUMBER IS
BEGIN
INSERT INTO employees (employee_id, last_name, email,
hire_date, job_id, salary)
VALUES (1, 'employee 1', 'emp1@company.com',
SYSDATE, 'SA_MAN', 1000);
RETURN (p_sal + 100);
END dml_call_sql;
/
UPDATE employees SET salary = dml_call_sql(2000)
WHERE employee_id = 102;
--------------------------------------------------------------------------------------
SELECT object_name
FROM user_objects
WHERE object_type = 'FUNCTION';

SELECT text
FROM user_source
WHERE name='DML_CALL_SQL' and type='FUNCTION'
ORDER BY line;
--------------------------------------------------------------------------------------
 PU Chapter 4 Creating Packages Scripts

Example (pu_04_01)

CREATE OR REPLACE PACKAGE pu_04_01 IS

PROCEDURE pro_1
(p_comm IN NUMBER);
PROCEDURE pro_2;
END pu_04_01;
/

CREATE OR REPLACE PACKAGE BODY pu_04_01

IS
PROCEDURE pro_1
(p_comm IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE (p_comm *0.5);
END pro_1;

PROCEDURE pro_2
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('This is the second procedure');
END pro_2;
END pu_04_01;

EXECUTE pu_04_01.pro_1 (30);

EXECUTE pu_04_01.pro_2
--------------------------------------------------------------------------------------
Example (pu_04_02)

CREATE OR REPLACE PACKAGE comm_package

IS
g_comm NUMBER := 0.10;
PROCEDURE reset_comm
(p_comm IN NUMBER);
END comm_package;
/

CREATE OR REPLACE PACKAGE BODY comm_package

IS
FUNCTION validate_comm (p_comm IN NUMBER)
RETURN BOOLEAN
IS
v_max_comm NUMBER;
BEGIN
SELECT MAX(commission_pct)
INTO v_max_comm
FROM employees;
IF p_comm > v_max_comm THEN
RETURN(FALSE);
ELSE
RETURN(TRUE);
END IF;
END validate_comm;
 PROCEDURE reset_comm (p_comm IN NUMBER)
IS
BEGIN
IF validate_comm(p_comm) THEN
g_comm:=p_comm; --reset global variable
ELSE
RAISE_APPLICATION_ERROR(-20210,'Invalid commission');
END IF;
END reset_comm;

END comm_package;

EXECUTE comm_package.reset_comm(0.05);
EXECUTE DBMS_OUTPUT.PUT_LINE(comm_package.g_comm);
EXECUTE comm_package.reset_comm(15);
--------------------------------------------------------------------------------------
Example (pu_04_03)

CREATE OR REPLACE PACKAGE global_consts

IS
mile_2_kilo CONSTANT NUMBER := 1.6093;
kilo_2_mile CONSTANT NUMBER := 0.6214;
yard_2_meter CONSTANT NUMBER := 0.9144;
meter_2_yard CONSTANT NUMBER := 1.0936;
END global_consts;

SET SERVEROUTPUT ON
EXECUTE DBMS_OUTPUT.PUT_LINE('20 miles = '||20* global_consts.mile_2_kilo||' km')
--------------------------------------------------------------------------------------
Example (pu_04_04)

CREATE OR REPLACE PROCEDURE meter_to_yard

(p_meter IN NUMBER, p_yard OUT NUMBER)
IS
BEGIN
p_yard := p_meter * global_consts.meter_2_yard;
END meter_to_yard;

VARIABLE yard NUMBER

EXECUTE meter_to_yard (1, :yard)
--------------------------------------------------------------------------------------
SELECT object_name
FROM user_objects
WHERE object_type = 'PACKAGE';

SELECT text
FROM user_source
WHERE name='COMM_PACKAGE' and type= 'PACKAGE'
ORDER BY line;

SELECT object_name
FROM user_objects
WHERE object_type = 'PACKAGE BODY';

SELECT text
FROM user_source
WHERE name='COMM_PACKAGE' and type= 'PACKAGE BODY'
ORDER BY line;
--------------------------------------------------------------------------------------
 PU Chapter 05 Working with Package Scripts

Example (pu_05_01)

CREATE OR REPLACE PACKAGE pu_05_01

IS
PROCEDURE pro_1 (ab NUMBER);
PROCEDURE pro_1 (ab DATE);
END pu_05_01;
/

CREATE OR REPLACE PACKAGE BODY pu_05_01

IS
PROCEDURE pro_1 (ab NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE (ab * 3.14);
END pro_1;

PROCEDURE pro_1 (ab DATE)

IS
BEGIN
DBMS_OUTPUT.PUT_LINE (ADD_MONTHS (ab,3));
END pro_1;

END pu_05_01;

EXECUTE pu_05_01.pro_1 (40);

EXECUTE pu_05_01.pro_1(sysdate)
--------------------------------------------------------------------------------------
Example (pu_05_02)

CREATE OR REPLACE PACKAGE over_pack

IS
PROCEDURE add_dept
(p_deptno IN departments.department_id%TYPE,
p_name IN departments.department_name%TYPE
DEFAULT 'unknown',
p_loc IN departments.location_id%TYPE DEFAULT 0);
PROCEDURE add_dept
(p_name IN departments.department_name%TYPE
DEFAULT 'unknown',
p_loc IN departments.location_id%TYPE DEFAULT 0);
END over_pack;
/

CREATE OR REPLACE PACKAGE BODY over_pack

IS
PROCEDURE add_dept
(p_deptno IN departments.department_id%TYPE,
p_name IN departments.department_name%TYPE DEFAULT 'unknown',
p_loc IN departments.location_id%TYPE DEFAULT 0)
IS
BEGIN
INSERT INTO departments (department_id,
department_name, location_id)
VALUES (p_deptno, p_name, p_loc);
END add_dept;
 PROCEDURE add_dept
(p_name IN departments.department_name%TYPE DEFAULT 'unknown',
p_loc IN departments.location_id%TYPE DEFAULT 0)
IS
BEGIN
INSERT INTO departments (department_id,
department_name, location_id)
VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;

END over_pack;

EXECUTE over_pack.add_dept (981, 'Education ',2500)

EXECUTE over_pack.add_dept ('Training', 2400)
--------------------------------------------------------------------------------------

Example (pu_05_03)

CREATE OR REPLACE PACKAGE taxes_pack

IS
FUNCTION tax (p_value IN NUMBER) RETURN NUMBER;
END taxes_pack;
/

CREATE OR REPLACE PACKAGE BODY taxes_pack

IS
FUNCTION tax (p_value IN NUMBER) RETURN NUMBER
IS
v_rate NUMBER := 0.08;
BEGIN
RETURN (p_value * v_rate);
END tax;
END taxes_pack;

SELECT taxes_pack.tax(salary), salary, last_name

FROM employees;
--------------------------------------------------------------------------------------
Example (pu_05_04)

CREATE OR REPLACE PACKAGE pack_cur

IS
CURSOR c1 IS
SELECT employee_id
FROM employees
ORDER BY employee_id DESC;
PROCEDURE proc1_3rows;
PROCEDURE proc4_6rows;
END pack_cur;
/

CREATE OR REPLACE PACKAGE BODY pack_cur

IS
v_empno NUMBER;
PROCEDURE proc1_3rows
IS
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_empno;
DBMS_OUTPUT.PUT_LINE('Id :' ||(v_empno));
EXIT WHEN c1%ROWCOUNT >= 3;
END LOOP;
END proc1_3rows;
 PROCEDURE proc4_6rows
IS
BEGIN
LOOP
FETCH c1 INTO v_empno;
DBMS_OUTPUT.PUT_LINE('Id :' ||(v_empno));
EXIT WHEN c1%ROWCOUNT >= 6;
END LOOP;
CLOSE c1;
END proc4_6rows;
END pack_cur;

SET SERVEROUTPUT ON
EXECUTE pack_cur.proc1_3rows
EXECUTE pack_cur.proc4_6rows
--------------------------------------------------------------------------------------
Example (pu_05_05)

CREATE OR REPLACE PACKAGE emp_package IS

TYPE emp_table_type IS TABLE OF employees%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
PROCEDURE read_emp_table
(p_emp_table OUT emp_table_type);
END emp_package;
/

CREATE OR REPLACE PACKAGE BODY emp_package IS

PROCEDURE read_emp_table
(p_emp_table OUT emp_table_type) IS
i BINARY_INTEGER := 1;
BEGIN
FOR emp_record IN (SELECT * FROM employees)LOOP
p_emp_table(i) := emp_record;
i:= i+1;
END LOOP;
END read_emp_table;
END emp_package;

DECLARE
v_emp_table emp_package.emp_table_type;
BEGIN
emp_package.read_emp_table(v_emp_table);
DBMS_OUTPUT.PUT_LINE('An example: '||v_emp_table(4).last_name);
END;
-------------------------------------------------------------------------------------
wrap iname=pu_05_05.sql -- see demo_05_wrap.bat
Run plb file from iSQL -- load file => pu_05_05.plb
-------------------------------------------------------------------------------------
 PU Chapter 06 Oracle Supplied Packages Scripts

Example (PU_06_01)

CONN system/sys
CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_files';
GRANT READ, WRITE ON DIRECTORY my_dir TO HR;

CREATE OR REPLACE PROCEDURE PU_05_01

IS
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file :=UTL_FILE.FOPEN ('MY_DIR','file_1.txt','W');
UTL_FILE.PUT (v_file,'Oracle 10g');
UTL_FILE.PUT (v_file,'PL/SQL');
UTL_FILE.PUT (v_file,'Course');
UTL_FILE.FCLOSE (v_file);
END PU_06_01;
EXECUTE pu_06_01;
-------------------------------------------------------------------------------------
Example (PU_06_02)

CREATE OR REPLACE PROCEDURE pu_06_02

IS
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file :=UTL_FILE.FOPEN ('MY_DIR','file_1.txt','A');
IF NOT UTL_FILE.IS_OPEN (v_file) THEN
DBMS_OUTPUT.PUT_LINE ('the file is not opend');
ELSE
UTL_FILE.NEW_LINE (v_file);
UTL_FILE.PUT (v_file,'ORACLE 10g');
UTL_FILE.PUT_LINE (v_file,'PL/SQL');
UTL_FILE.NEW_LINE (v_file,5);
UTL_FILE.PUTF (v_file,'Start at %s by %s',sysdate,user);
UTL_FILE.FCLOSE (v_file);
END IF;
END PU_06_02;
EXECUTE pu_06_02;
-------------------------------------------------------------------------------------
Example (PU_06_03)

CREATE OR REPLACE PROCEDURE pu_06_03

IS
v_file UTL_FILE.FILE_TYPE;
v_string_out VARCHAR2(2000);
BEGIN
v_file :=UTL_FILE.FOPEN ('MY_DIR','file_2.txt','W');
UTL_FILE.PUT_LINE (v_file,'statment 1');
UTL_FILE.PUT_LINE (v_file,'statment 2');
UTL_FILE.PUT_LINE (v_file,'statment 3');
UTL_FILE.PUT_LINE (v_file,'statment 4');
UTL_FILE.FCLOSE (v_file);
v_file :=UTL_FILE.FOPEN ('MY_DIR','file_2.txt','R');
UTL_FILE.GET_LINE (v_file,v_string_out);
DBMS_OUTPUT.PUT_LINE (v_string_out);
UTL_FILE.GET_LINE (v_file,v_string_out);
DBMS_OUTPUT.PUT_LINE (v_string_out);
UTL_FILE.GET_LINE (v_file,v_string_out);
DBMS_OUTPUT.PUT_LINE (v_string_out);
UTL_FILE.FCLOSE (v_file);
END PU_06_03;
EXECUTE pu_06_03;
-------------------------------------------------------------------------------------
Example (PU_06_04)

CREATE OR REPLACE PROCEDURE pu_06_04

IS
v_file UTL_FILE.FILE_TYPE;
v_string_out VARCHAR2(2000);
BEGIN
v_file :=UTL_FILE.FOPEN ('MY_DIR','file_3.txt','W');
UTL_FILE.PUT_LINE (v_file,'statment 1');
UTL_FILE.PUT_LINE (v_file,'statment 2');
UTL_FILE.PUT_LINE (v_file,'statment 3');
UTL_FILE.PUT_LINE (v_file,'statment 4');
UTL_FILE.FCLOSE (v_file);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR (-20005,'unknown path');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR (-20003,'unknown File name');
WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR (-20006,'unknown operation');
END PU_06_04;
EXECUTE pu_06_04;
-------------------------------------------------------------------------------------
Example (PU_06_05)

CREATE OR REPLACE PROCEDURE sal_status

(P_filename varchar2)
IS
v_filehandle UTL_FILE.FILE_TYPE;
CURSOR emp_info IS
SELECT last_name, salary, department_id
FROM employees
ORDER BY department_id;
v_newdeptno employees.department_id%TYPE;
v_olddeptno employees.department_id%TYPE := 0;
BEGIN
v_filehandle := UTL_FILE.FOPEN ('MY_DIR', p_filename,'w');
UTL_FILE.PUTF (v_filehandle,'SALARY REPORT: GENERATED ON %s\n' ,SYSDATE);
UTL_FILE.NEW_LINE (v_filehandle);

FOR v_emp_rec IN emp_info LOOP

v_newdeptno := v_emp_rec.department_id;
IF v_newdeptno <> v_olddeptno THEN
UTL_FILE.PUTF(v_filehandle, 'DEPARTMENT: %s\n',v_emp_rec.department_id);
END IF;
UTL_FILE.PUTF ( v_filehandle,' EMPLOYEE: %s earns: %s\n',
v_emp_rec.last_name, v_emp_rec.salary);

v_olddeptno := v_newdeptno;
END LOOP;

UTL_FILE.PUT_LINE (v_filehandle, '*** END OF REPORT ***');

UTL_FILE.FCLOSE (v_filehandle);

EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR (-20001, 'Invalid File.');
WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR (-20002, 'Unable to write to file');
END sal_status;

EXECUTE sal_status ('emp_data.txt')

-------------------------------------------------------------------------------------
Example (PU_06_06)

--RUN UTLMAIL.SQL
CREATE OR REPLACE PROCEDURE pu_06_06
IS
BEGIN
UTL_MAIL.SEND('abc@oracle.com','xya@oracle.com',
MESSAGE => 'How are you Today’,
subject => 'For you');
END pu_06_06;
-------------------------------------------------------------------------------------
Example (pu_06_07)
CREATE OR REPLACE PROCEDURE send_mail_logo
IS
BEGIN
UTL_MAIL.SEND_ATTACH_RAW (
sender => 'me@oracle.com',
recipients => 'you@somewhere.net',
MESSAGE => '<HTML><BODY> See attachment</BODY></HTML>',
subject => 'oracle Logo',
mime_type => 'text/html',
attachment => 'oracle.gif',
att_inline => true,
att_mime_type => 'image/gif',
att_filename => 'oralogo.gif');
END send_mail_logo;
-------------------------------------------------------------------------------------
Example (PU_06_08)
CREATE OR REPLACE PROCEDURE send_mail_file
IS
BEGIN
UTL_MAIL.SEND_ATTACH_VARCHAR2 (
sender => 'me@oracle.com',
recipients => 'you@somewhere.net',
MESSAGE => '<HTML><BODY> See attachment</BODY></HTML>',
subject => 'oracle Notes',
mime_type => 'text/html',
attachment => 'note_txt',
att_inline => false,
att_mime_type => 'text/plain',
att_filename => 'notes.txt');
END send_mail_file;
-------------------------------------------------------------------------------------
 PU Chapter 07 Dynamic SQL and Metadata Scripts
Example (pu_07_01)

CONN SYSTEM/SYS
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO HR;

CREATE OR REPLACE PROCEDURE pu_07_01 (p_table_name varchar2)

IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '||p_table_name|| ' as select * from employees';
DBMS_OUTPUT.PUT_LINE ('The table is created successfully');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('The can not be createed, There is an Error');
END pu_07_01;

EXECUTE pu_07_01('emp_2017')
-------------------------------------------------------------------------------------
Example (pu_07_02)

CREATE TABLE employees_copy AS SELECT * FROM EMPLOYEES;

CREATE OR REPLACE PROCEDURE del_rows(table_name VARCHAR2)

IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM '||table_name;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||' rows are deleted from table '||table_name);
END del_rows;
-------------------------------------------------------------------------------------
Example (pu_07_03)

CREATE TABLE DEPT_100 (dept_id NUMBER, dept_name VARCHAR2(40));

CREATE TABLE UNIT_100 (unit_id NUMBER, unit_name VARCHAR2(40));

CREATE OR REPLACE PROCEDURE add_row(table_name VARCHAR2,

id NUMBER, name VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO '||table_name||
' VALUES (:1, :2)' USING id, name;
END add_row;

EXEC ADD_ROW ('dept_100',12,'dept_12');

EXEC ADD_ROW ('unit_100',1,'Mega Byte');
-------------------------------------------------------------------------------------
Example (pu_07_04)
CREATE OR REPLACE FUNCTION get_emp(emp_id NUMBER)
RETURN employees%ROWTYPE IS
stmt VARCHAR2(200);
emprec employees%ROWTYPE;
BEGIN
stmt := 'SELECT * FROM employees ' ||
'WHERE employee_id = :id';
EXECUTE IMMEDIATE stmt INTO emprec USING emp_id;
RETURN emprec;
END get_emp;

DECLARE
emprec employees%ROWTYPE := get_emp(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('Emp: '||emprec.last_name);
END;
-------------------------------------------------------------------------------------
Example (pu_07_05)
CREATE OR REPLACE PROCEDURE list_employees(deptid NUMBER) IS
TYPE emp_refcsr IS REF CURSOR;
emp_cv emp_refcsr;
emprec employees%ROWTYPE;
stmt varchar2(200) := 'SELECT * FROM employees';
BEGIN
IF deptid IS NULL THEN
OPEN
FOR stmt;
ELSE
stmt := stmt || ' WHERE department_id = :id';
OPEN emp_cv FOR stmt USING deptid;
END IF;
LOOP
FETCH emp_cv INTO emprec;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emprec.department_id||
' ' ||emprec.last_name);
END LOOP;
CLOSE emp_cv;
END list_employees;
-------------------------------------------------------------------------------------
Example (pu_07_06)
CREATE OR REPLACE PROCEDURE delete_all_rows
(p_tab_name IN VARCHAR2)
IS
cursor_name INTEGER;
v_row_count NUMBER :=0;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (cursor_name, 'DELETE FROM '||p_tab_name, DBMS_SQL.NATIVE );
V_row_count := DBMS_SQL.EXECUTE (cursor_name);
DBMS_OUTPUT.PUT_LINE( v_row_count
|| ' rows are deleted from table'
||p_tab_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END delete_all_rows;
-------------------------------------------------------------------------------------
 PU Chapter 08 Design Considerations for PL/SQL Code Scripts

Example (PU_08_01)

CREATE OR REPLACE PACKAGE error_pkg IS

fk_err EXCEPTION;
seq_nbr_err EXCEPTION;
PRAGMA EXCEPTION_INIT (fk_err, -2292);
PRAGMA EXCEPTION_INIT (seq_nbr_err, -2277);
END error_pkg;
/

CREATE OR REPLACE PROCEDURE PU_08_01

(p_department_id departments.department_id%TYPE)
IS
fk_err EXCEPTION;
PRAGMA EXCEPTION_INIT(fk_err, -2292);
BEGIN
DELETE
FROM departments
WHERE department_id = p_department_id;
DBMS_OUTPUT.PUT_LINE('The Department no '||p_department_id ||' is deleted.');
EXCEPTION
--WHEN fk_err THEN
WHEN error_pkg.fk_err THEN
DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||
TO_CHAR(p_department_id)|| '. Employees exist. ');
END PU_08_01;
-------------------------------------------------------------------------------------
Example (PU_08_02)

CREATE OR REPLACE PACKAGE constant_pkg IS

c_order_received CONSTANT VARCHAR(2) := 'OR';
c_order_shipped CONSTANT VARCHAR(2) := 'OS';
c_min_sal CONSTANT NUMBER(3) := 900;
END constant_pkg;
/
BEGIN
UPDATE employees
SET salary = salary + 200
WHERE salary <= constant_pkg.c_min_sal;
END;
-------------------------------------------------------------------------------------
Example (PU_08_03)

CREATE PROCEDURE employee_sal(id NUMBER) IS

emp employees%ROWTYPE;
FUNCTION tax(salary VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN salary * 0.825;
END tax;
BEGIN
SELECT * INTO emp
FROM EMPLOYEES WHERE employee_id = id;
DBMS_OUTPUT.PUT_LINE('Tax: '||tax(emp.salary));
END;
-------------------------------------------------------------------------------------
Example (PU_08_04)

CREATE TABLE C_1 (cust_id number (4), total_sales NUMBER (8,2));

CREATE TABLE C_2 (cust_id number(4), sales_date date);

CREATE OR REPLACE PROCEDURE cust_log (p_cust_id number)

IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO c_2 values (p_cust_id, sysdate);
COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE cust_sales (p_cust_id number, p_total number)

IS
BEGIN
cust_log(p_cust_id);
INSERT INTO c_1 VALUES (p_cust_id, p_total);
END;

-------------------------------------------------------------------------------------
Example (PU_08_05)

CREATE PROCEDURE update_salary(emp_id NUMBER) IS

name employees.last_name%TYPE;
new_sal employees.salary%TYPE;
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = emp_id
RETURNING last_name, salary INTO name, new_sal;
END update_salary;
-------------------------------------------------------------------------------------
Example (PU_08_06)

CREATE PROCEDURE raise_salary(percent NUMBER) IS

TYPE numlist IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
id numlist;
BEGIN
id(1) := 100; id(2) := 102;
id(3) := 106; id(3) := 110;
FORALL i IN id.FIRST .. id.LAST
UPDATE employees
SET salary = (1 + percent/100) * salary
WHERE manager_id = id(i);
END;

EXECUTE raise_salary(10)

-------------------------------------------------------------------------------------
Example (PU_08_07)

CREATE TABLE num_table (n NUMBER);

DECLARE
TYPE NumList IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
nums NumList;
BEGIN
nums(1) := 1;
nums(2) := 3;
nums(3) := 5;
nums(4) := 7;
nums(5) := 11;
FORALL i IN nums.FIRST .. nums.LAST
INSERT INTO num_table (n) VALUES (nums(i));
FOR i IN nums.FIRST .. nums.LAST
LOOP
dbms_output.put_line('Inserted ' ||
SQL%BULK_ROWCOUNT(i) || ' row(s)'
|| ' on iteration ' || i);
END LOOP;
END;
/
-------------------------------------------------------------------------------------
Example (PU_08_08)

CREATE OR REPLACE PROCEDURE get_departments(loc NUMBER) IS

TYPE dept_tabtype IS
TABLE OF departments%ROWTYPE;
depts dept_tabtype;
BEGIN
SELECT * BULK COLLECT INTO depts
FROM departments
WHERE location_id = loc;
FOR I IN 1 .. depts.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(depts(i).department_id
||' '||depts(i).department_name);
END LOOP;
END;
-------------------------------------------------------------------------------------
Example (PU_08_09)

CREATE OR REPLACE PROCEDURE get_departments(loc NUMBER) IS

CURSOR dept_csr IS SELECT * FROM departments
WHERE location_id = loc;
TYPE dept_tabtype IS TABLE OF dept_csr%ROWTYPE;
depts dept_tabtype;
BEGIN
OPEN dept_csr;
FETCH dept_csr BULK COLLECT INTO depts;
CLOSE dept_csr;
FOR I IN 1 .. depts.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(depts(i).department_id
||' '||depts(i).department_name);
END LOOP;
END;
-------------------------------------------------------------------------------------
 PU Chapter 09 Creating Triggers Scripts

Example (PU_09_01)

CREATE OR REPLACE TRIGGER PU_09_01

BEFORE INSERT ON DEPARTMENTS
BEGIN
IF USER <> 'HR' THEN
RAISE_APPLICATION_ERROR
(-20201,'You cannot insert in the departments table.');
END IF;
END PU_09_01;

CONN system/sys
INSERT INTO hr.departments VALUES (302,'i302',112,1500);

-----------------------------------------------------------------------------
Example (PU_09_02)

CREATE OR REPLACE TRIGGER PU_09_02

BEFORE UPDATE OR INSERT OR DELETE ON employees
BEGIN
IF to_char(sysdate,'dd') > 15 THEN
RAISE_APPLICATION_ERROR
(-20202,'You cannot modify the employees data contact admin.');
END IF;
END PU_09_02;

-----------------------------------------------------------------------------
Example (PU_09_03)

CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))
OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
THEN
RAISE_APPLICATION_ERROR
(-20500,'You may insert into EMPLOYEES table
only during business hours.');
END IF;
END secure_emp;
-----------------------------------------------------------------------------
Example (PU_09_04)

CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))
OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR
(-20502,'You may delete from EMPLOYEES table
only during business hours.');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR
(-20500,'You may insert into EMPLOYEES table
only during business hours.');
ELSIF UPDATING ('SALARY') THEN
RAISE_APPLICATION_ERROR
(-20503,'You may update SALARY
only during business hours.');
ELSE
RAISE_APPLICATION_ERROR
(-20504,'You may update EMPLOYEES table
only during normal hours.');
END IF;
END IF;
END secure_emp ;
-----------------------------------------------------------------------------
Example (PU_09_05)

CREATE OR REPLACE TRIGGER PU_10_05

BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 10000 then
:NEW.salary := :OLD.salary;
END IF;
END PU_10_06;

-- UPDATE employees SET salary = 12000 WHERE employee_id = 140

-- UPDATE employees SET salary = 2600 WHERE employee_id = 140
-----------------------------------------------------------------------------
Example (PU_09_06)

CREATE TABLE audit_emp_table

(user_name VARCHAR2(30) , timestamp date,
id NUMBER(9) ,
old_last_name VARCHAR2(40) , new_last_name VARCHAR2(40),
old_title VARCHAR2(30) ,new_title VARCHAR2(30),
old_salary NUMBER(8,2) , new_salary NUMBER(8,2));

CREATE OR REPLACE TRIGGER audit_emp_values

AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERTINTO audit_emp_table (user_name, timestamp,id, old_last_name,
new_last_name, old_title, new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :OLD.employee_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :OLD.salary, :NEW.salary );
END audit_emp_values;

-- UPDATE employees SET salary = salary *1.1 WHERE employee_id = 101;

-- SELECT * FROM audit_emp_table;
-----------------------------------------------------------------------------
Example (PU_09_07)

CREATE OR REPLACE TRIGGER derive_commission_pct

BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.job_id = 'SA_REP')
BEGIN
IF INSERTING THEN
:NEW.commission_pct := 0;
ELSIF :OLD.commission_pct IS NULL THEN
:NEW.commission_pct := 0;
ELSE
:NEW.commission_pct := :OLD.commission_pct + 0.05;
END IF;
END derive_commission_pct;
-----------------------------------------------------------------------------

Example (PU_09_08)

CREATE VIEW dept_3

AS SELECT ROWNUM rowno,department_id, department_name,location_id
FROM departments;

CREATE OR REPLACE TRIGGER new_dept

INSTEAD OF INSERT ON dept_3
FOR EACH ROW
BEGIN
INSERT INTO departments (department_id, department_name, location_id)
values (:NEW.department_id,:NEW.department_name, :NEW.location_id);
END new_dept;
INSERT INTO dept_3 (department_id, department_name, location_id )
values (309,'i309',1500);
-----------------------------------------------------------------------------
 PU Chapter 10 Creating Compound DDL and Event Triggers Scripts

Example (PU_10_01)

CREATE TABLE audit_create (t_user VARCHAR2(30), t_date DATE);

CREATE OR REPLACE TRIGGER audit_create

BEFORE CREATE ON SCHEMA
BEGIN
INSERT INTO audit_create VALUES (USER, SYSDATE);
END audit_create;

CREATE SEQUENCE seq_1;

--------------------------------------------------------------------------
Example (PU_10_02)

CONN SYS/SYS@orcl

CREATE OR REPLACE TRIGGER audit_db_create

BEFORE CREATE ON DATABASE
BEGIN
INSERT INTO HR.audit_create VALUES (USER, SYSDATE);
END audit_db_create;

CONN SCOTT/TIGER

CRATE SEQUENCE seq_00;

---------------------------------------------------------------------------
Example (PU_10_03)

CONN SYS/SYS@orcl
CREATE TABLE log_trig_table (l_user VARCHAR2(30), l_date DATE, l_action VARCHAR2(20))

CREATE OR REPLACE TRIGGER logon_trig

AFTER LOGON ON DATABASE
BEGIN
INSERT INTO log_trig_table (l_user, l_date, l_action)
VALUES (USER, SYSDATE, 'Logging on');
END logon_trig_db;

CREATE OR REPLACE TRIGGER logoff_trig

BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO log_trig_table (l_user, l_date, l_action)
VALUES (USER, SYSDATE, 'Logging off');
END logoff_trig_db;
-----------------------------------------------------------------------------
Example (pu_10_04)

CREATE TABLE server_errors

(e_user VARCHAR2(30),e_date DATE, e_code NUMBER(8),e_message VARCHAR2(100))

CREATE OR REPLACE TRIGGER server_error_trig

AFTER SERVERERROR ON SCHEMA
DECLARE
v_code NUMBER;
v_error_message VARCHAR2(100);
BEGIN
v_code :=SQLCODE;
v_error_message := SQLERRM;
INSERT INTO server_errors VALUES (USER,SYSDATE,v_code, v_error_message);
END server_error_trig;
-----------------------------------------------------------------------------
Example (PU_10_05)

CREATE OR REPLACE PROCEDURE print_data IS

BEGIN
DBMS_OUTPUT.PUT_LINE ('Oracle Develper 10g ');
END print_data; /

CREATE OR REPLACE TRIGGER print_trig

BEFORE UPDATE OF salary ON employees
FOR EACH ROW
CALL print_data /
-----------------------------------------------------------------------------
Example (PU_10_06)
CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT OR UPDATE OF salary, job_id
ON employees
FOR EACH ROW
WHEN (NEW.job_id <> 'AD_PRES')
DECLARE
v_minsalary employees.salary%TYPE;
v_maxsalary employees.salary%TYPE;
BEGIN
SELECT MIN(salary), MAX(salary)
INTO v_minsalary, v_maxsalary
FROM employees
WHERE job_id = :NEW.job_id;
IF :NEW.salary < v_minsalary OR :NEW.salary > v_maxsalary THEN
RAISE_APPLICATION_ERROR(-20505,'Out of range');
END IF;
END;
UPDATE employees SET salary = 3400 WHERE last_name = 'Stiles';
-----------------------------------------------------------------------------
Example (PU_10_07)

CREATE TABLE holiday (holiday_date DATE);

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
DECLARE
v_dummy VARCHAR2(1);
BEGIN
IF (TO_CHAR (SYSDATE, 'DY') IN ('SAT','SUN')) THEN
RAISE_APPLICATION_ERROR (-20506,'You may only
change data during normal business hours.');
END IF;
SELECT COUNT(*)
INTO v_dummy FROM holiday
WHERE holiday_date = TRUNC (SYSDATE);
IF v_dummy > 0 THEN
RAISE_APPLICATION_ERROR(-20507, 'You may not change data on a holiday.');
END IF;
END;
-----------------------------------------------------------------------------
Example (pu_10_08)

CREATE OR REPLACE TRIGGER check_salary

BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary < OLD.salary)
BEGIN
RAISE_APPLICATION_ERROR (-20508, ‘Do not decrease salary.’);
END check_salary;
-----------------------------------------------------------------------------
Example (PU_10_09)

CREATE OR REPLACE TRIGGER cascade_updates

AFTER UPDATE OF department_id ON departments
FOR EACH ROW
BEGIN
UPDATE employees
SET employees.department_id=:NEW.department_id
WHERE employees.department_id=:OLD.department_id;
UPDATE job_history
SET department_id=:NEW.department_id
WHERE department_id=:OLD.department_id;
END;
-----------------------------------------------------------------------------
Example (PU_10_10)

ALTER TABLE departments ADD (total_sal number DEFAULT 0);

CREATE OR REPLACE PROCEDURE increment_salary

(p_id IN departments.department_id%TYPE,
p_salary IN departments.total_sal%TYPE)
IS
BEGIN
UPDATE departments
SET total_sal = NVL (total_sal, 0)+ p_salary
WHERE department_id = p_id;
END increment_salary;

CREATE OR REPLACE TRIGGER compute_salary

AFTER INSERT OR UPDATE OF salary OR DELETE ON employees
FOR EACH ROW
BEGIN
IF DELETING THEN
increment_salary(:OLD.department_id,(-1*:OLD.salary));
ELSIF UPDATING THEN
increment_salary(:NEW.department_id,(:NEW.salary-:OLD.salary));
ELSE
increment_salary(:NEW.department_id,:NEW.salary);--INSERT
END IF;
END;

SELECT trigger_name, trigger_type, triggering_event, table_name, referencing_names, status, trigger_body

FROM user_triggers
WHERE UPPER(trigger_name) = UPPER('compute_salary')
-----------------------------------------------------------------------------
 PU Chapter 12 Managing Dependencies Scripts

Example (PU_12_01)

SELECT name, type, referenced_name, referenced_type

FROM user_dependencies
WHERE referenced_name IN ('EMPLOYEES','EMP_VW' );

-------------------------------------------------------------------------------------
Example (PU_12_02)

EXECUTE deptree_fill('TABLE','HR','EMPLOYEES');

SELECT nested_level, type, name

FROM deptree
ORDER BY seq#;

-------------------------------------------------------------------------------------