预言机日期转换错误

Question

当iv_from_date的输入以这种格式04/01/2013给出时，我收到错误，当输入是这种格式04/Jan/201 MON_RR 3时，它完美工作。我的输入应该是这种格式 04/01/2013.我该怎么做

CREATE OR REPLACE procedure Sp_SaveNewTaxPercentage(iv_category_id    number,
                                                    iv_tax_percentage number,
                                                    iv_from_date      varchar2,
                                                    iv_to_date        varchar2 default '12/31/9998',
                                                    iv_created_date   date,
                                                    ov_err_code       out nocopy varchar2,
                                                    ov_err_msg        out nocopy varchar2) is
  lv_category_id    varchar2(25);
  LV_TO_DATE        varchar2(25);
  lv_cat_id         varchar2(12);
  IV_TAX_ID         VARCHAR2(12);
  lv_tax_percentage varchar(20);
begin
  ov_err_code := 0;
  SELECT MAX(TAX_ID)
    INTO IV_TAX_ID
    FROM TAX_P
   WHERE CATEGORY_ID IN (iv_category_id);
  for J in (SELECT CATEGORY_ID, FROM_DATE, tax_percentage
              FROM TAX_P
             WHERE TAX_ID = IV_TAX_ID) loop
    SELECT to_date(iv_from_date, 'dd/mm/yyyy') - 1
      INTO LV_TO_DATE
      FROM DUAL;
    lv_cat_id         := J.CATEGORY_ID;
    lv_tax_percentage := j.tax_percentage;
    begin
      UPDATE TAX_P
         SET TO_dATE = LV_TO_DATE
       WHERE TAX_PERCENTAGE = lv_tax_percentage
         and to_date = iv_to_date;
      commit;
      insert into tax_p
        (TAX_ID,
         CATEGORY_ID,
         TAX_PERCENTAGE,
         FROM_DATE,
         TO_DATE,
         CREATE_DATE)
      values
        (tax_seq.nextval,
         iv_category_id,
         to_char(iv_tax_percentage, '99D99'),
         iv_from_date,
         iv_to_Date,
         sysdate);
      commit;
    end;
  end loop;
  IF LV_CAT_ID IS NULL THEN
    insert into tax_p
      (TAX_ID,
       CATEGORY_ID,
       TAX_PERCENTAGE,
       FROM_DATE,
       TO_DATE,
       CREATE_DATE)
    values
      (tax_seq.nextval,
       iv_category_id,
       to_char(iv_tax_percentage, '99D99'),
       iv_from_date,
       IV_TO_DATE,
       sysdate);
  END IF;
  commit;
  select 'Successfully Saved' into ov_err_msg from dual;
  commit;
Exception
  when others then
    rollback;
    ov_err_code := 1;
    ov_err_msg  := 'Error while saving' || SQLERRM;
end Sp_SaveNewTaxPercentage;

Answer 1

请避免对变量和参数使用VARCHAR2类型，实际上是 DATE 类型。Oracle 尝试将您的 varchar 参数隐式转换为日期。当格式与 NLS_DATE_FORMAT 中设置的格式相同时，它成功，否则失败。将iv_from_date和iv_to_date键入为 DATE 。如果表 TAX_P 中的相应列键入为 varchar2则在插入语句中进行显式格式转换。