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

最新推荐文章于 2023-05-04 17:12:11 发布

梦里折花

最新推荐文章于 2023-05-04 17:12:11 发布

阅读量605

点赞数

文章标签： oracle 数据库 java

本文链接：https://blog.csdn.net/qw634113264/article/details/122189843

版权

Java Oracle NLS_LANG 日期格式触发器

关键词由CSDN通过智能技术生成

最近用系统同步数据的时候用了一条类似这个的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