Hello everyone.
Please help me resolve the following issue.
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Oracle returns ORA-00036 when I try to delete a row from the child table which in turn deletes a row from the parent table, which in turn again deletes next row from the child table and so on.
create or replace type t_number_list as table of number;
create table parent_t (aen_id number(9) primary key);
create table child_t (aen_id number(9)
,closing_aen_id number(9)
,constraint fk_aen_id foreign key (aen_id) references parent_t(aen_id) on delete cascade
,constraint fk_closing_aen_id foreign key (closing_aen_id) references parent_t(aen_id));
insert into parent_t (aen_id) values (1);
insert into parent_t (aen_id) values (2);
insert into parent_t (aen_id) values (3);
insert into parent_t (aen_id) values (4);
insert into child_t (aen_id, closing_aen_id) values (1, 2);
insert into child_t (aen_id, closing_aen_id) values (2, 3);
insert into child_t (aen_id, closing_aen_id) values (3, 4);
create or replace trigger tr_child
for delete on child_t
compound trigger
g_aen_list t_number_list := t_number_list();
before statement is
begin
g_aen_list.delete;
end before statement;
after each row is
begin
g_aen_list.extend;
g_aen_list(g_aen_list.last) := :old.closing_aen_id;
end after each row;
after statement is
begin
delete from parent_t
where aen_id in (select column_value from table(g_aen_list));
--this does not work either
/* forall i in 1 .. g_aen_list.count()
delete from parent_t
where aen_id = g_aen_list(i); */
end after statement;
end tr_child;
/
delete from child_t where aen_id = 1;