Using a cursor in SQL Developer to generate a subsequent query for each record

Question

I have an SQL Developer query in which I would like to use a cursor to generate subsequent select statements.

But, I'm doing something wrong. I don't seem to be using the cursor correctly.

Here is my code thus far. Thanks much.

DECLARE

  numMaterials  NUMBER := 0;

  CURSOR item_id_cur
      IS
       select id from item_h
       where value = 'myvalue';
                 
    l_item_id   item_id_cur%ROWTYPE;
BEGIN

  OPEN item_id_cur;

  LOOP
    FETCH item_id_cur INTO l_item_id;
    EXIT WHEN item_id_cur%NOTFOUND;

    SELECT count(*)
     INTO numMaterials
     FROM item_material_h
        WHERE fk_item = l_item_id;    --Does NOT like this line!!
    
    DBMS_OUTPUT.put_line (numMaterials);
   
  END LOOP; 
END;

Answer 1

You have to reference particular column, i.e.

WHERE fk_item = l_item_id.id;   

because of

CURSOR item_id_cur IS select id from item_h
                             --
                             this

---

Alternatively, you could have declared

l_item_id item_h.id%type;          --> note ".id%type"!

and then use it as you already did:

FETCH item_id_cur INTO l_item_id;
(...)       

WHERE fk_item = l_item_id;   

Don't forget to close the cursor!

---

Another option you might be interested in is a cursor FOR loop which is simpler than the one you used because Oracle does many things instead of you: you don't have to explicitly declared cursor (just put select statement into the loop) nor cursor variable, open nor close the cursor nor worry about exiting the loop:

declare
  numMaterials number;
begin
  for item_id_cur in (select id from item_h where value = 'myvalue') loop
    select count(*) into numMaterials
      from item_material_h
      where fk_item = item_id_cur.id;
    dbms_output.put_line(numMaterials);
  end loop;
end;