aboutsummaryrefslogtreecommitdiff
path: root/oracle/custom/custom.sql
diff options
context:
space:
mode:
authorBoris Kolpackov <boris@codesynthesis.com>2012-07-10 15:17:16 +0200
committerBoris Kolpackov <boris@codesynthesis.com>2012-07-10 15:17:16 +0200
commit6b8def06796d1e4fc9e6e7e75ce59bccf6899261 (patch)
treef7d440fda64cf07549173cefd5d45a92bf1845d6 /oracle/custom/custom.sql
parent7ba8d1469e331f07040352c069cf7b9d24ac57a3 (diff)
Add support for custom database type mapping
New pragma qualifier, map, and specifiers: as, to, from. New tests: <database>/custom.
Diffstat (limited to 'oracle/custom/custom.sql')
-rw-r--r--oracle/custom/custom.sql61
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;