Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Поиск по всем таблицам  [new]
sqlhaveme
Member

Откуда:
Сообщений: 2
Добрый день!
Задача: поискать во всех моих таблицах какое-нибудь значение (например, слово search)
   DECLARE 
      match_count INTEGER; 
      query_str VARCHAR2(300);
    BEGIN 
      FOR t IN (SELECT table_name, column_name FROM all_tab_columns where owner='ANTON') LOOP 
 	  	match_count := 0;
		query_str := 'SELECT COUNT(*) FROM ' || t.table_name || ' WHERE ' || t.column_name || ' = :1';
        EXECUTE IMMEDIATE query_str
		  INTO match_count
 		  USING '%search%'; 
        IF match_count > 0 THEN 
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count ); 
        END IF; 
       END LOOP; 
     END; 
Возникает ошибка:
ORA-01722: invalid number
ORA-06512: at line 8
Помогите разобраться, в чем дело.
17 май 10, 23:25    [8789167]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по всем таблицам  [new]
AmKad
Member

Откуда:
Сообщений: 5222
sqlhaveme,

declare 
lnNum number;
begin

    execute immediate 'select * from (select 1 id from dual union all
                                      select 2 id from dual 
                                     )
                       where id = :1' 
    into lnNum
    using '%search%';
    dbms_output.put_line(lnNum);
end;

ORA-01722: неверное число
---------------------------------------------
declare 
lnNum number;
begin

    execute immediate 'select * from (select 1 id from dual union all
                                      select 2 id from dual 
                                     )
                       where id = :1' 
    into lnNum
    using '1';
    dbms_output.put_line(lnNum);
end;

1
17 май 10, 23:44    [8789220]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по всем таблицам  [new]
AmKad
Member

Откуда:
Сообщений: 5222
sqlhaveme,

declare 
lnNum number;
begin

    execute immediate 'select count(*) from (select 1 id from dual union all
                                             select 2 id from dual 
                                            )
                       where to_char(id) = :1' 
    into lnNum
    using 'h';
    dbms_output.put_line(lnNum);
end;

0
17 май 10, 23:47    [8789227]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по всем таблицам  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18487
sqlhaveme
Добрый день!
Задача: поискать во всех моих таблицах какое-нибудь значение (например, слово search)
Поищи, тут кучу раз давалось решение через XMLQUERY
sqlhaveme
   DECLARE 
      match_count INTEGER; 
      query_str VARCHAR2(300);
    BEGIN 
      FOR t IN (SELECT table_name, column_name FROM all_tab_columns where owner='ANTON') LOOP 
 	  	match_count := 0;
		query_str := 'SELECT COUNT(*) FROM ' || t.table_name || ' WHERE ' || t.column_name || ' = :1';
        EXECUTE IMMEDIATE query_str
		  INTO match_count
 		  USING '%search%'; 
        IF match_count > 0 THEN 
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count ); 
        END IF; 
       END LOOP; 
     END; 
Возникает ошибка:
ORA-01722: invalid number
ORA-06512: at line 8
1. У тебя все столбцы всех таблиц только символьные?
2, 3. = и % как то мало совмещаются (нет, конечно бывают варианты). Но, обычно, если %, то LIKE :)
18 май 10, 01:54    [8789340]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по всем таблицам  [new]
Добрый Э - Эх
Guest
sqlhaveme
Добрый день!
Задача: поискать во всех моих таблицах какое-нибудь значение (например, слово search)


