Oracle Database Administration

1. Lecture 5 Triggers PL/SQL – advanced Oracle Database Administration

2. Triggers - introduction • Triggers is a piece of code executed when specified action occurs, for example: • user inserts row into a table • user deletes something from a table • user logs in • Triggers cannot be executed as a result of SELECT statement

3. Triggers • Triggers are often used to: • automatically populate table columns, for example generate primary key identifier from a sequence • automatically update related tables, for example: update parent table when records are inserted into the child table • guarantee that specific operation is performed, for example: automatically create records in the history tables

4. Triggers • Do not use triggers to duplicate built-in features: • for relations use Foreign Keys • to check if single record data is valid use NOT NULL and CHECK constraints • for access control use GRANT and REVOKE

5. Types of triggers • DML triggers on tables • UPDATE, DELETE, INSERT • INSTEAD OF triggers on views • System triggers on: • DATABASE - triggers fire for each event for each user • SCHEMA - triggers fire for each event for specific user

6. System triggers • System triggers can be created for the following events: • DDL statements - CREATE, ALTER, DROP • Database operations: • SERVERERROR • LOGON • LOGOFF • STARTUP • SHUTDOWN

7. System triggers • Example system trigger: CREATE OR REPLACE TRIGGER On_Logon AFTER LOGON ON USER_NAME.Schema BEGIN Do_Something; END;

8. System triggers • Example system trigger: CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR (1017)) THEN ... ELSE ... END IF; END;

9. DML triggers - options • BEFORE/AFTER - trigger can fire before the operation or after the operation • Trigger can fire one time (statement trigger) or multiple times (row trigger) • Row trigger can have when condition • Row triggers can access new and old row values • Trigger on update can have column list

10. Before/After triggers • Use Before triggers to: • modify values that are about to be inserted/updated • Use After triggers to: • access newly inserted/updated values (e.g. using foreign keys) • Before triggers are slightly faster than After triggers

11. Example statement trigger CREATE OR REPLACE TRIGGER trg_1 BEFORE DELETE OR INSERT OR UPDATE ON test1 BEGIN IF INSERTING THEN INSERT INTO statement_log(log) VALUES ('inserting to test1'); ELSIF DELETING THEN INSERT INTO statement_log(log) VALUES ('deleting from test1'); ELSE INSERT INTO statement_log(log) VALUES ('updating test1'); END IF; END;

12. Example row triggers CREATE TRIGGER order_insert BEFORE INSERT ON orders FOR EACH ROW BEGIN INSERT INTO order_history(hist_id, type, id, order_value) VALUES (hist_seq.nextval, 'insert', :new.id, :new.order_value); END; CREATE TRIGGER order_update BEFORE UPDATE ON orders FOR EACH ROW BEGIN INSERT INTO order_history(hist_id, type, id, order_value) VALUES (hist_seq.nextval, 'update', :new.id, :new.order_value); END; CREATE TRIGGER order_update BEFORE DELETE ON orders FOR EACH ROW BEGIN INSERT INTO order_history(hist_id, type, id, order_value) VALUES (hist_seq.nextval, 'update',:old.id, :old.order_value); END;

13. Row triggers • Insert trigger has access to new values only • Delete trigger has access to old values only. New values are null and cannot be modified • Update trigger has access to new and old values. • new values can be modified in the Before trigger only • old and new values are available in both Before and After trigger • if a new value is modified in a Before trigger, modified value is visible in the After trigger

14. Example triggers CREATE TRIGGER expensive_order BEFORE UPDATE ON orders FOR EACH ROW WHEN (new.order_value > 100000 AND old.order_value < 100000) BEGIN ... END; CREATE TRIGGER value_change BEFORE UPDATE OF order_value ON orders FOR EACH ROW BEGIN ... END;

15. Instead of triggers • Instead of trigger is used for views which are not updateable • View is not updateable if it contains: • set operator (union, intersect etc.) • distinct operator • aggregate function (sum, max, count, etc.) • group by, order by, connect by, start with • subquery in a select list • joins with some exceptions

16. Instead of triggers • Example Instead of trigger definition: CREATE OR REPLACE TRIGGER trigger_name INSTEAD OF INSERT ON view_name REFERENCING NEW AS n FOR EACH ROW DECLARE rowcnt number; BEGIN SELECT COUNT(*) FROM .... ...

17. Triggers and transactions • Unless autonomous transactions are used: • trigger executes in the context of the current transaction (the transaction that executed the statement which caused the trigger to fire) • if a transaction is rolled back, trigger results are also rolled back • if a trigger raises an exception, the statement fails and statement-level rollback occurs • trigger cannot use transaction control statements (rollback, commit, savepoint)

18. Enabling/disabling triggers • Triggers can be in enabled and disabled state • Disabled triggers do not execute • Triggers are created enabled unless the DISABLE clause is used • Commands to enable/disable triggers: ALTER TRIGGER trigger_name ENABLE; ALTER TRIGGER trigger_name DISABLE; ALTER TABLE table_name ENABLE ALL TRIGGERS;

19. PL/SQL packages • Package is a group of: • functions • procedures • variables • cursors • type declarations • Package consists of two parts: • package specification • package body

20. Package specification • Package specification contains declarations of public objects: functions, procedures etc. • Only public objects can be accessed from outside the package • Package specification does not contain any code, just declarations • Package specification is created using the CREATE PACKAGEcommand

21. Example package specification CREATE PACKAGE pack1 IS PROCEDURE p1(param1 IN NUMBER); FUNCTION f1 RETURN VARCHAR2; var1 INTEGER; CURSOR c1 IS SELECT * FROM TEST; END;

22. Accessing package objects BEGIN pack1.p1(0); result := pack1.f1; pack1.var1 := 1; FOR rec IN pack1.c1 LOOP ... END LOOP; END;

23. Package body • Package body contains implementation of objects defined in the package specification • Package body is created using the CREATE PACKAGE BODYcommand • Package body must include implementation of all functions and procedures declared in the specification • Package body may define private functions, that will be accessible only from the package body

24. Example package body CREATE PACKAGE BODY pack1 IS PROCEDURE p1(param1 IN NUMBER) IS BEGIN p2; -- call private procedure END; FUNCTION f1 RETURN VARCHAR2 IS BEGIN ... END; PROCEDURE p2 IS BEGIN ... END; END;

25. RECORD type • RECORD type: • similar to C structure – contains multiple variables • must be defined as TYPE – RECORD declaration creates new type that can be later used for declaring variable of that type • RECORD can be declared: • in PACKAGE specification • in declaration part of PL/SQL block

26. RECORD type in a package CREATE PACKAGE record_package IS TYPE DeptRec IS RECORD ( dept_id dept.deptno%TYPE, dept_name VARCHAR2(14) DEFAULT ‘ABC’, dept_loc VARCHAR2(13) ); END;

27. RECORD type in declaration DECLARE TYPE DeptRec IS RECORD ( dept_id dept.deptno%TYPE, dept_name VARCHAR2(14), dept_loc VARCHAR2(13) ); -- type declaration recordVar DeptRec; -- variable -- declaration

28. RECORD type • RECORD members: • can have default values • can have NOT NULL constraint • are accessed by "." operator: recordVar.member • RECORD variables: • can be used as function/procedure parameters, function result • can be used as collection elements • cannot be stored in database (table column cannot have type RECORD)

29. RECORD type • Each table has predefined record for all table columns: DECLARE tableRec TABLE1%ROWTYPE; -- type record • RECORD can be used in SELECT INTO statement: SELECT * INTO tableRec FROM TABLE1 where ID = 1;

30. RECORD type • RECORD can be used in UPDATE statement: UPDATE TABLE1 SET ROW = tableRec where ID = 1; • RECORD can be used in INSERT statement: INSERT INTO TABLE1 VALUES tableRec;

31. PL/SQL exceptions • PL/SQL supports exceptions • Exceptions are thrown (raised): • as a result of executing SQL statement • as a result of calling predefined PL/SQL function procedure or package • manually by the user • Catching exceptions: • Exceptions can be caught in PL/SQL block • Uncaught exceptions are propagated to the caller

32. PL/SQL exceptions • Exceptions and transactions: • exception in SQL statement rolls back current statement, not the entire transaction • exception thrown from PL/SQL does not cause rollback

33. PL/SQL exceptions • Predefined exceptions: • NO_DATA_FOUND – select into statement • TOO_MANY_ROWS – select into statement • DUP_VAL_ON_INDEX – unique index violated • INVALID_NUMBER – text cannot be converted into number (e.g. TO_NUMBER)

34. User exceptions • User can create custom exceptions: DECLARE myError EXCEPTION; BEGIN IF ... THEN RAISE myError; END IF; EXCEPTION WHEN myError THEN ROLLBACK; RAISE; END;

