Sunday, 20 May 2018

ORA-04091: table XXX is mutating, trigger/function may not see it

Requirement: Solution for mutating table error. The error description is given below.

Error: Error description is given below.

ORA-04091: table SYSTEM.STUDENTS is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TRG_STUDENT", line 6
ORA-04088: error during execution of trigger 'SYSTEM.TRG_STUDENT'

Environment Set Up: We will be using the Oracle 11g XE for our demonstration purpose.
Let us create the PROFESSORS table using the below create statement.

CREATE TABLE professors (
    professor_id      NUMBER(10) PRIMARY KEY,
    prof_first_name   VARCHAR2(15),
    prof_last_name    VARCHAR2(15)
);

Insert data into the PROFESSORS table using the below insert statements.

INSERT INTO professors VALUES (1,'Debabrata','Mazumdar');
INSERT INTO professors VALUES (2,'Moumita','Mitra');
INSERT INTO professors VALUES (3,'Diphlu','Mondal');

Let us create the STUDENTS table using the below create statement.

CREATE TABLE students (
    student_id       NUMBER(10) PRIMARY KEY,
    stu_first_name   VARCHAR2(15),
    stu_last_name    VARCHAR2(15),
    professor_id     NUMBER(10),
    CONSTRAINT fk_professor_id FOREIGN KEY ( professor_id )
        REFERENCES professors ( professor_id )
);

Insert data into the STUDENTS table using the below insert statements.

INSERT INTO students VALUES (1,'Susanto','Paul',1);
INSERT INTO students VALUES (2,'Shuhansh','Paul',1);
INSERT INTO students VALUES (3,'Anjali','Paul',1);
INSERT INTO students VALUES (4,'Moumita','Deb',2);
INSERT INTO students VALUES (5,'Ankur','Paul',1);
INSERT INTO students VALUES (6,'Mousumi','Modak',1);
INSERT INTO students VALUES (7,'Sunita','Deb',2);
INSERT INTO students VALUES (8,'Susanto','Paul',3);

COMMIT the above changes.

Thus the data in the PROFESSORS table is shown below.

SELECT * FROM professors;

Thus the data in the STUDENTS table is shown below.

SELECT * FROM students;

Replication of mutating table error: Let us create a TRIGGER named trg_student. The purpose of this trigger is to check the number of students each processor is teaching. Now we will try to update/insert a new record in the STUDENTS table. But before insert or update of STUDENTS table, it should check the number of students each professor is teaching. If the number of students taught by each professor is more than 5, immediate it should through exception saying “Professor 1 is already occupied.” Else it should update the table.

CREATE OR REPLACE TRIGGER trg_student BEFORE
    INSERT OR UPDATE ON students
    FOR EACH ROW
DECLARE
    v_total   NUMBER(10);
    v_name    VARCHAR2(30);
BEGIN
    --Check if the current professor is overbooked.
    SELECT COUNT(*)INTO v_total FROM students
        WHERE professor_id =:new.professor_id;
    IF v_total >= 5 THEN
        SELECT prof_first_name || ' ' || prof_last_name
            INTO v_name FROM professors
WHERE professor_id =:new.professor_id;
        raise_application_error(-20000,'Professor '
                    || v_name || ' is already occupied.');
    END IF;
EXCEPTION
    WHEN no_data_found THEN
        raise_application_error(-20001,'This is not a valid professor');
END;

Now let us issue the below update statement against the STUDENTS table.

UPDATE students SET professor_id = 1 WHERE student_id = 7;

When we issued the above update statement against the STUDENTS table, the following error is encountered.

Error starting at line : 1 in command -
UPDATE students SET professor_id = 1 WHERE student_id = 7
Error report -
ORA-04091: table SYSTEM.STUDENTS is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TRG_STUDENT", line 6
ORA-04088: error during execution of trigger 'SYSTEM.TRG_STUDENT'

Thus the mutating table error is replicated.

The solution for mutating table error: For the solution of the above mutating table error follow the steps as shown below:

Step 1: To record the professor_id and name, two global variables must be declared with the help of the PL/SQL package a shown below.

CREATE OR REPLACE PACKAGE professor_gv AS
    g_professor_id professors.professor_id%TYPE;
    g_name VARCHAR2(30);
END;

This package specification contains declarations for the two global variables, g_professor_id and g_name.

Step 2: An existing trigger trg_student must be modified so that its record's the professor_id, queries the professors' table, and records the name.

CREATE OR REPLACE TRIGGER trg_student BEFORE
    INSERT OR UPDATE ON students
    FOR EACH ROW
BEGIN
    IF :new.professor_id IS NOT NULL THEN
    BEGIN
    -- Assign new professor_id to the global variable g_professor_id
        professor_gv.g_professor_id := :new.professor_id;
        SELECT prof_first_name || ' ' || prof_last_name
            INTO professor_gv.g_name FROM professors
            WHERE professor_id = professor_gv.g_professor_id;
        EXCEPTION
            WHEN no_data_found THEN
                raise_application_error(-20001,'This is not a valid professor');
        END;
    END IF;
