What could be the logical reason in my cursor calling invalid cursor 01001

Question

is there a logical error in the following procedure that I cannot find, can you find what it is? The procedure below causes the following error:

ora-01001 invalid cursor 01001

and this is the procedure:

CREATE OR REPLACE PROCEDURE P_C is
   v_tab_name varchar2(40);
  -- v_col_name varchar2(100);
   var1 varchar2(2000);
   var2 varchar2(2000);
   tab_var varchar2(2000);

   /* First cursor */
   CURSOR get_tables IS
     SELECT  tab.table_name
     FROM user_tables tab;

   /* Second cursor */
   CURSOR get_columns IS
     SELECT DISTINCT cols.column_name
     FROM user_tab_cols cols
     WHERE cols.table_name = v_tab_name;

   BEGIN
var1 := null;
   -- Open first cursor
for gettab in get_tables
   LOOP
tab_var :=gettab.table_name;

      -- Open second cursor
for getcols in get_columns
      LOOP

if var1 is null then
var1 :=getcols.column_name;
else
var1 := var1 ||' , '|| getcols.column_name;
end if;

 END LOOP;

      CLOSE get_columns;

   END LOOP;

   CLOSE get_tables;

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

end P_C;

Answer 1

check the following code, it will work. The main thing is to use the cursor variable. Thus, the code will be much more understandable and simpler. But keep in mind that you will get all the columns of all tables. It's a lot!

CREATE OR REPLACE PROCEDURE P_C is
  var1    varchar2(32000);

  /* First cursor */
  CURSOR get_tables IS
    SELECT tab.table_name FROM user_tables tab;

  /* Second cursor */
  CURSOR get_columns (ci_tab_name in varchar2) IS
    SELECT DISTINCT cols.column_name
      FROM user_tab_cols cols
     WHERE cols.table_name = ci_tab_name;

BEGIN
  var1 := null;

  for c1 in get_tables loop
    for c2 in get_columns (c1.table_name) loop
      if var1 is not null then
        var1 := var1 || ', ';
      end if;
      var1 := var1 || c2.column_name;
    end loop;
  end loop;


EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001,
                            'An error was encountered - ' || SQLCODE ||
                            ' -ERROR- ' || SQLERRM);

end P_C;

Answer 2

If I understand your code correctly, I think you should change the second cursor to something like:

   /* Second cursor */
   CURSOR get_columns(v_tab_p VARCHAR2) IS
     SELECT DISTINCT cols.column_name
     FROM user_tab_cols cols
     WHERE cols.table_name = v_tab_p;

then change the second loop to something like:

tab_var :=gettab.table_name;

      -- Open second cursor
for getcols in get_columns(tab_var) LOOP

and finally, I'm not sure if you need to explicitly close 2 cursors.