Oracle Database Administration

1
Oracle Database Administration

• Lecture 5
• Triggers
• PL/SQL advanced

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 gt 100000
• AND old.order_value lt 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
  PACKAGE command

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
  BODY command
• 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.deptnoTYPE,
dept_name VARCHAR2(14) DEFAULT ABC,
dept_loc VARCHAR2(13) ) END
27
RECORD type in declaration
DECLARE TYPE DeptRec IS RECORD ( dept_id
dept.deptnoTYPE, 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 TABLE1ROWTYPE -- 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 employeesROWTYPE
• 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 ...

51
Opening cursor
DECLARE cursor_var GenericCurTyp BEGIN IF
.... THEN OPEN cursor_var FOR
SELECT FROM table1 ELSE OPEN
cursor_var FOR 'SELECT FROM '
tableName ' WHERE ID p'
USING id END IF FETCH cursor_var INTO
rec CLOSE cursor_var END
52
Opening cursor

• CREATE FUNCTION selectFunc(tableName IN VARCHAR2)
  RETURN GenericCurTyp
• DECLARE
• cursor_var GenericCurTyp
• BEGIN
• OPEN cursor_var FOR
• 'SELECT FROM ' tableName
• RETURN cursor_var
• END
• Caller must close the returned cursor
• cursorVar selectFunc('some_table')
• CLOSE cursorVar

53
PL/SQL collection types

• PL/SQL does not support regular collections
  arrays, lists, hash maps etc.
• PL/SQL supports three types of collections
• index-by tables
• nested tables
• varrays

54
index-by tables

• Declaration
• DECLARE
• TYPE tab IS TABLE OF VARCHAR2(100)
• INDEX BY BINARY-INTEGER
• Characteristics
• similar to hash-tables in other languages
• index-by table can store any number of elements
• can be indexed by number or character type
• cannot be stored in a database

55
Using index-by tables

• DECLARE
• TYPE tab IS TABLE OF VARCHAR2(100)
• INDEX BY BINARY-INTEGER
• var tab
• BEGIN
• var(1) 'First item'
• var(-100) 'item before first'
• var(100) 'last item'
• var(10000) 'item after last'
• IF var.exists(20) THEN ... END IF
• IF var.first -100 THEN ... END IF
• END

56
VARRAYs

• Declaration
• DECLARE
• TYPE varray_type IS VARRAY(50)
• OF INTEGER
• Characteristics
• array with variable size up to the specified
  limit
• dense array (index starts at 1)
• similar to normal array in other languages
• can be stored in a database
• must be constructed before use (initially is NULL)

57
Using VARRAYs
DECLARE TYPE varray_type IS VARRAY(50)
OF INTEGER var
varray_type BEGIN var(1) 10 -- ERROR
var IS NULL var varray_type()
var.extend -- add element var(1) 10
-- ok var varray_type(10, 20, 30) ok
var.extend(51) -- ERROR limit is 50 END
58
Using VARRAYs in SQL
CREATE TABLE tab1 ( id NUMBER PRIMARY
KEY, name VARCHAR2(100), varray_col
varray_type ) INSERT INTO tab1 VALUES (1, 'some
name', varray_type(10, 20, 30, 40,
-100)) DECLARE var varray_type(10, -100, 20,
-100) BEGIN update tab1 set varray_col
var WHERE id 1 END
59
Nested tables

• Declaration
• CREATE TYPE nested_type
• AS TABLE OF VARCHAR(1000)
• Characteristics
• array with no size limit
• initially dense, can become sparse when elements
  are removed
• can be stored in a database
• must be constructed before use (initially is NULL)

60
Using nested tables
DECLARE TYPE nested_type IS TABLE OF
VARCHAR(1000) var nested_type() BEGIN
var.extend(100) var(1) 'first element'
var(2) 'second element' IF var(3) IS NULL
THEN ... END IF IF var(101) IS NULL - ERROR
END IF END IF
61
Using nested tables in SQL
CREATE TABLE tab2 ( ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100), nested_col
nested_type) INSERT INTO tab2 VALUES (1,
'name', nested_type('val1', 'val2',
'val3'))
62
Using collection methods

• The following methods exist for collections
• EXISTS checks if index exists
• COUNT number of objects in the collection
• LIMIT maximum number of elements in a
  collection (VARRAY-s)
• FIRST and LAST lowest and highest index
• PRIOR and NEXT previous next element in the
  collection (useful in index-by tables)
• EXTEND adds new element (VARRAY-s and nested
  tables)
• TRIM remove elements from the end
• DELETE delete elements

63
FORALL statement

• bulk-bind faster than normal FOR statement
• DECLARE
• TYPE NumList IS VARRAY(10) OF NUMBER
• depts NumList
• NumList(20,30,50,55,57,60,70,75,90,92)
• BEGIN
• FORALL j IN 4..7
• UPDATE emp SET sal sal 1.10
• WHERE deptno depts(j)
• END

