oracle - 我的光标中导致无效光标 01001 的逻辑原因可能是什么

Question

以下程序有逻辑错误，我找不到，你能找到是什么吗？以下过程导致以下错误:

ora-01001 无效游标 01001

这是程序:

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

检查以下代码，这将起作用。最主要的是，使用游标变量。所以代码会更清晰、更容易。
但请考虑，您将获得所有表的所有列。好多啊!

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;