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;

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!