blob: 3200e74647b4177378aea10243b1e14813ab2966 (
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;
|
