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

what do you get when SQL> desc TBL_FIBER_VALID_TRANS_DATA issued. — Barbaros Özhan, Apr 16 '20 at 17:38
@BarbarosÖzhan: the structure of the table, which consists of several other columns — Nad, Apr 16 '20 at 17:57
I meant whether the table contains the column named `RJ_MAINTENANCE_ZONE_NAME` , are you sure this column exists within the table ? — Barbaros Özhan, Apr 16 '20 at 17:58
@BarbarosÖzhan let me check again and post the table structure here — Nad, Apr 16 '20 at 18:01
@BarbarosÖzhan: after executing the records are not getting inserted. any idea ? — Nad, Apr 16 '20 at 18:06
@BarbarosÖzhan @Ergi have records but while running i got error as `ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "APP_FIBERINV.FIBER_TRANSM_VALID_DATA", line 29` — Nad, Apr 16 '20 at 18:12
`TBL_` prefix is missing for `FIBER_TRANSM_VALID_DATA` in this case. Btw, your error suggests that you have records with length more than 50. — Barbaros Özhan, Apr 16 '20 at 18:22
@BarbarosÖzhan: u mean to say where, table columns or the variables i created and assigned ? — Nad, Apr 16 '20 at 18:23

Lalit Kumar B · Answer 1 · 2020-04-16T18:30:57.767

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.

edited Apr 16 '20 at 18:30

answered Apr 16 '20 at 18:25

Lalit Kumar B

47,486
13
97
124

thanks lalit for your answer, but i want to do it with cursor, is it possible.? i am almost done. I guess because the way u answered is taking too time when I remove the ROWNUM part – Nad Apr 16 '20 at 18:30
@nkb SQL is always faster than PL/SQL. If you still want to do row-by-row a.k.a. slow-by-slow in PL/SQL using cursor then look at `BULK COLLECT` using `FORALL` statement. Regarding `ROWNUM`, see the link I gave. – Lalit Kumar B Apr 16 '20 at 18:32

Littlefoot · Accepted Answer · 2020-04-17T06:31:01.080

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

edited Apr 17 '20 at 06:31

answered Apr 17 '20 at 06:13

Littlefoot

131,892
15
35
57

thanks littlefoot for your answer. I will surely try your code and test whether its working accordingly or not. Just give me sometime and i will update u accordingly – Nad Apr 17 '20 at 06:19
i am getting this error while running it `ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "APP_FIBERINV.FIBER_TRANSM_VALID_DATA", line 12 ORA-06512: at line 2` – Nad Apr 17 '20 at 06:25
It is because of this line: `if cur_r.rj_span_id > 0 `. RJ_SPAN_ID seems to be VARCHAR2 (string), so you can't compare it to 0 (zero, a number). Modifying it to if `cur_r.rj_span_id > '0' ` (enclose 0 into single quotes) fixes *this* error, but - I don't know whether that's something you really want to do. – Littlefoot Apr 17 '20 at 06:32
yeah that was the cause, Now the error is gone so after running i tested the table, still no records where inserted, shall I remove that if condition and check ? – Nad Apr 17 '20 at 06:36
Did you **make sure** that `select` returns any result? – Littlefoot Apr 17 '20 at 06:38
you know what, after removing the if condition it worked smoothly.. can we add one condition if span_id does not have any values it should exit the loop? – Nad Apr 17 '20 at 06:39
Good. You don't need any condition, then (no IFs). If cursor doesn't return anything, loop won't be executed at all. If cursor returns e.g. 5 rows, 5 rows will be inserted into the target table and loop will **automatically** exit. – Littlefoot Apr 17 '20 at 06:42
oh that's great then. anything to add in this SP so that it wont take too much time as i m just executing 10 rows only. BTW i upvoted your answer which was simple and easy to understand – Nad Apr 17 '20 at 06:46
Nothing to add. But - as you were previously told: simple INSERT statement would do the job much faster than a loop. – Littlefoot Apr 17 '20 at 06:51
yes that was too much faster.. thanks a lot littlefoot – Nad Apr 17 '20 at 06:54
hi littlefoot, if u don't mind can I ask you something more related to this cursor ? – Nad Apr 20 '20 at 14:29

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

2 Answers2