Вопросы Теги

Oracle Equivalent для поиска значений во всех таблицах [дубликат]

Чтобы получить буферное изображение с ImageIO.read, это очень тяжелый метод, так как он создает полную несжатую копию изображения в памяти. Для png вы также можете использовать pngj и код: 

if (png)
    PngReader pngr = new PngReader(file);
    width = pngr.imgInfo.cols;
    height = pngr.imgInfo.rows;
    pngr.close();
}
92
задан Lalit Kumar B 4 December 2015 в 09:32
поделиться

15 ответов

Цитата:

Я попытался использовать этот оператор ниже, чтобы найти соответствующий столбец, основанный на том, что, по моему мнению, он должен быть назван, но не дал никаких результатов. * 

SELECT * from dba_objects WHERE
object_name like '%DTN%'

Столбец не является объектом. Если вы имеете в виду, что вы ожидаете, что имя столбца будет равно «% DTN%», запрос, который вы хотите, это: 

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';

Но если строка «DTN» - это просто догадка с вашей стороны, это вероятно, не поможет.

Кстати, насколько вы уверены, что «1/22 / 2008P09RR8» - это значение, выбранное непосредственно из одного столбца? Если вы вообще не знаете, откуда это происходит, это может быть конкатенация нескольких столбцов или результат некоторой функции или значение, находящееся в вложенном объекте таблицы. Таким образом, вы можете оказаться на дикой охоте на гусей, пытаясь проверить каждый столбец за это значение. Не можете ли вы начать с того, какое клиентское приложение отображает это значение, и попытаться выяснить, какой запрос он использует для его получения?

