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

Your entire PL/SQL code could be written in a simple INSERT INTO..SELECT. No need of looping through each record in cursor, just do it in plain SQL:

INSERT INTO TBL_FIBER_VALID_TRANS_DATA 
  (SPAN_ID, MAINTENANCE_ZONE_NAME)
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
AND SPAN_ID > 0; --> This is the check you are using in your PL/SQL code

AND ROWNUM < 11

I hope you are aware that ROWNUM is just going to give you random rows it fetches, it won't be in any particular order unless you specifically mention an ORDER BY and then apply rownum on top of that. In your code, you will get 10 random rows. To understand more, see How ROWNUM works.

Answer 2

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;
/