35. Handling Oracle errors • Oracle reports errors as "ORA-xxxxx": ERROR at line 1: ORA-01403: no data found • Some exceptions have PL/SQL names, like NO_DATA_FOUND, TOO_MANY_ROWS • To catch exception without PL/SQL name: • find Oracle error code for that exception • declare symbolic name for that exception • catch that exception in the EXCEPTION block

36. Handling Oracle errors • For example: deadlock exception has error code ORA-00060: ERROR at line 1: ORA-00060: deadlock detected while waiting for resource • To declare that exception, PRAGMA directive must be used with error code: -60 DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT( deadlock_detected, -60);

37. Handling Oracle errors DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT( deadlock_detected, -60); BEGIN ... -- Some operation that -- causes an ORA-00060 error EXCEPTION WHEN deadlock_detected THEN -- handle the error END;

38. Custom error messages • application can raise custom errors with custom error messages: raise_application_error( error_number, message[, {TRUE | FALSE}]); • error_number should be in range -20000 .. -20999 • error message can be up to 2048 characters

39. Accessing error information • Exception handler has access to SQLCODE and SQLERRM functions • SQLCODE contains Oracle error number • SQLERRM contains error message • Example: WHEN OTHERS THEN IF SQLCODE = -60 THEN -- deadlock detected ELSE -- other error DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM); END IF END;

40. Dynamic SQL • PL/SQL enables execution of dynamic sql (SQL unknown at compilation time) • Dynamic SQL can be executed using: • EXECUTE IMMEDIATE command • OPEN FOR, FETCH, CLOSE statements • DBMS_SQL package

41. EXECUTE IMMEDIATE • Example: EXECUTE IMMEDIATE 'DELETE FROM ' || table_name; EXECUTE IMMEDIATE 'CREATE TABLE test(id NUMBER)'; • EXECUTE IMMEDIATE • executes SQL command as text • SQL command can be dynamically built at run time

42. EXECUTE IMMEDIATE • EXECUTE IMMEDIATE does not have access to PL/SQL variables: DECLARE v INTEGER; BEGIN EXECUTE IMMEDIATE 'DELETE FROM test WHERE id = v'; -- Run time error END;

43. EXECUTE IMMEDIATE • EXECUTE IMMEDIATE can execute: • any DML statement • DDL statements, session control statements, system control statements • can use bind variables and return results DECLARE sql_code VARCHAR2(100) := 'UPDATE table1 SET col1 = :val'; value1 NUMBER := 10; BEGIN EXECUTE IMMEDIATE sql_code USING value1; END;

44. DDL in PL/SQL BEGIN EXECUTE IMMEDIATE 'CREATE TABLE TAB1(ID NUMBER)'; EXECUTE IMMEDIATE 'INSERT INTO TAB1(ID) VALUES (1)'; INSERT INTO TAB1(ID) VALUES (2) – error -- table TAB1 does not exist when the code -- is compiled END;

45. Example usage CREATE FUNCTION count_rows( table_name VARCHAR2) RETURN NUMBER CNT NUMBER; IS BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO :cnt FROM ' || table_name INTO CNT; RETURN CNT; END;

46. Example usage CREATE PROCEDURE delete_from( table_name VARCHAR2, id NUMBER) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || ' WHERE id = :id' USING ID; END;

47. CURSOR variables • CURSOR variables are variables that contain reference to cursors (pointers to cursors) • CURSOR variables can be returned from functions and passed to other programming languages, for example: • Java or C++ program calls PL/SQL procedure • PL/SQL procedure opens cursor • Cursor is returned back to Java or C++ (to the client) • The client reads cursor data, like it does with normal SELECT statements • CURSOR variables can also be passed between PL/SQL functions

48. CURSOR variables • Using CURSOR variable requires: • declaring CURSOR TYPE • declaring CURSOR variable • opening CURSOR • CURSOR must be closed when it is no longer required • Cursor type can be weak or strong • Structure of the strong cursor is known at compile time (number and types of columns) • Weak cursor can be opened for SQL statement returning any set of columns

49. CURSOR type • Declaring generic cursor type: DECLARE -- weak cursor type TYPE GenericCurTyp IS REF CURSOR; BEGIN • Declaring strong cursor type: DECLARE TYPE TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE; • strong cursor type can only be used with queries that return declared type

50. CURSOR variable • Cursor variable can be declared in DECLARE block: DECLARE cursor_var GenericCurTyp; • Can be used as function parameter: CREATE PROCEDURE proc1 ( emp_cv IN OUT EmpCurTyp) IS ... • Can be returned from a function: CREATE FUNCTION func1 RETURN GenericCurTyp IS ...