For Loop on item: 'TELLER' is not a cursor

Question

I have the following cursor:

FUNCTION managers_meerdere_depart RETURN t_managers_table_type AS
    CURSOR manager_ids IS SELECT manager_id, count(manager_id) AS teller FROM departments
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id;

When I try to iterate over the counter (teller),

    FOR i IN teller LOOP <----- 
      SELECT d.department_id
      INTO v_dep_id
      FROM EMPLOYEES e
      JOIN DEPARTMENTS d ON e.employee_id = d.manager_id
      WHERE employee_id = manager_id_rec.manager_id;
      v_dep_ids(i) := v_dep_id;  
    END LOOP;

it gives me the following error:

Error(84,33): PLS-00456: item 'TELLER' is not a cursor

score 0 · Answer 1 · answered Aug 28 '15 at 07:51

0

Shouldn't you use teller instead of counter:

FOR manager_id_rec IN manager_ids LOOP IF (manager_id_rec.teller> 1) THEN SELECT first_name, last_name INTO v_first_name, v_last_name FROM employees WHERE employee_id = manager_id_rec.manager_id;

because you give the count(manager_id) an alias which is teller

answered Aug 28 '15 at 07:51

othman.Da

621
4
16

My apologies. I translated teller (dutch for counter) to counter in my question. I've updated my question. Can you please check again – Verhelst Aug 28 '15 at 07:57
you should iterate over cursor name and not a column. 'for i in manager_ids' – othman.Da Aug 28 '15 at 08:03
It should be the following: `FOR i IN 0..manager_id_rec.teller LOOP` – Verhelst Aug 28 '15 at 08:07
no it shouldn't be that way since you are browsing records that are given by the cursor, you should use only the name of the cursor in your for loop. `FOR i IN manager_ids` – othman.Da Aug 28 '15 at 08:14
yes, my outer loop is for the name of the cursor. But i needed a nested loop where my inner loop needs to use the counter (teller) – Verhelst Aug 28 '15 at 08:20
I'm in a similar situation. How did you make it work? – Nikhil May 31 '18 at 09:25

For Loop on item: 'TELLER' is not a cursor

1 Answers1