set showmode off echo off
set heading off pagesize 0 linesize 640 timing off feedback off
rem
rem Script: fieldin.sql
rem
rem Purpose: Find all tables containing a field with a name like a
rem    given substring (such as ACTIVITY_DATE), and count the records
rem    in those tables where that field contains a given value (date,
rem    number, or character), except for nulls.  It will ask you for
rem    the partial field name to find, the type of field (guessing
rem    from the first field like that name that it finds), and the
rem    value to search for.  NOTE: To search in the views in addition
rem    to the tables, place "--" in front of "and b.table_type = 'TABLE'"
rem    in the three selects.
rem
rem Author: Stephen Rea
rem Released: 8/17/98
rem 4/20/99: Y2K Fix
rem 6/2/99: Added string comparison wildcards
rem 8/27/01: Added owner to handle same table names with different owners
rem 5/17/02: Just compare fields that are the same type as what we are
rem          looking for (date, number, or character).
rem 6/14/02: Allow multiple values, separated by commas, to be searched
rem          for (exact match, no wildcards).  Shows each value found and
rem          its record count, along with the total for each table, for
rem          multiple value matches and wildcard matches.  Check search
rem          values for proper types.  Allow placeholder ('_') wildcards
rem          in character search strings.  NOTE: To do searches for strings
rem          with quotes, commas, blanks, etc., use a placeholder wildcard
rem          in place of those special characters, such as Smith_s_Grocery
rem          for Smith's Grocery.
rem 2/21/03: Allow ranges of values, entered with a dash between the low
rem          and high values such as 'value1-value2', to be searched for.
rem          NOTE: To do searches for strings with dashes or tildes (~),
rem          use a placeholder wildcard in place of those two charcters.
rem
!echo
rem
rem Get the partial name of the field to find.
rem
accept fieldstr char prompt 'Enter Partial Field Name to find: '
set termout off verify off
column fieldstr new_value fieldstr
select upper('&fieldstr') fieldstr from dual;
rem
rem Get the default type of the field by looking at the data type
rem of the first similarly-named field found.
rem
define fieldtyp = U
column fieldtyp new_value fieldtyp
select decode(data_type,'VARCHAR','C','VARCHAR2','C','CHAR','C','NCHAR','C',
'NVARCHAR2','C','DATE','D','NUMBER','N','DECIMAL','N','FLOAT','N','INTEGER','N',
'SMALLINT','N','U') fieldtyp from all_tab_columns
where column_name like '%&fieldstr%' and owner not in ('SYS','SYSTEM','SCOTT')
and ((owner not like '%1') or (length(table_name) <= 7))  -- Banner specific
and rownum = 1;
rem
rem Bypass rest of script if field not found.
rem
spool fieldina.sql
select '!echo Error: No matching field was found.' from dual
where '&fieldtyp' = 'U'; 
select '/*' from dual where '&fieldtyp' = 'U';
spool off
@fieldina.sql
rem
rem Let the user override the field type if we guess wrong.
rem
spool fieldina.sql
select 'set termout on verify on' from dual;
select 'accept fieldtyp2 char prompt ''Enter Field Type (C-Char, D-Date, ' ||
'N-Number), if not ' || '&fieldtyp' || ': ''' from dual;
select 'set termout off verify off' from dual;
spool off
@fieldina.sql
select upper(decode('&fieldtyp2','','&fieldtyp','&fieldtyp2')) fieldtyp
from dual;
rem
rem Get the value to search for in that field, prompting for appropriate type.
rem
spool fieldina.sql
select 'set termout on verify on' from dual;
select '!echo ' || decode('&fieldtyp','D','Date examples: 4-MAY-1947 or ' ||
'14-FEB-02,16-MAR-98 or 1-JAN-02-15-JAN-02',
'N','Number examples: 1500.00 or 256,1024,2048 or 25-50',
'Character examples: abc or %a_c% or abc1,abc2,abc3 or abc1-abc3') from dual;
select 'accept fieldval char prompt ''Enter &fieldstr to find: ''' from dual;
select 'set termout off verify off' from dual;
spool off
@fieldina.sql
define fieldval0=&fieldval
spool fieldina.sql
set serveroutput on
rem
rem Convert dates (including 2-digit and 4-digit years) to a common format
rem (YYYYMMDD), check for valid dates and numbers and wildcards, and add
rem quotes around values, depending on type.  Also, change the range dash
rem to a tilde (~) to distinguish it from the dashes in dates and negative
rem numbers.
rem
declare
   bgnstr number := 0;
   endstr number := 0;
   endstr2 number := 0;
   lenstr number := length('&fieldval');
   fldstr varchar2(320) := '&fieldval';
   valstr varchar2(40);
   seprat varchar2(1) := '';
   dummy number;
begin
   if '&fieldtyp' = 'D' or '&fieldtyp' = 'N'
   then
      fldstr := '';
      while endstr < lenstr
      loop
         bgnstr := endstr + 1;
         endstr := instr('&fieldval',',',bgnstr);
         if '&fieldtyp' = 'D'
         then
            endstr2 := instr('&fieldval','-',bgnstr,3);
         else
            endstr2 := instr('&fieldval','-',bgnstr);
         end if;
         if endstr2 > 1 and (endstr = 0 or endstr2 < endstr)
         then
            endstr := endstr2;
         end if;
         if endstr = 0
         then
            endstr := lenstr + 1;
         end if;
         valstr := ltrim(rtrim(substr('&fieldval',bgnstr,
            endstr-bgnstr)));
         if '&fieldtyp' = 'D'
         then
            if length(valstr) < 10
            then
               fldstr := fldstr || seprat ||
                  to_char(to_date(valstr,'DD-MON-RR'),'YYYYMMDD');
            else
               fldstr := fldstr || seprat ||
                  to_char(to_date(valstr,'DD-MON-YYYY'),'YYYYMMDD');
            end if;
         else
            dummy := to_number(valstr);
            fldstr := fldstr || seprat || valstr;
         end if;
         if endstr = endstr2
         then
            seprat := '~';
         else
            seprat := ',';
         end if;
      end loop;
   end if;
   if '&fieldtyp' = 'C'
   then
      fldstr := replace('&fieldval','-','~');
   end if;
   if instr(fldstr,',') > 0 and '&fieldtyp' != 'N'
   then
      fldstr := '"' || '''''' || replace(fldstr,',',''''',''''') || '''''' ||
         '"';
   end if;
   if instr(fldstr,'~') > 0 and '&fieldtyp' != 'N'
   then
      fldstr := '"' || '''''' || replace(fldstr,'~','''''~''''') || '''''' ||
         '"';
   end if;
   if (instr(fldstr,'%') > 0 or instr(fldstr,'_') > 0)
      and ('&fieldtyp' != 'C' or instr(fldstr,',') > 0 or instr(fldstr,'~') > 0)
   then
      dbms_output.put_line('!echo Error: Wildcards not allowed for dates, numbers, lists, or ranges.');
      dbms_output.put_line('/*');
   elsif instr(fldstr,',') > 0 and instr(fldstr,'~') > 0
   then
      dbms_output.put_line('!echo Error: Ranges not allowed in lists.');
      dbms_output.put_line('/*');
   else 
      dbms_output.put_line('define fieldval = ' || fldstr);
   end if;
exception
   when others
   then
      if '&fieldtyp' = 'D'
      then
         dbms_output.put_line('!echo "Error: Invalid search value ('
            || valstr || ') entered for date search."');
      elsif '&fieldtyp' = 'N'
      then
         dbms_output.put_line('!echo "Error: Invalid search value ('
            || valstr || ') entered for number search."');
      end if;
      dbms_output.put_line('/*');
end;
/
spool off
@fieldina.sql
rem
rem Generate the SQL for all selections on all fields matching that
rem partial field name and containing the given value.
rem
!echo
!echo "Searching ..."
column tblinfo format a78 trunc
column filler format a77
column cnt format 999999
column col format a30 trunc
column val format a118 trunc
spool fieldina.sql
select 'break on tblinfo noduplicates' from dual
where instr('&fieldval',',') > 0 or instr('&fieldval','~') > 0
or instr('&fieldval','%') > 0 or instr('&fieldval','_') > 0;
select 'clear breaks' from dual
where instr('&fieldval',',') = 0 and instr('&fieldval','~') = 0
and instr('&fieldval','%') = 0 and instr('&fieldval','_') = 0;
select 'compute sum of cnt on tblinfo' from dual
where instr('&fieldval',',') > 0 or instr('&fieldval','~') > 0
or instr('&fieldval','%') > 0 or instr('&fieldval','_') > 0;
select 'clear computes' from dual
where instr('&fieldval',',') = 0 and instr('&fieldval','~') = 0
and instr('&fieldval','%') = 0 and instr('&fieldval','_') = 0;
select 'alter session set nls_date_format = ''YYYYMMDD'';' from dual
where '&fieldtyp' = 'D';
select 'select rpad(substr(own || ''.'' || tbl,1,24),24) || ''  '' || ' ||
'com tblinfo,'' '' filler,cnt,col,val from ' ||
'(select distinct count(*) cnt,''' || a.owner || ''' own,''' || a.table_name ||
''' tbl,''' || a.column_name || ''' col,' || a.column_name || ' val,''' ||
replace(b.comments,'''') || ''' com from ' || a.owner || '.' || a.table_name ||
' where ' || a.column_name ||
decode(instr('&fieldval',','),0,decode(instr('&fieldval','~'),0,
decode(instr('&fieldval','%'),0,decode(instr('&fieldval','_'),0,
' = ''&fieldval''',' like ''&fieldval'''),' like ''&fieldval'''),
' between ' || substr('&fieldval',1,instr('&fieldval','~')-1) ||
' and ' || substr('&fieldval',instr('&fieldval','~')+1)),
' in (&fieldval)') ||
' group by ' || a.column_name || ' having count(*) > 0);'
from all_col_comments a,all_tab_comments b,all_tab_columns c
where a.column_name like '%&fieldstr%'
and b.table_type = 'TABLE'
and a.table_name = b.table_name and a.owner = b.owner
and a.table_name = c.table_name and a.owner = c.owner
and a.column_name = c.column_name and c.data_type in ('VARCHAR','VARCHAR2',
'CHAR','NCHAR','NVARCHAR2')
and a.owner not in ('SYS','SYSTEM','SCOTT')
and ((a.owner not like '%1') or (length(a.table_name) <= 7))  -- Banner specific
and '&fieldtyp' = 'C'
order by a.owner,a.table_name,a.column_name;
column srt noprint
select 'select rpad(substr(own || ''.'' || tbl,1,24),24) || ''  '' || ' ||
'com tblinfo,'' '' filler,cnt,col,val from ' ||
'(select distinct count(*) cnt,''' || a.owner || ''' own,''' ||
a.table_name || ''' tbl,''' || a.column_name || ''' col,to_char(' ||
a.column_name || ',''DD-MON-YYYY'') val,''' || replace(b.comments,'''') ||
''' com,to_char(' || a.column_name || ',''YYYYMMDD'') srt ' ||
'from ' || a.owner || '.' || a.table_name ||
' where to_char(' || a.column_name || ',''YYYYMMDD'')' ||
decode(instr('&fieldval',','),0,decode(instr('&fieldval','~'),0,
' = ''&fieldval''',
' between ' || substr('&fieldval',1,instr('&fieldval','~')-1) ||
' and ' || substr('&fieldval',instr('&fieldval','~')+1)),
' in (&fieldval)') ||
' group by to_char(' || a.column_name || ',''YYYYMMDD''),' ||
'to_char(' || a.column_name || ',''DD-MON-YYYY'') having count(*) > 0);'
from all_col_comments a,all_tab_comments b,all_tab_columns c
where a.column_name like '%&fieldstr%'
and b.table_type = 'TABLE'
and a.table_name = b.table_name and a.owner = b.owner
and a.table_name = c.table_name and a.owner = c.owner
and a.column_name = c.column_name and c.data_type = 'DATE'
and a.owner not in ('SYS','SYSTEM','SCOTT')
and ((a.owner not like '%1') or (length(a.table_name) <= 7))  -- Banner specific
and '&fieldtyp' = 'D'
order by a.owner,a.table_name,a.column_name;
select 'select rpad(substr(own || ''.'' || tbl,1,24),24) || ''  '' || ' ||
'com tblinfo,'' '' filler,cnt,col,val from ' ||
'(select distinct count(*) cnt,''' || a.owner || ''' own,''' ||
a.table_name || ''' tbl,''' || a.column_name || ''' col,to_char(' ||
a.column_name || ') val,''' || replace(b.comments,'''') || ''' com ' ||
'from ' || a.owner || '.' || a.table_name ||
' where ' || a.column_name ||
decode(instr('&fieldval',','),0,decode(instr('&fieldval','~'),0,
' = &fieldval',
' between ' || substr('&fieldval',1,instr('&fieldval','~')-1) ||
' and ' || substr('&fieldval',instr('&fieldval','~')+1)),
' in (&fieldval)') ||
' group by ' || a.column_name || ' having count(*) > 0);'
from all_col_comments a,all_tab_comments b,all_tab_columns c
where a.column_name like '%&fieldstr%'
and b.table_type = 'TABLE'
and a.table_name = b.table_name and a.owner = b.owner
and a.table_name = c.table_name and a.owner = c.owner
and a.column_name = c.column_name and c.data_type in ('NUMBER','DECIMAL',
'FLOAT','INTEGER','SMALLINT')
and a.owner not in ('SYS','SYSTEM','SCOTT')
and ((a.owner not like '%1') or (length(a.table_name) <= 7))  -- Banner specific
and '&fieldtyp' = 'N'
order by a.owner,a.table_name,a.column_name;
select 'alter session set nls_date_format = ''DD-MON-YY'';' from dual
where '&fieldtyp' = 'D';
spool off
set linesize 160
set space 2
spool fieldina.lst
@fieldina
spool off
set space 1
rem
rem Output the results and let the user scroll through and print them.
rem
!echo
set linesize 80
spool fieldin.lst
select '' from dual;
select 'Tables containing &fieldstr &fieldval0:' from dual;
spool off
!fold -w 80 fieldina.lst | grep '[^ ]' | grep -v '^\*' | grep -v '^sum' >>fieldin.lst
!/home/common/view_or_print.shl fieldin.lst 'List of &fieldstr Tables'
!/home/common/all_rights.shl fieldin.lst
rem End of bypass if field not found or bad search entered.
-- */
!rm fieldina.sql fieldina.lst
set heading on pagesize 24 linesize 80 timing on feedback 6
set termout on verify on echo on showmode both