oraudb

本插件是基于orafce 的UDB定制版本，UDB在orafce的基础上对部分功能进行了增强。该插件的启用方法如下：

[root@unvdb orafce-VERSION_3_18_1]# ud_sql -c "CREATE EXTENSION oraudb;"

鉴于oraudb的一些对象建立在oracle schema下，可以根据需要在会话级、database级或实例级，设置search_path 包含oracle schema。 例如：

ALTER ROLE role_name SET search_path to "$user",public,oracle ;

或者

ALTER DATABASE database_name  SET search_path = "$user",public,oracle ;

这样就配置完成了，但是需要注意时区问题，orafce 默认使用的是UDB-TX 数据库系统的时区，由单独的参数 orafce.timezone 控制。

[root@unvdb ~]# vi unvdbsvr.conf
log_timezone = 'PRC'
timezone = 'PRC'
orafce.timezone = 'PRC'
[root@unvdb ~]# 
[root@unvdb ~]# ud_sql
unvdbsvr=# show orafce.timezone;
 orafce.timezone
-----------------
 PRC
(1 row)
unvdbsvr=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |        clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
 2022-04-01 10:32:42 | 2022-04-01 10:32:41.543877+08 | 2022-04-01 10:32:41.543877+08 | 2022-04-01 10:32:41.543979+08
(1 row)

与 Oracle 数据库兼容的特性

数据类型

数据类型	说明
VARCHAR2	可变长度字符数据类型
NVARCHAR2	可变长度国家字符数据类型
DATE	存储日期和时间的数据类型

