Oracle 模式租户操作 DATE 或 TIMESTAMP 类型值时遇到 ORA-01843 报错的原因和解决方法

问题现象

在 OceanBase 数据库 Oracle 模式中查询/更新 DATE 或 TIMESTAMP 类型的字段值时遇到了 ORA-01843: not a valid month 的报错，通过 source 命令执行 SQL 脚本也会遇到同样的报错。

obclient(SYS@oracle)[SYS]> create table sales_day (day_of_sale DATE, amount INT);
Query OK, 0 rows affected (0.145 sec)

obclient(SYS@oracle)[SYS]> insert into sales_day values ('2015-01-02', 100);
ORA-01843: not a valid month

obclient(SYS@oracle)[SYS]> create table sales_timestamp(timestamp_of_sale TIMESTAMP, amount INT);
Query OK, 0 rows affected (0.098 sec)

obclient(SYS@oracle)[SYS]> insert into sales_timestamp values ('2015-01-02 10:30:00', 100);
ORA-01843: not a valid month

问题原因

在 OceanBase 数据库 Oracle 模式租户中，租户变量 NLS_DATE_FORMAT 控制了 DATE 类型转 STR 的格式，以及 STR 隐式转 DATE 的格式，该变量的默认值为 DD-MON-RR。

obclient(SYS@oracle)[SYS]> show variables like 'nls_date_format';
+-----------------+-----------+
| VARIABLE_NAME   | VALUE     |
+-----------------+-----------+
| nls_date_format | DD-MON-RR |
+-----------------+-----------+
1 row in set (0.003 sec)

因此，如果为 DATE 类型的列插入一个 YYYY-MM-DD 格式的字符串常量时，就会遇到 ORA-01843: not a valid month 的报错。 类似的，租户变量 NLS_TIMESTAMP_FORMAT 控制了 TIMESTAMP 或 TIMESTAMP LTZ 类型转 STR 的格式，以及 STR 隐式转 TIMESTAMP 或 TIMESTAMP LTZ 的格式，该变量的默认值为 DD-MON-RR HH.MI.SSXFF AM。

obclient(SYS@oracle)[SYS]> show variables like 'nls_timestamp%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VALUE                        |
+-------------------------+------------------------------+
| nls_timestamp_format    | DD-MON-RR HH.MI.SSXFF AM     |
| nls_timestamp_tz_format | DD-MON-RR HH.MI.SSXFF AM TZR |
+-------------------------+------------------------------+
2 rows in set (0.005 sec)

问题的风险及影响

查询/更新 DATE 或 TIMESTAMP 类型的字段值遇到了 ORA-01843 的报错。

适用版本

OceanBase 数据库 Oracle 模式租户所有版本。

解决方法

• 将字符串常量显示转换为对应的 DATE 或 TIMESTAMP 类型。

    obclient(SYS@oracle)[SYS]> insert into sales_day values (to_date('2015-01-02','YYYY-MM-DD'), 100);
    Query OK, 1 row affected (0.005 sec)
  
    obclient(SYS@oracle)[SYS]> select * from sales_day;
    +-------------+--------+
    | DAY_OF_SALE | AMOUNT |
    +-------------+--------+
    | 02-JAN-15   |    100 |
    +-------------+--------+
    1 row in set (0.003 sec)
  
    obclient(SYS@oracle)[SYS]> insert into sales_timestamp values (to_timestamp('2015-01-02 10:30:00','YYYY-MM-DD HH24:MI:SS'), 100);
    Query OK, 1 row affected (0.006 sec)
  
    obclient(SYS@oracle)[SYS]> select * from sales_timestamp;
    +------------------------------+--------+
    | TIMESTAMP_OF_SALE            | AMOUNT |
    +------------------------------+--------+
    | 02-JAN-15 10.30.00.000000 AM |    100 |
    +------------------------------+--------+
    1 row in set (0.004 sec)
  

• 设置会话级别的 NLS_DATE_FORMAT 和 NLS_TIMESTAMP_FORMAT 等变量。

  ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
  ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9';
  ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
  

  或者：

  SET SESSION NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
  SET SESSION NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9';
  SET SESSION NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
  

  设置后确保在同一个会话中执行。

    obclient(SYS@oracle)[SYS]> insert into sales_day values ('2015-01-02', 100);
    Query OK, 1 row affected (0.016 sec)
  
    obclient(SYS@oracle)[SYS]> select * from sales_day;
    +---------------------+--------+
    | DAY_OF_SALE         | AMOUNT |
    +---------------------+--------+
    | 2015-01-02 00:00:00 |    100 |
    +---------------------+--------+
    1 row in set (0.002 sec)
  
    obclient(SYS@oracle)[SYS]> insert into sales_timestamp values ('2015-01-02 10:30:00', 100);
    Query OK, 1 row affected (0.003 sec)
  
    obclient(SYS@oracle)[SYS]> select * from sales_timestamp;
    +-------------------------------+--------+
    | TIMESTAMP_OF_SALE             | AMOUNT |
    +-------------------------------+--------+
    | 2015-01-02 10:30:00:000000000 |    100 |
    +-------------------------------+--------+
    1 row in set (0.000 sec)
  

• 设置全局级别的nls_date_format和nls_timestamp_format等变量。

  SET GLOBAL NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
  SET GLOBAL NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9';
  SET GLOBAL NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
  

  设置后确保在新开启的会话中执行。

    obclient(SYS@oracle)[SYS]> insert into sales_day values ('2015-01-02', 100);
    Query OK, 1 row affected (0.005 sec)
  
    obclient(SYS@oracle)[SYS]> select * from sales_day;
    +---------------------+--------+
    | DAY_OF_SALE         | AMOUNT |
    +---------------------+--------+
    | 2015-01-02 00:00:00 |    100 |
    +---------------------+--------+
    1 row in set (0.002 sec)
  
    obclient(SYS@oracle)[SYS]> insert into sales_timestamp values ('2015-01-02 10:30:00', 100);
    Query OK, 1 row affected (0.016 sec)
  
    obclient(SYS@oracle)[SYS]> select * from sales_timestamp;
    +-------------------------------+--------+
    | TIMESTAMP_OF_SALE             | AMOUNT |
    +-------------------------------+--------+
    | 2015-01-02 10:30:00.000000000 |    100 |
    +-------------------------------+--------+
    1 row in set (0.002 sec)
  

规避方式

无。