I am trying to run a block of code in oracle and it exits the block if it throws some error. How do I overcome it? I tried adding some exceptions and it didn’t work. Below is the code and its error.
> begin for i in (
> select constraint_name , table_name
> from user_constraints
> where constraint_type ='C'
> and status = 'ENABLED' ) LOOP dbms_utility.exec_ddl_statement('alter table "'|| i.table_name || '"
> disable constraint ' || i.constraint_name); end loop; end; /
and it throws the following error which should be ignored and the block should continue executing.
begin
*
ERROR at line 1:
ORA-30671: cannot modify NOT NULL constraint on an identity column
ORA-06512: at "SYS.DBMS_UTILITY", line 574
ORA-06512: at line 9
I tried adding Exceptions which didn’t work well.
asked Sep 21, 2015 at 12:10
2
You should use, nested begin-end block here, the exception handling been inside the INNER block.
begin for i in (
select constraint_name , table_name
from user_constraints
where constraint_type ='C'
and status = 'ENABLED' )
LOOP
BEGIN
dbms_utility.exec_ddl_statement('alter table "'|| i.table_name || '"disable constraint ' || i.constraint_name);
EXCEPTION
WHEN OTHERS THEN
/* Your exception handing here. */
NULL;
END;
end loop;
end;
/
answered Sep 21, 2015 at 12:16
Sometimes application queries come with the specific hint, which may impact your database performance. And it is difficult to find and remove these hints from each query.
Oracle provided an undocumented hidden parameter, _optimizer_ignore_hint. If this parameter is set to true, Then it will ignore the hints mentioned in the SQL queries.
DEFAULT VALUE OF _optimizer_ignore_hint is FALSE.
Let’s run a test, to see how it behaves.
EXAMPLE:
Parameter is set to FALSE:(DEFAULT)
SQL> show parameter _optimizer NAME TYPE VALUE ------------------ ----------- ------------- _optimizer_ignore_hints boolean FALSE
Execute a query with a HINT.
SQL> select /*+ FULL(TEST5) */ count(*) from TEST5 where owner='SYS';
COUNT(*)
----------
42814
Execution Plan
----------------------------------------------------------
Plan hash value: 529722805
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 480 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| TEST5 | 1184 | 8288 | 480 (1)| 00:00:01 | ------- >>>> .FULL SCAN DUE TO FULL HINT
----------------------------------------------------------------------------
We can see, as we have used a FULL hint, TABLE ACCESS FULL is used. That does not change the query behavior.
Now, lets set it to TRUE and re-run the same query.
The parameter is set to TRUE:
SQL> alter session set "_optimizer_ignore_hints"=TRUE ;
Session altered.
SQL> select /*+ FULL(TEST5) */ count(*) from TEST5 where owner='SYS';
COUNT(*)
----------
42814
Execution Plan
----------------------------------------------------------
Plan hash value: 1056693648
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| TE | 1184 | 8288 | 3 (0)| 00:00:01 | ------ >>>> IGNORED FULL HINT, WENT FOR INDEX .
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Now despite using a FULL hint, it was ignored because of the parameter _optimizer_ignore_hints.
NOTE – Don’t use in production database without proper testing, Because it will disable all the hints used in sql queries, which might be recommended by your Application.
EXCEPTION:
One interesting point, we observed is that, this parameter is not having any impact on PARALLEL hint. I.e even if we set this to TRUE, PARALLEL hint will work as expected.
SQL> show parameter _optimizer
NAME TYPE VALUE
------------------ ----------- -------------
_optimizer_ignore_hints boolean TRUE
SQL> select /*+ parallel(4) */ count(*) from TEST5;
COUNT(*)
----------
107708
Execution Plan
----------------------------------------------------------
Plan hash value: 4160549356
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 107K| 133 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TEST5 | 107K| 133 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of hint
While I agree that 99% of the time it is bad practice to silently ignore exceptions without at least logging them somewhere, there are specific situations where this is perfectly acceptable.
In these situations, NULL is your friend:
[...]
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Two typical situations where ignoring exceptions might be desirable are:
1) Your code contains a statement which you know will fail occasionally and you don’t want this fact to interrupt your program flow.
In this case, you should enclose you statement in a nested block, as the following example shows:
CREATE OR REPLACE PROCEDURE MY_PROCEDURE()
IS
l_empoyee_name EMPLOYEES.EMPLOYEE_NAME%TYPE;
BEGIN
-- Catch potential NO_DATA_FOUND exception and continue
BEGIN
SELECT EMPLOYEE_NAME
INTO l_empoyee_name
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 12345;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END;
do_stuff();
EXCEPTION
WHEN OTHERS THEN
-- Propagate exception
RAISE;
END;
Note that PL/SQL generally does not allow for the On Error Resume Next type of exception handling known from Visual Basic, where all exceptions are ignored and the program continues to run as if nothing happened (see On error resume next type of error handling in PL/SQL oracle). You need to explicitly enclose potentially failing statements in a nested block.
2) Your procedure is so unimportant that ignoring all exceptions it throws will not affect your main program logic. (However, this is very rarely the case and can often result in a debugging nightmare in the long run)
BEGIN
do_stuff();
EXCEPTION
WHEN OTHERS THEN
-- Ignore all exceptions and return control to calling block
NULL;
END;
Обновлено
Вопрос:
Я пытаюсь запустить блок кода в oracle, и он выходит из блока, если он вызывает некоторую ошибку. Как мне его преодолеть? Я попытался добавить некоторые исключения, и это не сработало. Ниже приведен код и его ошибка.
> begin for i in (
> select constraint_name , table_name
> from user_constraints
> where constraint_type ='C'
> and status = 'ENABLED' ) LOOP dbms_utility.exec_ddl_statement('alter table "'|| i.table_name || '"
> disable constraint ' || i.constraint_name); end loop; end; /
и он выдает следующую ошибку, которую следует игнорировать, и блок должен продолжить выполнение.
begin
*
ERROR at line 1:
ORA-30671: cannot modify NOT NULL constraint on an identity column
ORA-06512: at "SYS.DBMS_UTILITY", line 574
ORA-06512: at line 9
Я попытался добавить Исключения, которые не работали хорошо.
Лучший ответ:
Вы должны использовать вложенный начальный блок здесь, обработка исключений находится внутри блока INNER.
begin for i in (
select constraint_name , table_name
from user_constraints
where constraint_type ='C'
and status = 'ENABLED' )
LOOP
BEGIN
dbms_utility.exec_ddl_statement('alter table "'|| i.table_name || '"disable constraint ' || i.constraint_name);
EXCEPTION
WHEN OTHERS THEN
/* Your exception handing here. */
NULL;
END;
end loop;
end;
/
Updated: ASR Pro
Improve your computer’s performance by clicking here to download the software.
Sometimes your system may display a message that Oracle SQL is ignoring errors. There can be many reasons for this error to appear.
While I understand that it’s bad 99% of the time to gracefully ignore exceptions, at least without visiting them anywhere, there are some unforeseen events when it’s perfectly acceptable.
Updated: ASR Pro
Is your computer running slow? Is it plagued with frustrating errors and problems? Then you need ASR Pro � the ultimate software for repairing and optimizing your Windows PC. With ASR Pro, you can fix any Windows issue with just a few clicks � including the dreaded Blue Screen of Death. Plus, the software will detect and resolve files and applications that are crashing frequently, so you can get back to work as quickly as possible. Don’t let your computer hold you back � download ASR Pro today!
[...]AN EXCEPTION IF OTHERS THEN ZERO;END;
1) Your code contains some kind of assertion that you know will get lost from time to time, and you don’t want the fact that the element exists to break your procedural flow.In this case, you mustYou include your business report in a nested block, as shown in the following example:
What happens when ignore=N in SQL Server?
If the table already exists plus IGNORE=n, errors will be reported as the table will be ignored without row insertion. Table-specific objects such as indexes, permissions, and restrictions are also not created.
CREATE OR REPLACE PROCEDURE MY_PROCEDURE()IS AN l_employee_name EMPLOYEE.EMPLOYEE_NAME%TYPE;TO BEGIN -- Catch a possible NO_DATA_FOUND exception, but continue TO BEGIN SELECT EMPLOYEE_NAME INTO l_employee_name BY EMPLOYEES WHERE EMPLOYEE_ID = 12345; AN EXCEPTION IF NO_DATA_FOUND THEN ZERO; IF OTHERS THEN LIFT UP; END; Do something();AN EXCEPTION IF OTHERS THEN -- throw an exception LIFT UP;END;
Note that PL/SQL generally does not allow, for example, the Resume on Error type, most of the exception handling operations known to Visual Basic where all exceptions are ignored and the program continues to run as if nothing happened. happened (see error type in Oracle PL/SQL). You should be able to explicitly include potentially erroneous declarations in a nested block.
Are syntax error hints ignored in Oracle?
Syntax error indicators are ignored. Oracle does not report errors in the hint, and since hints are inlined according to comments, syntax errors often occur and your hint attempt is not processed. If the tooltip name is misspelled, sometimes the tooltip is not ignored. Here we see a doubt with a misspelled hint name:
2) Your procedure is considered unimportant, so ignoring the exceptions it throws will not affect the actual logic of the main program. (However, this is oftenhappens very rarely and can often lead to a long debugging problem)
START Do something();AN EXCEPTION IF OTHERS THEN -- Ignore all exceptions and transfer control to the recovery block ZERO;END;- script name How NOT to handle exceptions in PL/SQL
- description This script comes up with a few variations on bad ideas when it comes to exception handling. And finish the climb with a good font example! If you don’t want to just watch the code, check out their YouTube playlist which covers everything taken from all of this stuff: https://www.youtube.com/playlist?list=PL0mkplxCP4ygg0C_z6I3aZ_w7_api2zq4.
- zoned General PL/SQL
- donor Stephen Flintstone Oracle
- Established Wednesday, January 25, 2017