Repository navigation
Expand file tree
/
Copy pathorafce--4.16--4.17.sql
More file actions
284 lines (251 loc) · 11.7 KB
/
Copy pathorafce--4.16--4.17.sql
File metadata and controls
284 lines (251 loc) · 11.7 KB
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
CREATE FUNCTION oracle.hextoraw(text)
RETURNS bytea
AS $$ SELECT pg_catalog.decode($1, 'hex') $$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION oracle.hextoraw(text) IS 'Converts a string of hexadecimal digits to a raw (bytea) value';
CREATE FUNCTION oracle.rawtohex(bytea)
RETURNS text
AS $$ SELECT pg_catalog.upper(encode($1, 'hex')) $$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION oracle.rawtohex(bytea) IS 'Converts a raw (bytea) value to a string of hexadecimal digits';
CREATE FUNCTION oracle.empty_clob()
RETURNS text
AS $$ SELECT ''::text $$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
COMMENT ON FUNCTION oracle.empty_clob() IS 'Returns an empty character LOB';
CREATE FUNCTION oracle.empty_blob()
RETURNS bytea
AS $$ SELECT ''::bytea $$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
COMMENT ON FUNCTION oracle.empty_blob() IS 'Returns an empty binary LOB';
CREATE FUNCTION oracle.from_tz(timestamp, text)
RETURNS timestamp with time zone
AS $$
-- A numeric offset ('+02:00') is applied as an interval so it follows the
-- ISO sign convention (east of UTC is positive), as Oracle does; passing it
-- straight to AT TIME ZONE as text would use PostgreSQL's inverted POSIX
-- sign. A region name ('Europe/Prague') is looked up in the zone database.
SELECT CASE
WHEN $2 ~ '^[+-]?[0-9]{1,2}:[0-9]{2}$' THEN $1 AT TIME ZONE ($2)::interval
ELSE $1 AT TIME ZONE $2
END
$$
LANGUAGE sql STABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION oracle.from_tz(timestamp, text) IS 'Interprets a timestamp as being in the given time zone and returns a timestamp with time zone';
-- UTL_RAW: Oracle's RAW/bytea manipulation package. CAST_TO_RAW / CAST_TO_VARCHAR2
-- use the database character set (like Oracle, which uses the database charset for
-- the VARCHAR2<->RAW casts). The bitwise operators follow Oracle's rule that, when
-- the operands differ in length, the operation stops at the end of the shorter one
-- and the unprocessed tail of the longer is appended to the result.
CREATE SCHEMA utl_raw;
CREATE FUNCTION utl_raw.cast_to_raw(text)
RETURNS bytea
AS $$ SELECT pg_catalog.convert_to($1, pg_catalog.current_setting('server_encoding')) $$
LANGUAGE sql STABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION utl_raw.cast_to_raw(text) IS 'Converts a value to a raw (bytea) value using the database character set';
CREATE FUNCTION utl_raw.cast_to_varchar2(bytea)
RETURNS text
AS $$ SELECT pg_catalog.convert_from($1, pg_catalog.current_setting('server_encoding')) $$
LANGUAGE sql STABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION utl_raw.cast_to_varchar2(bytea) IS 'Converts a raw (bytea) value to a value using the database character set';
CREATE FUNCTION utl_raw.length(bytea)
RETURNS integer
AS $$ SELECT pg_catalog.length($1) $$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION utl_raw.length(bytea) IS 'Returns the number of bytes in a raw (bytea) value';
CREATE FUNCTION utl_raw.substr(bytea, integer, integer DEFAULT NULL)
RETURNS bytea
AS $$
DECLARE
total integer;
pos integer := coalesce($2, 0);
len integer;
BEGIN
-- Oracle UTL_RAW.SUBSTR is 1-based; a negative position counts from the end,
-- a position of 0 or NULL is taken as 1, and an omitted or NULL length runs
-- to the end of the value. Oracle raises VALUE_ERROR (ORA-06502) for a NULL
-- raw, a length below 1, and a range that is not wholly inside the value.
IF $1 IS NULL THEN
RAISE EXCEPTION USING
ERRCODE = 'null_value_not_allowed',
MESSAGE = 'argument ''r'' must not be NULL';
END IF;
total := pg_catalog.length($1);
IF pos = 0 THEN
pos := 1;
ELSIF pos < 0 THEN
pos := total + pos + 1;
END IF;
IF pos < 1 OR pos > total THEN
RAISE EXCEPTION USING
ERRCODE = 'invalid_parameter_value',
MESSAGE = 'argument ''pos'' is outside the value';
END IF;
len := coalesce($3, total - pos + 1);
IF len < 1 THEN
RAISE EXCEPTION USING
ERRCODE = 'invalid_parameter_value',
MESSAGE = 'argument ''len'' must be a number greater than 0';
END IF;
IF pos + len - 1 > total THEN
RAISE EXCEPTION USING
ERRCODE = 'invalid_parameter_value',
MESSAGE = 'argument ''len'' runs past the end of the value';
END IF;
RETURN substring($1 FROM pos FOR len);
END;
$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
COMMENT ON FUNCTION utl_raw.substr(bytea, integer, integer) IS 'Returns a portion of a raw (bytea) value';
CREATE FUNCTION utl_raw.concat(VARIADIC bytea[])
RETURNS bytea
AS $$ SELECT coalesce(pg_catalog.string_agg(part, ''::bytea), ''::bytea) FROM unnest($1) AS part $$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
COMMENT ON FUNCTION utl_raw.concat(VARIADIC bytea[]) IS 'Concatenates raw (bytea) values';
CREATE FUNCTION utl_raw.bit_and(bytea, bytea)
RETURNS bytea
AS 'MODULE_PATHNAME','orafce_utl_raw_bit_and'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION utl_raw.bit_and(bytea, bytea) IS 'Bitwise AND of two raw (bytea) values';
CREATE FUNCTION utl_raw.bit_or(bytea, bytea)
RETURNS bytea
AS 'MODULE_PATHNAME','orafce_utl_raw_bit_or'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION utl_raw.bit_or(bytea, bytea) IS 'Bitwise OR of two raw (bytea) values';
CREATE FUNCTION utl_raw.bit_xor(bytea, bytea)
RETURNS bytea
AS 'MODULE_PATHNAME','orafce_utl_raw_bit_xor'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
COMMENT ON FUNCTION utl_raw.bit_xor(bytea, bytea) IS 'Bitwise XOR of two raw (bytea) values';
GRANT USAGE ON SCHEMA utl_raw TO PUBLIC;
-- The USER_* views describe the objects the current user owns, so they are
-- restricted to the current schema. Without that restriction they described
-- every table in the database, PostgreSQL's own catalogs included.
CREATE OR REPLACE VIEW oracle.user_tab_columns AS
select table_name,
column_name,
data_type,
coalesce(character_maximum_length, numeric_precision) AS data_length,
numeric_precision AS data_precision,
numeric_scale AS data_scale,
is_nullable AS nullable,
ordinal_position AS column_id,
is_updatable AS data_upgraded,
table_schema
from information_schema.columns
where table_schema = current_schema();
CREATE OR REPLACE VIEW oracle.user_tables AS
select table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema = current_schema();
/*
* orafce--4.7--4.8.sql declared the string functions parallel safe, but it
* missed these five, so an installation created before 4.8 still excludes
* every query that uses them from parallel execution.
*/
ALTER FUNCTION plvstr.instr(text, text) PARALLEL SAFE;
ALTER FUNCTION plvstr.instr(text, text, int) PARALLEL SAFE;
ALTER FUNCTION plvstr.instr(text, text, int, int) PARALLEL SAFE;
ALTER FUNCTION plvstr.substr(text, int) PARALLEL SAFE;
ALTER FUNCTION plvstr.substr(text, int, int) PARALLEL SAFE;
/*
* orafce--3.21--3.22.sql qualified these references with pg_catalog, but the
* install script kept the unqualified bodies, so an installation that was
* created from an install script still resolves them through search_path.
*/
CREATE OR REPLACE FUNCTION oracle.sinh(float8)
RETURNS float8 AS
$$ SELECT (pg_catalog.exp($1) - pg_catalog.exp(-$1)) / 2; $$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION oracle.cosh(float8)
RETURNS float8 AS
$$ SELECT (pg_catalog.exp($1) + pg_catalog.exp(-$1)) / 2; $$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION oracle.last_day(TIMESTAMPTZ)
RETURNS TIMESTAMP
AS $$ SELECT (pg_catalog.date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day' + $1::time)::oracle.date; $$
LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
/*
* These functions reduce their timestamptz argument to a local date or a
* local time, so the result depends on the TimeZone setting and they must
* not be marked IMMUTABLE. PostgreSQL marks date(timestamptz) and
* date_trunc(text, timestamptz) STABLE for the same reason.
*/
ALTER FUNCTION oracle.add_months(timestamptz, int) STABLE;
ALTER FUNCTION oracle.last_day(timestamptz) STABLE;
ALTER FUNCTION oracle.months_between(timestamptz, timestamptz) STABLE;
ALTER FUNCTION oracle.next_day(timestamptz, int) STABLE;
ALTER FUNCTION oracle.next_day(timestamptz, text) STABLE;
ALTER FUNCTION oracle.round(timestamptz) STABLE;
ALTER FUNCTION oracle.round(timestamptz, text) STABLE;
ALTER FUNCTION oracle.trunc(timestamptz) STABLE;
ALTER FUNCTION oracle.trunc(timestamptz, text) STABLE;
/*
* These overloads take the substitution keyword from the session state set
* by plvsubst.setsubst() whenever the subst argument is absent or NULL, so
* they are not immutable either.
*/
ALTER FUNCTION plvsubst.string(text, text) STABLE;
ALTER FUNCTION plvsubst.string(text, text, text) STABLE;
ALTER FUNCTION plvsubst.string(text, text, text, text) STABLE;
ALTER FUNCTION plvsubst.string(text, text[]) STABLE;
ALTER FUNCTION plvsubst.string(text, text[], text) STABLE;
/*
* These function was marked as STABLE, should be IMMUTABLE.
*
* For consistency with orafce_concat2(varchar2, varchar2).
* The function is not really IMMUTABLE because it depends
* on orafce.varchar2_null_safe_concat. But without overwritting
* volatility, this functionality was useable. Overwriting volatility
* is known workaround with known risks.
*/
ALTER FUNCTION oracle.orafce_concat2(oracle.nvarchar2, oracle.nvarchar2) IMMUTABLE;
/*
* These bodies resolved upper(), encode(), trunc(), date_part(),
* translate(), convert_to(), convert_from(), current_setting(),
* string_agg() and unnest() through the caller's search_path, so anybody
* who could create objects in a schema the caller had in its search_path
* could change what they return. Qualify the calls with pg_catalog.
*/
CREATE OR REPLACE FUNCTION oracle.substr(numeric,numeric)
RETURNS text AS $$
SELECT oracle.substr($1::text,pg_catalog.trunc($2)::int);
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
CREATE OR REPLACE FUNCTION oracle.substr(numeric,numeric,numeric)
RETURNS text AS $$
SELECT oracle.substr($1::text,pg_catalog.trunc($2)::int,pg_catalog.trunc($3)::int);
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
CREATE OR REPLACE FUNCTION oracle.substr(varchar,numeric)
RETURNS text AS $$
SELECT oracle.substr($1,pg_catalog.trunc($2)::int);
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
CREATE OR REPLACE FUNCTION oracle.substr(varchar,numeric,numeric)
RETURNS text AS $$
SELECT oracle.substr($1,pg_catalog.trunc($2)::int,pg_catalog.trunc($3)::int);
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
CREATE OR REPLACE FUNCTION oracle.subtract(oracle.date,oracle.date)
RETURNS double precision AS $$
SELECT pg_catalog.date_part('epoch', ($1::timestamp - $2::timestamp)/3600/24);
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
CREATE OR REPLACE FUNCTION oracle.rawtohex(bytea)
RETURNS text
AS $$ SELECT pg_catalog.upper(pg_catalog.encode($1, 'hex')) $$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION plvchr.stripped(str text, char_in text)
RETURNS varchar
AS $$ SELECT pg_catalog.translate($1, 'A'||$2, 'A'); $$
LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement, autoflush bool)
RETURNS bool
AS $$SELECT utl_file.put_line($1, $2::text, $3); $$
LANGUAGE SQL VOLATILE;
ALTER FUNCTION plvdate.add_bizdays(date, integer) VOLATILE;
ALTER FUNCTION plvdate.nearest_bizday(date) VOLATILE;
ALTER FUNCTION plvdate.next_bizday(date) VOLATILE;
ALTER FUNCTION plvdate.bizdays_between(date, date) VOLATILE;
ALTER FUNCTION plvdate.prev_bizday(date) VOLATILE;
ALTER FUNCTION plvdate.isbizday(date) VOLATILE;
ALTER FUNCTION dbms_random.initialize(integer) VOLATILE;
ALTER FUNCTION dbms_random.seed(integer) VOLATILE;
ALTER FUNCTION dbms_random.seed(text) VOLATILE;