PLS-00457 Error: How to resolve an array of ids to execute a statement in Oracle

the following script gives me an error message:

PLS-00457: expressions have to be of SQL types

DECLARE
    type t_num is table of number;
    bp_id INTEGER := 14058;
    zab_ids            t_num;
BEGIN

    SELECT zab.ID_ZAB BULK COLLECT 
    into zab_ids
    FROM TB_ZAB zab
             left join TB_ZBEZ zbez
                       on zab.ID_ZBEZ = zbez.ID_ZBEZ
    where zbez.ID_BP = bp_id;
    EXECUTE IMMEDIATE 'DELETE FROM TB_EGZAB where TB_EGZAB.ID_ZAB in (:ids)' USING zab_ids;
END;

I am quite new to PLSQL so I do not really understand why this error shows up? Maybe I have to convert the ids into a comma separated string? The ids should be reused later, so I thought it is a good id to store them away into a variable.

Thanks and Best Regards

Solution

You don't need PL/SQL or a collection:

DELETE FROM TB_EGZAB
WHERE  ID_ZAB in (
         SELECT zab.ID_ZAB
         FROM   TB_ZAB zab
                INNER JOIN TB_ZBEZ zbez
                ON zab.ID_ZBEZ = zbez.ID_ZBEZ
         WHERE  zbez.ID_BP = 14058
       );

Note: WHERE zbez.ID_BP = 14058 will only be true when a zbez row has been found so your LEFT OUTER JOIN will be implicitly converted to an INNER JOIN.

The error message is telling you that you cannot use collections defined in a PL/SQL scope in SQL queries.

If you want to use the ids later then use a collection defined in the SQL scope (not in a PL/SQL scope) and then you can use it in SQL queries (and do not need to use dynamic SQL):

CREATE TYPE number_list IS TABLE OF NUMBER;

Then:

DECLARE
  bp_id   TB_ZBEZ.ID_ZBEZ%TYPE := 14058;
  zab_ids number_list;
BEGIN
  SELECT zab.ID_ZAB
  BULK COLLECT INTO zab_ids
  FROM   TB_ZAB zab
         INNER JOIN TB_ZBEZ zbez
         ON zab.ID_ZBEZ = zbez.ID_ZBEZ
  WHERE  zbez.ID_BP = bp_id;

  DELETE FROM TB_EGZAB
  WHERE  ID_ZAB MEMBER OF zab_ids;

  FOR i IN 1 .. zab_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(zab_ids(i));
  END LOOP;
END;
/

If you only want the ids of the deleted rows then do the DELETE in a single query and use RETURNING ... INTO ...:

DECLARE
  TYPE t_num IS TABLE OF NUMBER;
  v_ids t_num;
BEGIN
  DELETE FROM TB_EGZAB
  WHERE  ID_ZAB in (
           SELECT zab.ID_ZAB
           FROM   TB_ZAB zab
                  INNER JOIN TB_ZBEZ zbez
                  ON zab.ID_ZBEZ = zbez.ID_ZBEZ
           WHERE  zbez.ID_BP = 14058
         )
  RETURNING id_zab BULK COLLECT INTO v_ids;

  -- Do something with the array in PL/SQL, since it is only defined in PL/SQL
  FOR i IN 1 .. v_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_ids(i));
  END LOOP;
END;
/

fiddle