unvdbsvr=# set search_path="$user", public, oracle;
SET
unvdbsvr=# CREATE TABLE tt (
unvdbsvr(#  name VARCHAR2(64) NOT NULL,
unvdbsvr(#  status NVARCHAR2(2000),
unvdbsvr(#  update_time DATE default (SYSDATE())
unvdbsvr(# );
CREATE TABLE
unvdbsvr=# \d tt
                        Table "public.tt"
   Column    |      Type       | Collation | Nullable |  Default
-------------+-----------------+-----------+----------+-----------
 name        | varchar2(64)    |           | not null |
 status      | nvarchar2(2000) |           |          |
 update_time | date            |           |          | sysdate()
# 测试在 date 数据类型的字段上创建分区表
create table test_range(id serial, create_time date) partition by range(create_time);
create table test_range_20220301 PARTITION of test_range FOR VALUES FROM ('2022-03-01 00:00:00') TO ('2022-03-02 00:00:00');
create table test_range_20220302 PARTITION of test_range FOR VALUES FROM ('2022-03-02 00:00:00') TO ('2022-03-03 00:00:00');
create table test_range_20220303 PARTITION of test_range FOR VALUES FROM ('2022-03-03 00:00:00') TO ('2022-03-04 00:00:00');
create table test_range_20220304 PARTITION of test_range FOR VALUES FROM ('2022-03-04 00:00:00') TO ('2022-03-05 00:00:00');
create table test_range_20220305 PARTITION of test_range FOR VALUES FROM ('2022-03-05 00:00:00') TO ('2022-03-06 00:00:00');
create table test_range_default partition of test_range default;
unvdbsvr=# \d+ test_range
                                            Partitioned table "public.test_range"
   Column    |  Type   | Collation | Nullable |                Default                 | Storage | Stats target | Description
-------------+---------+-----------+----------+----------------------------------------+---------+--------------+-------------
 id          | integer |           | not null | nextval('test_range_id_seq'::regclass) | plain   |              |
 create_time | date    |           |          |                                        | plain   |              |
Partition key: RANGE (create_time)
Partitions: test_range_20220301 FOR VALUES FROM ('2022-03-01') TO ('2022-03-02'),
            test_range_20220302 FOR VALUES FROM ('2022-03-02') TO ('2022-03-03'),
            test_range_20220303 FOR VALUES FROM ('2022-03-03') TO ('2022-03-04'),
            test_range_20220304 FOR VALUES FROM ('2022-03-04') TO ('2022-03-05'),
            test_range_20220305 FOR VALUES FROM ('2022-03-05') TO ('2022-03-06'),
            test_range_default DEFAULT
# 向分区表中插入数据
unvdbsvr=# insert into test_range (create_time) values (sysdate());
INSERT 0 1
# 查询分区表里的数据
unvdbsvr=# select * from test_range;
 id | create_time
----+-------------
  2 | 2022-07-31
(1 row)
unvdbsvr=# select to_char(create_time,'YYYY-MM-DD HH24:MI:SS') from test_range;
       to_char
---------------------
 2022-07-31 00:00:00
(1 row)

为什么这里的 date 数据类型似乎只存储 ‘年月日’，而 Oracle 的 date 数据类型会存储 ‘年月日 时分秒’。 这里就要说一下使用 orafce 的注意事项，详见后面章节<使用 orafce 的注意事项>。

支持 DUAL 表

unvdbsvr=# \d+ dual
                                  View "public.dual"
 Column |       Type        | Collation | Nullable | Default | Storage  | Description
--------+-------------------+-----------+----------+---------+----------+-------------
 dummy  | character varying |           |          |         | extended |
View definition:
 SELECT 'X'::character varying AS dummy;
unvdbsvr=# \dv public.*
               List of relations
 Schema |        Name        | Type |  Owner
--------+--------------------+------+----------
 public | dual               | view | unvdbsvr
 public | pg_stat_statements | view | unvdbsvr
(2 rows)
unvdbsvr=# select 1 from dual;
 ?column?
----------
        1
(1 row)
unvdbsvrs=# select * from dual;
 dummy
-------
 X
(1 row)
unvdbsvr=# SELECT CURRENT_DATE "date" FROM DUAL;
    date
------------
 2022-04-01

SQL 函数

数学函数

函数名称	说明	对比无插件UDB-TX22.4
BITAND	Performs a bitwise AND operation	增强
COSH	Calculates the hyperbolic cosine of a number	自带
SINH	Calculates the hyperbolic sine of a number	自带
TANH	Calculates the hyperbolic tangent of a number	自带

字符串函数

函数名称	说明	对比无插件UDB-TX22.4
INSTR	Returns the position of a substring in a string	新增
LENGTH	Returns the length of a string in number of characters	增强
LENGTHB	Returns the length of a string in number of bytes	新增
LPAD	Left-pads a string to a specified length with a sequence of characters	增强
LTRIM	Removes the specified characters from the beginning of a string	增强
NLSSORT	Returns a byte string used to sort strings in linguistic sort sequence based on locale	新增
REGEXP_COUNT	searches a string for a regular expression, and returns a count of the matches	新增
REGEXP_INSTR	returns the beginning or ending position within the string where the match for a pattern was located	新增
REGEXP_LIKE	condition in the WHERE clause of a query, causing the query to return rows that match the given pattern	新增
REGEXP_SUBSTR	returns the string that matches the pattern specified in the call to the function	新增
REGEXP_REPLACE	replace substring(s) matching a POSIX regular expression	增强
RPAD	Right-pads a string to a specified length with a sequence of characters	增强
RTRIM	Removes the specified characters from the end of a string	增强
SUBSTR	Extracts part of a string using characters to specify position and length	增强
SUBSTRB	Extracts part of a string using bytes to specify position and length	新增

Date/time 函数

函数名称	说明	对比无插件UDB-TX22.4
ADD_MONTHS	Adds months to a date	新增
DBTIMEZONE	Returns the value of the database time zone	新增
LAST_DAY	Returns the last day of the month in which the specified date falls	新增
MONTHS_BETWEEN	Returns the number of months between two dates	新增
NEXT_DAY	Returns the date of the first instance of a particular day of the week that follows the specified date	新增
ROUND	Rounds a date	增强
SESSIONTIMEZONE	Returns the time zone of the session	新增
SYSDATE	Returns the system date	新增
TRUNC	Truncates a date	增强

数据类型格式化函数

函数名称	说明	对比无插件UDB-TX22.4
TO_CHAR	Converts a value to a string	增强
TO_DATE	Converts a string to a date in accordance with the specified format	增强
TO_MULTI_BYTE	Converts a single-byte string to a multibyte string	新增
TO_NUMBER	Converts a value to a number in accordance with the specified format	增强
TO_SINGLE_BYTE	Converts a multibyte string to a single-byte string	新增

条件表达式函数

函数名称	说明	对比无插件UDB-TX22.4
DECODE	Compares values, and if they match, returns a corresponding value	增强
GREATEST	Returns the greatest of the list of one or more expressions	增强
LEAST	Returns the least of the list of one or more expressions	增强
LNNVL	Evaluates if a value is false or unknown	新增
NANVL	Returns a substitute value when a value is not a number (NaN)	新增
NVL	Returns a substitute value when a value is NULL	新增
NVL2	Returns a substitute value based on whether a value is NULL or not NULL	新增

聚合函数

函数名称	说明	对比无插件UDB-TX22.4
LISTAGG	Returns a concatenated, delimited list of string values	新增
MEDIAN	Calculates the median of a set of values	新增

• 返回内部信息的函数

函数名称	说明	对比无插件UDB-TX22.4
DUMP	Returns internal information of a value	新增

SQL 运算符

名称	说明	对比无插件UDB-TX22.4
Datetime operator	Datetime operator for the DATE type	-

Packages 包

名称	说明	对比无插件UDB-TX22.4
DBMS_ALERT	Sends alerts to multiple sessions	-
DBMS_ASSERT	Validates the properties of an input value	-
DBMS_OUTPUT	Sends messages to clients	-
DBMS_PIPE	Creates a pipe for inter-session communication	-
DBMS_RANDOM	Generates random numbers	-
DBMS_UTILITY	Provides various utilities	-
UTL_FILE	Enables text file operations	-