Sqlplus Does No Commit On Exit When Using Logoff Schema Trigger (Doc ID 1416691.1)

Last updated on JUNE 24, 2016

Applies to:

PL/SQL - Version 11.2.0.2 to 12.1.2.0.0 [Release 11.2 to 12.1]
Oracle Database - Enterprise Edition - Version 11.2.0.2 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms


In sqlplus session with COMMIT ON EXIT turned on, when calling the stored procedure to delete all the children data of a parent and exit with logoff trigger, the commit did not work and no data changed.

Testcase:
=======

create table mgparent(oid number primary key, name varchar2(10), active char(1));

insert into mgparent values (1, 'Parent A', 'X');
insert into mgparent values (2, 'Parent B', 'X');
insert into mgparent values (3, 'Parent C', null);
insert into mgparent values (4, 'Parent D', null);
insert into mgparent values (5, 'Parent E', 'X');
commit;

create table mgchild(oid number primary key, parent_oid number, name varchar2(30),
foreign key (parent_oid) references mgparent (oid) on delete cascade);

create index mgchild_i1 on mgchild(parent_oid);

insert into mgchild values (1, 1, 'Child A from Parent A');
insert into mgchild values (2, 1, 'Child B from Parent A');
insert into mgchild values (3, 2, 'Child A from Parent B');
insert into mgchild values (4, 2, 'Child B from Parent B');
insert into mgchild values (5, 5, 'Child A from Parent E');
insert into mgchild values (6, 5, 'Child B from Parent E');
insert into mgchild values (7, 5, 'Child C from Parent E');
commit;

create or replace package mgexecute as
childParentOid number := null;
procedure deleteChildsofParent(parentOid in number);
procedure checkExit;
end mgexecute;
/

create or replace package body mgexecute as
  procedure deleteChildsofParent(parentOid in number) is

  begin
     savepoint deletechild;
     begin
       delete from mgchild where parent_oid = parentOid;
       childParentOid := parentOid;
       exception when others then
          rollback to deletechild;
     end;
 end deleteChildsofParent;

procedure checkExit is

begin 

   if childParentOid is not null then
      delete from mgparent where oid = childParentOid;
   end if;
end ;
end mgexecute;
/

create or replace trigger mgtrg_on_exit

  before logoff on scott.schema

  call mgexecute.checkExit ;
/

Nothing changed or no data committed with the following steps:

exec mgexecute.deleteChildsofParent(2);
exit

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms