Ошибка ora 01476 - Не ошибается лишь тот, кто ничего не делает!

ORA-01476

ORA-01476: делитель эквивалентен нулю

Причина:

Вы пытаетесь разделить выражение на ноль.

Действие:

Поправьте выражение, затем повторите операцию снова.

I have a simple procedure in which I am using a cursor to fetch some valid items. Using these valid items, I am obtaining some values and doing a calculation on them. The code looks like this:

PROCEDURE p_loadanalyse
IS
qty_1       NUMBER;
qty_2      NUMBER := 0;
V_CALC      Number := 0;
Item_No_Rec valid_items%Rowtype;

CURSOR c_validitem
IS
SELECT DISTINCT item_no
From valid_items A;

Begin

For Item_No_Rec In C_Validitem

Loop

SELECT ROUND(SUM(Qty1),2)
INTO qty_1 -- FCST_QTY_PCD
FROM qty1_table
WHERE item_No        = item_No_rec.item_No;

SELECT SUM(Qty2)
INTO qty_2
FROM qty2_table
WHERE A.Item_No       = Item_No_Rec.Item_No;

V_CALC      := (QTY_1  /QTY_2)*100;

Dbms_Output.Put_Line('deviation' ||V_Deviation); 

END LOOP;
EXCEPTION 
WHEN NO_DATA_FOUND THEN
qty_1 := 0;
qty_2 := 0;
WHEN OTHERS THEN
DBMS_output.put_line('There is an error');
END;

The problem is obviously in the statement V_CALC := (QTY1 /QTY2)*100; when QTY2 is 0. So I try to handle it like:

 IF QTY_2 <> 0 THEN
  V_CALC      := (QTY_1  /QTY_2)*100;
 ELSE
  V_CALC      := 0;
 END IF;

This surprisingly hangs my SQL Developer when I try to compile the procedure. And gives a timeout occurred while trying to lock object after 10-11 mins. What I did was not rocket science and Oracle should have behaved the way I wanted, but instead I had to think and try all other ways of handling this divide by zero thingy like:

Including EXCEPTION WHEN ZERO_DIVIDE THEN QTY_2 := 0;
Using CASE instead of IF

After trying all permutations and combinations for 3 days, I am out of ideas and reasons and still with no output. Any hints or suggestions are welcome.

Источник

Время на прочтение
4 мин

Количество просмотров 3.5K

Этот пост навеян статьями Часть 1. Логирование событий в Oracle PL/SQL и Часть 2. Идентификация событий происходящих в Oracle PL/SQL. В первую очередь, как специалисту по performance tuning и troubleshooting, хотелось бы прокомментировать некоторые нюансы.

1. Уровни детализации логгирования

В показанной системе не хватает гибкости настройки логгирования: как уровня детализации, так и места, куда их выводить. Можно было позаимствовать функциональность из широко известных систем логгирования а-ля java.util.logging (SLF4j, log4j и его вариации для других языков/систем, и тд), гибкую настройку для какого кода с какого уровня сообщений и куда их сохранять. Например, в том же log4plsql можно настроить вывод и в alert.log, и в trace file (с помощью `dbms_system.ksdwrt()`)

2. Пользовательские ошибки и сообщения

Из самой внутренней системы ошибок Оракл можно было позаимствовать использование UTL_LMS.FORMAT_MESSAGE. Кстати, сами ошибки(и events) можно посмотреть с помощью sys.standard.sqlerrm(N):

SQL> select sys.standard.sqlerrm(-1476) errmsg from dual;

ERRMSG
-------------------------------------
ORA-01476: divisor is equal to zero

Примеры: err_by_code.sql, trace_events.sql

Кроме того, я, конечно, понимаю, что не все ошибки можно предусмотреть, но все-таки считаю, что их надо добавлять в exception handler после того как они уже были отловлены. Это будет полезно как минимум при последующих изменениях логики и будет проще видеть нестандартные или непредусмотренные ранее ситуации.

3. Что же делать в случае незалоггированных ошибок

Естественно, может случиться так, что существующая система логгирования не регистрирует какие-то неординарные ошибки, или даже ее совсем нет в базе. Тут могут быть полезны триггеры `after servererror on database/schema`. Простой минимальный пример.

