ORA-01861：文字与格式字符串不匹配 . 执行程序时-Java 学习之路

我有书面程序，其日期参数定义如下：

in_Spendpaidstartdt            IN     DATE,
in_Spendpaidenddt              IN     DATE,

在程序中我称这些参数为：

AND (   in_Spendpaidstartdt IS NULL
                       OR err.Spendpaiddt >= in_Spendpaidstartdt)
                  AND (   in_Spendpaidenddt IS NULL
                       OR err.Spendpaiddt <= in_Spendpaidenddt));

但是oracle发出以下错误：

“ORA-01861：文字与格式字符串不匹配”

有人请建议解决 .

2 回答

这是假的：

CREATE OR REPLACE PROCEDURE XYZ (
   in_startdt            IN     DATE,
   in_enddt              IN     DATE,
   output                        OUT SYS_REFCURSOR)
IS
   rcrdnums   VARCHAR2 (32767);
   rcrd_cnt   INT;
BEGIN
   rcrd_cnt := 500;

   SELECT RTRIM (
             XMLCAST (
                XMLAGG (XMLELEMENT (e, RCRDNUM) ORDER BY RCRDNUM) AS CLOB),
             ',')
     INTO rcrdnums
     FROM (SELECT (ERR.RCRDNUM || ',') AS RCRDNUM
             FROM Table_NAME ERR
                     WHERE     ROWNUM <= rcrd_cnt
                 and (   in_startdt IS NULL
                       OR to_date(err.paiddt, 'dd/mm/yyyy') >= to_date(in_startdt, 'dd/mm/yyyy'))
                  AND (   in_enddt IS NULL
                       OR to_date(err.paiddt, 'dd/mm/yyyy') <= to_date(in_enddt, 'dd/mm/yyyy')));

   IF LENGTH (rcrdnums) = 1
   THEN
      rcrdnums := NULL;
   ELSE
      rcrdnums := rcrdnums;
      --SUBSTR (rcrdnums, 1, LENGTH (rcrdnums) - 1);
   END IF;

   DBMS_OUTPUT.PUT_LINE (rcrdnums);

   OPEN outputFOR
      SELECT *
        FROM Table_NAME ERR
             INNER JOIN (    SELECT REGEXP_SUBSTR (rcrdnums,
                                                   '[^,]+',
                                                   1,
                                                   LEVEL)
                                       AS EVENT
                               FROM DUAL
                         CONNECT BY REGEXP_SUBSTR (rcrdnums,
                                                   '[^,]+',
                                                   1,
                                                   LEVEL)
                                       IS NOT NULL) EVENT_P
                ON EVENT_P.EVENT = ERR.RCRDNUM;
END;
/

回复于 2024-05-06T13:15:49+08:00

正如@XING已经提到的，你的问题是双重的 .

当你在's already a DATE - something I'已经提到elsewhere on stackoverflow的事情上使用 to_date 时，你强迫Oracle将DATE隐式转换回字符串！
调用程序时（可能）没有正确传递参数 .

以下是我修改程序的方法：

CREATE OR REPLACE PROCEDURE XYZ (
   in_startdt            IN     DATE,
   in_enddt              IN     DATE,
   output                        OUT SYS_REFCURSOR)
IS
   rcrdnums   VARCHAR2 (32767);
   rcrd_cnt   INT;
BEGIN
   rcrd_cnt := 500;

   SELECT RTRIM (
             XMLCAST (
                XMLAGG (XMLELEMENT (e, RCRDNUM) ORDER BY RCRDNUM) AS CLOB),
             ',')
     INTO rcrdnums
     FROM (SELECT (ERR.RCRDNUM || ',') AS RCRDNUM
             FROM Table_NAME ERR
                     WHERE     ROWNUM <= rcrd_cnt
                 and (   in_startdt IS NULL
                       OR to_date(err.paiddt, 'dd/mm/yyyy') >= in_startdt) -- in_startdt is already a DATE, so no need to convert it
                  AND (   in_enddt IS NULL
                       OR to_date(err.paiddt, 'dd/mm/yyyy') <= in_enddt)); -- in_enddt is already a DATE, so no need to convert it

   IF LENGTH (rcrdnums) = 1
   THEN
      rcrdnums := NULL;
   ELSE
      rcrdnums := rcrdnums;
      --SUBSTR (rcrdnums, 1, LENGTH (rcrdnums) - 1);
   END IF;

   DBMS_OUTPUT.PUT_LINE (rcrdnums);

   OPEN output FOR
      SELECT *
        FROM Table_NAME ERR
             INNER JOIN (    SELECT REGEXP_SUBSTR (rcrdnums,
                                                   '[^,]+',
                                                   1,
                                                   LEVEL)
                                       AS EVENT
                               FROM DUAL
                         CONNECT BY REGEXP_SUBSTR (rcrdnums,
                                                   '[^,]+',
                                                   1,
                                                   LEVEL)
                                       IS NOT NULL) EVENT_P
                ON EVENT_P.EVENT = ERR.RCRDNUM;
END;
/

为了测试，我会像你这样称呼你的程序：

declare
  v_refcur sys_refcursor;
begin
  xyz(in_startdt => to_date('01/10/2016', 'dd/mm/yyyy'),
      in_enddt => to_date('05/10/2016', 'dd/mm/yyyy'),
      output => v_refcur);
end;
/

注：在生产环境代码中使用“select *”是不好的做法 - 你应该明确指定你想要回来的列;这样，如果有人添加了一列，您的代码将不会导致某些内容中断，因为它不会传递该额外的列 .

回复于 2024-05-06T13:15:49+08:00

ORA-01861：文字与格式字符串不匹配 . 执行程序时

2 回答

相关问题