oracle – 可能是我的游标中导致无效游标01001的逻辑原因

在以下程序中有一个我无法找到的逻辑错误,你能找到它是什么吗？以下过程导致以下错误：

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;

解决方法

检查以下代码,这将工作.主要的是,使用游标变量.所以代码将更清晰,更容易.

但请考虑一下,您将获得所有表的所有列.好多啊！

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;