Suppose thr are 10 DMLs(insert,update,delete ) in the main section of the PL/SQL block .The exception in them is handled as a whole in the exception handling section .....The error may occur in any of this DMLs ,so how can we understand that which DML has failed ??

  • Interview Candidate
  • 19
  • 6197

Showing Answers 1 - 19 of 19 Answers

rampratap409

  • Oct 9th, 2006
 

declare

stmt varchar2(30);

begin

stmt := 'update statement1 ';

update statement...............

stmt := 'update statement2 ';

update ....................

stmt := 'insert statement';

insert into.............

....................

exception

when others then

dbms_output.put_line(stmt||'   '||sqlerrm);

end;

/

chethan

  • Nov 16th, 2006
 

i think u can use show error option and come to know the error........

  Was this answer useful?  Yes

madhaviguthula

  • Mar 6th, 2007
 

That you need to manipulate programtically. Like you can declare a variable called some counter. And after each statement u increase the value of that counter variable. In the exception block u can conditionally print the error msg on your own as you know the value of the variable and in which sql statement u got the exception.

  Was this answer useful?  Yes

Teju_hassan

  • Apr 19th, 2011
 

The best way i feel is to have a output statement after each DML so that we can trace it easily

update table1
set column1='value1'
where column2='condition 2'

commit;
 DBMS_OUTPUT.PUT_LINE(table 1 update completed);

update table2
set column2='value2'
where column2='condition 3'

commit;
DBMS_OUTPUT.PUT_LINE(table 2 update completed);

If any error comes then the output statement after that update will not be
present in the log

  Was this answer useful?  Yes

BakulPatel

  • May 7th, 2011
 

To simplify  Remove exception block. Oracle will show line number with error thrown

Other method:

begin

location :=1;

 dml1

location :=2;
 dml2

exception
    when others than
        dbms_output('error at location ' || location || sqlerrm);
end;

  Was this answer useful?  Yes

sandeshshinde000

  • Jun 4th, 2011
 

begin

  DML 1
  '
  '
  '
  '
  '
  DML 10

exception
when others than
dbms_output('error at location '||dbms_utility.format_error_backtrace);
end;

dbms_utility.format_error_backtrace gives line number at which error has occured and its reason also.

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