ORA-01722：无效数字ORA-06512：在“数据库名.包名”,line196 ORA-06512:在 line3

2019-04-11 Aurora 框架开发--租房信息管理系统踩坑

ORA-01722：无效数字ORA-06512：在“HEC3DEV.TRAIN_HRMS_CHECK_IN_PKG”,line196 ORA-06512:在 line3

 

问题所在：数据库存的字段类型为date类型，但是自己存储过程声明的变量为varchar2类型

数据类型不匹配。（第七行代码）变量声明改为date之后又报错为，本为number类型参数，但是接收到的数

据类型为date（第四十一行代码），应为在计算时间天数的时候使用了to_char()函数，将字段变量转为了

char类型

 

代码示例：

错误代码

--退房过程记录
  procedure update_check_out(p_check_in_id      number,
                             p_contract_info_id number,
                             p_check_in_date    number,
                             p_person_info_id   number,
                             p_house_info_id    number,
			     p_check_out_time   varchar2,--错误变量类型
                             p_check_out_by     number,
                             p_last_updated_by  number, --最后更新人
                             p_last_update_date date --最后更新时间
                             ) is
    v_residue_bed        varchar2(30);
    v_check_in_time_from date;
  begin
    --更新人员状态
    update train_hrms_person_info
       set person_status    = 'ALREADY_CHECKOUT',
           last_updated_by  = p_last_updated_by,
           last_update_date = sysdate
     where person_info_id = p_house_info_id;
  
    --更新房屋床位
    select residue_bed
      into v_residue_bed
      from train_hrms_house_info
     where house_info_id = p_house_info_id;
  
    update train_hrms_house_info
       set residue_bed      = v_residue_bed + 1,
           last_updated_by  = p_last_updated_by,
           last_update_date = sysdate
     where house_info_id = p_house_info_id;
  
    --更新入住过程  更新入住天数，和退房日期
    select check_in_time_from
      into v_check_in_time_from
      from train_hrms_check_in_process
     where check_in_id = p_check_in_id;
    update train_hrms_check_in_process
       set check_in_date   =
	    (p_check_out_time - to_char(v_check_in_time_from)),--错误变量类型
           check_out_time   = p_check_out_time,
           check_out_by     = p_check_out_by,
           last_updated_by  = p_last_updated_by,
           last_update_date = sysdate
     where check_in_id = p_check_in_id;

 

正确代码

--退房过程记录
  procedure update_check_out(p_check_in_id      number,
                             p_contract_info_id number,
                             p_check_in_date    number,
                             p_person_info_id   number,
                             p_house_info_id    number,
                             p_check_out_time   date,--正确代码
                             p_check_out_by     number,
                             p_last_updated_by  number, --最后更新人
                             p_last_update_date date --最后更新时间
                             ) is
    v_residue_bed        varchar2(30);
    v_check_in_time_from date;
  begin
    --更新人员状态
    update train_hrms_person_info
       set person_status    = 'ALREADY_CHECKOUT',
           last_updated_by  = p_last_updated_by,
           last_update_date = sysdate
     where person_info_id = p_house_info_id;
  
    --更新房屋床位
    select residue_bed
      into v_residue_bed
      from train_hrms_house_info
     where house_info_id = p_house_info_id;
  
    update train_hrms_house_info
       set residue_bed      = v_residue_bed + 1,
           last_updated_by  = p_last_updated_by,
           last_update_date = sysdate
     where house_info_id = p_house_info_id;
  
    --更新入住过程  更新入住天数，和退房日期
    select check_in_time_from
      into v_check_in_time_from
      from train_hrms_check_in_process
     where check_in_id = p_check_in_id;
    update train_hrms_check_in_process
       set check_in_date   =
           (p_check_out_time - v_check_in_time_from),--正确代码
           check_out_time   = p_check_out_time,
           check_out_by     = p_check_out_by,
           last_updated_by  = p_last_updated_by,
           last_update_date = sysdate
     where check_in_id = p_check_in_id;