oracle - 如何处理Oracle数据格式错误？

Question

我有一个带有以下 header 的过程:

CREATE OR REPLACE PROCEDURE ABC.PRC_MYPROC
    (
       P_Cursor      OUT SYS_REFCURSOR,
       P_Year        DATE,
       P_Unit        CLOB,
       P_Location    CLOB, 
       P_Vp          CLOB,
       P_Director    CLOB, 
       P_Manager     CLOB
   )

我想测试此过程，以确保它返回正确的结果。

我在TOAD中编写以下代码以测试该过程:

VARIABLE io_cursor refcursor;
Execute ABC.PRC_MYPROC(:io_cursor, TO_DATE('2015'), 'TTV_ARC_UMF', 'Olympia', 'Mark Brant', 'Jane Lowe', 'Stanley Drake');
print io_cursor;

当我执行代码时，会 pop TOAD中的一个窗口，要求我设置io_cursor的值。我将类型设置为CURSOR，方向和值显示为灰色。

单击确定时，出现以下错误:

ORA-01861: literal does not match format string
ORA-06512: at line 1

有人知道我能做什么吗？

Answer 1

这是您如何调用过程的问题，而不是过程本身的问题。

您正在使用单个参数调用TO_DATE()，因此您提供的文字将使用 session 的NLS_DATE_FORMAT转换为日期。大概不只是“YYYY”。

SELECT TO_DATE('2015') FROM DUAL;

Error report -
SQL Error: ORA-01861: literal does not match format string

您可以通过显式提供更长格式的模型来看到同一件事，例如:

SELECT TO_DATE('2015', 'DD/MM/YYYY') FROM DUAL;

Error report -
SQL Error: ORA-01861: literal does not match format string

无论如何，您都不应该依赖NLS设置，尽管进行临时测试也可以。但是由于您的设置与您传递的值不匹配，因此需要指定它:

SELECT TO_DATE('2015', 'YYYY') FROM DUAL;

TO_DATE('2015','YYYY')
----------------------
2015-06-01 00:00:00   

如您所见，如果仅提供年份，则日期默认为当前月份的第一天 as mentioned in the documentation:

If you specify a date value without a time component, then the default time is midnight (00:00:00 or 12:00:00 for 24-hour and 12-hour clock time, respectively). If you specify a date value without a date, then the default date is the first day of the current month.

目前尚不清楚您打算如何使用传递的值，但是如果您希望它成为今年的第一年，则需要使用更长的文字和格式模型，或更简单地使用日期文字:

SELECT DATE '2015-01-01' FROM DUAL;

DATE'2015-01-01'  
-------------------
2015-01-01 00:00:00

或者，如果您想基于当前年份的值，则可以将其截断:

SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;

TRUNC(SYSDATE,'YYYY')
---------------------
2015-01-01 00:00:00