Локализация ошибок в процедурах

Alexey Polovinkin
Дата: 09.06.2006 14:08:47
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


есть нцать процедур, в которых нет возможность расставить EXCEPTION-блоки.
Периодически возникают в них ошибки.

Пробовал поискать по форуму но ничего не нашел подобного.
Пробовал повесить триггер типа: AFTER SERVERERROR

вот что получилось:
SQL> CREATE OR REPLACE TRIGGER scott_trap_error
  2    AFTER SERVERERROR ON SCOTT.SCHEMA
  3
  4  DECLARE
  5    PRAGMA AUTONOMOUS_TRANSACTION;
  6
  7    v_logdate DATE;
  8    v_logtext VARCHAR2(500);
  9
 10  BEGIN
 11
 12    v_logdate := SYSDATE;
 13    v_logtext := server_error(1);
 14
 15     INSERT INTO loging_schema_errors(logdate, logtext) VALUES(v_logdate, v_
ogtext);
 16     COMMIT;
 17  END;
 18  /

Trigger created.

SQL> CREATE OR REPLACE PROCEDURE error_proc IS
  2
  3    v_b VARCHAR2(500);
  4
  5  BEGIN
  6
  7  SELECT b
  8    INTO v_b
  9    FROM test_tbl1 t
 10   WHERE a = (SELECT a FROM test_tbl3 WHERE a = t.a -- test_tbl3 содержит дубликаты, поэтому подзапрос вернет 2 строки и вызовет ошибку);
 11
 12  END;
 13  /

Procedure created.

SQL> create table LOGING_SCHEMA_ERRORS
  2  (
  3    LOGDATE DATE not null,
  4    LOGTEXT VARCHAR2(500)
  5  );

Table created.

SQL> select * from LOGING_SCHEMA_ERRORS;

no rows selected
 -- SQL запрос
SQL> SELECT b
  2    FROM test_tbl1 t
  3   WHERE a = (SELECT a FROM test_tbl3 WHERE a = t.a);
ERROR:
ORA-01427: single-row subquery returns more than one row



no rows selected

SQL> select * from LOGING_SCHEMA_ERRORS;

LOGDATE  LOGTEXT
--------   -----------------------------------------------------------------
06.09.06  1427
 -- ошибка залогировалась

SQL> truncate table LOGING_SCHEMA_ERRORS;

Table truncated.

 -- теперь попробуем вызвать процедуру которая выполняет такой запрос
SQL> exec error_proc;
BEGIN error_proc; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCOTT.ERROR_PROC", line 7
ORA-06512: at line 1

SQL> select * from LOGING_SCHEMA_ERRORS;

no rows selected

Knowledge Xpert for PL/SQL

AFTER SERVERERROR triggers fire after an Oracle error is raised, unless the error is one of the following:

ORA-00600 Oracle internal error
ORA-01034 Oracle not available
ORA-01403 No data found
ORA-01422 Exact fetch returns more than requested number of rows
ORA-01423 Error encountered while checking for extra rows in an exact fetch
ORA-04030 Out-of-process memory when trying to allocate N bytes


Вопрос: Какие есть средства для того чтобы отловить ошибку в процедуре (локализовать ее)
Т.к. приложение выдает ошибку: [Error|SomeUnit.cpp|line|Execute()] ORA-01422 Exact fetch returns more than requested number of rows
dmidek
Дата: 09.06.2006 14:30:47
Алексей, прости за банальность.
Глаза...
Если я вижу - такую ошибку, то это SELECT.
Мне нужно просмотреть все SELECTы и выяснить, в каких из них такие ситуации возможны. В 90% все сразу становится ясно. В остальных 10% SELECTы запускаются из SQL например с HAVING или row_number > 1.
Очень ИМХО, быстрее чем искать средства, а потом бороться уже с этими средствами.
Мне помогает :)
andrey_anonymous
Дата: 09.06.2006 14:53:00
SELECT a, min(rowid), max(rowid), count(*) FROM test_tbl3 group by a having count(a)>1
Alexey Polovinkin
Дата: 09.06.2006 15:10:51
dmidek
Алексей, прости за банальность.
Глаза...
Если я вижу - такую ошибку, то это SELECT.
Мне нужно просмотреть все SELECTы и выяснить, в каких из них такие ситуации возможны. В 90% все сразу становится ясно. В остальных 10% SELECTы запускаются из SQL например с HAVING или row_number > 1.
Очень ИМХО, быстрее чем искать средства, а потом бороться уже с этими средствами.
Мне помогает :)

