Cursor for loop error

Question

I am trying to write cursor that would insert into table, but I am receiving error, need help with this. Error that I am receiving is ORA-06550.

    DECLARE
    CURSOR cur_rating IS
           SELECT bc.name, bc.title, bc.checkoutdate, bc.returneddate,    
               b.categoryname,b.publisher, ba.authorname
               FROM bookshelf_checkout bc INNER JOIN bookshelf b
               ON bc.title = b.title
               INNER JOIN bookshelf_author ba
               ON bc.title = ba.title
               FOR UPDATE NOWAIT;
            lv_totdays_num NUMBER(4) := 0;
            lv_rating_txt VARCHAR2(2);
    BEGIN
       FOR rec_rating IN cur_rating LOOP
           lv_totdays_num := rec_rating.returneddate -  
           rec_rating.checkoutdate;
       IF lv_totdays_num <= 10 THEN lv_rating_txt := 'DR';
       ELSIF lv_totdays_num <= 25 THEN lv_rating_txt := 'CR';
       ELSIF lv_totdays_num <= 35 THEN lv_rating_txt := 'BR';
       ELSE lv_rating_txt := 'A';
       END IF;
       INSERT INTO bookshelf_audit (title, publisher, categoryname,  
                                   new_rating, auditdate)
       VALUES      (rec_rating.title, rec_rating.publisher,   
                 rec_rating.categoryname, lv_rating_txt, sysdate)
       WHERE CURRENT OF cur_rating;
    END LOOP;
    COMMIT;
    END;

please paste the entire error message: ORA-06550 line number ? column ? There is a missing semicolon in INSERT clause — jose_bacoy
– jose_bacoy, Commented Mar 17, 2018 at 13:35

William Robertson · Accepted Answer · 2018-03-17 14:32:37Z

2

You need to remove the where clause from your insert ... values statement:

INSERT INTO bookshelf_audit
    (title, publisher, categoryname,  
     new_rating, auditdate)
VALUES
    (rec_rating.title, rec_rating.publisher,   
     rec_rating.categoryname, lv_rating_txt, sysdate)
WHERE CURRENT OF cur_rating;

should be

INSERT INTO bookshelf_audit
    (title, publisher, categoryname,  
     new_rating, auditdate)
VALUES
    (rec_rating.title, rec_rating.publisher,   
     rec_rating.categoryname, lv_rating_txt, sysdate);

edited Mar 17, 2018 at 14:32

answered Mar 17, 2018 at 13:38

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DKCroat Over a year ago

Works perfectly, Thank you @William Robertson!

Barbaros Özhan · Accepted Answer · 2018-03-17 13:50:06Z

2

The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted :

UPDATE table_name
  SET set_clause
  WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

but not applicable for an INSERT statement. So, remove WHERE CURRENT OF cur_rating part only, your code will run. That's make your INSERT statement as :

INSERT INTO bookshelf_audit (title, publisher, categoryname,  
                                   new_rating, auditdate)
       VALUES      (rec_rating.title, rec_rating.publisher,   
                 rec_rating.categoryname, lv_rating_txt, sysdate);

edited Mar 17, 2018 at 13:50

answered Mar 17, 2018 at 13:33

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

Cursor for loop error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related