Resume next statement after exception. PL/SQL Oracle 9i
If I have a procedure like the one below that has an exception on statement2, like table does not exist, how do I resume so the next line, statement3, is executed? I realize I could probably have another exception block between statement2 and statement3, but I was hoping that there was a more elegant solution, that would allow it to resume. Thanks!
CREATE OR REPLACE PROCEDURE PROC IS
BEGIN
EXECUTE IMMEDIATE 'STATEMENT1';
EXECUTE IMMEDIATE 'STATEMENT2';
EXECUTE IMMEDIATE 'STATEMENT3';
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
audit_detail(MY AUDIT PACKAGE);
END PROC;
CREATE OR REPLACE PROCEDURE PROC IS
BEGIN
EXECUTE IMMEDIATE 'STATEMENT1';
EXECUTE IMMEDIATE 'STATEMENT2';
EXECUTE IMMEDIATE 'STATEMENT3';
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
audit_detail(MY AUDIT PACKAGE);
END PROC;
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just FYI, Oracle PL\SQL is *NOT* optimized to handle dynamic SQL like this. Yes, it is supported, and if you use a separate block for each statement as angellll suggested, it can work. But be aware that procedures like this do not scale well to large numbers of users, or to working with large volumes of data.
I realize, at times they can reduce the number of procedures that need to be written and maintained, but I wanted you to be aware of the disadvantages.
I realize, at times they can reduce the number of procedures that need to be written and maintained, but I wanted you to be aware of the disadvantages.
ASKER
Can you be more specific in reguards to why they're bad using large volumes of data? Is there a timeout?
No, this is not a timeout issue. It is related to the way that Oracle is designed to be a high-performacne database. One of the things that Oracle does to improve performance is to "cache" all SQL statements that get executed in the "shared_pool" area of the Oracle SGA in the server's memory. When Oracle sees the same statement being executed again that was executed recently, it can skip a lot of overhead, and quickly process the statement again. But when you use "execute immediate..." in PL\SQL you don't get that benefit.
Another big thing that you give up when you use "execute immediate" in PL\SQL is the "early binding" (at complie time) of the table and column names in a PL\SQL procedure to the database table and column names. In "normal" PL\SQL procedures with hard-coded (static) table and column names, this allows Oracle to execute them quickly because it knows the tables and columns are valid, that the user has permission to use them etc. When you use "execute immediate..." all of these "overhead" checks need to be done at runtime, so the performacne is slower.
Another big thing that you give up when you use "execute immediate" in PL\SQL is the "early binding" (at complie time) of the table and column names in a PL\SQL procedure to the database table and column names. In "normal" PL\SQL procedures with hard-coded (static) table and column names, this allows Oracle to execute them quickly because it knows the tables and columns are valid, that the user has permission to use them etc. When you use "execute immediate..." all of these "overhead" checks need to be done at runtime, so the performacne is slower.
ASKER