Так и делаю....
НО!
две схемы, порядка 50 таких процедур в каждой, по 500-2000 строк в каждой.
Процедура - сплошные select'ы - правила. Регулярно изменяются. Я замахаюсь искать. К том же я не помню что менялось а что нет и не факт что изменение в правиле вызвало ошибку, а не более старое правило. все зависит от данных.
ORA-01422 Exact fetch returns more than requested number of rows - не единственно возможная ошибка.
К тому же было бы хорошо настроить рассылку СМСок чтобы реагировать оперативно на них.

andrey_anonymous

SELECT a, min(rowid), max(rowid), count(*) FROM test_tbl3 group by a having count(a)>1


не совсем понял какое это имеет отношение к теме?
Alexey Polovinkin
Дата: 09.06.2006 15:14:43
andrey_anonymous
SELECT a, min(rowid), max(rowid), count(*) FROM test_tbl3 group by a having count(a)>1


Если это предложение искать дубликаты - то не подходит. Дубликаты по ключам и так отваливаются.
dmidek
Дата: 09.06.2006 15:16:38
Alexey Polovinkin
andrey_anonymous
SELECT a, min(rowid), max(rowid), count(*) FROM test_tbl3 group by a having count(a)>1


Если это предложение искать дубликаты - то не подходит. Дубликаты по ключам и так отваливаются.

Почему по ключам ?
По условиям в SELECT, которые могут спровоцировать TOO_MANY_ROWS
dmidek
Дата: 09.06.2006 15:19:42
Alexey Polovinkin

две схемы, порядка 50 таких процедур в каждой, по 500-2000 строк в каждой.
Процедура - сплошные select'ы - правила. Регулярно изменяются. Я замахаюсь искать.


А разве Вам имя процедуры не возвращается ?
andrey_anonymous
Дата: 09.06.2006 15:20:09
Alexey Polovinkin
не совсем понял какое это имеет отношение к теме?

SQL> create table ane_test(a number);

Table created

SQL> insert into ane_test values(100);

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT a, min(rowid), max(rowid), count(*) FROM ane_test group by a having count(a)>1;

         A MIN(ROWID)         MAX(ROWID)           COUNT(*)
---------- ------------------ ------------------ ----------

SQL> 
SQL> SELECT a FROM ane_test t
  2  WHERE a = (SELECT a FROM ane_test WHERE a = t.a);

         A
----------
       100

SQL> insert into ane_test values(100);

1 row inserted

SQL> SELECT a, min(rowid), max(rowid), count(*) FROM ane_test group by a having count(a)>1;

         A MIN(ROWID)         MAX(ROWID)           COUNT(*)
---------- ------------------ ------------------ ----------
       100 AALe5MADjAAIHufAAA AALe5MADjAAIHufAAB          2

SQL> 
SQL> SELECT a FROM ane_test t
  2  WHERE a = (SELECT a FROM ane_test WHERE a = t.a);

SELECT a FROM ane_test t
WHERE a = (SELECT a FROM ane_test WHERE a = t.a)

ORA-01427: single-row subquery returns more than one row

SQL> 
Alexey Polovinkin
Дата: 09.06.2006 15:41:10
Андрей, я очень хорошо понимаю причины возникновения такой ошибки.

Мне интересен сам факт - могу ли я установить источник ошибки в схеме.
(имя в процедуре и номер стоки) вместо того чтобы лопатить немеряное количество кода.

2dmidek: Потому что дубликаты и так не вставляются в таблицу из-за нарушения первичного ключа. А в подзапросе поля из первичного ключа (ну почти)
Alexey Polovinkin
Дата: 09.06.2006 15:43:27
Короче понял - придется все перековырять руками.