К сожалению, для полноценного и срочного решения неординарных проблем, ни системы логгирования, ни таких триггеров, может быть недостаточно, и тут на помощь приходит вся мощь самой встроенной системы событий Oracle.

Например, недавно Nenad Noveljic расследовал проблему c «TNS-12599: TNS:cryptographic checksum mismatch» для чего ему нужно было получить callstack:

К счастью, помимо использованного у него в статье «ERRORSTACK», есть еще большой список «ACTIONS», включающий в себя и «CALLSTACK»:

В этой команде 12599 — это номер события(event), callstack — инструктирует сделать дамп call стека, level 2 — указывает вывести еще и аргументы функций, lifetime 1 — только один раз.

Более подробно об этом у Tanel Poder с примерами:

http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc
https://tanelpoder.com/2010/06/23/the-full-power-of-oracles-diagnostic-events-part-2-oradebug-doc-and-11g-improvements/

Мало того, как сам Танел и посоветовал, можно еще воспользоваться и «trace()» для форматированного вывода shortstack():

Так что этим же мы можем воспользоваться этим для вывода callstack:

alter system set events '12599 trace("stack is: %n", shortstack())';

Или в более новом формате:

alter system set events 'kg_event[12599]{occurence: start_after 1, end_after 1} trace("stack is: %n", shortstack())';

Как вы видите, здесь я еще добавил фильтр на количество срабатываний: после первого выполнения и только 1 раз.

Покажу на примере «ORA-01476: divisor is equal to zero»:

alter system set events 'kg_event[1476]{occurence: start_after 1, end_after 1} trace("stack is: %n", shortstack())';

Здесь kg_event — это Kernel Generic event, 1476 — ORA-1476. После этого запускаем в своей сессии:

SQL> alter session set events 'kg_event[1476]{occurence: start_after 1, end_after 1} trace("stack is: %n", shortstack())';

Session altered.

SQL> select 1/0 x from dual;
select 1/0 x from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> select 1/0 x from dual;
select 1/0 x from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> select 1/0 x from dual;
select 1/0 x from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

И в трейсфайле получаем:

# cat ORA19_ora_12981.trc
Trace file /opt/oracle/diag/rdbms/ora19/ORA19/trace/ORA19_ora_12981.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
Build label:    RDBMS_19.9.0.0.0DBRU_LINUX.X64_200930
ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      b7c493c7f9b0
Release:        3.10.0-1062.12.1.el7.x86_64
Version:        #1 SMP Tue Feb 4 23:02:59 UTC 2020
Machine:        x86_64
Instance name: ORA19
Redo thread mounted by this instance: 1
Oracle process number: 66
Unix process pid: 12981, image: oracle@b7c493c7f9b0


*** 2021-05-08T14:12:27.000816+00:00 (PDB1(3))
*** SESSION ID:(251.9249) 2021-05-08T14:12:27.000846+00:00
*** CLIENT ID:() 2021-05-08T14:12:27.000851+00:00
*** SERVICE NAME:(pdb1) 2021-05-08T14:12:27.000855+00:00
*** MODULE NAME:(sqlplus.exe) 2021-05-08T14:12:27.000859+00:00
*** ACTION NAME:() 2021-05-08T14:12:27.000862+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-05-08T14:12:27.000865+00:00
*** CONTAINER ID:(3) 2021-05-08T14:12:27.000868+00:00

stack is: dbgePostErrorKGE<-dbkePostKGE_kgsf<-kgeade<-kgeselv<-kgesecl0<-evadiv<-kpofcr<-qerfiFetch<-opifch2<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-__libc_start_main

Или, например, недавно я посоветовал использовать alter system set events 'trace[sql_mon.*] [SQL: ...] disk=high,memory=high,get_time=highres'; для выяснения причин, почему конкретный запрос не мониторится/сохраняется real-time SQL монитором (RTSM — Real Time SQL Monitor).

Получилось несколько сумбурно, в связи с недостатком времени, но на этом, пожалуй, закончу. Будут рад вопросам — задавайте, и я постараюсь их раскрыть отдельно.

Источник

