Errors when I create Oracle cursor

Question

I am new to Oracle programming (started a month ago). I've created a cursor to retrieve a value from a table 'CDF_LU' and then use the cursor to insert into another table 'test_1'. However there is an error when I run it.

Here is my code:

DECLARE
  c_cdf_table   CDF_LU.PROD_COLUMN_NAME%type; 
  
  -- create cursor.
  CURSOR c_CDF_Table_Name IS
  SELECT  PROD_COLUMN_NAME  
  FROM CDF_LU  
  ORDER BY CDF;
  -- create record.    
   c_cdf_table   c_CDF_Table_Name%ROWTYPE;
BEGIN

  OPEN c_CDF_Table_Name;

  LOOP
    FETCH  c_CDF_Table_Name  INTO  c_cdf_table;
    EXIT WHEN c_CDF_Table_Name%NOTFOUND;

    -- insert to table_1.
      
    INSERT into test_1
    
    
       select A,B,C from table_1 where some_conditions
     
       
  END LOOP;
  CLOSE c_CDF_Table_Name;
END;

When I run this code, there are following errors:

1. In line 'FETCH c_CDF_Table_Name INTO c_cdf_table;', SQL statement ignored.
2. In line 'FETCH c_CDF_Table_Name INTO c_cdf_table;', at most one declaration for "C_CDF_TABLE" is permitted.
3. In line 'INSERT into test_1', SQL statement ignored.

I wrote the SQL codes above by strictly following the syntax of cursors, so I'm not sure where the problem is.

Could you please advise? Thank you!

Answer 1

The way you wanted to do it is possible (of course) when errors are fixed; something like this:

declare
  -- cursor
  cursor c_cdf_table_name is
    select prod_column_name
    from cdf_lu
    order by cdf;
  -- cursor variable
  c_cdf_table c_cdf_table_name%rowtype;
begin
  open c_cdf_table_name;
  loop
    fetch c_cdf_table_name into c_cdf_table;
    exit when c_cdf_table_name%notfound;
    
    insert into test1 (col1, col2, co3)
      select a, b, c from table1
      where d = c_cdf_table.prod_column_name;
  end loop;
  close c_cdf_table_name;
end;
/

---

However, there's a way shorter & simpler option - a cursor FOR loop. As you can see, you don't have to declare a cursor variable, open the cursor, fetch from it, take care about exiting the loop nor closing the cursor - Oracle does all that for you:

begin
  for cur_r in (select prod_column_name
                from cdf_lu
                order by cdf)
  loop
    insert into test1 (col1, col2, co3)
      select a, b, c from table1
      where d = c_cdf_table.prod_column_name;
  end loop;
end;
/