[icinga-checkins] icinga.org: icinga-core/r1.2: idoutils: fix after core restart/ reload on oracle db ORA-01403: no data found #1059

git at icinga.org git at icinga.org
Mon Jan 17 15:20:20 CET 2011


Module: icinga-core
Branch: r1.2
Commit: 09c06e41e99a48c8c5bbc098c3660491d81e58c0
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=09c06e41e99a48c8c5bbc098c3660491d81e58c0

Author: Michael Friedrich <michael.friedrich at univie.ac.at>
Date:   Mon Jan 17 15:20:04 2011 +0100

idoutils: fix after core restart/reload on oracle db ORA-01403: no data found #1059

tracked down to unixts2date when the date calculation returns
no data for whatever reason. normally this should have been
added long time ago, but here it is ...

other exceptions will still raise, but no_data_found is just
returning a zero datetime string, so that the application
will have to figure out theirselves what to do in that case.

idoutils will just insert the zero value, and let the application
decide then.

thx to Ronny for the ideas on fixing it.

fixes #1059

---

 Changelog                                          |    1 +
 module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql |   41 ++++++++++++++++++--
 module/idoutils/db/oracle/oracle.sql               |    2 +
 3 files changed, 40 insertions(+), 4 deletions(-)

diff --git a/Changelog b/Changelog
index 4a5342d..2ff0e40 100644
--- a/Changelog
+++ b/Changelog
@@ -50,6 +50,7 @@ FIXES
 * idoutils: fix solaris <= lacks asprintf (Julian Wiesner) #1048
 * idoutils: fix unfreed oci handles in prepared statements #1093
 * idoutils: fix libdbi 0.8.3 deprecated functions dbi_result_get_(u)long #559
+* idoutils: fix after core restart/reload on oracle db ORA-01403: no data found #1059
 
 
 1.2.1 - 25/10/2010
diff --git a/module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql b/module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql
index dcba97f..4c11744 100644
--- a/module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql
+++ b/module/idoutils/db/oracle/oracle-upgrade-1.2.2.sql
@@ -1,11 +1,9 @@
 -- -----------------------------------------
--- upgrade path for Icinga IDOUtils 1.0.3
+-- upgrade path for Icinga IDOUtils 1.2.2
 --
 -- add index for statehistory
 -- -----------------------------------------
--- Copyright (c) 2010 Icinga Development Team (http://www.icinga.org)
---
--- Initial Revision: 2010-11-04 Michael Friedrich <michael.friedrich(at)univie.ac.at>
+-- Copyright (c) 2010-2011 Icinga Development Team (http://www.icinga.org)
 --
 -- Please check http://docs.icinga.org for upgrading information!
 -- -----------------------------------------
@@ -16,3 +14,38 @@
 
 CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on statehistory(instance_id, object_id, state_type, state_time);
 
+-- -----------------------------------------
+-- fix NO_DATA_FOUND exception
+-- -----------------------------------------
+
+-- set escape character
+SET ESCAPE \
+
+-- --------------------------------------------------------
+-- unix timestamp 2 oradate function
+-- --------------------------------------------------------
+
+CREATE OR REPLACE FUNCTION unixts2date( n_seconds   IN    PLS_INTEGER)
+        RETURN    DATE
+IS
+        unix_start  DATE    := TO_DATE('01.01.1970','DD.MM.YYYY');
+        unix_max    PLS_INTEGER  := 2145916799;
+        unix_min    PLS_INTEGER     := -2114380800;
+
+BEGIN
+
+        IF n_seconds > unix_max THEN
+                RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too large for 32 bit limit' );
+        ELSIF n_seconds < unix_min THEN
+                RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too small for 32 bit limit' );
+        ELSE
+                RETURN unix_start + NUMTODSINTERVAL( n_seconds, 'SECOND' );
+        END IF;
+
+EXCEPTION
+        WHEN NO_DATA_FOUND THEN
+                RETURN TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
+        WHEN OTHERS THEN
+                RAISE;
+END;
+/
diff --git a/module/idoutils/db/oracle/oracle.sql b/module/idoutils/db/oracle/oracle.sql
index b3a0711..7c8bc2f 100644
--- a/module/idoutils/db/oracle/oracle.sql
+++ b/module/idoutils/db/oracle/oracle.sql
@@ -57,6 +57,8 @@ BEGIN
         END IF;
 
 EXCEPTION
+        WHEN NO_DATA_FOUND THEN
+                RETURN TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
         WHEN OTHERS THEN
                 RAISE;
 END;





More information about the icinga-checkins mailing list