Not able to insert the records in table using cursor in oracle

Question

I want to insert the records into the table by using a cursor. So below is the query for the same.

CREATE OR REPLACE PROCEDURE FIBER_TRANSM_VALID_DATA 
AS 

BEGIN

DECLARE

SPANID NVARCHAR2(50);
MZONENAME NVARCHAR2(50);


CURSOR CR_SPAN_VALID_DATA IS

 SELECT RJ_SPAN_ID, RJ_MAINTENANCE_ZONE_NAME
        FROM APP_FTTX.transmedia@SAT
        WHERE  LENGTH(RJ_SPAN_ID) = 21
       AND INVENTORY_STATUS_CODE = 'IPL'
       AND REGEXP_LIKE(rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$')
       AND RJ_MAINTENANCE_ZONE_CODE IN ('INMUNVMB01')
       AND ROWNUM < 11;  


BEGIN      
    OPEN CR_SPAN_VALID_DATA;
    LOOP    
    FETCH CR_SPAN_VALID_DATA INTO SPANID, MZONENAME;
    EXIT WHEN CR_SPAN_VALID_DATA%NOTFOUND;    

    IF SPANID > 0
    THEN
        BEGIN                              
            INSERT INTO TBL_FIBER_VALID_TRANS_DATA (RJ_SPAN_ID, RJ_MAINTENANCE_ZONE_NAME)
            VALUES (SPANID, MZONENAME);           

        END;
       END IF; 
          COMMIT;

       END LOOP;  
  CLOSE CR_SPAN_VALID_DATA;  

END;
END FIBER_TRANSM_VALID_DATA;

But i am getting error as

Error(36,13): PL/SQL: SQL Statement ignored Error(36,65): PL/SQL: ORA-00904: "RJ_MAINTENANCE_ZONE_NAME": invalid identifier

update

the table structure is below

SPAN_ID                       NVARCHAR2(50)  
MAINTENANCE_ZONE_NAME         NVARCHAR2(50)  
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

If it has to be a cursor loop, see another approach - cursor FOR loop. It is way easier to write and maintain as Oracle does most of things for you, i.e. you don't have to explicitly declare cursor and cursor variable(s), open it, fetch from it, pay attention when to exit the loop, close the cursor. All that dirty job is done for you.

All you have to worry about is that select you wrote actually returns some rows because I've seen comment you wrote that - although procedure was compiled, it didn't insert any rows. As we don't have your data, we can't help about it.

Here you go (presuming that tables and columns really exist):

create or replace procedure fiber_transm_valid_data as 
begin
  for cur_r in (select rj_span_id, 
                       rj_maintenance_zone_name
                from app_fttx.transmedia@sat
                where length(rj_span_id) = 21
                  and inventory_status_code = 'IPL'
                  and regexp_like(rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$')
                  and rj_maintenance_zone_code in ('INMUNVMB01')
               )
  loop               
    if cur_r.rj_span_id > '0' then
       insert into tbl_fiber_valid_trans_data 
         (span_id, maintenance_zone_name)
       values (cur_r.rj_span_id, cur_r.rj_maintenance_zone_name);
    end if;
  end loop;  
end fiber_transm_valid_data;

A few more notes: don't COMMIT in the loop as it causes problems (such as snapshot too old error). Consider moving it out of the procedure entirely and let the caller decide whether to commit or not.

Did you actually execute the procedure? You did create it but - if you never called it, that might be a reason why you don't see any rows being inserted. So:

begin
  fiber_transm_valid_data ;
end;
/