How using PL/SQL Locally Defined Types with TABLE Operator

Question

I am developing a trigger that keeps a table updated where the departments are recorded and the total sum of their salaries. Each time the EMP table is updated. The trigger updates this table. To avoid the error of mutant tables, I have implemented a "COMPOUND TRIGGER" type trigger.

Here I put your implementation:

CREATE OR REPLACE TRIGGER EJERCICIO_27 
FOR INSERT OR UPDATE OR DELETE ON EMP
COMPOUND TRIGGER

    TYPE t_DeptToUpdate IS TABLE OF NUMBER(2,0);
    deptToUpdate t_DeptToUpdate;

-- AFTER EACH ROW Section:
AFTER EACH ROW IS
BEGIN

    IF NOT :OLD.DEPTNO MEMBER OF deptToUpdate THEN
        deptToUpdate.EXTEND;
        deptToUpdate(deptToUpdate.LAST) := :OLD.DEPTNO;
    END IF;

END AFTER EACH ROW;
-- AFTER STATEMENT Section:
AFTER STATEMENT IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('SE ACTUALIZAN UN TOTAL DE: ' || deptToUpdate.COUNT);
    MERGE INTO SALARIO_DEPARTAMENTOS SD USING (
        SELECT DEPTNO, NVL(SUM(SAL), 0) AS SUM_TOTAL FROM EMP
        WHERE DEPTNO IN (SELECT * FROM TABLE(deptToUpdate))
        GROUP BY DEPTNO
    ) D
    ON (SD.DEPTNO = D.DEPTNO)
    WHEN MATCHED THEN 
        UPDATE SET
            SAL_TOT = D.SUM_TOTAL
            WHERE SD.DEPTNO = D.DEPTNO
    WHEN NOT MATCHED THEN 
        INSERT (DEPTNO, SAL_TOT)
        VALUES(D.DEPTNO, D.SUM_TOTAL);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR : ' || SQLCODE || 'MENSAJE: ' || SQLERRM);

END AFTER STATEMENT;
END;
/

The problem I have when using the collection as a source for querying the MERGE statement. I understood that in Oracle 12c (I am using Oracle 12c R2 Enterprise) I could use the TABLE operator with locally defined types. As discussed in this post Using the TABLE Operator with Locally Defined Types in PL/SQL.

Exactly the errors that the compiler returns me are:

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/5     PL/SQL: SQL Statement ignored
22/40    PL/SQL: ORA-22905: no se puede acceder a las filas de un elemento de tabla no anidada
22/46    PLS-00642: tipos de recopilación local no permitidos en sentencias SQL

Someone can tell me what approach to use without having to create any type in the schematic?. Thanks in advance.

EDIT

Translation of error messages:

ORA-22905: Rows of a non-nested table element can not be accessed
PLS-00642: Local collection types not allowed in SQL statements

Try changing the definition of the type to either TYPE t_DeptToUpdate IS TABLE OF NUMBER or TYPE t_DeptToUpdate IS TABLE OF EMP.DEPTNO%TYPE. Best of luck. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 30, 2017 at 20:34
I'm a little curious on why you want to go throught the trouble of having a trigger updating a table when you could just create an really easy and fast view that does it for you and is always up to date. Maybe i'm missing something — LauDec
– LauDec, Commented Jul 30, 2017 at 20:37
You can also try DEPTNO MEMBER OF deptToUpdate instead of DEPTNO IN (SELECT * FROM TABLE(deptToUpdate)) — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 30, 2017 at 20:53
Did you consider FORALL? Perhaps you have to transform the MERGE into INSERT and UPDATE statement. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 30, 2017 at 20:54
My understanding is even in 12c the type must be defined in package spec. It doesn't work if the type is declared in a package body or a trigger. — APC
– APC, Commented Jul 31, 2017 at 5:39

Kirby · Accepted Answer · 2017-08-02 05:19:46Z

1

Are you required to use a collection? If not, then it seems that a simple row-level trigger should work, something like this:

Create Or Replace Trigger emp_trig
After Insert Or Update Or Delete On emp
For Each Row
Begin
  -- Subtract old salary from old department
  Update department_salary ds
     Set ds.tot_salary = ds.tot_salary - :old.salary
   Where ds.dept =  :old.dept;

  If Inserting Or Updating Then
    -- Add new salary to new department
    Update department_salary ds
       Set ds.tot_salary = ds.tot_salary + :new.salary
     Where ds.dept =  :new.dept;

    If SQL%Rowcount = 0 Then
      Insert Into department_salary  (dept, tot_salary) Values(:new.dept, :new.salary);
    End If;
  End If;
End;
/

The separate update statements for old and new values handle cases when an updated row might contain a changed department number.

edited Aug 2, 2017 at 5:19

answered Aug 2, 2017 at 4:55

Kirby

7244 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sergio Sánchez Over a year ago

Thanks, it's perfect

Kirby · Accepted Answer · 2017-07-30 23:35:43Z

1

As LauDec commented, a very straightforward way to maintain a list of departments and total salary would be with a view such as this:

Create Or Replace View salario_departamentos_view As
Select deptno, Sum(sal) As sal_tot From emp
Group By deptno;

The beauty of this solution is that the data is always in perfect harmony with the EMP table, and it's very easy to understand, even for someone with minimal SQL skills.

answered Jul 30, 2017 at 23:35

Kirby

7244 silver badges8 bronze badges

1 Comment

Sergio Sánchez Over a year ago

Thanks for the reply. In the proposed exercise I have to use a trigger. I understand that the ideal would be to use a view.

Collectives™ on Stack Overflow

How using PL/SQL Locally Defined Types with TABLE Operator

EDIT

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related