From 6b8def06796d1e4fc9e6e7e75ce59bccf6899261 Mon Sep 17 00:00:00 2001
From: Boris Kolpackov <boris@codesynthesis.com>
Date: Tue, 10 Jul 2012 15:17:16 +0200
Subject: Add support for custom database type mapping

New pragma qualifier, map, and specifiers: as, to, from. New tests:
<database>/custom.
---
 oracle/custom/custom.sql | 61 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 61 insertions(+)
 create mode 100644 oracle/custom/custom.sql

(limited to 'oracle/custom/custom.sql')

diff --git a/oracle/custom/custom.sql b/oracle/custom/custom.sql
new file mode 100644
index 0000000..3200e74
--- /dev/null
+++ b/oracle/custom/custom.sql
@@ -0,0 +1,61 @@
+/* This file contains custom type definitions and helper functions.
+ */
+
+SET FEEDBACK OFF;
+WHENEVER SQLERROR EXIT FAILURE;
+WHENEVER OSERROR EXIT FAILURE;
+
+-- @@ Temporary workaround: we cannot replace a type if there are
+--    tables that use it. So need to drop the tables first, then
+--    create/replace the type, and then create the tables.
+--
+--CREATE OR REPLACE TYPE Numbers AS VARRAY(100) OF NUMBER(10);
+
+BEGIN
+  BEGIN
+    EXECUTE IMMEDIATE 'CREATE TYPE Numbers AS VARRAY(100) OF NUMBER(10)';
+  EXCEPTION
+    WHEN OTHERS THEN
+      IF SQLCODE != -955 THEN RAISE; END IF;
+  END;
+END;
+/
+
+CREATE OR REPLACE FUNCTION string_to_numbers(in_str IN VARCHAR2) RETURN Numbers
+IS
+  ret          Numbers := Numbers();
+  s_pos        NUMBER := 1;
+  e_pos	       NUMBER := 0;
+BEGIN
+   IF in_str IS NOT NULL THEN
+     LOOP
+       e_pos := INSTR(in_str, ',', s_pos);
+       EXIT WHEN e_pos = 0;
+       ret.extend;
+       ret(ret.COUNT) := CAST(SUBSTR(in_str, s_pos, e_pos - s_pos) AS NUMBER);
+       s_pos := e_pos + 1;
+     END LOOP;
+     ret.extend;
+     ret(ret.COUNT) := CAST(SUBSTR(in_str, s_pos) AS NUMBER);
+   END IF;
+   RETURN ret;
+END;
+/
+
+CREATE OR REPLACE FUNCTION numbers_to_string(in_nums IN Numbers) RETURN VARCHAR2
+IS
+  ret          VARCHAR2(1500);
+BEGIN
+  IF in_nums.COUNT != 0 THEN
+    FOR i IN in_nums.FIRST .. in_nums.LAST LOOP
+      IF i != in_nums.FIRST THEN
+        ret := ret || ',';
+      END IF;
+      ret := ret || in_nums(i);
+    END LOOP;
+  END IF;
+  RETURN ret;
+END;
+/
+
+EXIT;
-- 
cgit v1.1