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

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;

>Solution :

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;