В любом случае ответ diciu дает один метод генерации SQL-запросов для проверки каждого столбца каждого таблицу для значения. Вы также можете делать подобные вещи полностью в одном сеансе SQL, используя блок PL / SQL и динамический SQL. Вот какой-то поспешно написанный код для этого: 

    SET SERVEROUTPUT ON SIZE 100000

    DECLARE
      match_count INTEGER;
    BEGIN
      FOR t IN (SELECT owner, table_name, column_name
                  FROM all_tab_columns
                  WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP

        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
          ' WHERE '||t.column_name||' = :1'
          INTO match_count
          USING '1/22/2008P09RR8';

        IF match_count > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
        END IF;

      END LOOP;

    END;
    /

Есть некоторые способы сделать его более эффективным.

В этом случае, учитывая значение, которое вы ищете, вы можете четко исключить любой столбец с типом NUMBER или DATE, что уменьшит количество запросов. Возможно, даже ограничьте его столбцами, где тип похож на «% CHAR%».

Вместо одного запроса на столбец можно построить один запрос для таблицы следующим образом: 

SELECT * FROM table1
  WHERE column1 = 'value'
     OR column2 = 'value'
     OR column3 = 'value'
     ...
     ;
75
ответ дан Dave Costa 17 August 2018 в 19:21
поделиться
  • 1
    Вы должны ограничить его столбцами char, varchar и varchar2, поскольку столбцы номера и даты не могут содержать эту строку. – Erich Kitzmueller 10 November 2009 в 19:54
  • 2
    @ammoQ - как я уже говорил во втором абзаце? – Dave Costa 11 November 2009 в 17:31
  • 3
    Я запустил это на 9i, и я получил неизвестную ошибку column_name. Может ли кто-нибудь сказать мне, какая модификация потребуется для запуска на 9i? – Regmi 4 May 2012 в 01:23
  • 4
    @Regmi - извините, это была ошибка в моем коде, а не проблема с версией. Цикл должен управляться с помощью all_tab_columns не all_tables. Я исправил это. – Dave Costa 4 May 2012 в 13:08
  • 5
    @DaveCosta - Спасибо за исправление, но я по-прежнему получаю ошибку «таблица или представление не существует» в строке 6. Строка 6 «Выполнение немедленного». – Regmi 7 May 2012 в 19:27

Изменение кода для поиска без учета регистра с использованием запроса LIKE вместо нахождения точных совпадений ... 

DECLARE
  match_count INTEGER;
  -- Type the owner of the tables you want to search.
  v_owner VARCHAR2(255) :='USER';
  -- Type the data type you're looking for (in CAPS). Examples include: VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';
  -- Type the string you are looking for.
  v_search_string VARCHAR2(4000) :='Test';
BEGIN
  dbms_output.put_line( 'Starting the search...' );
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE LOWER('||t.column_name||') LIKE :1'
    INTO match_count
    USING LOWER('%'||v_search_string||'%');
    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;
  END LOOP;
END;
2
ответ дан Alexandru 17 August 2018 в 19:21
поделиться

У меня были следующие проблемы для ответа @Lalit Kumars, 

ORA-19202: Error occurred in XML processing
ORA-00904: "SUCCESS": invalid identifier
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 -  "Error occurred in XML processing%s"
*Cause:    An error occurred when processing the XML function
*Action:   Check the given error message and fix the appropriate problem

Решение: 

WITH  char_cols AS
  (SELECT /*+materialize */ table_name, column_name
   FROM   cols
   WHERE  data_type IN ('CHAR', 'VARCHAR2'))
SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
       SUBSTR (table_name, 1, 14) "Table",
       SUBSTR (column_name, 1, 14) "Column"
FROM   char_cols,
       TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
       || column_name
       || '" from "'
       || table_name
       || '" where upper("'
       || column_name
       || '") like upper(''%'
       || :val
       || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
ORDER  BY "Table"
/
0
ответ дан bekur 17 August 2018 в 19:21
поделиться

Да, вы можете и ваш администратор базы данных будет ненавидеть вас, и вы обнаружите, что вы прибиваете ботинки к полу, потому что это вызовет множество операций ввода-вывода и приведет к снижению производительности базы данных по мере очистки кеша. 

select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;

для начала.

Я бы начал с запущенных запросов, используя v$session и v$sqlarea. Это изменяется в зависимости от версии oracle. Это сократит пространство и не ударит все.

7
ответ дан David Robinson 17 August 2018 в 19:21
поделиться

Я не имею простого решения на SQL promprt. Howeve существует довольно много таких инструментов, как жаба и разработчик PL / SQL, которые имеют графический интерфейс, в котором пользователь может ввести строку для поиска, и она вернет таблицу / процедуру / объект, где это будет найдено.

1
ответ дан Dheer 17 August 2018 в 19:21
поделиться

Я сделал некоторые изменения для вышеуказанного кода, чтобы заставить его работать быстрее, если вы ищете только одного владельца. Вам просто нужно изменить 3 переменные v_owner, v_data_type и v_search_string в соответствии с тем, что вы ищете. 

SET SERVEROUTPUT ON SIZE 100000

DECLARE
  match_count INTEGER;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='ENTER_USERNAME_HERE';

-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at
  v_search_string VARCHAR2(4000) :='string to search here...';

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
    INTO match_count
    USING v_search_string;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
END;
/
28
ответ дан Flood 17 August 2018 в 19:21
поделиться
  • 1
    Я должен был прокомментировать первую строку, чтобы иметь возможность запускать этот запрос. Также мне не удалось удалить фильтр владельца и запустить запрос. – Popa Andrei 18 August 2016 в 07:28
  • 2
    Мне нужно было поставить двойные кавычки вокруг имени таблицы / столбца, чтобы избежать проблем, когда они должны быть указаны: 'SELECT COUNT(*) FROM "'||t.table_name||'" WHERE "'||t.column_name||'" = :1' – Steve Chambers 13 September 2017 в 15:00

Процедура поиска по всей базе данных: 

    CREATE or REPLACE PROCEDURE SEARCH_DB(SEARCH_STR IN VARCHAR2, TAB_COL_RECS OUT VARCHAR2) IS
      match_count integer;
      qry_str varchar2(1000);
      CURSOR TAB_COL_CURSOR IS 
          SELECT TABLE_NAME,COLUMN_NAME,OWNER,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE DATA_TYPE in ('NUMBER','VARCHAR2') AND OWNER='SCOTT';
          BEGIN  
            FOR TAB_COL_REC  IN TAB_COL_CURSOR
            LOOP
              qry_str := 'SELECT COUNT(*) FROM '||TAB_COL_REC.OWNER||'.'||TAB_COL_REC.TABLE_NAME|| 
              ' WHERE '||TAB_COL_REC.COLUMN_NAME;
               IF TAB_COL_REC.DATA_TYPE = 'NUMBER' THEN
                      qry_str := qry_str||'='||SEARCH_STR; 
               ELSE
                       qry_str := qry_str||' like '||SEARCH_STR; 
               END IF;
                       --dbms_output.put_line( qry_str );
                EXECUTE IMMEDIATE  qry_str  INTO match_count;
                IF match_count > 0 THEN          
                   dbms_output.put_line( qry_str );
                  --dbms_output.put_line( TAB_COL_REC.TABLE_NAME ||' '||TAB_COL_REC.COLUMN_NAME ||' '||match_count);     
                    TAB_COL_RECS := TAB_COL_RECS||'@@'||TAB_COL_REC.TABLE_NAME||'##'||TAB_COL_REC.COLUMN_NAME;
                END IF; 
          END LOOP;
     END SEARCH_DB;

Выполнение инструкции 

  DECLARE
    SEARCH_STR VARCHAR2(200);
    TAB_COL_RECS VARCHAR2(200);
    BEGIN
      SEARCH_STR := 10;
      SEARCH_DB(
        SEARCH_STR => SEARCH_STR,
        TAB_COL_RECS => TAB_COL_RECS
      );
     DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS);
     END;

