Oracle PL/SQL/Trigger/Trigger Error

shows an illegal use of LOBs in a trigger.

 
SQL>
SQL> CREATE TABLE lobdemo (
  2    key NUMBER,
  3    clob_col CLOB,
  4    blob_col BLOB);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER LOBTrigger
  2    BEFORE UPDATE ON lobdemo
  3    FOR EACH ROW
  4  DECLARE
  5    v_InitialString VARCHAR2(50) :=
  6      "LOBTrigger: ";
  7  BEGIN
 10    DBMS_LOB.WRITE(:new.clob_col, LENGTH(v_InitialString),
 11                   1, v_InitialString);
 12  END LOBTrigger;
 13  /
Trigger created.
SQL> drop table lobdemo;
Table dropped.
SQL>
SQL>

This trigger will raise an ORA-4091 mutating table error.

 
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER LimitMajors
  2    BEFORE INSERT OR UPDATE OF major ON lecturer
  3    FOR EACH ROW
  4  DECLARE
  5    studentMax CONSTANT NUMBER := 5;
  6    studentCount NUMBER;
  7  BEGIN
  8    SELECT COUNT(*)
  9      INTO studentCount
 10      FROM lecturer
 11      WHERE major = :new.major;
 12
 13    IF studentCount + 1 > studentMax THEN
 14      RAISE_APPLICATION_ERROR(-20000,
 15        "Too many lecturer in major " || :new.major);
 16    END IF;
 17  END LimitMajors;
 18  /
Trigger created.
SQL>
SQL> UPDATE lecturer
  2    SET major = "History"
  3    WHERE ID = 10003;
UPDATE lecturer
       *
ERROR at line 1:
ORA-04091: table sqle.LECTURER is mutating, trigger/function may not see it
ORA-06512: at "sqle.LIMITMAJORS", line 5
ORA-04088: error during execution of trigger "sqle.LIMITMAJORS"

SQL>
SQL> DROP TRIGGER LimitMajors;
Trigger dropped.
SQL>
SQL> drop table lecturer;
Table dropped.