oracle 10g提交作业函数错误

发布于 2024-09-14 05:06:46 字数 522 浏览 8 评论 0原文

我正在尝试在 oracle 10g 中安排一项工作，但它显示：

ORA-01846：不是一周中的有效日期。

这是我的代码：

declare
    v_job_id1 number(19,0);
    v_job_id2 number(19,0);
begin
    dbms_job.submit(v_job_id1, 'CTX_DDL.OPTIMIZE_INDEX(''PSO_KEYWORD_SEARCH_IDX'', ''FULL'', 45);', NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24, NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24 + 7 );
     commit;
end;
/

但这按预期工作：

select NEXT_DAY(TRUNC(SYSDATE - 1), 4) + 13/24 from dual;

有什么想法吗？

谢谢你！

乌多

原文

I'm trying to schedule a job in oracle 10g but it says:

ORA-01846: not a valid day of the week.

Heres my code:

declare
    v_job_id1 number(19,0);
    v_job_id2 number(19,0);
begin
    dbms_job.submit(v_job_id1, 'CTX_DDL.OPTIMIZE_INDEX(''PSO_KEYWORD_SEARCH_IDX'', ''FULL'', 45);', NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24, NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24 + 7 );
     commit;
end;
/

But this work as intended:

select NEXT_DAY(TRUNC(SYSDATE - 1), 4) + 13/24 from dual;

ANy ideas?

Thank you!

Udo

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倥絔 2024-09-21 05:06:46

间隔参数必须是带有 SQL 表达式的字符串，由 Oracle 在计算下次运行的日期和时间时执行。

因此必须引用表达式：

dbms_job.submit(
  JOB       =>  v_job_id1, 
  WHAT      => 'CTX_DDL.OPTIMIZE_INDEX(''PSO_KEYWORD_SEARCH_IDX'', ''FULL'', 45);',
  NEXT_DATE => NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24, 
  INTERVAL  => 'NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24 + 7'
);

Interval parameter must be a string with SQL expression, executed by Oracle when calculating date and time of next run.

Therefore expression must be quoted:

dbms_job.submit(
  JOB       =>  v_job_id1, 
  WHAT      => 'CTX_DDL.OPTIMIZE_INDEX(''PSO_KEYWORD_SEARCH_IDX'', ''FULL'', 45);',
  NEXT_DATE => NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24, 
  INTERVAL  => 'NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24 + 7'
);

回复收藏 0 原文

九局 2024-09-21 05:06:46

我无法解释为什么会发生这种情况，但 Oracle 在普通 SQL 中接受无效的 NEXT_DAY(...) 调用语法。

但 PL/SQL 代码中则不然。试试这个：

declare 
  d date;
begin
  d := NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24 + 7;
end;

你会得到完美的 ORA-01846 错误。

Oracle 建议解决方法是在调用 NEXT_DAY 之前将 NLS_DATE_LANGUAGE 会话参数切换为“AMERICAN”并在计算后将其返回。

示例：

DECLARE
FUNCTION get_next_day(dn IN VARCHAR2,ln IN VARCHAR2) RETURN DATE IS
CURSOR cr1 IS
SELECT value
  FROM nls_session_parameters
 WHERE parameter = 'NLS_DATE_LANGUAGE';
CURSOR cr2(dn1 IN VARCHAR2) IS
SELECT next_day(SYSDATE,UPPER(dn1))
  FROM dual;
day DATE;
old_date_lang varchar2(128);
BEGIN
  OPEN cr1;
  FETCH cr1 INTO old_date_lang;
  CLOSE cr1;
  dbms_session.set_nls('NLS_DATE_LANGUAGE',ln);
  OPEN cr2(dn);
  FETCH cr2 INTO day;
  CLOSE cr2;
  dbms_session.set_nls('NLS_DATE_LANGUAGE', old_date_lang);
  RETURN (day);
END;
BEGIN
  dbms_output.put_line(TO_CHAR(get_next_day('MONDAY','AMERICAN'),'DAY dd/mm/yyyy'));
END;

从我的角度来看，最好完全避免使用 NLS 感知功能，但根据定义，一周中的某天 NLS 取决于：在某些国家/地区，一周从星期日开始，在其他国家/地区 - 从星期一开始

......可以尝试使用带有“D”选项的 TRUNC() 函数来降低 NLS 效果：

select 
  NEXT_DAY(TRUNC(SYSDATE), 4)          as D1, 
  NEXT_DAY(TRUNC(SYSDATE), 'THU')      as D2, 
  case     
    -- next Thursday on this week
    when (TRUNC(SYSDATE,'D') + 4) > trunc(sysdate) then (TRUNC(SYSDATE,'D') + 4)
    -- next Thursday on next week
    else (TRUNC(SYSDATE,'D') + 4) + 7 
  end                                  as D3
from dual

在您的情况下，这看起来像这样：

dbms_job.submit(
  JOB       =>  v_job_id1, 
  WHAT      => 'CTX_DDL.OPTIMIZE_INDEX(''PSO_KEYWORD_SEARCH_IDX'', ''FULL'', 45);',
  NEXT_DATE => (
                 case     
                   -- next Thursday on this week
                   when (TRUNC(SYSDATE,'D') + 4) > trunc(sysdate) then (TRUNC(SYSDATE,'D') + 4)
                   -- next Thursday on next week
                   else (TRUNC(SYSDATE,'D') + 4) + 7 
                 end               
               ), 
  INTERVAL  => '
      case     
        when (TRUNC(SYSDATE,''D'') + 4) > trunc(sysdate) then (TRUNC(SYSDATE,''D'') + 4)
        else (TRUNC(SYSDATE,''D'') + 4) + 7 
      end               
  '
);

更新：

完美的解决方法是获取当前 NLS 设置中的日期并用作日期名称。
因为 2010 年 8 月 12 日肯定是星期四，您可以使用它作为基准日期来获取星期几名称：

select to_char(to_date('20100812','yyyymmdd'), 'DAY') from dual

然后将名称添加到函数调用而不是 4 常量：

dbms_job.submit(
  JOB       =>  v_job_id1, 
  WHAT      => 'CTX_DDL.OPTIMIZE_INDEX(''PSO_KEYWORD_SEARCH_IDX'', ''FULL'', 45);',
  NEXT_DATE => NEXT_DAY(TRUNC(SYSDATE), to_char(to_date('20100812','yyyymmdd'), 'DAY')) + 13/24, 
  INTERVAL  => 'NEXT_DAY(TRUNC(SYSDATE), to_char(to_date(''20100812'',''yyyymmdd''), ''DAY'')) + 13/24 + 7'
);

I can't explain why it happens, but Oracle accepts invalid NEXT_DAY(...) call syntax in plain SQL.

But not in PL/SQL code. Try this:

declare 
  d date;
begin
  d := NEXT_DAY(TRUNC(SYSDATE), 4) + 13/24 + 7;
end;

and you got perfect ORA-01846 error.

Oracle suggests workaround with switching NLS_DATE_LANGUAGE session parameter before calling NEXT_DAY to 'AMERICAN' and return it back after calculation.

Example:

DECLARE
FUNCTION get_next_day(dn IN VARCHAR2,ln IN VARCHAR2) RETURN DATE IS
CURSOR cr1 IS
SELECT value
  FROM nls_session_parameters
 WHERE parameter = 'NLS_DATE_LANGUAGE';
CURSOR cr2(dn1 IN VARCHAR2) IS
SELECT next_day(SYSDATE,UPPER(dn1))
  FROM dual;
day DATE;
old_date_lang varchar2(128);
BEGIN
  OPEN cr1;
  FETCH cr1 INTO old_date_lang;
  CLOSE cr1;
  dbms_session.set_nls('NLS_DATE_LANGUAGE',ln);
  OPEN cr2(dn);
  FETCH cr2 INTO day;
  CLOSE cr2;
  dbms_session.set_nls('NLS_DATE_LANGUAGE', old_date_lang);
  RETURN (day);
END;
BEGIN
  dbms_output.put_line(TO_CHAR(get_next_day('MONDAY','AMERICAN'),'DAY dd/mm/yyyy'));
