ORA-06502: PL/SQL: numeric or value error: character to number conversion error while inserting data through cursor

Question

I have a simple cursor which fetches the data and inserts the data based on the conditions into 2 tables. Now for valid conditions I am getting the below error. Please guide me how to check and match the conditions.

The error is below

ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "APP_FIBERINV.FIP_VAL_INV_DATA", line 37

And below is the cursor.

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;

UPDATE

Data structure of the table 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

Without knowing your columns data type it is a bit hard to guess the real error but I think it comes from the and l_state_name = 1 criteria because l_state_name must be a varchar and as you compare it to 1, the engine tries to cast l_state_name to a number

Fix with and l_state_name = '1'

It must be the same with and l_maint_zone_code = 1 => and l_maint_zone_code = '1'

By the way change also length(cur_r.rj_span_id) = '21' to length(cur_r.rj_span_id) = 21