bad. It depends. Before you apply the tool
that is doing the partitioning, you need to
understand the goal for using partitioning in
the first place to see if it makes sense.
Code Listing 4: Missing RAISE or RAISE APPLICATION ERROR returning compilation warnings
SQL> alter session set
2 PLSQL_Warnings = 'enable:all'
3 /
Session altered.
WHY YOU REALLY WANT TO LET
EXCEP TIONS PROPAGATE
I’ve seen a programming pattern (antipattern
is probably more descriptive) that frequently
causes a large number of bugs in developers’
code. That programming pattern involves the
use of exception handling and many developers’ irrational fear of allowing an exception
to propagate out of their code. The fact is
that most exceptions should never be caught
in PL/SQL, or if they are, they should be
immediately reraised. However, in real life, I
see the opposite happening in many cases.
Developers often code something like this:
SQL> create or replace procedure some_proc( p_str in varchar2 )
2 as
3 begin
4 dbms_output.put_line( p_str );
5 exception
6 when others
7 then
8 -- call some log_error() routine
9 null;
10 end;
11 /
SP2-0804: Procedure created with compilation warnings
SQL> show errors procedure some_proc
Errors for PROCEDURE P:
procedure p ( …, return_code
in out number )
begin
return_code := 0;
…
exception
when others then
log_the_error;
return_code := - 1;
end;
LINE/COL ERROR
———————————— ————————————————————————————————————————————————————————————————————————————————————————————————————————
1/1 PLW-05018: unit SOME_PROC omitted optional AUTHID clause; default
value DEFINER used
6/8 PLW-06009: procedure "SOME_PROC" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
That is, they wrap all their code in a
WHEN OTHERS exception handler to catch
any error, log it using some generic routine,
and then output a return code. This is a very
wrong way to deal with exception handling—
for two main reasons.
The first reason is that it is far too easy
for someone who invokes this procedure
to ignore the return code. There is nothing
forcing the user to check the code, and it is
just too easy to forget—especially if you are
calling a procedure that “cannot fail” (and
anything that cannot fail will almost certainly
fail). Return codes are error-prone.
The second reason is far more important.
It has to do with the A in the ACID proper-
ties of relational databases. The A stands
for atomicity. Transactions are atomic in
Oracle Database, meaning that either all the
statements that constitute the transaction
are committed (made permanent) or all of
them are rolled back. This atomic protec-
tion is extended to individual statements as
well. Either a statement entirely succeeds,
or it is entirely rolled back. Note that I said
that the statement is rolled back. The failure
of one statement does not cause previously
executed statements to be rolled back.
(Their work is preserved and must either be
committed or rolled back.) This atomicity
extends to anonymous blocks as well.
SQL> begin
2 p;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-02290: check constraint
( OPS$TKYTE.SYS_C0018095) violated
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 2
SQL> create table t ( x int check
( x>0 ) );
Table created.
SQL> select from t;
no rows selected
SQL> create or replace procedure p
2 as
So, you have a procedure you know will
fail, and the second INSERT will always fail in
this case. Let’s see what happens if I run that
stored procedure:
As you can see, Oracle Database treated
the stored procedure call as an atomic
statement. The client submitted a block of
code—BEGIN P; END;—and Oracle Database
wrapped a SAVEPOINT around it. Because P
failed, Oracle Database restored the database
back to the point right before it was called.
