aboutsummaryrefslogtreecommitdiff
path: root/oracle/custom/custom.sql
blob: 94ff5f59bd00a43b4fdf13b34329cb44e5e99e4c (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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;