求组这段PL/SQL有关问题_Oracle管理

求组这段PL/SQL问题

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



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 where data_type = 'NUMBER'  ; 
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;
/

这段程序目的是把数据库中所有表的数值型字段一个个提取出来进行搜索，搜索值为639737的表和字段出来。

在sql developer里面运行报错：ORA-00942: table or view does not exist ----在“execute immediate v_sql into v_count;”这行中

当用dbms_output.put_line(v_sql) 这个测试的时候，可以看到每一个v_sql变量是类似‘select count(*) from SYS.MAP_FILE_EXTENT$ where ELEM_IDX=639737’ 这样的sql语句，拷贝到数据库中是可以正常运行索引的，是符合需求的。但是一运行到execute那里就说table找不到，求助！

------解决思路----------------------
all_tab_columns里面有可能查到其他用户的表，需要判断当前用户是否有权限访问。建议楼主改造语句，缩小查询到的范围
------解决思路----------------------
同意楼上两位说的。
匹配一下表和属主，确认是否有权限访问对应的表。
------解决思路----------------------
我不太同意楼上说的，没有权限访问的表是不会出现在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;

------解决思路----------------------

引用:

Quote: 引用:

过程中犯了一个错误。默认的，直接想到了是表与列的关系。

查看ALL_TAB_COLUMNS的注释内容
comment on column SYS.ALL_TAB_COLUMNS.TABLE_NAME is 'Table, view or cluster name';
里面是包含 cluster 的，所以，出现了ORA-00942的错误。

有办法去掉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;
/