'Column not allowed' error when adding data from cursor to table in pl/sql

Question

I'm working on some practice problems to help me learn pl/sql. I'm required to use a cursor to add data from one table to another. My code is below. I've used dbms_output.put_line to make sure there is data in the cursor and the data in the cursor is making it into the variables c_ename and c_salary one row at a time through the loop. The problem seems to be that the data in the variables cannot be inserted into the top_paid_emp table. I'm getting a ORA-00984: column not allowed here error. I'm thinking it's a syntax error, but I don't understand where. Any help much appreciated.

DECLARE
    c_ename VARCHAR2(20);
    c_salary NUMBER(10);
    CURSOR c_topfive IS 
    SELECT ename, salary
    FROM (SELECT ename, salary, 
        RANK() OVER (ORDER BY salary DESC) AS rank
        FROM emp1)
    WHERE rank <= 5;
BEGIN
    OPEN c_topfive;
        LOOP
        FETCH c_topfive INTO c_ename, c_salary;
        dbms_output.put_line(c_ename);
        EXIT WHEN c_topfive%notfound;
        INSERT INTO top_paid_emp (empname, salary) VALUES (c_name,c_salary);
        END LOOP;
    CLOSE c_topfive;
END;

Littlefoot · Accepted Answer · 2021-10-19T20:49:53.663

1

If you're fetching

FETCH c_topfive INTO c_ename, c_salary;
                     -------

then you should have used it in insert as well

INSERT INTO top_paid_emp (empname, salary) VALUES (c_name,c_salary);
                                                   ------

Note that you could've done it using a cursor FOR loop, such as

begin
  for cur_r in (select ename, salary
                from (select ename, salary, 
                             rank() over (order by salary desc) as rank
                      from emp1)
                where rank <= 5) 
  loop
    insert into top_paid_emp (empname, salary) values (cur_r.ename, cur_r.salary);
  end loop;
end;

As you can see, it saves you from quite a lot of work: you don't have to declare any cursor variables, open the cursor, fetch, pay attention when to exit the loop, close the cursor - Oracle does all of it for you.

edited Oct 19 '21 at 20:49

answered Oct 19 '21 at 20:37

Littlefoot

131,892
15
35
57

awesome! thanks for pointing out the error in variable names! I knew it had to be something simple. It works perfectly now – Crystal Oct 19 '21 at 20:41
I added some more info; have a look, please. – Littlefoot Oct 19 '21 at 20:50
Thanks for simplifying, I thought there might be an easier way to go. I used the cursor though because the practice problem required me to, but I definitely prefer your version. – Crystal Oct 19 '21 at 22:41

'Column not allowed' error when adding data from cursor to table in pl/sql

1 Answers1