求组这段PL/SQL问题

Question

本帖最后由 alex8315 于 2015-02-26 18:04:00 编辑

Answer 1

认真debug一下，确认v_sql拼接的字段没有语法错误。还有就是当前用户有没有权限访问SYS表

Answer 2

all_tab_columns里面有可能查到其他用户的表，需要判断当前用户是否有权限访问。建议楼主改造语句，缩小查询到的范围

Answer 3

我不太同意楼上说的，没有权限访问的表是不会出现在all_tab_columns中
更可能的原因是，查询出来的用户名和表名(尤其是表名)需要区分大小写，例如，all_tab_columns中也会出现在回收站中的表，而该类表的表名会以BIN$xxxx开头，是区分大小写的。
两个解决办法，1是，把回收站中的表排除在外，加个条件，not like 'BIN$%'或not in (select owner,object_name from dba_recyclebin)都行
2是，拼接语句中，在用户名和表名两边分别加上双引号，对大小写进行严格区分再者，在loop里加上一个代码块，把出现错误的表打印出来，不影响其他表的查询修改如下：
DECLARE
  V_CONST NUMBER(7, 0) := 639737;
  V_SQL   VARCHAR2(5000);
  V_COUNT NUMBER(7, 0);
  CURSOR C_COLUMN IS
    SELECT TABLE_NAME, COLUMN_NAME, OWNER
      FROM ALL_TAB_COLUMNS T
     WHERE DATA_TYPE = 'NUMBER'
       AND NOT EXISTS (SELECT 1
              FROM DBA_RECYCLEBIN RB
             WHERE RB.OWNER = T.OWNER
               AND RB.OBJECT_NAME = T.TABLE_NAME
               AND RB.TYPE = 'TABLE');
BEGIN
  FOR V_COLUMN IN C_COLUMN LOOP
    BEGIN
      V_SQL := 'select count(*)  from "' || V_COLUMN.OWNER || '"."' ||
               V_COLUMN.TABLE_NAME || '" where "' || V_COLUMN.COLUMN_NAME ||
               '"=:1';
      --dbms_output.put_line(v_sql);
      EXECUTE IMMEDIATE V_SQL
        INTO V_COUNT
        USING V_CONST;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(V_COLUMN.OWNER || '.' || V_COLUMN.TABLE_NAME || '--' ||
                             V_COLUMN.COLUMN_NAME || ' (' || SQLERRM || ')');
    END;
  END LOOP;
  COMMIT;
END;

Answer 4

sql developer不大会用，请问如何调试，使用类似指针的东西，在报错的时候那个状态，那个v_sql的变量值可以显示出来呢？多谢！

Answer 5

sql developer不大会用，请问如何调试，使用类似指针的东西，在报错的时候那个状态，那个v_sql的变量值可以显示出来呢？多谢！

Answer 6

sql developer不大会用，请问如何调试，使用类似指针的东西，在报错的时候那个状态，那个v_sql的变量值可以显示出来呢？多谢！

Answer 7

你可以把 块 封装一下，变成存储过程
选择 对应的过程，右键 TEST

Answer 8

过程中犯了一个错误。默认的，直接想到了是表与列的关系。查看ALL_TAB_COLUMNS的注释内容
 comment on column SYS.ALL_TAB_COLUMNS.TABLE_NAME is 'Table, view or cluster name';
里面是包含 cluster  的，所以，出现了ORA-00942的错误。

Answer 9

你好，我按照你的程序拷贝进去运行，结果需要5,6个小时才能运行结束，然后下面是sql developer的截图，可能到dbms output是空的，不知道为何，script output里的anonymous block completed说明自后是运行成功结束了更没有方向了另外请问版主知道sql developer如何调试呀，使用类似指针的东西，在报错的时候那个状态，那个v_sql的变量值可以显示出来呢？多谢！

Answer 10

有办法去掉cluster吗？那就不去掉cluster了，直接查找表中的数据，其他的都不管。declare
v_const    number(7,0) := 639737;
v_sql    varchar2(5000);
v_count number(7,0);
cursor c_column is 
select table_name, column_name, atc.owner
  from all_tab_columns atc, all_objects ao
 where data_type = 'NUMBER'
   and ao.OBJECT_TYPE = 'TABLE'
   and atc.table_name = ao.OBJECT_NAME;
begin
  for v_column in c_column loop
       v_sql := 'select count(*)  from '||v_column.owner||'.'||v_column.table_name||' where '||v_column.column_name||'='||v_const ;   
       --dbms_output.put_line(v_sql);
    execute immediate v_sql into v_count;
  end loop;   
commit;
end;
/

Answer 11

有办法去掉cluster吗？那就不去掉cluster了，直接查找表中的数据，其他的都不管。declare
v_const    number(7,0) := 639737;
v_sql    varchar2(5000);
v_count number(7,0);
cursor c_column is 
select table_name, column_name, atc.owner
  from all_tab_columns atc, all_objects ao
 where data_type = 'NUMBER'
   and ao.OBJECT_TYPE = 'TABLE'
   and atc.table_name = ao.OBJECT_NAME;
begin
  for v_column in c_column loop
       v_sql := 'select count(*)  from '||v_column.owner||'.'||v_column.table_name||' where '||v_column.column_name||'='||v_const ;   
       --dbms_output.put_line(v_sql);
    execute immediate v_sql into v_count;
  end loop;   
commit;
end;
/果然可以了，多谢啊！另外还是想请教是否了解sql developer如何调试呀，使用类似指针的东西，在报错的时候那个状态，那个v_sql的变量值可以显示出来呢？多谢！