Search tables of schemas for a string

declare
   -- constants
   C_SEARCH_PATTERN                constant varchar2(32767 char) := 'search_pattern';
   C_SCHEMA_INCLUSION_PATTERN      constant varchar2(32767 char) := 'SDM_DATA';
   C_LIST_NO_HITS                  constant boolean := false;
   C_TAB_NAME_EXCLUSION_PATTERN    constant varchar2(32767 char)
      := '(^(BCKP|BACKUP|BAK|BCK|BKP)_|_ERROR_)' ;
   C_TAB_NAME_INCLUSION_PATTERN    constant varchar2(32767 char) := '.*';
   C_COL_NAME_EXCLUSION_PATTERN    constant varchar2(32767 char)
      := '(^(BCKP|BACKUP|BAK|BCK|BKP)_|_ERROR_)' ;
   C_COL_NAME_INCLUSION_PATTERN    constant varchar2(32767 char) := '.*';
   C_DATA_TYPE_EXCLUSION_PATTERN   constant varchar2(32767 char)
                                               := 'LONG'; -- LONG is a nuisance
   C_DATA_TYPE_INCLUSION_PATTERN   constant varchar2(32767 char)
      := '^(CHAR|CLOB|NCHAR|NCLOB|NVARCHAR2|VARCHAR2)$' ;
   C_PARALLEL_DEGREE               constant integer := 4;
   C_PLACEHOLDER_SCHEMA_NAME       constant char(6 char) := 'çPHSNç';
   C_PLACEHOLDER_TABLE_NAME        constant char(6 char) := 'çPHTNç';
   C_PLACEHOLDER_COLUMN_NAME       constant char(6 char) := 'çPHcNç';
   C_STATEMENT_STUB                constant clob
      := 'select /*+ parallel(' ||
         C_PARALLEL_DEGREE ||
         ') */' ||
         CHR(10)||
         '      COUNT(*)' ||
         CHR(10)||
         '  from ' ||
         C_PLACEHOLDER_SCHEMA_NAME ||
         '.' ||
         C_PLACEHOLDER_TABLE_NAME ||
         CHR(10)||
         ' where regexp_like(' ||
         C_PLACEHOLDER_COLUMN_NAME ||
         ', :C_SEARCH_PATTERN)' ;

   -- variables
   V_STATEMENT                              clob;
   V_NUMBER_OF_HITS                         integer := 0;
begin
   for REC
      in (
            with TC as
                    (select OWNER as SCHEMA_NAME
                          , TABLE_NAME
                          , COLUMN_NAME
                          , DATA_TYPE
                       from ALL_TAB_COLS
                      where REGEXP_LIKE(OWNER, C_SCHEMA_INCLUSION_PATTERN)
                        and not REGEXP_LIKE(
                                            TABLE_NAME
                                          , C_TAB_NAME_EXCLUSION_PATTERN
                                           )
                        and REGEXP_LIKE(
                                        TABLE_NAME
                                      , C_TAB_NAME_INCLUSION_PATTERN
                                       )
                        and not REGEXP_LIKE(
                                            COLUMN_NAME
                                          , C_COL_NAME_EXCLUSION_PATTERN
                                           )
                        and REGEXP_LIKE(
                                        COLUMN_NAME
                                      , C_COL_NAME_INCLUSION_PATTERN
                                       )
                        and not REGEXP_LIKE(
                                            DATA_TYPE
                                          , C_DATA_TYPE_EXCLUSION_PATTERN
                                           )
                        and REGEXP_LIKE(
                                        DATA_TYPE
                                      , C_DATA_TYPE_INCLUSION_PATTERN
                                       )
                        and HIDDEN_COLUMN = 'NO')
              select TC.*
                from TC
                     left outer join ALL_VIEWS V
                        on TC.SCHEMA_NAME = V.OWNER
                       and TC.TABLE_NAME = V.VIEW_NAME
               where V.OWNER is null
            order by TC.SCHEMA_NAME asc
                   , TC.TABLE_NAME asc
                   , TC.COLUMN_NAME asc
         )
   loop
      begin

         V_STATEMENT      :=
            REPLACE(
               REPLACE(
                  REPLACE(
                          C_STATEMENT_STUB
                        , C_PLACEHOLDER_SCHEMA_NAME
                        , REC.SCHEMA_NAME
                         )
                , C_PLACEHOLDER_TABLE_NAME
                , REC.TABLE_NAME)
             , C_PLACEHOLDER_COLUMN_NAME
             , REC.COLUMN_NAME);

         execute immediate V_STATEMENT
            into V_NUMBER_OF_HITS
            using C_SEARCH_PATTERN;

         if C_LIST_NO_HITS or V_NUMBER_OF_HITS > 0 then
             DBMS_OUTPUT.PUT(
                             'Schema: ' ||
                             RPAD(REC.SCHEMA_NAME, 30)
                            );
             DBMS_OUTPUT.PUT(
                             'Table: ' ||
                             RPAD(REC.TABLE_NAME, 30)
                            );
             DBMS_OUTPUT.PUT(
                             ', Column: ' ||
                             RPAD(REC.COLUMN_NAME, 30)
                            );
             DBMS_OUTPUT.PUT(
                             ', Type: ' ||
                             RPAD(REC.DATA_TYPE, 30)
                            );
             DBMS_OUTPUT.PUT_LINE(
                                  ', Number of hits: ' ||
                                  V_NUMBER_OF_HITS
                                 );
         end if;
      exception
         when others
         then
            DBMS_OUTPUT.PUT_LINE(
               CHR(10)||
               'Following statement failed with $C_SEARCH_PATTERN = ''' ||
               C_SEARCH_PATTERN ||
               '''. ' ||
               CHR(10)||
               V_STATEMENT ||
               CHR(10));
            DBMS_OUTPUT.PUT_LINE('*** Error stack ***');
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
            DBMS_OUTPUT.PUT_LINE('*** Error backtrace ***');
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
            DBMS_OUTPUT.PUT_LINE('');
      end;
   end loop;
end;
/