rp0428 wrote:My point was that PL/SQL does that automatically. We don't have to.
If you have multiple blocks that should be handled differently you can use a SAVEPOINT after the first block and then if the second block causes an exception you can either rollback to the SAVEPOINT (which saves the work the first block did) or rollback the entire transaction.
Steve Cosner wrote:I interpret Tom Kyte's statements very differently.
Looking at Tom Kyte's statement:A when others is almost always a BUG unless it is immediately followed by a RAISEI would take a contrary position: A when others can be used to gracefully report an unexpected error.
Writing WHEN OTHERS THEN RAISE should never be used...
Please explain where you got this 'roll back the block' stuff and why you think Oracle will roll anything back just because an exception is raised.
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 atomicity extends to anonymous blocks as well.And a bit further down:
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.Scrolling further:
I consider virtually all code that contains a WHEN OTHERS exception handler that does not also include a RAISE or RAISE_APPLICATION_ERROR to reraise the exception to be a bug. It silently ignores the error, and it changes the transaction semantics.In other words, "doubly dangerous" as I said above.
BluShadow wrote:Who ever said that? Where did rp claim I said that?
I think the confusion here is that rp is simply making the point that, just because you issue a RAISE in an exception block, it doesn't necessarily mean that the transaction will automatically be rolled back.
Yes, there may be some implicit rollbacks depending on the environment and level of call being madeSo we agree. I'm waiting to see if rp agrees with us.
but in a regular call tree within an application it's good to be clear that RAISE does not cause a rollback itself.RAISE does not cause a transaction-level rollback.
The PL/SQL block that inserted the second row got rolled back. The transaction as a whole did not get rolled back.
SQL> CREATE TABLE T(N NUMBER); table T created. SQL> insert into t values(1); 1 rows inserted. SQL> BEGIN INSERT INTO T VALUES(2); RAISE no_data_found; END; / Error starting at line 5 in command: BEGIN INSERT INTO T VALUES(2); RAISE no_data_found; END; Error report: ORA-01403: no data found ORA-06512: at line 3 01403. 00000 - "no data found" *Cause: *Action: SQL> commit; committed. SQL> select * from t; N - 1
Dave Rabone wrote:Well, my second test case doesn't even have an "original exception" - but so what?
Splitting hairs I suppose, but a RAISE doesn't cause the statement level rollback, the original exception does, unless it is trapped by an exception handler and not re-raised.
See, the prior statement was not rolled back because I told the P/SQL engine to ignore the exception.
SQL> begin insert into t values(1); insert into t values(1); exception when others then null; end; / anonymous block completed SQL> select * from t; N ---------- 1
That clearly shows one statement succeeding and the other one being rolled back. The reason atomicity is maintained is because the second statement, having been rolled back is effectively removed from the transaction. The defintion of atomicity was
SQL> begin insert into t values(1); insert into t values(1); exception when others then null; end; / anonymous block completed SQL> select * from t; N ---------- 1 See, the prior statement was not rolled back because I told the P/SQL engine to ignore the exception.
Now let's call that procedure and see what result we get
create or replace procedure p1 as begin insert into t values(3); insert into t values('x'); end; /
The first INSERT in the procedure was successful even though the second raised an exception.
begin insert into t values (4); p1; exception when others then null; end; SQL> select * from t; N ---------- 4 3
Both inserts are rolled back because the NO_DATA_FOUND exception was never handled.
SQL> begin 2 begin 3 insert into t values (1); 4 begin 5 insert into t values(2); 6 raise no_data_found; 7 end; 8 exception 9 when others then 10 raise ZERO_DIVIDE; 11 end; 12 exception 13 when no_data_found then 14 -- when zero_divide then 15 null; 16 end; 17 / begin * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 10 ORA-01403: no data found SQL> select * from t; no rows selected
rp0428 wrote:Good grief RP, you draw strange conclusions. All the code I write, and I would think practically everyone else writes, is code that needs to be called somewhere. Do you write code for no good reason??? I write code that is first requested, specified and documented, before I even start one line of PL/SQL.
If you design your architecture and code based on where you think it is going be called then IMO your approach it flawed to begin with.
That kind of implementation isn't scalable or portable.How do you draw that conclusion?
>I work in a real-world development group. We develop reusable code wherever it is possible. But the truth of the matter is, when we write code for a specific job or task, the reality is that 99% of the time, it will not ever be used by any other process.
When I write a package of processes that is designed to be called by non-database, and sometimes non-Oracle software
. . .
If the process is designed to be run by a SQL Plus process
So the code you write and run in SQL Plus isn't appropriate to be run by other non-database software. Even though the code itself may be perfectedly suited for its purpose you have to refactor the exception handling to run it by some other process.
That is the exact opposite of good software design.Just love the way you are so quick to blast away at environments you cannot relate to.
The first reason is that it is far too easy for someone who invokes this procedure to ignore the return code.My client-side process wouldn't pass the first quality assurance test if it ignored return codes and error text messages. On top of that, any return code or message would require the top-level process to execute a Rollback.
It's hard to believe that is really what you do or intended to say. Please provide an example of that and explain why you think the same code base should be duplicated with different exception handling if it will be called by two different external processes.The handling of exceptions and errors is sometimes 90% of the work required to write a high-quality end-user process. Oracle's default exception handling and error messages might be good enough for those reusable low-level internal pieces of code. But they are only a part of the entire spectrum of tools required to create an end-user application.
How do you draw that conclusion?
That kind of implementation isn't scalable or portable.
Rahul_India wrote:Ok, let's make it simple... in terms of your original question...
I am all confused now.
Rahul_India wrote:Your original question asked for a proof of an opinion , albeit a respected opnion from a highly-esteemed practitioner in the Oracle community. Consequently what you got was a slew of interpretation.
I am all confused now.