ORA-01855: AM/A.M. or PM/P.M. required

最新推荐文章于 2024-06-26 23:53:45 发布

原创最新推荐文章于 2024-06-26 23:53:45 发布 · 697 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #java

在使用Java连接Oracle数据库时遇到日期格式不识别的问题，由于客户端未安装Oracle，无法设置NLS_LANG环境变量。通过创建数据库触发器，在用户登录后自动修改session的NLS_LANG为中文环境，确保日期格式正确解析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近用系统同步数据的时候用了一条类似这个的SQL

select to_timestamp('1970-1-1 12:00:00.000 上午','yyyy-mm-dd hh:mi:ss.ff AM')from dual;

报了这个错误，原因是因为访问数据库的客户端设置的NLS_LANG没法识别上午，下午这些中文时间格式。

一般在客户端的oracle用户环境变量里设置好NLS_LANG就行的

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"

但是我的客户端没有安装oracle，用的是java连接，导致连接的时候还是默认的NLS_LANG。最后使用的是建立触发器，用户登录之后，自动将session的NLS_LANG修改为中文。

CREATE OR REPLACE EDITIONABLE TRIGGER "用户名"."nls_lang_same" AFTER LOGON ON DATABASE 
BEGIN
  execute immediate 'alter session set NLS_LANGUAGE =''SIMPLIFIED CHINESE''';
	execute immediate 'alter session set NLS_TERRITORY =''CHINA''';
	execute immediate 'alter session set NLS_CURRENCY =''￥''';
	execute immediate 'alter session set NLS_ISO_CURRENCY =''CHINA''';
	execute immediate 'alter session set NLS_NUMERIC_CHARACTERS =''.,''';
	execute immediate 'alter session set NLS_CALENDAR =''GREGORIAN''';
	execute immediate 'alter session set NLS_DATE_FORMAT =''DD-MON-RR''';
	execute immediate 'alter session set NLS_DATE_LANGUAGE =''SIMPLIFIED CHINESE''';
	execute immediate 'alter session set NLS_CHARACTERSET =''AL32UTF8''';
	execute immediate 'alter session set NLS_SORT =''BINARY''';
	execute immediate 'alter session set NLS_TIME_FORMAT =''HH.MI.SSXFF AM''';
	execute immediate 'alter session set NLS_TIMESTAMP_FORMAT =''DD-MON-RR HH.MI.SSXFF AM''';
	execute immediate 'alter session set NLS_TIME_TZ_FORMAT =''HH.MI.SSXFF AM TZR''';
	execute immediate 'alter session set NLS_TIMESTAMP_TZ_FORMAT =''DD-MON-RR HH.MI.SSXFF AM TZR''';
	execute immediate 'alter session set NLS_DUAL_CURRENCY =''￥''';
	execute immediate 'alter session set NLS_NCHAR_CHARACTERSET =''AL16UTF16''';
	execute immediate 'alter session set NLS_COMP =''BINARY''';
END;
ALTER TRIGGER "用户名"."nls_lang_same" ENABLE