END;

In the above trigger stu_trigger, the global variables g_professor_id and g_name are initialized if the incoming value of the professor’s id is not null. The variables names are prefixed by the package name- a convention dot notation.

Step 3: A new trigger must be created on the students table. This trigger should be statement level trigger which will be fired after the INSERT or UPDATE statement has been issued.

CREATE OR REPLACE TRIGGER trg_students_2 AFTER
    INSERT OR UPDATE ON students
DECLARE
    v_total   NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_total FROM students
        WHERE professor_id = professor_gv.g_professor_id;
    IF v_total >= 5 THEN
        raise_application_error(-20000,'Professor '
        || professor_gv.g_professor_id|| ' is already occupied.');
    END IF;
END;

Step 4: Update students table using the below query.

UPDATE students SET professor_id = 1 WHERE student_id = 7;

Output: Thus we can see in the below output that we are no more getting the mutating table error. It is throwing “ORA-20000: Professor 1 is already occupied.” Which we are throwing explicitly.

Error starting at line : 1 in command -
UPDATE students SET professor_id = 1 WHERE student_id = 7
Error report -
ORA-20000: Professor 1 is already occupied.
ORA-06512: at "SYSTEM.TRG_STUDENTS_2", line 7
ORA-04088: error during execution of trigger 'SYSTEM.TRG_STUDENTS_2'

Hence, the solution for our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.

Wednesday, 16 May 2018

Retrieve the nth highest or nth lowest column value from a table

Requirement: How to retrieve the nth highest or nth lowest emp_salary from the EMP table?
Let us take the question in a better way. Let us suppose we have an EMP table with the data as shown below. Now we want to retrieve the nth highest emp_salary AND nth lowest emp_salary from the below table.
Environment Set Up: For the solution of the above requirement let us create an EMP table using the below script.

CREATE TABLE emp (
    emp_id       NUMBER(10)PRIMARYKEY,
    emp_name     VARCHAR2(20),
    emp_salary   NUMBER(10,2)
);

Insert data into EMP table using the below INSERT statements.

INSERT INTO emp VALUES(1,'Susanto',15000.25);
INSERT INTO emp VALUES(2,'Moumita',16000.67);
INSERT INTO emp VALUES(3,'Shuhansh',22000.23);
INSERT INTO emp VALUES(4,'Anjali',12000.65);
INSERT INTO emp VALUES(5,'Ankur',14000.95);
INSERT INTO emp VALUES(6,'Deep',15000.11);

Commit the changes using COMMIT statement.

Thus the data in the EMP table in descending order is shown below:

SELECT*FROM emp ORDER BY emp_salary DESC;


Please note, we don’t have any duplicate EMP_SALARY values. Going forward we will insert some duplicate EMP_SALARY values and try to validate the queries we are going to discuss now.

Solution 1: To get the second highest EMP_SALARY from the EMP table we can use the below statement.

SELECT MAX(emp_salary)second_highest_salaryFROM emp WHERE emp_salary <(SELECT MAX(emp_salary)FROM emp);


Similarly to get the second lowest EMP_SALARY from the EMP table we can use the below statement.

SELECT MIN(emp_salary)second_lowest_salaryFROM emp WHERE emp_salary >(SELECT MIN(emp_salary)FROM emp);


Disadvantages of Solution 1:
  • In case we want to get the nth highest or nth lowest EMP_SALARY from the EMP table, then the queries of Solution 1 may not suffice.
  • It is expensive
  • Also, in case where we have multiple duplicate entries in the EMP_SALARY column (say the second highest value has duplicate entry), and we want to display as many times as the duplicate value occurring in the EMP table in second highest or second lowest position, in this case also, the queries of Solution 1 will display the second highest or second lowest EMP_SALARY, but only time, not as many time as it occurs in the EMP table.
For example, let us enter two more records with duplicate values of the second highest and second lowest EMP_SALARY.

INSERT INTO emp VALUES(7,'Deb',16000.67);
INSERT INTO emp VALUES(8,'Dipendu',14000.95);

Commit the changes using COMMIT statement.

Thus the data in the EMP table in descending order is shown below:


To get the second highest EMP_SALARY as many times as it occurs in the EMP table we can use the below statement.

SELECT MAX(emp_salary) second_highest_salary FROM emp WHERE emp_salary <(SELECT MAX(emp_salary)FROM emp);


We can see clearly that though EMP_SALARY of 16000.67 is present two times, it is displaying only once.

Similarly to get the second lowest EMP_SALARY as many times as it occurs in the EMP table we can use the below statement.

SELECT MIN(emp_salary)second_lowest_salaryFROM emp WHERE emp_salary >(SELECT MIN(emp_salary)FROM emp);


