获取ORA-01861-文字与仅SQLPlus上的格式字符串不匹配 [英] Getting ORA-01861 - Literal does not match format string on SQLPlus Only

问题描述

我有一个采用以下参数/参数的PL/SQL过程

I have a PL/SQL procedure that takes the following arguments/parameters

Procedure create_test(mode_in in number, value1_in in number, 
        value2_in in number, value3_in in varchar2);

我正在使用下面的pl/sql块来调用和执行过程

I am using the following pl/sql block to call and execute the procedure

DECLARE
  lv_mode NUMBER;
  lv_value1_in NUMBER;
  lv_value2_in NUMBER;
  lv_value3 VARCHAR2(3);
BEGIN
  lv_mode := 1;
  lv_value1_in := 1;
  lv_value2_in := 1;
  lv_value3_in := 'ES';

  CREATE_TEST(
    mode_in => lv_mode ,
    value1_in => lv_value1_in,
    value2_in => lv_value2_in,
    value3_in => lv_value3_in
  );
--rollback; 
END;
/

如果我将上面的sql块粘贴到SQLDeveloper中并执行它，它将运行没有问题. 如果我将其放在文件中并通过SQL plus执行，则会收到以下错误消息(如果直接在SQLPLus中运行，则会出现相同的问题):

If i paste the above sql block into SQLDeveloper and execute it, it runs with no problems. If i put it in a file and execute it through SQL plus, i get the following error (same problem if run it directly in SQLPLus):

ORA-01861: literal does not match format string

通常，当我收到此错误时，该问题通常与日期有关.我不确定上面有什么问题，因为没有涉及日期-特别是考虑到同一SQL块可在IDE中工作，但不能在SQLPLus中工作的事实. SQLPlus处理文字的方式与IDE略有不同吗?

Usually when i get this error, the issue is usually related to Dates. I am not sure what is wrong with the above as there are no dates involved - Especially given the fact that the same SQL block works in an IDE but not SQLPLus. Is SQLPlus handling the literals slightly differently than the IDE?

我的猜测是，SQLPlus中的某些参数对它的处理方式有所不同-但哪一个呢?

My guess is that some parameter in SQLPlus is handling it differently - but which one?

推荐答案

有趣的错误:TO_DATE(SYSDATE, 'DD-MON-YYYY HH:MI:SS')

只需在过程中用SYSDATE替换此表达式即可.

Funny mistake: TO_DATE(SYSDATE, 'DD-MON-YYYY HH:MI:SS')

Just replace this expression with SYSDATE in the procedure..

SYSDATE函数的返回数据类型为DATE:
https://docs.oracle.com/cd/B19306_01/server .102/b14200/functions172.htm

The returned datatype of SYSDATE function is DATE:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm

TO_DATE函数将CHAR，VARCHAR2，NCHAR或NVARCHAR2类型作为第一个参数:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm

TO_DATE function expects either CHAR, VARCHAR2, NCHAR, or NVARCHAR2 type as it's first parameter:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm

该过程将SYSDATE(类型为DATE)作为第一个参数传递，该参数应为字符串(VARCHAR，CHAR等).在这种情况下，Oracle使用内部的TO_CHAR函数执行DATE到CHHAR/VARCHAR2的隐式转换.
可以说上面的表达式在内部转换为:

The procedure passes SYSDATE (of type DATE) as a first parameter, which is expected to be a string (VARCHAR, CHAR etc.). In this case Oracle performs an implicit conversion of DATE to CHHAR/VARCHAR2, using TO_CHAR function internally.
One can say that the above expression is converted internally to:

TO_DATE(  TO_CHAR( sysdate ) , 'DD-MON-YYYY HH:MI:SS' )

有关隐式转换规则的更多信息，请参见此处(滚动至数据转换-隐式和显式数据转换"部分):
https://docs.oracle.com/cd/B19306_01/server .102/b14200/sql_elements002.htm

More on imlicit conversion rules can be found here (scroll to section: "Data Conversion - Implicit and Explicit Data Conversion):
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm

上述链接中的一个基本片段:

An essential fragment from the above link:

隐式转换取决于发生转换的上下文，并且可能 在每种情况下都无法以相同的方式工作.例如，隐式转换 从日期时间值到VARCHAR2值可能会返回意外 年份，具体取决于NLS_DATE_FORMAT参数的值.

Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.

---

换句话说-没有第二个参数(格式)的TO_CHAR( some-date )使用从会话中获取的NLS_DATE_FOMRAT变量的值.

如果在SQL-Developer上检查此参数，则可能为:'DD-MON-YYYY HH:MI:SS'(在SQL-Developer中，此参数的值在选项中配置:工具/首选项/数据库/NLS

但是，如果您在SQL-Plus中检查NLS_DATE_FORMAT值，则该值将与"DD-MON-YYYY HH:MI:SS"不同. SQL-Plus根据您的环境(Windows，Linux等)的语言设置以及NLS_LANG enironment变量(如果有)来确定NLS设置的值.

您可以使用以下方法在会话中更改此参数:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI:SS';
并且(旧)过程将起作用.

---

In other words - TO_CHAR( some-date ) without a second parameter (format) uses a value of NLS_DATE_FOMRAT variable taken from the session.

If you check this parameter on SQL-Developer, it will be probably: 'DD-MON-YYYY HH:MI:SS' (in SQL-Developer a value of this parameter is configured in option: Tools/Preferences/Database/NLS

But if you check NLS_DATE_FORMAT value in SQL-Plus, it will differ from 'DD-MON-YYYY HH:MI:SS'. SQL-Plus determines a value of NLS setting from language settings of your environment (Windows, Linux etc.) and from NLS_LANG enironment varable (if present).

You can change this parameter in your session using:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI:SS';
and the (old) procedure will work.

这篇关于获取ORA-01861-文字与仅SQLPlus上的格式字符串不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！