ORA-06502:PL/SQL:数字或值错误:通过光标插入数据时出现字符到数字的转换错误

Question

我有一个简单的cursor，它获取数据并根据条件将数据插入两个表中。现在，对于有效的条件，我得到以下错误。请指导我如何检查和匹配条件。

错误低于

ORA-06502:PL/SQL:数字或值错误：字符到数字的转换错误ORA-06512：在"APP_FIBERINV.FIP_VAL_INV_DATA"，第37行

下面是光标。

create or replace PROCEDURE FIP_VAL_INV_DATA AS 
l_state_name r4g_lb.mantainenceboundary_evw.jiostatename%type;
l_maint_zone_code r4g_lb.mantainenceboundary_evw.maintenancezonecode%type;
begin
for cur_r in (select rj_span_id, 
rj_maintenance_zone_name,
rj_maintenance_zone_code,
rj_state_name,
rj_network_category,
rj_network_type,
rj_construction_methodology,
inventory_status_code,
rj_route_name,
rj_intracity_link_id,
calculated_length 
from app_fttx.transmedia@sat   
where rownum < 100
)

loop         
select max(jiostatename) 
into l_state_name
from r4g_lb.mantainenceboundary_evw
where jiostatename = cur_r.rj_state_name
and rownum = 1;
select max(maintenancezonecode) 
into l_maint_zone_code
from r4g_lb.mantainenceboundary_evw
where maintenancezonecode = cur_r.rj_maintenance_zone_code
and rownum = 1;

if length(cur_r.rj_span_id) = '21'        
--  if cur_r.rj_span_id > '0'        
and l_state_name = 1
and cur_r.inventory_status_code = 'IPL'
and regexp_like(cur_r.rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$')
and l_maint_zone_code = 1

then       
INSERT INTO tbl_fiber_valid_trans_data 
(span_id, maintenance_zone_name, maintenance_zone_code, r4g_state_name, inventory_status_code)
values 
(cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code, cur_r.rj_state_name, cur_r.inventory_status_code);
else
INSERT INTO TBL_FIBER_INVALID_TRANS_DATA 
(span_id, maintenance_zone_name, maintenance_zone_code, r4g_state_name, inventory_status_code)
values 
(cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code, cur_r.rj_state_name, cur_r.inventory_status_code);
end if;       
end loop;  
END FIP_VAL_INV_DATA;

更新

tbl_fiber_valid_trans_data表的数据结构

Name                     Null Type           
------------------------ ---- -------------- 
SPAN_ID                       NVARCHAR2(100) 
MAINTENANCE_ZONE_NAME         NVARCHAR2(100) 
MAINTENANCE_ZONE_CODE         NVARCHAR2(50)  
R4G_STATE_NAME                NVARCHAR2(50)  
STATE_NAME                    NVARCHAR2(50)  
NETWORK_CATEGORY              NVARCHAR2(100) 
NETWORK_TYPE                  NVARCHAR2(100) 
CONSTRUCTION_METHODOLOGY      NVARCHAR2(50)  
INVENTORY_STATUS_CODE         NVARCHAR2(20)  
OWNERSHIP_TYPE_CODE           NVARCHAR2(20)  
ROUTE_NAME                    NVARCHAR2(100) 
INTRACITY_LINK_ID             NVARCHAR2(100) 
CALCULATED_LENGTH             NUMBER(38,8)   
LAST_UPDATED_BY               NVARCHAR2(100) 
LAST_UPDATED_DATE             DATE

Answer 1

在不知道列数据类型的情况下，很难猜测真正的错误，但我认为它来自and l_state_name = 1标准，因为l_state_name必须是varchar，当您将其与1进行比较时，引擎会尝试将l_state_name强制转换为数字

使用and l_state_name = '1'修复

必须与and l_maint_zone_code = 1=>and l_maint_zone_code = '1'相同

顺便说一下，也把length(cur_r.rj_span_id) = '21'改成length(cur_r.rj_span_id) = 21