Trigger

A trigger is a program in a database that gets called each time a row in a table is INSERTED, UPDATED, or DELETED. Triggers allow you to check that any changes are correct, or to fill in missing information before it is COMMITed. Triggers are normally written in PL/SQL or Java.

[edit]Examples

Audit logging:

CREATE TABLE t1 (c1 NUMBER);
CREATE TABLE audit_log(stamp TIMESTAMP, usr VARCHAR2(30), new_val NUMBER);

CREATE TRIGGER t1_trig
  AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  INSERT INTO audit_log VALUES (SYSTIMESTAMP, USER, :NEW.c1);
END;
/

Prevent certain DML operations:

CREATE OR REPLACE TRIGGER t1_trig
  BEFORE INSERT OR UPDATE OR DELETE
  ON t1
BEGIN
  raise_application_error(-20001,'Inserting and updating are not allowed!');
END;
/

Function







A function is a block of PL/SQL code named and stored within the database. A function always returns a single value to its caller.



Contents


1 Creating and dropping functions
2 Calling functions
3 Examples
4 Also see




[edit]Creating and dropping functions

Create a function:
CREATE OR REPLACE FUNCTION mult(n1 NUMBER, n2 NUMBER) RETURN NUMBER
AS
BEGIN
  RETURN n1 * n2;
END;
/


Remove the function from the database:
DROP FUNCTION mult;


[edit]Calling functions

Call the above function from SQL:
SQL>  SELECT mult(10, 2) FROM dual;
MULT(10,2)
----------
        20


Call the above function from SQL*Plus:
SQL> VARIABLE val NUMBER
SQL> EXEC :val := mult(10, 3);
PL/SQL procedure successfully completed.
SQL> PRINT :val
       VAL
----------
        30


Calling the function from PL/SQL:
DECLARE
  v_val NUMBER;
BEGIN
  v_val := mult(10, 4);
  Dbms_output.Put_Line('Value is: '|| v_val);
END;
/


[edit]Examples

Simple lookup function (lookup an employee's salary):
CREATE OR REPLACE FUNCTION get_salary (p_empno NUMBER)
   RETURN NUMBER
AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
  RETURN v_sal;
END;
/


Package







A package is a collection of procedures and functions stored within the database.

A package usually has a specification and a body stored separately in the database. The specification is the interface to the application and declares types, variables, exceptions, cursors and subprograms. The body implements the specification.

When a procedure or function within the package is referenced, the whole package gets loaded into memory. So when you reference another procedure or function within the package, it is already in memory.

[edit]Example
CREATE OR REPLACE PACKAGE my_pack AS
  g_visible_variable VARCHAR2(20);
  FUNCTION calc(n1 NUMBER, n2 NUMBER) RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY my_pack AS

  g_hidden_variable CONSTANT INTEGER := 2;

  FUNCTION calc(n1 NUMBER, n2 NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN g_hidden_variable * n1 * n2;
  END;

END;
/

DBMS SCHEDULER

DBMS_SCHEDULER is a more sophisticated job scheduler introduced in Oracle 10g. The older job scheduler, DBMS_JOB, is still available, is easier to use in simple cases and fit some needs that DBMS_SCHEDULER does not satisfy.

Contents 1 Create a job 2 Remove a job 3 Run a job now 4 Change job attributes 5 Enable / Disable a job 6 Monitoring jobs

[edit]Create a job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
     job_name           => 'my_java_job',
     job_type           => 'EXECUTABLE',
     job_action         => '/usr/bin/java myClass',
     repeat_interval    => 'FREQ=MINUTELY',
     enabled            => TRUE
  );
END;
/

Unlike DBMS_JOB you do not need to commit the job creation for it to be taken into account. As a corollary, if you want to cancel it, you have to remove or disable it (see below).

[edit]Remove a job

EXEC DBMS_SCHEDULER.DROP_JOB('my_java_job');

[edit]Run a job now

To force immediate job execution:

EXEC dbms_scheduler.run_job('myjob');

[edit]Change job attributes

Examples:

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');

BEGIN 
  DBMS_SCHEDULER.SET_ATTRIBUTE
     ('WEEKNIGHT_WINDOW', 'repeat_interval', 
      'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');
END;

[edit]Enable / Disable a job

BEGIN 
  DBMS_SCHEDULER.ENABLE('myjob');
END;

BEGIN 
  DBMS_SCHEDULER.DISABLE('myjob');
END;

[edit]Monitoring jobs

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'MY_JAVA_JOB';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'MY_JAVA_JOB';

or checking from JOB owner schema

 SELECT * FROM user_scheduler_jobs WHERE job_name = 'MY_JAVA_JOB';
 SELECT * FROM user_scheduler_job_log WHERE job_name = 'MY_JAVA_JOB';

Use user_scheduler_jobs and user_scheduler_job_log to only see jobs that belong to your user (current schema).

Database Transaction Isolation Levels

The ANSI/ISO SQL standard (SQL92) defines four levels of transaction isolation with differing degrees of impact on transaction processing throughput. These isolation levels are defined in terms of three phenomena that must be prevented between concurrently executing transactions.
The three preventable phenomena are:

• Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
• Nonrepeatable (fuzzy) reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
• Phantom reads (or phantoms): A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

SQL92 defines four levels of isolation in terms of the phenomena a transaction running at a particular isolation level is permitted to experience. They are shown in Table 13-1:

Table 13-1 Preventable Read Phenomena by Isolation Level

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible