How to execute a function that returns a table and at the same time deletes data from another table?

Question

Oracle

It is necessary to make the function of removing all employees from a given unit. The return value is a list of remote employees in the format: employee code, employee name.

I wrote this function. The name of the department is passed to the input. A cursor is created with which I will get all the employees working in this department. Then, in the for loop, I remove the employees from the table.

CREATE TYPE EMP_ROW AS OBJECT (
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(150)
);
/

CREATE TYPE EMP_TAB AS TABLE OF EMP_ROW;
/
CREATE OR REPLACE FUNCTION DROP_EMPS (DEPT_NAME IN DEPARTMENTS.DEPARTMENT_NAME%TYPE) 
RETURN EMP_TAB PIPELINED AS   
  CURSOR C_EMPS IS
      SELECT E.EMPLOYEE_ID, (E.FIRST_NAME||' '||E.LAST_NAME) EMP_NAME
      FROM EMPLOYEES E
      WHERE E.DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE D.DEPARTMENT_NAME = DEPT_NAME)
      FOR UPDATE;

  BEGIN
    FOR EMP IN C_EMPS LOOP
     
      PIPE ROW(EMP_ROW(EMP.EMPLOYEE_ID, EMP.EMP_NAME)); 
      
      DELETE FROM EMPLOYEES     
      WHERE CURRENT OF C_EMPS;      
      
    END LOOP;       
    RETURN;
    
  END DROP_EMPS;
/

I'm trying to run: I get an error:
SELECT * FROM TABLE(DROP_EMPS('Administration'));

ORA-14551: cannot perform a DML operation inside a query

As I understand it, it is impossible to delete from the table in a SELECT query ...

If you remove the deletion of records in the function, then it works fine, i.e. the problem is precisely this.

Answer 1

In general, I got out of this situation ...
Instead of a pipelined table function, I used a simple table function. Returned the received value to the corresponding variable type in the pl/sql block, and then displayed the received data through the for loop.
Function code:

CREATE OR REPLACE FUNCTION DROP_EMPS (DEPT_NAME IN DEPARTMENTS.DEPARTMENT_NAME%TYPE) 
  RETURN EMP_TAB AS  	

    CURSOR C_EMPS IS
        SELECT E.EMPLOYEE_ID, (E.FIRST_NAME||' '||E.LAST_NAME) EMP_NAME
        FROM EMPLOYEES E
        WHERE E.DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE D.DEPARTMENT_NAME = DEPT_NAME)
        FOR UPDATE;

      CURSOR C_DEPTS IS
      	SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE D.DEPARTMENT_NAME = DEPT_NAME;

      V_TMP_DEPT_ID DEPARTMENTS.DEPARTMENT_ID%TYPE;

      EMPS EMP_TAB := EMP_TAB();    

      MISSING_DEPT EXCEPTION;

    BEGIN
      OPEN C_DEPTS;
      FETCH C_DEPTS INTO V_TMP_DEPT_ID;

      IF C_DEPTS%NOTFOUND THEN
        RAISE MISSING_DEPT;
      END IF;
      
      CLOSE C_DEPTS;

      FOR EMP IN C_EMPS LOOP			
            UPDATE EMPLOYEES
            SET MANAGER_ID = NULL
            WHERE MANAGER_ID = EMP.EMPLOYEE_ID;
            
            DELETE FROM JOB_HISTORY
            WHERE EMPLOYEE_ID = EMP.EMPLOYEE_ID;
            
            EMPS.EXTEND;
            EMPS(EMPS.LAST) := EMP_ROW(EMP.EMPLOYEE_ID, EMP.EMP_NAME);
          
            DELETE FROM EMPLOYEES     
            WHERE CURRENT OF C_EMPS;      	      
      END LOOP;       
      RETURN EMPS;

      EXCEPTION
      	WHEN MISSING_DEPT THEN
      		RAISE_APPLICATION_ERROR(-20005, 'MISSING DEPARTMENT');     
      
    END DROP_EMPS;
END PKG_EMP;

Function call:

SET SERVEROUTPUT ON
DECLARE
  DELETED_EMPS EMP_TAB;
  V_EMP_ID NUMBER;
BEGIN
  DBMS_OUTPUT.enable;
  DELETED_EMPS := DROP_EMPS('IT');
  FOR I IN 1..DELETED_EMPS.COUNT LOOP
    DBMS_OUTPUT.put_line(DELETED_EMPS(I).EMP_ID||' '||DELETED_EMPS(I).EMP_NAME);
  END LOOP;      
END;
/

Answer 2

To delete data from a table using a function call in a select, that function must be compiled with an autonomous transaction pragma. If you want to return a table, you can use the returning clause to get the deleted records, like so

CREATE PACKAGE PKG_EMP 
IS
TYPE emp_rec IS RECORD (id emp.id%TYPE,
                       name emp.name%TYPE);

TYPE emp_nt IS TABLE OF emp_rec;

FUNCTION delete_emp (in_deptid IN department.id%TYPE) RETURN emp_nt PIPELINED;
END;
/
CREATE PACKAGE BODY PKG_EMP IS
FUNCTION delete_emp (in_deptid IN department.id%TYPE) RETURN emp_nt PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_return_emp emp_nt;
BEGIN
    BEGIN
        DELETE FROM EMP
        WHERE deptid = in_deptid
        RETURNING id, name BULK COLLECT INTO v_return_emp;
            COMMIT;
        EXCEPTION WHEN OTHERS THEN 
            ROLLBACK;
    END;
    
    FOR indx IN 1 .. v_return_emp.COUNT
        LOOP
            PIPE ROW (v_return_emp(indx));
        END LOOP;
    RETURN;
END;
END;
/
SELECT * FROM TABLE (PKG_EMP.DELETE_EMP (10) )