Page 1 of 1
Determining the need for commit / rollback
Posted: Tue 19 Mar 2013 12:18
by 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
Re: Determining the need for commit / rollback
Posted: Tue 19 Mar 2013 14:31
by 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
Re: Determining the need for commit / rollback
Posted: Sat 06 Apr 2013 21:49
by 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.
Re: Determining the need for commit / rollback
Posted: Mon 08 Apr 2013 09:02
by 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
Re: Determining the need for commit / rollback
Posted: Mon 08 Apr 2013 11:06
by 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
Re: Determining the need for commit / rollback
Posted: Mon 08 Apr 2013 14:00
by 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.
Re: Determining the need for commit / rollback
Posted: Mon 08 Apr 2013 14:33
by 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)
Re: Determining the need for commit / rollback
Posted: Tue 09 Apr 2013 12:28
by 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.