Результаты выборки 

Connecting to the database test.
SELECT COUNT(*) FROM SCOTT.EMP WHERE DEPTNO=10
SELECT COUNT(*) FROM SCOTT.DEPT WHERE DEPTNO=10
TAB_COL_RECS = @@EMP##DEPTNO@@DEPT##DEPTNO
Process exited.
Disconnecting from the database test.
2
ответ дан Hemanth 17 August 2018 в 19:21
поделиться

- он завершен - нет ошибки 

    SET SERVEROUTPUT ON SIZE 100000

DECLARE
   v_match_count     INTEGER;
   v_counter         INTEGER;




v_owner           VARCHAR2 (255) := 'VASOA';
v_search_string   VARCHAR2 (4000) := '99999';
v_data_type       VARCHAR2 (255) := 'CHAR';
v_sql             CLOB := '';

BEGIN
   FOR cur_tables
      IN (  SELECT owner, table_name
              FROM all_tables
             WHERE     owner = v_owner
                   AND table_name IN (SELECT table_name
                                        FROM all_tab_columns
                                       WHERE     owner = all_tables.owner
                                             AND data_type LIKE
                                                       '%'
                                                    || UPPER (v_data_type)
                                                    || '%')
          ORDER BY table_name)
   LOOP
      v_counter := 0;
      v_sql := '';

      FOR cur_columns
         IN (SELECT column_name, table_name
               FROM all_tab_columns
              WHERE     owner = v_owner
                    AND table_name = cur_tables.table_name
                    AND data_type LIKE '%' || UPPER (v_data_type) || '%')
      LOOP
         IF v_counter > 0
         THEN
            v_sql := v_sql || ' or ';
         END IF;

         IF cur_columns.column_name is not null
         THEN
            v_sql :=
                  v_sql
               || 'upper('
               || cur_columns.column_name
               || ') ='''
               || UPPER (v_search_string)||'''';

            v_counter := v_counter + 1;
         END IF;

      END LOOP;

      IF v_sql is  null
      THEN
         v_sql :=
               'select count(*) from '
            || v_owner
            || '.'
            || cur_tables.table_name;

      END IF;

      IF v_sql is not null
      THEN
         v_sql :=
               'select count(*) from '
            || v_owner
            || '.'
            || cur_tables.table_name
            || ' where '
            || v_sql;
      END IF;

      --v_sql := 'select count(*) from ' ||v_owner||'.'|| cur_tables.table_name ||' where '||  v_sql;


      --dbms_output.put_line(v_sql);
      --DBMS_OUTPUT.put_line (v_sql);

      EXECUTE IMMEDIATE v_sql INTO v_match_count;

      IF v_match_count > 0
      THEN
        DBMS_OUTPUT.put_line (v_sql);
        dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
      END IF;

   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error when executing the following: '
         || DBMS_LOB.SUBSTR (v_sql, 32600));
END;
/
0
ответ дан iCrazybest 17 August 2018 в 19:21
поделиться

Я знаю, что это старая тема. Но я вижу комментарий к вопросу о том, можно ли это сделать в SQL вместо использования PL/SQL. Итак, мы решили опубликовать решение.

Ниже показана поиск VALUE во всех COLUMNS всех ТАБЛИЦЕЙ во всей SCHEMA :

  • Поиск типа CHARACTER

Давайте посмотрим на значение KING в схеме SCOTT. 

SQL> variable val varchar2(10)
SQL> exec :val := 'KING'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          Column
----------- -------------- --------------
KING        EMP            ENAME

SQL>
  • Поиск типа NUMERIC

Давайте посмотрим на значение 20 в схеме SCOTT. 

SQL> variable val NUMBER
SQL> exec :val := 20

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          Column
----------- -------------- --------------
20          DEPT           DEPTNO
20          EMP            DEPTNO
20          EMP            HIREDATE
20          SALGRADE       HISAL
20          SALGRADE       LOSAL

SQL>
6
ответ дан Lalit Kumar B 17 August 2018 в 19:21
поделиться
  • 1
    hrmm .... использование xml выглядит излишним. Кроме того: Error occurred in XML processing ORA-00932: inconsistent datatypes: expected NUMBER got BLOB – towi 12 February 2015 в 09:40
  • 2
    ORA-19202: ошибка в обработке XML ORA-00932: непоследовательные типы данных: ожидается, что CHAR получил BLOB ORA-06512: в строке «SYS.DBMS_XMLGEN», строка 288 ORA-06512: в строке 1 19202. 00000 - «Ошибка произошла в Обработка XML% s & quot; * Причина: Произошла ошибка при обработке функции XML * Действие: Проверьте данное сообщение об ошибке и исправьте соответствующую проблему – Mohammad Faisal 28 March 2017 в 05:47
  • 3
    Есть идеи? ORA-19202: произошла ошибка в обработке XML ORA-22813: значение операнда превышает системные ограничения ORA-06512: в строке «SYS.DBMS_XMLGEN», строка 288 ORA-06512: в строке 1 – Menelaos Bakopoulos 8 June 2017 в 10:09

Я бы сделал что-то вроде этого (генерирует все нужные вам). Вы можете позже передать их в sqlplus: 

echo "select table_name from user_tables;" | sqlplus -S user/pwd | grep -v "^--" | grep -v "TABLE_NAME" | grep "^[A-Z]" | while read sw;
do echo "desc $sw" | sqlplus -S user/pwd | grep -v "\-\-\-\-\-\-" | awk -F' ' '{print $1}' | while read nw;
do echo "select * from $sw where $nw='val'";
done;
done;

Он дает: 

select * from TBL1 where DESCRIPTION='val'
select * from TBL1 where ='val'
select * from TBL2 where Name='val'
select * from TBL2 where LNG_ID='val'

И что он делает - для каждого table_name из user_tables получить каждое поле (от desc) и создать выбор * из таблицы, где поле равно «val».

5
ответ дан LPL 17 August 2018 в 19:21
поделиться

Я модифицировал скрипт Flood для выполнения раз для каждой таблицы, а не для каждого столбца каждой таблицы для более быстрого выполнения. Для этого требуется Oracle 11g или больше. 

    set serveroutput on size 100000

declare
    v_match_count integer;
    v_counter integer;

    -- The owner of the tables to search through (case-sensitive)
    v_owner varchar2(255) := 'OWNER_NAME';
    -- A string that is part of the data type(s) of the columns to search through (case-insensitive)
    v_data_type varchar2(255) := 'CHAR';
    -- The string to be searched for (case-insensitive)
    v_search_string varchar2(4000) := 'FIND_ME';

    -- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2 in PL/SQL
    v_sql clob := '';
begin
    for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in 
                       (select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' ||  upper(v_data_type) || '%')
                       order by table_name) loop
        v_counter := 0;
        v_sql := '';

        for cur_columns in (select column_name from all_tab_columns where 
                            owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
            if v_counter > 0 then
                v_sql := v_sql || ' or ';
            end if;
            v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
            v_counter := v_counter + 1;
        end loop;

        v_sql := 'select count(*) from ' || cur_tables.table_name || ' where ' || v_sql;

        execute immediate v_sql
        into v_match_count;

        if v_match_count > 0 then
            dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
        end if;
    end loop;

    exception
        when others then
            dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600));
end;
/
3
ответ дан Mike Rodey 17 August 2018 в 19:21
поделиться

Заимствование, немного улучшающее и упрощающее из этот пост в блоге , выглядит следующим образом: SELECT DISTINCT (:val) "Search Value", TABLE_NAME "Table", COLUMN_NAME "Column" FROM cols, TABLE (XMLSEQUENCE (DBMS_XMLGEN.GETXMLTYPE( 'SELECT "' || COLUMN_NAME || '" FROM "' || TABLE_NAME || '" WHERE UPPER("' || COLUMN_NAME || '") LIKE UPPER(''%' || :val || '%'')' ).EXTRACT ('ROWSET/ROW/*'))) ORDER BY "Table";

0
ответ дан Steve Chambers 17 August 2018 в 19:21
поделиться

, если мы знаем имена таблиц и столбцов, но хотим узнать количество строк, появляющихся для каждой схемы: 

Declare

owner VARCHAR2(1000);
tbl VARCHAR2(1000);
cnt number;
ct number;
str_sql varchar2(1000);
reason varchar2(1000);
x varchar2(1000):='%string_to_be_searched%';

cursor csr is select owner,table_name 
from all_tables where table_name ='table_name';

type rec1 is record (
ct VARCHAR2(1000));

type rec is record (
owner VARCHAR2(1000):='',
table_name VARCHAR2(1000):='');

rec2 rec;
rec3 rec1;
begin

for rec2 in csr loop

--str_sql:= 'select count(*) from '||rec.owner||'.'||rec.table_name||' where CTV_REMARKS like '||chr(39)||x||chr(39);
--dbms_output.put_line(str_sql);
--execute immediate str_sql

execute immediate 'select count(*) from '||rec2.owner||'.'||rec2.table_name||' where column_name like '||chr(39)||x||chr(39)
into rec3;
if rec3.ct <> 0 then
dbms_output.put_line(rec2.owner||','||rec3.ct);
else null;
end if;
end loop;
end;
4
ответ дан umesh 17 August 2018 в 19:21
поделиться

Есть некоторые бесплатные инструменты, которые делают такой поиск, например, этот файл отлично работает, и исходный код доступен: https://sites.google.com/site/freejansoft/dbsearch

Вам понадобится драйвер ODBC Oracle и DSN для использования этого инструмента.

1
ответ дан user272735 17 August 2018 в 19:21
поделиться

Вот еще одна модифицированная версия, которая будет сравнивать меньшее совпадение подстроки. Это работает в Oracle 11g. 

DECLARE
  match_count INTEGER;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='OWNER_NAME';

-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at
  v_search_string VARCHAR2(4000) :='%lower-search-sub-string%';

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE lower('||t.column_name||') like :1'
    INTO match_count
    USING v_search_string;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
END;
/
6
ответ дан xojins 17 August 2018 в 19:21
поделиться
Другие вопросы по тегам:

Похожие вопросы: