ORACLE: ORA-06550 Error at "Select into"

Question

I get error ORA-06550 on the SELECT INTO line when executing following SQL:

DECLARE 
  new_type INTEGER;
  cur_idCardProfileId DECIMAL;

  CURSOR IDCardProfileCursor
   IS
    SELECT idcardprofileid FROM idcard;    
BEGIN
  FOR cur_idCardProfileId IN IDCardProfileCursor
  LOOP
    SELECT cardtype INTO new_type FROM idcardprofile WHERE idcardprofileid = cur_idCardProfileId;
  END LOOP;        
END;

Both idcardprofileid (table 'idcard') and idcardprofileid (table 'idcardprofile') are of type DECIMAL.

I got no clue anymore... Thank you in advance.

Answer 1

You're referring to the cursor row incorrectly. Your cursor row is being named cur_idCardProfileId, which takes precedence over the DECIMAL value you've declared with the same name; that is not used at all here.

When you refer to the value from the cursor you need to use the column name as well as the row variable name. Using a shorter name cur and with a table alias to avoid ambiguity you could do:

DECLARE 
  new_type INTEGER;

  CURSOR IDCardProfileCursor
   IS
    SELECT idcardprofileid FROM idcard;    
BEGIN
  FOR cur IN IDCardProfileCursor
  LOOP
    SELECT cardtype INTO new_type
    FROM idcardprofile i
    WHERE i.idcardprofileid = cur.idcardprofileid;
  END LOOP;        
END;
/

Of course you wouldn't normally do a select inside a loop like this, you'd have the cursor joining the two tables as that's more efficient.