Oracle Pl Sql Loop Exception Continue
Question: What is a PL/SQL exception? Why are PL/SQL exceptions important and how do I define a PL/SQL exception?
Answer (by Dr, Hall) Exceptions are like flags that are raised when a predefined event occurs, normally error conditions. Most programming languages "throw" exceptions but PL/SQL "raises" exceptions. Whether thrown or raised, the meaning is the same. When an exception is raised, program execution stops and jumps to the nearest exception handler. If the exception handler catches the exception, program execution resumes at the point right after the exception handler code.
Program execution never returns to the code that raised the exception unless the module is subsequently re-executed. If there is no exception handler in the module that raised the exception, execution returns to the calling block's exception handler. This continues until the exception is handled or the exception jumps out of the PL/SQL module and the exception is passed to the calling application (such as SQL*Plus or a script). Since none of the modules that we have presented so far have exception handlers, all exceptions are passed back to SQL*Plus which then displayed the exception along with the error messages to the user. Every PL/SQL block can have an optional exception handler.
SQL> declare
2 n_1 number := 5;
3 n_2 number := 0;
4 begin
5 n_1 := n_1/n_2; -- divide by zero
6 dbms_output.put_line(n_1);
7 end;
8 /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5
In the example above there is an exception raised because the code tries to divide by zero. Since the block has no exception handler, the exception is passed back to SQL*Plusto handle. An exception handler or exception code is placed at the end of a block before the END clause . If there is no exception raised, the exception code is jumped and not executed.
begin
----- Code goes here
exception
----- Exception code goes here
end;
The exception code follows the format:
when <exception> then <handle code>;
when others then <handle code>;
The OTHERS optionwill catch all exceptions that are not handled above the OTHERS clause. As with the CASE statement, an exception will be handled by the first WHEN clause that matches the exception, as show below.
SQL> declare You Divided By Zero
2 n_1 number := 5;
3 n_2 number := 0;
4 begin
5 n_1 := n_1/n_2; -- divide by zero
6 dbms_output.put_line(n_1);
7 exception
8 when ZERO_DIVIDE
9 then dbms_output.put_line('You Divided By
Zero');
10 end;
11 /
Line 5 raises the exception when the division by zero occurs. Line 6 is jumped as the exception moves execution to the exception handler starting at line 7. Lines 8 and 9 actually handle the exception. Once the exception is handled, execution resumes at line 10, where the block ends.
As stated earlier, if an exception is not handled, it will fall through to the next higher or calling block's exception handler. This is shown in the example below where two functionsare created, both with errors. The first will raise a CASE_NOT_FOUNDexception on certain values. The second divides by zero.
SQL> create or replace function bad_convert
2 (n_number IN number)
3 return varchar2
4 as
5 begin
6 case n_number
7 when 1 then return 'one';
8 when 2 then return 'two';
9 when 3 then return 'three';
10 end case;
11 end;
12 /
Function created.
SQL> create or replace function divide_by_zero
2 (n_1 IN number)
3 return number
4 as
5 n_2 number := 0;
6 begin
7 n_2 := n_1/n_2; -- divide by zero
8 return n_2;
9 end;
10 /
Function created.
Neither of the functionsabove contains an exception handler. The PL/SQL blockbelow will call these functions and cause an exception.
SQL> declare
2 v_result varchar2(100);
3 v_numb number;
4 begin
5 -- buggy code, encase in a block
6 begin
7 for i in 1 .. 6 loop
8 dbms_output.put_line(bad_convert(i));
9 end loop;
10 exception
11 when INVALID_NUMBER
12 then dbms_output.put_line('Invalid Number
Exception');
13 when ZERO_DIVIDE
14 then dbms_output.put_line('Divide By Zero
Exception');
15 end;
16
17 -- more buggy code
18 begin
19 v_numb := divide_by_zero(25);
20 end;
21
22 exception
23 when others
24 then dbms_output.put_line('Caught at the
End');
25 end;
26 /
one
two
three
Caught at the End
The code begins to loop on line 7 but a CASE_NOT_FOUND exception is raised by the bad_convert function on line 8 when the loop index i equals 4. The exception is not handled in the function so the program execution instead jumps out of the function to the exception handler for the calling block which is line 10. This handler does not handle the CASE_NOT_FOUND exception so execution jumps to the outer block's exception handler at line 22.
This handler catches all exceptions with the OTHERS clause. Execution resumes at line 25 which is the end of the block. Notice that the procedure ended successfully. Since the exception was handled, SQL*Plus does not see the exception and instead sees the module end normally. The best place to handle the exception is normally in the offending block. A corrected version below now handles the exception.
SQL> create or replace function bad_convert
2 (n_number IN number)
3 return varchar2
4 as
5 begin
6 case n_number
7 when 1 then return 'one';
8 when 2 then return 'two';
9 when 3 then return 'three';
10 end case;
11 exception
12 when CASE_NOT_FOUND
13 then return 'Bad Conversion';
14 end;
15 /
Function created.
SQL> declare
2 v_result varchar2(100);
3 v_numb number;
4 begin
5 -- buggy code, encase in a block
6 begin
7 for i in 1 .. 6 loop
8 dbms_output.put_line(bad_convert(i));
9 end loop;
10 exception
11 when INVALID_NUMBER
12 then dbms_output.put_line('Invalid Number
Exception');
13 when ZERO_DIVIDE
14 then dbms_output.put_line('Divide By Zero
Exception');
15 end;
16
17 -- more buggy code
18 begin
19 v_numb := divide_by_zero(25);
20 end;
21
22 exception
23 when others
24 then dbms_output.put_line('Caught at the
End');
25 end;
26 /
one
two
three
Bad Conversion
Bad Conversion
Bad Conversion
Caught at the End
PL/SQL procedure successfully completed.
The function bad_conversion now handles the exception and we can see this in the output above. But there is still an exception caused by the divide_by_zero function. The example below handles the divide by zero exception in the calling block.
SQL> declare
2 v_result varchar2(100);
3 v_numb number;
4 begin
5 -- buggy code, encase in a block
6 begin
7 for i in 1 .. 6 loop
8 dbms_output.put_line(bad_convert(i));
9 end loop;
10 end;
11
12 -- more buggy code
13 begin
14 v_numb := divide_by_zero(25);
15 exception
16 when INVALID_NUMBER
17 then dbms_output.put_line('Invalid
Number');
18 when ZERO_DIVIDE
19 then dbms_output.put_line('Divide By
Zero');
20 end;
21
22 exception
23 when others
24 then dbms_output.put_line('Caught at the
End');
25 end;
26 /
one
two
three
Bad Conversion
Bad Conversion
Bad Conversion
Divide By Zero
The exception is handled in the calling block at line 18. The final exception routine is not executed as all exceptions are already handled. The block execution ends normally. Now that we know the basics, let's take a closer look at defining and raising exception in PL/SQL.
 | Get the Complete |
alcantaracyricionsien.blogspot.com
Source: http://dba-oracle.com/t_pl_sql_exception_define.htm
0 Response to "Oracle Pl Sql Loop Exception Continue"
ارسال یک نظر