ORA-01861: literal does not match format string 错误！

来源：互联网发布：手机gps加强软件编辑：程序博客网时间：2024/04/30 23:59

SQL> CREATE OR REPLACE PROCEDURE "SGREPORTS"."METRIC_DATA_ROLLUP" (rollup_time DATE)
2 AS
3     default_time   DATE := rollup_time;
4 BEGIN
5     begin
6     EXECUTE IMMEDIATE ('ALTER SESSION SET TIME_ZONE = DBTIMEZONE');
7      execute immediate ('alter session set nls_date_format=''YYYY-MM-DD HH24:MI:SS''');
8      execute immediate ('alter session set nls_language=american');
9     IF rollup_time IS NULL
10     THEN
11        default_time := TRUNC (SYSDATE, 'HH24') - 1 / 24;
12     ELSE
13        default_time := TRUNC (default_time, 'HH24');
14     END IF;
15    DELETE FROM metric_data_hourly
16           WHERE datetime = cast(TO_TIMESTAMP_TZ(default_time, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP WITH LO
17    EXCEPTION
18     WHEN OTHERS
19     THEN
20        dbms_output.put_line('syntax error:'||SQLerrm);
21    end;
22    begin
23     INSERT INTO metric_data_hourly SELECT /*+INDEX(A1,PK_METRIC_DATA_HOURLY)*/ *
24            FROM sgreports.metric_data_hourly@RPTS.NILE002A_ZONE01.EAST A1
25           WHERE datetime between cast(TO_TIMESTAMP_TZ(default_time, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP W
26       and cast(TO_TIMESTAMP_TZ(default_time, 'YYYY-MM-DD HH24:MI:SS')+ 3599 / 86400 AS TIMESTAMP WITH LOCA
27     EXCEPTION
28        WHEN OTHERS
29        THEN
30           dbms_output.put_line(SQLerrm);
31     END;
32 end;
33 /

过程已创建。

SQL> exec metric_data_rollup ('2009-04-11 21:00:00');
BEGIN metric_data_rollup ('2009-04-11 21:00:00'); END;

*
第 1 行出现错误:
ORA-01861: literal does not match format string
ORA-06512: at line 1

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> alter session set nls_language=american;

Session altered.

SQL> exec metric_data_rollup ('2009-04-11 21:00:00');