ORA-01476: divisor is equal to zero

ORA-01476 is a very general error, and it comes when we try to divide any number by 0. In mathematics, DIVIDEND/0 has no meaning. Or we can simply say division by zero is undefined. In Oracle database when we try to divide by 0, Oracle Database throws an exception — «ORA-01476: divisor is equal to zero». For Example:

ankush@thavali> WITH T AS (
2 SELECT 1 DIVIDEND, 0 DIVISOR FROM DUAL
3 )
4 SELECT DIVIDEND/DIVISOR AS QUOTIENT FROM T;
SELECT DIVIDEND/DIVISOR AS QUOTIENT FROM T
*
ERROR at line 4:
ORA-01476: divisor is equal to zero

Now the question is how to properly handle ORA-01476 properly in SQL, and return NULL (undefined) in such cases. I use following very simple methods to avoid ORA-01476

1. Case When Then

Here we simply return NULL is DIVISOR is ZERO, otherwise we divide.

ankush@thavali> WITH T AS (
  2   SELECT 1 DIVIDEND, 0 DIVISOR FROM DUAL
  3  )
  4  SELECT
  5     CASE WHEN DIVISOR = 0
  6     THEN NULL
  7     ELSE DIVIDEND/DIVISOR
  8     END QUOTIENT
  9  FROM T;

  QUOTIENT
----------

2. NULLIF(expr1, expr2)

I prefer this over CASE WHEN THEN approach

NULLIF compares expr1 and expr2. If they are equal, then the function returns NULL. If they are not equal, then the function returns EXPR1. In Oracle Database any mathematical operation involving NULL is evaluated to NULL — DIVIDEND/NULL = NULL

ankush@thavali&gt; WITH T AS (
2 SELECT 1 DIVIDEND, 0 DIVISOR FROM DUAL
3 )
4 SELECT
5 DIVIDEND/NULLIF(DIVISOR ,0) QUOTIENT
6 FROM T;

QUOTIENT
----------

Источник

I’m creating an SQL Query under oracle 10g, the result should give me something like that :

----------------------------------------------------------------------------------
TEXT         VALUE1        VALUE2          VALUE3               VALUE4
---------------------------------------------------------------------------------
TEXT1        8795           5684        value1-value2          value3/value2*100
TEXT2        235             568            ...                   ...
TEXT3        125             23             ...                   ...
TEXT4        789             58             ...                   ...
TEXTN         0               0             ...                   ...

when i try to calculate VALUE4 column i get this error :

ORA-01476: le diviseur est égal à zéro
01476. 00000 -  "divisor is equal to zero"
*Cause:    
*Action:

I tried DECODE function but i stil have the same error, it’s the same for CASE

NB : VALUE1, VALUE2, VALUE3 and VALUE4 are calculated columns; VALUE1 = sum(col1)+sum(col2).. and so for other VALUE2 column.

Thanks and regards

asked Jun 10, 2013 at 11:19

archavinarchavin

3132 gold badges5 silver badges12 bronze badges

It all depends on whether you want to calculate a value if it would result in an infinite value or not. You can either ignore these particular instances and calculate on the remainder as Gordon’s answer suggests with:

case when value2 <> 0 then value3 / value2 * 100 end

Alternatively, if want to ignore them you can use NULLIF() to change the value to NULL and not calculate anything:

value3 / nullif(value2, 0) * 100

I do not understand your contention that this being a calculated column causes an issue. If it’s a virtual column then your table would never have created, as specified in the documentation a virtual column cannot refer to another by name.

If it’s not a virtual column then you can do this in a select statement as normal.

answered Jun 10, 2013 at 11:32

BenBen

51.5k36 gold badges127 silver badges148 bronze badges

In your code, where you have:

select value3/value2*100 as value4

You should have:

select (case when value2 <> 0 then value3/value2*100 end) as value4

Given your question:

select value1, value2, value3,
       (case when value2 is not null then value3 / value2 * 100 end) as value4
from (select value1, value2, (value1 - value2) as value3
      from . . .
     )

answered Jun 10, 2013 at 11:22

Gordon LinoffGordon Linoff

1.2m57 gold badges639 silver badges781 bronze badges

Источник