Delete rows in parent table based on criteria in child table – Oracle

deleteforeign keyoracleoracle-10g

I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The rows in table A cannot be deleted first because the reference in table B restricts the deletion, but I also have to get the keys of A from the rows to delete in B.

Here is a SQLFiddle with a sample table structure:
http://sqlfiddle.com/#!4/f156c/4/0.

My first inclination was to attempt to save the keys by SELECTing them from B into a variable, and then use that to DELETE from A.

DECLARE
  A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
  SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
  FROM (SELECT A_ID
        FROM B
        WHERE LENGTH(B_DATA) > 4
       );

  DELETE FROM B
  WHERE LENGTH(B_DATA) > 4;

  DELETE FROM A
  WHERE A_ID IN A_ID_TO_DELETE;
END;
/

But that just gives an PLS-00382: expression is of wrong type error. The error itself is coming from the DELETE on A; I know this because if I comment it out, the block runs.

How can I either get around the expression is of wrong type error, or what's another way to approach this?

Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

(Yes, I'm well aware of how old that is. Client's choice of DB, not ours.)

Best Answer

Try like this,

DECLARE
     A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
     SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
     FROM (SELECT A_ID
           FROM   B
           WHERE  LENGTH(B_DATA) > 4
          );

     DELETE FROM B
     WHERE LENGTH(B_DATA) > 4;

     FORALL i IN A_ID_TO_DELETE.first..A_ID_TO_DELETE.last
          DELETE FROM A
          WHERE A_ID = A_ID_TO_DELETE(i);
END;
/
Related Question