We can see clearly that though EMP_SALARY of 14000.95 is present two times, it is displaying only once.

Hence, in case we want to display the second highest or second lowest EMP_SALARY as many time as it occurs in the EMP table than the above query is not suitable. Hence we should use some other approach which I will be discussing in the latter part of the explanation.

When to use the Solution 1 approach:

When we compulsorily know that we want to display the second highest or the second lowest EMP_SALARY, and we are least bother how many time the second highest or the second lowest EMP_SALARY is present in the EMP table. We are just interested in knowing the second highest or the second lowest EMP_SALARY values irrespective of the number of time it is present in the EMP table.

Solution 2: To get the nth highest EMP_SALARY from the EMP table we can use the below statement

SELECT
    x.emp_salary
FROM
    emp x
WHERE
((SELECT COUNT(DISTINCT y.emp_salary)+1FROM emp y )- n )=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;

Here ‘n’ is the position for which we need the EMP_SALARY. If we need 2nd highest EMP_SALARY replace n with 2, if we need 3rd highest EMP_SALARY replace n with 3,……, if we need 10th highest EMPL_SALARY replace n with 10 and soon on.

SELECT
   x.emp_salary
FROM
    emp x
WHERE
((SELECT COUNT(DISTINCT y.emp_salary)+1FROM emp y )-2)=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


Please note the above query show the 2nd highest salary two times as we have two records with the 2nd highest EMP_SALARY value. In case we need only the 2nd highest EMP_SALARY value irrespective of the number of time it is the EMP table we can use DISTINCT (x.emp_salary) as shown below.

SELECT
DISTINCT x.emp_salary
FROM
    emp x
WHERE
((SELECT COUNT(DISTINCT y.emp_salary)+1FROM emp y )-2)=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


Thus, similarly, we can get the 4th highest EMP_SALARY, as shown below.

SELECT
DISTINCT x.emp_salary
FROM
    emp x
WHERE
((SELECT COUNT(DISTINCT y.emp_salary)+1FROM emp y )-4)=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


To get the nth lowest EMP_SALARY from the EMP table we can use the below statement

SELECT
DISTINCT x.emp_salary
FROM
    emp x
WHERE
    n =
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
; 

Here ‘n’ is the position for which we need the EMP_SALARY. If we need 2nd lowest EMP_SALARY replace n with 2, if we need 3rd lowest EMP_SALARY replace n with 3,……, if we need 10th lowest EMPL_SALARY replace n with 10 and soon on.

SELECT
    x.emp_salary
FROM
    emp x
WHERE
2=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


Please note the above query show the 2nd lowest salary two times as we have two records with the 2nd lowest EMP_SALARY value. In case we need only the 2nd lowest EMP_SALARY value irrespective of the number of time it is the EMP table we can use DISTINCT (x.emp_salary) as shown below.

SELECT
DISTINCT x.emp_salary
FROM
    emp x
WHERE
2=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


Similarly, we can get any nth lowest EMP_SALARY, just by replacing 2 of the above query with the nth value.

Solution 3: To get the nth highest EMP_SALARY from the EMP table we can use the below statement

SELECT
    emp_salary nth_highest_salary
FROM
(SELECT emp_salary,DENSE_RANK() OVER(ORDER BY emp_salary desc)AS nth_position FROM emp)
WHERE
    nth_position =n;

Here ‘n’ is the position for which we need the EMP_SALARY. If we need 2nd highest EMP_SALARY replace n with 2, if we need 3rd highest EMP_SALARY replace n with 3,……, if we need 10th highest EMPL_SALARY replace n with 10 and soon on.

To get the 2nd highest EMP_SALARY, use the below query. Here we are using ORDER BY emp_salary desc.

SELECT
    emp_salary second_highest_salary
FROM
(SELECT emp_salary,DENSE_RANK() OVER(ORDER BY emp_salary desc)AS nth_position FROM emp)
WHERE
    nth_position =2;


Please note the above query show the 2nd highest salary two times as we have two records with the 2nd highest EMP_SALARY value. In case we need only the 2nd highest EMP_SALARY value irrespective of the number of time it is the EMP table we can use DISTINCT (emp_salary) as shown below.

SELECT
DISTINCT emp_salary second_highest_salary
FROM
(SELECT emp_salary,DENSE_RANK() OVER(ORDER BY emp_salary desc)AS nth_position FROM emp)
WHERE
    nth_position =2;


To get the nth lowest EMP_SALARY from the EMP table we can use the below statement. Here we using ORDER BY emp_salary asc

 SELECT
    emp_salary third_lowest_salary
FROM
(SELECT emp_salary,DENSE_RANK() OVER(ORDER BY emp_salary asc)AS nth_position FROM emp)
WHERE
    nth_position =3;


Advantages of Solution 3: Among all the three solutions this will be the faster and will incur least cost compared to other two.

Hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.