ora-01846

本文介绍了解决Oracle Job定义时遇到ORA-01846错误的方法。该错误源于Job设置中使用的英文星期名称与数据库的语言设置不符。通过调整会话参数NLS_DATE_LANGUAGE为American,确保了Job能正确识别英文星期名称。


将1个job定义在每周六执行,但是报ora-01846,

是因为定义job的时候,next_day参数日期是英文,

但是 select * from v$nls_parameters a where a.PARAMETER='NLS_DATE_LANGUAGE';

是SIMPLIFIED CHINESE,

修改参数alter session set nls_date_language='american';

然后重新执行Job





declare
  jobnum number;
begin
  dbms_job.submit(jobnum,
                  'REFRESH_DETAILADDRESS;',
                  sysdate,
                  'next_day(trunc(sysdate),''Saturday'')');
end;




CREATE OR REPLACE PROCEDURE PERIODIC_INDICATORS_PH_DI IS v_last_period VARCHAR2(50); v_prev_period VARCHAR2(50); v_last_start_date DATE; v_last_end_date DATE; v_prev_start_date DATE; v_prev_end_date DATE; -- 异常变量 e_invalid_execution_date EXCEPTION; PRAGMA EXCEPTION_INIT(e_invalid_execution_date, -20001); BEGIN -- 获取正确的周期描述 v_last_period := get_period_dates(0); -- 最近结束的完整周期(上上周六-上周五) v_prev_period := get_period_dates(1); -- 前一完整周期(上上上周六-上上周五) -- 解析日期范围 v_last_start_date := TO_DATE(SUBSTR(v_last_period, 1, 10), 'YYYY-MM-DD')+8/24; v_last_end_date := TO_DATE(SUBSTR(v_last_period, 14, 10), 'YYYY-MM-DD')+8/24; v_prev_start_date := TO_DATE(SUBSTR(v_prev_period, 1, 10), 'YYYY-MM-DD')+8/24; v_prev_end_date := TO_DATE(SUBSTR(v_prev_period, 14, 10), 'YYYY-MM-DD')+8/24; -- 插入周期数据 INSERT INTO FK_ADS.ADS_PERIODIC_INDICATORS_PH_DI WITH CurrentPeriod AS ( SELECT COUNT(1) AS 放款总数, SUM(ACTUAL_AMOUNT/100) AS 放款金额, SUM(CASE WHEN STATE = 6 THEN (repayment_amount-actual_repayment_amount)/100 END) AS 在贷金额, count(case when user_type_product = 1 OR user_type_product = 2 THEN 1 END)/COUNT(1) AS 新准老客放款占比 FROM GHANA_CASH.LOAN_BORROW_INFO WHERE CONFIRM_DATE+8/24 BETWEEN v_last_start_date AND v_last_end_date AND country_code = 'ph' AND risk_serial_no <> 'googleplay' AND archived = 1 AND IN_FORCE_DATE IS NOT NULL ), PreviousPeriod AS ( SELECT SUM(ACTUAL_REPAYMENT_AMOUNT/100) - SUM(ACTUAL_AMOUNT/100) AS 实收营收, (SUM(ACTUAL_REPAYMENT_AMOUNT/100) - SUM(ACTUAL_AMOUNT/100)) / NULLIF(SUM(ACTUAL_AMOUNT/100), 0) AS 毛收益率, (SUM(ACTUAL_REPAYMENT_AMOUNT/100) - SUM(ACTUAL_AMOUNT/100)) / NULLIF(7.43 * COUNT(1), 0) AS ROI, (SUM(ACTUAL_REPAYMENT_AMOUNT/100) - SUM(ACTUAL_AMOUNT/100) - 7.43 * COUNT(1)) / NULLIF(COUNT(DISTINCT PHONE), 0) AS 户均贡献利润 FROM GHANA_CASH.LOAN_BORROW_INFO WHERE CONFIRM_DATE+8/24 BETWEEN v_prev_start_date AND v_prev_end_date AND country_code = 'ph' AND risk_serial_no <> 'googleplay' AND archived = 1 AND IN_FORCE_DATE IS NOT NULL ) SELECT v_last_start_date, v_last_end_date, cp.放款总数, cp.放款金额, pp.实收营收, ROUND(pp.毛收益率, 5), cp.在贷金额, ROUND(pp.ROI, 5), ROUND(pp.户均贡献利润, 5), ROUND(CP.新准老客放款占比, 5) FROM CurrentPeriod cp CROSS JOIN PreviousPeriod pp; COMMIT; END; / CREATE OR REPLACE FUNCTION get_period_dates( period_offset IN NUMBER ) RETURN VARCHAR2 IS v_current_date DATE := TRUNC(SYSDATE); v_last_friday DATE; v_target_friday DATE; v_start_date DATE; v_end_date DATE; BEGIN -- 计算最近结束的周五(严格小于当前日期) v_last_friday := TRUNC(NEXT_DAY(v_current_date - 8, 'FRIDAY')); -- 如果计算出的周五等于或大于当前日期,则再减7天 IF v_last_friday >= v_current_date THEN v_last_friday := v_last_friday - 7; END IF; -- 根据偏移量计算目标周期的结束日期 v_target_friday := v_last_friday - (7 * period_offset); -- 计算周期范围(周六到周五) v_start_date := v_target_friday - 6; -- 结束日期的前6天是周六 v_end_date := v_target_friday; -- 结束日期是周五 -- 返回格式化字符串 RETURN TO_CHAR(v_start_date, 'YYYY-MM-DD') || ' 至 ' || TO_CHAR(v_end_date, 'YYYY-MM-DD'); END get_period_dates; / begin PERIODIC_INDICATORS_PH_DI; end; ORA-01846: 周中的日无效 ORA-06512: 在 "GET_PERIOD_DATES", line 12 ORA-06512: 在 "PERIODIC_INDICATORS_PH_DI", line 16 ORA-06512: 在 line 2
07-23
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值