Determining the need for commit / rollback

[sinys]

Can I after expression in any way to determine whether I perform commit / rollback for SQL_PLSQL?
For example:
OraQuery.SQL.Text := 'begin insert ... end;';
if OraQuery.NeedFinishTransaction then // return true
OraQuery.Session.Commit;

OraQuery.SQL.Text := 'begin insert ...; rollback; end;';
if OraQuery.NeedFinishTransaction then // return false

[AlexP]

Hello,

If you haven't started the transaction explicitly using the OraSession.StartTransaction method, then Commit will be performed on execution of the PL/SQL block if RollBack is not invoked explicitly, (independently on whether this Commit is invoked in the block or not).
If the OraSession.StartTransaction method was invoked before the block execution, then the Commit and Rollback methods in the PL/SQL block will complete the started transaction (since Oracle doesn't support multiple transactions. You can control the status of the started transaction using the InTransaction property. The following samples demonstrate this kind of behaviour:

Code: Select all

  //transaction is not started explicitly, and data will be inserted during the PL/SQL block execution
  OraQuery1.SQL.Text :=  'begin insert into dept values(10, ''ACCOUNTING'',''NEW YORK''); end;';
  OraQuery1.ExecSQL;
  if OraSession1.InTransaction then; // false

Code: Select all

  //transaction is started explicitly, but neither Commit nor Rollback are invoked in the PL/SQL block
  //user chooses action
  //transaction remains active
  OraSession1.StartTransaction;
  OraQuery1.SQL.Text :=  'begin insert into dept values(10, ''ACCOUNTING'',''NEW YORK''); end;';
  OraQuery1.ExecSQL;
  if OraSession1.InTransaction then // True
    OraSession1.Commit;

Code: Select all

  //transaction is started explicitly, but either Commit or Rollback is not invoked in the PL/SQL block
  //data will be inserted or canceled during the PL/SQL block execution
  //transaction will be completed during the PL/SQL block execution
  OraSession1.StartTransaction;
  OraQuery1.SQL.Text :=  'begin insert into dept values(10, ''ACCOUNTING'',''NEW YORK''); COMMIT /*ROLLBACK*/; end;';
  OraQuery1.ExecSQL;
  if OraSession1.InTransaction then; // False

[sinys]

Code: Select all

  //transaction remains active
  OraSession1.StartTransaction;
  OraQuery1.SQL.Text :=  'begin null; end;';
  OraQuery1.ExecSQL;
  if OraSession1.InTransaction then // True
    OraSession1.Commit;

It is not good, because pl/sql statement dosn't have DML with open transacton state.

[AlexP]

Hello,

In your code, before the PL/SQL block execution, you are explicitly starting transaction, and since Commit is not called in the PL/SQL block, then the InTransaction property returns True

[sinys]

How can I know when to really need commit?
'begin null; end;'; // required FALSE
'begin insert ... end;'; // required TRUE
'begin insert ...; COMMIT /*ROLLBACK*/; end;'; required FALSE
'begin proc_insert_emp; end;'; required TRUE/FALSE according to exists commit inside procedure

[AlexP]

Hello,

After execution of the PL/SQL block, it is possible to define only the transaction status using the InTransaction property. The necessity to execute the COMMIT and ROLLBACK operations is specified by developer. We cannot define (especially in stored procedures) the operations executed in the PL/SQL blocks, therefore you have to implement work with transactions by yourself, dependently on the logic of your application.

[sinys]

In my situation it could be any sql or pl/sql block.
How does IDE for Oracle always know when require commit and process all showing situations correctly? (for example pl/sql developer)

[AlexP]

Hello,

Most possibly, in the utility you have provided, to define the transaction status, an additional query to the server is used, for example, like the following one:

Code: Select all

SELECT  Count(TADDR )
FROM    v$session v
WHERE   v.AUDSID = userenv('sessionid')
    AND v.TADDR IS NOT NULL;

We cannot add the same check, because, if to invoke this query after each operation, this will adversely affect our components performance.