64
BULK COLLECT

• SELECT INTO nested table
• DECLARE
• TYPE NumTab IS TABLE OF emp.empnoTYPE
• TYPE NameTab IS TABLE OF emp.enameTYPE
• enums NumTab -- no need to initialize
• names NameTab
• BEGIN
• SELECT empno, ename BULK COLLECT INTO
• enums, names FROM emp
• ...
• END

65
BULK COLLECT

• FETCH into nested tables
• DECLARE
• TYPE NameList IS TABLE OF emp.enameTYPE
• TYPE SalList IS TABLE OF emp.salTYPE
• CURSOR c1 IS SELECT ename, sal FROM emp WHERE
  sal gt 1000
• names NameList
• sals SalList
• BEGIN
• OPEN c1
• FETCH c1 BULK COLLECT INTO names, sals
• END

66
BULK COLLECT

• FETCH into nested table of type record
• DECLARE
• TYPE DeptRecTab IS TABLE OF deptROWTYPE
• dept_recs DeptRecTab
• CURSOR c1 IS
• SELECT deptno, dname, loc FROM dept WHERE
  deptno gt 10
• BEGIN
• OPEN c1
• FETCH c1 BULK COLLECT INTO dept_recs
• LIMIT 200
• END

67
PL/SQL security

• By default PL/SQL ignores roles, it only sees
  privileges granted directly
• To access table from some other schema
• grant direct access to it, e.g.
• GRANT select ON schema.name TO some_user
• define the procedure with invoker rights

68
Invoker rights

• To create procedure with invoker rights
• CREATE OR REPLACE PROCEDURE test
• AUTHID CURRENT_USER IS
• BEGIN
• ...
• END

69
Invoker rights

• AUTHID is specified in the header of a program
  unit. The same cannot be specified for individual
  programs or methods within a package or object
  type.
• Definer rights will always be used to resolve any
  external references when compiling a new routine.
• For an invoker rights routine referred in a view
  or a database trigger, the owner of these objects
  is always considered as the invoker, and not the
  user triggering it.

70
Standard PL/SQL packages

• DBMS_JOB handles database jobs
• DBMS_LOB handle BLOB and CLOB types
• DBMS_MVIEW manage materialized views
• DBMS_OUTPUT print messages to console
• DBMS_RANDOM generate random numbers
• UTL_FILE access files from PL/SQL programs
• UTL_HTTP make HTTP requests
• UTL_SMTP send email from PL/SQL
• UTL_TCP make TCP/IP connections

71
DBMS_JOB
DBMS_JOB.SUBMIT( JOB OUT BINARY_INTEGER,
WHAT IN VARCHAR2, NEXT_DATE IN DATE
DEFAULT SYSDATE, INTERVAL IN VARCHAR2
DEFAULT 'NULL', NO_PARSE IN BOOLEAN DEFAULT
FALSE, INSTANCE IN BINARY_INTEGER DEFAULT
ANY_INSTANCE,
FORCE IN BOOLEAN DEFAULT FALSE) DBMS_JOB.RUN(
JOB IN BINARY_INTEGER, FORCE IN
BOOLEAN DEFAULT FALSE)
72
DBMS_LOB
Functions OPEN open specified LOB READ
read values from LOB WRITE write to LOB
GETLENGTH get current size of the LOB CLOSE
close LOB CREATETEMPORARY create temporary
LOB FREETEMPORARY release temporary LOB
SUBSTR return part of the LOB
73
DBMS_MVIEW

• Package for managing materialized views
• Main functions
• REFRESH refreshes single materialized view
• REFRESH_ALL_MVIEWS

74
DBMS_OUTPUT

• Writes output that can be viewed on the console
• Useful for debugging PL/SQL code
• Main functions
• PUT_LINE write one line of text to console
• NEWLINE write end of line character
• PUT write text without end of line character
• Note there are limits on the size of the output
  buffer. Large texts may be truncated

75
DBMS_RANDOM

• Package for generating random numbers
• Main functions
• INITIALIZE
• TERMINATE
• RANDOM generate random number
• Possible uses
• SELECT FROM tab1 ORDER BY
• DBMS_RANDOM.RANDOM

76
UTL_FILE

• Functions for accessing files from the database
• Special privileges are required to access files
• Functions are similar to C stdio library
• FOPEN
• FCLOSE
• PUT
• PUT_RAW
• GET_LINE
• GET_RAW

77
Other packages

• UTL_HTTP
• Functions for accessing HTTP servers (also using
  SSL)
• UTL_SMTP
• Functions for sending email from PL/SQL (low
  level package)
• UTL_TCP
• Functions for connecting to servers using TCP/IP