1 files changed, 61 insertions, 0 deletions
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;