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