TOraQuery: implicit rollback?

[m.ghilardi]

Does ODAC perform some sort of implicit rollback whenever a PL/SQL block returns an error?

Start with an empty table.

Code: Select all

create table table1(a_name varchar2( 160 char ));

Paste this code inside a TOraQuery, then ExecSQL() (AutoCommit is false)

Code: Select all

begin
  insert into table1 values('first record');
  raise no_data_found;
end;

select * returns 0 rows.

Code: Select all

declare
  procedure do_insert
  is
  begin
    insert into table1
         values ( 'first record' );

    raise no_data_found;
  end;
begin
  do_insert;
exception
  when others then
    null;
end;

select * returns 1 row. This meas that internally Oracle does not rollback the procedure that raised the exception.

Now modify the last code like this

Code: Select all

declare
  procedure do_insert
  is
  begin
    insert into table1
         values ( 'first record' );

    raise no_data_found;
  end;
begin
  begin
    do_insert;
  exception
    when others then
      null;
  end;

  insert into table1
       values ( 'second record' );

  raise no_data_found;
end;

select * returns 0 rows. So, even if I deliberately ignore the first exception, when I raise the second one I automatically lose the data inserted by the first block.

Is there some way I can disable this "implicit rollback on exception" behaviour?

[AlexP]

Hello,

This is the behavior of the Oracle. You can ensure this by executing the code you have provided in the SQLPlus standard console utility. We send the code to the server as is. In order for rollback not to occur after data insert, you should call an explicit COMMIT in your PL/SQL code:

Code: Select all

begin
  insert into table1 values('first record');
  Commit;
  raise no_data_found;
end;

[delphi7]

Hi ,
its works here with odac v9.1.3
send me your odac if you work with another newer version to fix it.
thks.

[m.ghilardi]

Hello,

This is the behavior of the Oracle. You can ensure this by executing the code you have provided in the SQLPlus standard console utility. We send the code to the server as is. In order for rollback not to occur after data insert, you should call an explicit COMMIT in your PL/SQL code:

Code: Select all

begin
  insert into table1 values('first record');
  Commit;
  raise no_data_found;
end;

Hi AlexP, thanks for the reply.
From what I know, SQLPlus does implicit rollbacks, not Oracle (see this Ask Tom thread, for example https://asktom.oracle.com/pls/asktom/f? ... 8437682131). So I am guessing that ODAC is doing the same.

[AlexP]

As I wrote you earlier, your PL/SQL is fully transferred to the server and ODAC doesn't affect the result of this block execution.