Cacher is the code snippet organizer for pro developers

--Задача: поискать во всех моих таблицах какое-нибудь значение (например, слово 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(''%' ||
                                         
                                         'IT_Петр' --: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(
                                 
                                 'prokatmaster' --: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