Can we use commit or rollback command in the exception part of PL/SQL block?

  • Interview Candidate
  • 32
  • 24175

Editorial / Best Answer

Answered by: answertoyourquery

  • Nov 9th, 2005

Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function. The code in this part of the program gets executed like those in the body without any restriction. You can include any business functionality whenever a condition in main block(body of a proc/func) fails and requires a follow-thru process to terminate the execution gracefully!

Showing Answers 1 - 32 of 32 Answers

Sarat

  • Oct 19th, 2005
 

No

  Was this answer useful?  Yes

suresh

  • Nov 4th, 2005
 

how can u say we can use commands commit,rollback?

  Was this answer useful?  Yes

rishu

  • Nov 8th, 2005
 

Yes

  Was this answer useful?  Yes

answertoyourquery

  • Nov 9th, 2005
 

Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function. The code in this part of the program gets executed like those in the body without any restriction. You can include any business functionality whenever a condition in main block(body of a proc/func) fails and requires a follow-thru process to terminate the execution gracefully!

vinayak tripathi

  • Dec 14th, 2005
 

Yes, u can use commit or rollback in exception block.

PRATIK GHOSHAL

  • Jan 8th, 2006
 

yes we can

  Was this answer useful?  Yes

Rupjit Roy

  • Feb 23rd, 2006
 

Yes we can use commit/rollback in exception part. Example:

DECALRE

?..

BEGIN

??.

EXCEPTION

WHEN NO_DATA_FOUND THEN

 

            INSERT INTO err_log(

err_code, code_desc)

                        VALUES(?1403?, ?No data found?)

 

            COMMIT;

            RAISE;

END

Deepika S Verma

  • Apr 19th, 2006
 

yes you can .. try this code

create table test1(x varchar2(20))

declare

x number;

begin

select sal into x from emp where ename like 'TTT%';

exception when no_data_found then

insert into test1 values('no data');

commit;

end;

/

select * from test1;

  Was this answer useful?  Yes

neema

  • May 29th, 2006
 

Yes

  Was this answer useful?  Yes

Arup Chakraborty

  • Nov 3rd, 2006
 

Yes, we can use Commit / Rollback in the Exception part. Declare ---Variable declaration Begin ----Executable part Exception When others then dbms_output.put_line(sqlerrm); Rollback; END ;

aksingh283

  • May 27th, 2011
 

Yes , we can use commit or rollback in exception part of PL SQL Block .
Below is an anonymous PL SQL block , try this :

Declare
   myexception EXCEPTION;
Begin
   RAISE myexception;
EXCEPTION
   When myexception then
   insert into table_name values (......);
   COMMIT;
  
   insert into table_name values (.......);
   ROLLBACK;
END;

  Was this answer useful?  Yes

subhash

  • Jul 31st, 2011
 

yes we can......

  Was this answer useful?  Yes

geetha

  • Aug 4th, 2011
 

no, we can't use exceptions in ddl statements.normal form sql or sql*plus,without explicitly issuing commit or rollback statements.

  Was this answer useful?  Yes

Prashant

  • Aug 30th, 2011
 

Yes, you can use commit/rollback in exception block.

  Was this answer useful?  Yes

priya dharan raj

  • Nov 14th, 2011
 

we can use commi ,rollback inside exception block

EXCEPTION -- exception handlers begin
WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
COMMIT;

  Was this answer useful?  Yes

Susil Kumar Nagarajan

  • Jan 20th, 2012
 

Yes. You can very well use COMMIT or ROLLBACK in exception block. But it is not a good idea to use, as it will commit the previous transactions in the same PLSQL block. Make use of creating SAVEPOINTS and commiting them when required.

  Was this answer useful?  Yes

Leena Roja

  • Jul 13th, 2015
 

Correct Subhash..

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions