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
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
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
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.)