END;

From my point of view better to avoid using NLS-aware functions at all, but day-of-a-week NLS-depended by definition: at some countries week starts from Sunday, at other countries - from Monday ...

You can try to use TRUNC() function with 'D' option to lower NLS effect:

select 
  NEXT_DAY(TRUNC(SYSDATE), 4)          as D1, 
  NEXT_DAY(TRUNC(SYSDATE), 'THU')      as D2, 
  case     
    -- next Thursday on this week
    when (TRUNC(SYSDATE,'D') + 4) > trunc(sysdate) then (TRUNC(SYSDATE,'D') + 4)
    -- next Thursday on next week
    else (TRUNC(SYSDATE,'D') + 4) + 7 
  end                                  as D3
from dual

In your case this looks like that:

dbms_job.submit(
  JOB       =>  v_job_id1, 
  WHAT      => 'CTX_DDL.OPTIMIZE_INDEX(''PSO_KEYWORD_SEARCH_IDX'', ''FULL'', 45);',
  NEXT_DATE => (
                 case     
                   -- next Thursday on this week
                   when (TRUNC(SYSDATE,'D') + 4) > trunc(sysdate) then (TRUNC(SYSDATE,'D') + 4)
                   -- next Thursday on next week
                   else (TRUNC(SYSDATE,'D') + 4) + 7 
                 end               
               ), 
  INTERVAL  => '
      case     
        when (TRUNC(SYSDATE,''D'') + 4) > trunc(sysdate) then (TRUNC(SYSDATE,''D'') + 4)
        else (TRUNC(SYSDATE,''D'') + 4) + 7 
      end               
  '
);

Update:

Perfect workaround is to get name of the day from current NLS settings and use as day name.
Because Aug-12-2010 definitely Thursday you can use it as base date to get day-of-the-week name:

select to_char(to_date('20100812','yyyymmdd'), 'DAY') from dual

Then add name to function call instead of 4 constant:

dbms_job.submit(
  JOB       =>  v_job_id1, 
  WHAT      => 'CTX_DDL.OPTIMIZE_INDEX(''PSO_KEYWORD_SEARCH_IDX'', ''FULL'', 45);',
  NEXT_DATE => NEXT_DAY(TRUNC(SYSDATE), to_char(to_date('20100812','yyyymmdd'), 'DAY')) + 13/24, 
  INTERVAL  => 'NEXT_DAY(TRUNC(SYSDATE), to_char(to_date(''20100812'',''yyyymmdd''), ''DAY'')) + 13/24 + 7'
);

回复收藏 0 原文

一束光，穿透我孤独的魂 2024-09-21 05:06:46

我将此作为答案而不是评论发布，以允许更好的代码格式。 ThinkJet 关于间隔参数的评论是有效的，我仍然认为您的问题与 NEXT_DAY 的“4”参数有关 - 例如，此代码有效：

SQL> declare
  2      v_job_id1 number(19,0);
  3      v_job_id2 number(19,0);
  4  begin
  5      dbms_job.submit(v_job_id1,
  6        'begin null; end;',
  7        NEXT_DAY(TRUNC(SYSDATE), 'Thursday') + 13/24,
  8        'NEXT_DAY(TRUNC(SYSDATE), '||chr(39)||'Thursday'||chr(39)||') + 13/24 + 7' );
  9       commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

I'm posting this as an answer instead of a comment to allow better code formatting. ThinkJet's comment about the interval parameter is a valid point, and I still think your problem is related to the '4' arg to NEXT_DAY - for example, this code works:

SQL> declare
  2      v_job_id1 number(19,0);
  3      v_job_id2 number(19,0);
  4  begin
  5      dbms_job.submit(v_job_id1,
  6        'begin null; end;',
  7        NEXT_DAY(TRUNC(SYSDATE), 'Thursday') + 13/24,
  8        'NEXT_DAY(TRUNC(SYSDATE), '||chr(39)||'Thursday'||chr(39)||') + 13/24 + 7' );
  9       commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

回复收藏 0 原文