with 
  sql_text as
    (
       select /*+ materialize*/ 'select '''||owner||'.'||table_name||
              ''' as VALUE /*, 1 as flag*/ from dual where exists ( select null from '||
              owner||'.'||table_name||' where '|| rtrim(replace(max(sys_connect_by_path('upper('||
              column_name||')  like upper(''%'||:WHAT_SEARCH||'%'') OR ',',')) 
               keep(dense_rank last order by level),','),'OR ')||')' as sql_text
         from (
                select '"'||c.table_name||'"' as table_name, 
                       '"'||c.column_name||'"' as column_name, 
                       '"'||c.owner||'"' as owner,
                       row_number() over(partition by c.owner, c.table_name order by c.column_id) as rn
                  from all_tab_columns c, all_objects o
                 where data_type in ('CHAR','VARCHAR2')
                   and c.table_name = o.object_name
                   and o.object_type = 'TABLE'
                   and o.owner = c.owner
                   and o.owner = upper(:owner)
              ) v_tab_col
        start with rn = 1 
       connect by prior table_name = table_name
           and prior owner = owner
           and prior rn = rn - 1
         group by owner, table_name
    ) 
Select *
  from (
         select  extractvalue
                  (dbms_xmlgen.getxmltype
                    (sql_text)
                    , '/ROWSET/ROW/VALUE'
                  ) as value
           from sql_text
       )
 where value is not null
Входные параметры:
:owner - имя схемы, в которой будем искать таблички
:WHAT_SEARCH - искомая строка.
Поиск осуществляем по полям типа 'CHAR','VARCHAR2'
В результате работы запроса - список таблиц, в который присутствует искомое значение.
18 май 10, 08:02    [8789511]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по всем таблицам  [new]
Борн
Member

Откуда:
Сообщений: 29
Добрый Э - Эх,

вот так работает - ищет все кроме LONG и BLOB

DECLARE 
      match_count INTEGER; 
      query_str VARCHAR2(300);
    BEGIN 
      FOR t IN (SELECT table_name, column_name FROM all_tab_columns a where owner='EORN1'
                  and a.data_type not in ( 'LONG', 'BLOB')
              ) LOOP 
 	  	match_count := 0;
		query_str := 'SELECT COUNT(*) FROM ' || t.table_name || ' WHERE to_char(' || t.column_name || ') LIKE :1';
        EXECUTE IMMEDIATE query_str
		  INTO match_count
 		  USING '%search%'; 
        IF match_count > 0 THEN 
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count ); 
        END IF; 
       END LOOP; 
     END; 
     /
18 май 10, 11:09    [8790766]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по всем таблицам  [new]
Deadman2014
Member

Откуда: Москва
Сообщений: 144
Тынц...
18 май 10, 11:12    [8790801]     Ответить | Цитировать Сообщить модератору
 Re: Поиск по всем таблицам  [new]
Добрый Э - Эх
Guest
Борн
Добрый Э - Эх,

вот так работает - ищет все кроме LONG и BLOB
Ну уж если всё одно решил использовать PL/SQL, то что мешало реализовать поиск и по этим типам данных? Кроме того, с чего ты решил, что трудность для поиска будут представлять всего два эти типа данных? То есть, с пользовательскими типами данных тебе встречаться не доводилось?
+ <=== Кликни тут
select distinct data_type
  from dba_tab_columns
 order by 1

Query finished, retrieving results...

          DATA_TYPE                                    
------------------------------
ALERT_TYPE                                                                       
ANYDATA                                                                          
AQ$_AGENT                                                                        
AQ$_EVENT_MESSAGE                                                                
AQ$_NOTIFY_MSG                                                                   
AQ$_RECIPIENTS                                                                   
AQ$_SIG_PROP                                                                     
AQ$_SRVNTFN_MESSAGE                                                              
AQ$_SUBSCRIBERS                                                                  
BLOB                                                                             
CHAR                                                                             
CLOB                                                                             
DATE                                                                             
EXF$INDEXOPER                                                                    
FLOAT                                                                            
INTERVAL DAY(3) TO SECOND(0)                                                     
INTERVAL DAY(3) TO SECOND(2)                                                     
INTERVAL DAY(5) TO SECOND(1)                                                     
INTERVAL DAY(9) TO SECOND(6)                                                     
KU$_10_1_COL_STATS_LIST_T                                                        
KU$_10_1_PIND_STATS_LIST_T                                                       
KU$_10_1_PTAB_STATS_LIST_T                                                       
KU$_10_1_SPIND_STATS_LIST_T                                                      
KU$_10_1_TAB_PTAB_STATS_T                                                        
KU$_ADD_SNAP_LIST_T                                                              
KU$_AUDIT_DEFAULT_LIST_T                                                         
KU$_AUDIT_LIST_T                                                                 
KU$_CACHED_STATS_T                                                               
KU$_COLTYPE_T                                                                    
KU$_COLUMN_LIST_T                                                                
KU$_COL_STATS_LIST_T                                                             
KU$_CONSTRAINT0_LIST_T                                                           
KU$_CONSTRAINT0_T                                                                
KU$_CONSTRAINT1_LIST_T                                                           
KU$_CONSTRAINT1_T                                                                
KU$_CONSTRAINT2_LIST_T                                                           
KU$_CONSTRAINT2_T                                                                
KU$_CONSTRAINT_COL_LIST_T                                                        
KU$_DEFROLE_LIST_T                                                               
KU$_DOMIDX_2NDTAB_LIST_T                                                         
KU$_DOMIDX_PLSQL_T                                                               
KU$_EXTLOC_LIST_T                                                                
KU$_EXTTAB_T                                                                     
KU$_FGA_REL_COL_LIST_T                                                           
KU$_FHTABLE_T                                                                    
KU$_FILE_LIST_T                                                                  
KU$_HISTGRM_LIST_T                                                               
KU$_HISTGRM_T                                                                    
KU$_HTABLE_T                                                                     
KU$_INDARRAYTYPE_LIST_T                                                          
KU$_INDEXOP_LIST_T                                                               
KU$_INDEX_COL_LIST_T                                                             
KU$_INDEX_LIST_T                                                                 
KU$_INDEX_T                                                                      
KU$_IND_COL_LIST_T                                                               
KU$_IND_COMPART_LIST_T                                                           
KU$_IND_PARTOBJ_T                                                                
KU$_IND_PART_LIST_T                                                              
KU$_IND_SUBPART_LIST_T                                                           
KU$_IOTABLE_T                                                                    
KU$_IOT_PARTOBJ_T                                                                
KU$_JAVA_T                                                                       
KU$_JIJOIN_LIST_T                                                                
KU$_JIJOIN_TABLE_LIST_T                                                          
KU$_LOBCOMPPART_LIST_T                                                           
KU$_LOBFRAGINDEX_T                                                               
KU$_LOBFRAG_LIST_T                                                               
KU$_LOBINDEX_T                                                                   
KU$_LOB_T                                                                        
KU$_M_VIEW_LOG_T                                                                 
KU$_M_VIEW_SRT_LIST_T                                                            
KU$_M_VIEW_T                                                                     
KU$_NT_LIST_T                                                                    
KU$_NT_PARENT_T                                                                  
KU$_OIDINDEX_T                                                                   
KU$_OPANCILLARY_LIST_T                                                           
KU$_OPARG_LIST_T                                                                 
KU$_OPBINDING_LIST_T                                                             
KU$_OPQTYPE_T                                                                    
KU$_OUTLINE_HINT_LIST_T                                                          
KU$_OUTLINE_NODE_LIST_T                                                          
KU$_OV_TABLE_T                                                                   
KU$_OV_TABPART_LIST_T                                                            
KU$_PARTLOB_T                                                                    
KU$_PARTOBJ_T                                                                    
KU$_PART_COL_LIST_T                                                              
KU$_PCOLUMN_LIST_T                                                               
KU$_PFHTABLE_T                                                                   
KU$_PHTABLE_T                                                                    
KU$_PIND_STATS_LIST_T                                                            
KU$_PIOTABLE_T                                                                   
KU$_PIOT_PART_LIST_T                                                             
KU$_PKREF_CONSTRAINT_LIST_T                                                      
KU$_PRIM_COLUMN_LIST_T                                                           
KU$_PROCOBJ_LINES                                                                
KU$_PROC_T                                                                       
KU$_PROFILE_LIST_T                                                               
KU$_PROXY_ROLE_LIST_T                                                            
KU$_PSW_HIST_LIST_T                                                              
KU$_PTAB_STATS_LIST_T                                                            
KU$_QTAB_STORAGE_T                                                               
KU$_REFCOL_LIST_T                                                                
KU$_RESOCOST_LIST_T                                                              
KU$_RLS_SEC_REL_COL_LIST_T                                                       
KU$_SCHEMAOBJ_T                                                                  
KU$_SGI_COL_LIST_T                                                               
KU$_SIMPLE_COL_LIST_T                                                            
KU$_SIMPLE_COL_T                                                                 
KU$_SLOG_LIST_T                                                                  
KU$_SOURCE_LIST_T                                                                
KU$_SOURCE_T                                                                     
KU$_SPIND_STATS_LIST_T                                                           
KU$_STORAGE_T                                                                    
KU$_STRMCOLTYPE_T                                                                
KU$_STRMCOL_LIST_T                                                               
KU$_STRMSUBCOLTYPE_LIST_T                                                        
KU$_SUBCOLTYPE_LIST_T                                                            
KU$_SWITCH_COMPILER_T                                                            
KU$_TABCLUSTER_T                                                                 
KU$_TABLESPACE_T                                                                 
KU$_TAB_COL_LIST_T                                                               
KU$_TAB_COMPART_LIST_T                                                           
KU$_TAB_PARTOBJ_T                                                                
KU$_TAB_PART_LIST_T                                                              
KU$_TAB_PTAB_STATS_T                                                             
KU$_TAB_SUBPART_LIST_T                                                           
KU$_TAB_TSUBPART_LIST_T                                                          
KU$_TACTION_LIST_T                                                               
KU$_TLOB_COMPPART_LIST_T                                                         
KU$_TRIGGERCOL_LIST_T                                                            
KU$_TYPE_BODY_T                                                                  
KU$_TYPE_T                                                                       
KU$_VCNT                                                                         
KU$_XMLSCHEMA_ELMT_T                                                             
KUPC$_MESSAGE                                                                    
LCR$_ROW_RECORD                                                                  
LONG                                                                             
LONG RAW                                                                         
MGMT_JOB_INT_ARRAY                                                               
MGMT_JOB_PARAM_LIST                                                              
MGMT_JOB_VECTOR_PARAMS                                                           
MGMT_NOTIFY_NOTIFICATION                                                         
MLSLABEL                                                                         
NCHAR                                                                            
NCLOB                                                                            
NUMBER                                                                           
NVARCHAR2                                                                        
RAW                                                                              
RE$NV_LIST                                                                       
RLM$EQUALATTR                                                                    
RLM$ROWIDTAB                                                                     
ROWID                                                                            
SCHEDULER$_EVENT_INFO                                                            
SDO_DIM_ARRAY                                                                    
SDO_GEOMETRY                                                                     
SDO_STRING_ARRAY                                                                 
SDO_TOPO_GEOMETRY_LAYER_ARRAY                                                    
SQL_PLAN_TABLE_TYPE                                                              
SYS$RLBTYP                                                                       
TIMESTAMP(3)                                                                     
TIMESTAMP(4)                                                                     
TIMESTAMP(6)                                                                     
TIMESTAMP(6) WITH TIME ZONE                                                      
TIMESTAMP(9)                                                                     
TIMESTAMP(9) WITH TIME ZONE                                                      
UNDEFINED                                                                        
UROWID                                                                           
VARCHAR2                                                                         
VARCHAR2_TABLE                                                                   
WM$ED_UNDO_CODE_TABLE_TYPE                                                       
WM$EVENT_TYPE                                                                    
WM_PERIOD                                                                        
WRI$_ADV_ABSTRACT_T                                                              
XMLTYPE                                                                          

174 row(s) retrieved
18 май 10, 11:28    [8790959]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить