Home » SQL & PL/SQL » SQL & PL/SQL » Can we use WHEN OTHERS exception as first (Oracle 11g)
Can we use WHEN OTHERS exception as first [message #686027] Fri, 27 May 2022 11:48 Go to next message
rkhatiwala
Messages: 178
Registered: April 2007
Senior Member
Hi,
Can WHEN_OTHERS be used as the first exception in the EXCEPTION part of the block? Or does it have to be the last one?

Thanks,
RK
Re: Can we use WHEN OTHERS exception as first [message #686028 is a reply to message #686027] Fri, 27 May 2022 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You could use it at first but then the other ones will never be executed.
But, in fact, you should NEVER use it, read WHEN_OTHERS.

Re: Can we use WHEN OTHERS exception as first [message #686030 is a reply to message #686028] Mon, 30 May 2022 03:52 Go to previous message
Littlefoot
Messages: 21802
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, you can't use it as the first exception handler, even if you wanted to:

SQL> declare
  2    i number;
  3  begin
  4    i := 1 / 0;
  5  exception
  6    when others      then dbms_output.put_line('Others');
  7    when zero_divide then dbms_output.put_line('Divide by zero');
  8  end;
  9  /
  when others      then dbms_output.put_line('Others');
  *
ERROR at line 6:
ORA-06550: line 6, column 3:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated


SQL>

As Oracle says, it must be last:
SQL> declare
  2    i number;
  3  begin
  4    i := 1 / 0;
  5  exception
  6    when zero_divide then dbms_output.put_line('Divide by zero');
  7    when others      then dbms_output.put_line('Others');
  8  end;
  9  /
Divide by zero

PL/SQL procedure successfully completed.

SQL>
Previous Topic: Help me for a SQL query
Next Topic: Group by department name, employee
Goto Forum:
  


Current Time: Mon Feb 26 15:41:57 CST 2024