[icinga-checkins] icinga.org: icinga-core/mfriedrich/core: 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:32 CET 2011


Module: icinga-core
Branch: mfriedrich/core
Commit: 50943fde539effe425fdad8367ee0837f766f911
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=50943fde539effe425fdad8367ee0837f766f911

Author: Michael Friedrich <michael.friedrich at univie.ac.at>
Date:   Mon Jan 17 15:15: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.3.0.sql |   38 ++++++++++++++++++++
 module/idoutils/db/oracle/oracle.sql               |    4 ++-
 3 files changed, 42 insertions(+), 1 deletions(-)

diff --git a/Changelog b/Changelog
index e0caab2..77fbab9 100644
--- a/Changelog
+++ b/Changelog
@@ -80,6 +80,7 @@ FIXES
 * idoutils: fix unfreed oci handles in prepared statements #1093
 * idoutils: increase debugoutput for conversion functions #1094
 * 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.3.0.sql b/module/idoutils/db/oracle/oracle-upgrade-1.3.0.sql
index d69bd3b..3fc4aa5 100644
--- a/module/idoutils/db/oracle/oracle-upgrade-1.3.0.sql
+++ b/module/idoutils/db/oracle/oracle-upgrade-1.3.0.sql
@@ -30,3 +30,41 @@ INSERT (id, name, version) VALUES ('1', 'idoutils', '1.3.0');
 
 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 e28ac6c..4ca7675 100644
--- a/module/idoutils/db/oracle/oracle.sql
+++ b/module/idoutils/db/oracle/oracle.sql
@@ -28,7 +28,7 @@
 --
 --
 -- initial version: 2008-02-20 David Schmidt
--- current version: 2010-07-26 Michael Friedrich <michael.friedrich(at)univie.ac.at>
+-- current version: 2011-01-17 Michael Friedrich <michael.friedrich(at)univie.ac.at>
 --
 -- -- --------------------------------------------------------
 
@@ -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