Error: cursor already open in nested for loops

Question

create or replace PROCEDURE template2(
template_id_in IN RTEMPLATE_CONFIGURE.TEMPLATE_ID%TYPE)
AS 
  source_table rtemplate_configure.sobject_name%type;
  source_column rtemplate_configure.scolumn_name%type;
  target_table rtemplate_configure.tobject_name%type;
  target_column rtemplate_configure.tcolumn_name%type;
  tmp VARCHAR2(2000);
  tmp2 VARCHAR2(2000);

  CURSOR c_template_configure is 
    SELECT * FROM rtemplate_configure WHERE template_id = template_id_in order by source_table, target_table;

BEGIN 
FOR record_line in c_template_configure LOOP
  FOR record_line2 in c_template_configure LOOP
    IF record_line.sobject_name = record_line2.sobject_name 
      and record_line.tobject_name = record_line2.tobject_name
      and record_line.tcolumn_name <> record_line2.tcolumn_name
      and record_line.scolumn_name <> record_line2.scolumn_name
      THEN
      tmp2 := 'INSERT INTO  '||record_line.tobject_name||'('||record_line.tcolumn_name||','||record_line2.tcolumn_name||')'||' 
        SELECT '||record_line.scolumn_name||','||record_line.scolumn_name||' 
        FROM '||record_line.sobject_name||'';
        DBMS_OUTPUT.put_line 
        (tmp2);
    END IF;
  END LOOP;
END LOOP;
--COMMIT;
END template2;

I am getting error: PL/SQL: cursor already open, and I closed it properly, I guess? I am not sure if I used for loops properly as well, I just need that one cursor to go through the nested loops to check the data as seen in if statement.

Answer 1

You have opened cursor c_template_configure twice. You can't do that, you'll need to create a copy e.g. c_template_configure2.

Here is a very simple example of what you have done:

SQL> declare
  2    cursor c is select * from emp;
  3  begin
  4    for r1 in c loop -- Open cursor c once
  5      for r2 in c loop -- Open cursor c again, already open
  6        null;
  7      end loop;
  8    end loop;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at line 2
ORA-06512: at line 5

Now here is the corrected code:

  1  declare
  2    cursor c1 is select * from emp;
  3    cursor c2 is select * from emp;
  4  begin
  5    for r1 in c1 loop
  6      for r2 in c2 loop
  7        null;
  8      end loop;
  9    end loop;
 10* end;
SQL> /

Aside: If processing a lot of data, this is a very inefficient approach. Consider joining the data in the query for example:

select e1.empno as empno1, e2.empno as empno2
  from emp e1
  cross join emp e2
 where e1.empno != e2.empno;

Now you only have 1 cursor to open and it returns all the pairs of employees.