afchrchk

REM +=======================================================================+
REM |    Copyright (c) 1992, 2015 Oracle Corporation, Redwood Shores, CA, USA     |
REM |                         All rights reserved.                          |
REM +=======================================================================+
REM | FILENAME
REM |   afshrchk.sql
REM | USAGE   
REM |   sqlplus applsys/pwd @afchrchk <table> <column>
REM |     where <table> and <column> are optional 'like'-style clauses
REM | DESCRIPTION
REM |   Check requested table.columns for invalid trailing spaces and
REM |   and control characters.
REM |   These characters cause 'FRM-40654: Record has been updated.'
REM |   errors when column is queried in a form.
REM +=======================================================================+
REM $Header: afchrchk.sql 120.3.12010000.6 2015/09/17 07:21:56 srinnakk noship $
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
set serveroutput on
exec dbms_output.enable(1000000)
set verify off
prompt *** INSTRUCTIONS ***
prompt Enter the table and column names to check for leading or trailing
prompt spaces and control characters.  Like-style expressions are ok.
prompt Leave either table or column blank to check table/column.
prompt
prompt Table name (blank for all):
define tblarg = '&1'
prompt Column name (blank for all):
define colarg = '&2'
prompt
prompt Newline characters are acceptable in columns never queried in
prompt Forms, on only queried in multi-line items.
prompt Enter Y to also look for newline characters.
prompt Check for newline characters (Y/N)?
define newlinearg = '&3'
prompt
prompt Enter Y to automatically strip all leading/trailing spaces
prompt and control characters found.
prompt *** WARNING ***
prompt It is highly recommended to run first without stripping
prompt to be sure all detected values really should be fixed!
prompt
prompt Automatically fix all errors found (Y/N)?
define fixarg = '&4'
prompt
prompt Enter the absolute path of directory
prompt to which log file should be written to.
prompt Null will force the script to write to
prompt directory pointed by DB parameter utl_file_dir.
prompt
prompt Log file location?
define logdirarg = '&5'
prompt
prompt Enter the file name to which logs
prompt should be written to.
prompt Null will force the script to write to
prompt default file name.
prompt
prompt Log file Name?
define lognamearg = '&6'
declare
  badchars varchar2(80);
 
  badchars_nonew varchar2(80); -- variable added for bug 9769965
  -- Selecvt all columns to check
  /* Below cursor has been changed for bug 15936574, for NZDT code in 12.2
   physical columns of tables should not be accessed, any acces should be
   through the editioning view.    */
  cursor colcurs is
  select  C.TABLE_NAME, C.COLUMN_NAME
  from    ALL_TAB_COLUMNS C, USER_SYNONYMS SYN
  where   C.TABLE_NAME = SYN.TABLE_NAME
  and     C.OWNER = SYN.TABLE_OWNER
  and     C.DATA_TYPE = 'VARCHAR2'
  and     SYN.SYNONYM_NAME like nvl(upper('&tblarg'), '%')
  and     C.COLUMN_NAME like nvl(upper('&colarg'), '%')
  union
  select  UC.TABLE_NAME, UC.COLUMN_NAME
  from    USER_TAB_COLUMNS UC
  where   UC.DATA_TYPE = 'VARCHAR2'
  and     UC.TABLE_NAME like nvl(upper('&tblarg'), '%')
  and     UC.COLUMN_NAME like nvl(upper('&colarg'), '%')
  order by 1, 2;
  curs integer;            -- Cursor
  sqlbuf varchar2(2000);   -- Sql stmt
  ignore integer;          -- Retcode
  value varchar2(2000);    -- Value with bad chars selected from table
  newlineflag boolean;  -- Check for newlines
  fixflag boolean;      -- Strip trailing spaces
  badvalfound boolean;  -- Bad value detected for this column
  lang     varchar2(255);
  --- below vars have been added to write logs to a file.
  LOGFILE_LOCATION varchar2(255);
  LOGFILE_NAME  varchar2(255);
  LOGFILE_HANDLE  utl_file.file_type;
  LOGTOFILE  boolean := TRUE;
  MAX_LINESIZE   binary_integer := 32767;
  AFCHRCHK_LOG_LOCATION varchar2(255);
begin
  -- Initialize badchars to non-printable chars (except newline)
  -- Instead of using fnd_global.local_chr at all the following places, the
  -- logic invloved in fnd_global.local_chr is added after building the
  -- complete string of badchars. To exempt GSCC check 'File.Sql.10' on this
  -- file, a bug # 4383895 is raised and got approved.
  badchars :=
 chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||
 chr(9)||chr(11)||chr(12)||chr(13)||chr(14)||chr(15)||
 chr(16)||chr(17)||chr(18)||chr(19)||chr(20)||chr(21)||chr(22)||
 chr(23)||chr(24)||chr(25)||chr(26)||chr(27)||chr(28)||chr(29)||
 chr(30)||chr(31)||chr(127);
  lang := userenv('LANGUAGE');
  badchars := convert(badchars,
                          substr(lang, instr(lang,'.') + 1), 'US7ASCII');
  newlineflag := (upper('&newlinearg') = 'Y');
  fixflag := (upper('&fixarg') = 'Y');
  badchars_nonew := badchars;
  if (newlineflag) then
    -- Add in newline
    badchars := badchars||chr(10);
  end if;
  -- Convert to local codeset
  badchars := convert(badchars,
        substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') +1),
 'US7ASCII');
 LOGFILE_LOCATION :=  '&logdirarg';
 LOGFILE_NAME  :=  '&lognamearg';
 AFCHRCHK_LOG_LOCATION  :=      '&logdirarg';
if LOGFILE_LOCATION is null then 
  dbms_output.put_line('NULL has been entered for the log file directory '||
  'location, checking for DB utl_file_dir parameter');
 
   begin
 select translate(ltrim(value),',',' ') 
 into LOGFILE_LOCATION
 from v$parameter 
 where lower(name) = 'utl_file_dir';

 exception when others then
 LOGFILE_LOCATION := NULL;
 end;
 if (instr(LOGFILE_LOCATION,' ') > 0 and LOGFILE_LOCATION is not null)
        then
 LOGFILE_LOCATION := substrb(LOGFILE_LOCATION, 1,
         instr(LOGFILE_LOCATION,' ') - 1);
 AFCHRCHK_LOG_LOCATION := LOGFILE_LOCATION;
 end if; 
 if LOGFILE_LOCATION is null then
 dbms_output.put_line('Unable to retrieve the DB utl_file_dir '||
    'parameter, not logging to any file');
 LOGTOFILE := FALSE;
 end if;
else        
 begin
 execute immediate 'create or replace directory '||
 'AFCHRCHK_LOG_DBOBJECT as '''||AFCHRCHK_LOG_LOCATION||'''';
 execute immediate 'grant read, write on directory '||
 'AFCHRCHK_LOG_DBOBJECT to PUBLIC';
 LOGFILE_LOCATION := 'AFCHRCHK_LOG_DBOBJECT';
 exception when  others then
 dbms_output.put_line('Unable to open file in the specified '||
 'directory, not logging to any file');
 LOGTOFILE := FALSE;
 end;
end if;
if LOGFILE_NAME is null then
    
 begin
 select 'afchrchklog'||'_'||lower(host_name)||'_'||lower(instance_name)||'.txt'
 into LOGFILE_NAME
 from v$instance;
 exception when others then
  LOGFILE_NAME := NULL; 
 end;
 
 if LOGFILE_NAME is null then 
 dbms_output.put_line('Unable to query v$instance to get default file'||
    ' name, not logging to any file' );
 LOGTOFILE := FALSE;
 end if;
end if;
if LOGTOFILE then
 begin
 LOGFILE_HANDLE := utl_file.fopen(LOGFILE_LOCATION, LOGFILE_NAME, 'a',
     MAX_LINESIZE);
 dbms_output.put_line('Logging to file '||LOGFILE_NAME||' in location '||
    AFCHRCHK_LOG_LOCATION);

 exception when others then
 dbms_output.put_line('Unable to open the file '||LOGFILE_NAME||' in'||
  'location '||AFCHRCHK_LOG_LOCATION||' , not logging to any file');
 LOGTOFILE := FALSE;
 end ;
end if;
  for col in colcurs loop
    begin
 if LOGTOFILE then
 utl_file.put_line(LOGFILE_HANDLE,'Checking '||
     col.table_name||'.'||col.column_name);
 utl_file.fflush(LOGFILE_HANDLE);
 end if;
      badvalfound := FALSE;
      -- Open cursor to check single table.column
      curs := dbms_sql.open_cursor;
-- If condition has been added for bug 9769965, COMPILED_VALUE_ATTRIBUTES in
-- FND_FLEX_VALUES should be exempted from replacing newline chars.
 if((col.table_name = 'FND_FLEX_VALUES' or col.table_name = 'FND_FLEX_VALUES#') and col.column_name='COMPILED_VALUE_ATTRIBUTES') then 
   sqlbuf := 'SELECT '||col.column_name||' value '||
       ' FROM '||col.table_name||
      ' WHERE '||col.column_name||' != LTRIM(RTRIM(TRANSLATE('||
      col.column_name||', '''||badchars_nonew||''',  '' '')))';
 else
          sqlbuf := 'SELECT '||col.column_name||' value '||
                    ' FROM '||col.table_name||
                    ' WHERE '||col.column_name||' != LTRIM(RTRIM(TRANSLATE('||
                    col.column_name||', '''||badchars||''',  '' '')))';
 end if;
      dbms_sql.parse(curs, sqlbuf, dbms_sql.v7);
      dbms_sql.define_column(curs, 1, value, 2000);
      ignore := dbms_sql.execute(curs);
      -- Select all values with bad chars
      loop
        if (dbms_sql.fetch_rows(curs) = 0) then
          exit;
        end if;
        badvalfound := TRUE;
        dbms_sql.column_value(curs, 1, value);
        -- Print value selected
        if (length(value) < 250) then
  if LOGTOFILE then
  utl_file.put_line(LOGFILE_HANDLE,'### '''||value||'''');
  utl_file.fflush(LOGFILE_HANDLE);
  end if;
        else
  if LOGTOFILE then
  utl_file.put_line(LOGFILE_HANDLE,'### (TRUNCATED) '''||
    substr(value, 1, 235)||'''');
  utl_file.fflush(LOGFILE_HANDLE);
  end if;
        end if;
      end loop;
      dbms_sql.close_cursor(curs);
      if (badvalfound and fixflag) then
        -- Strip spaces and control characters
 if LOGTOFILE then
 utl_file.put_line(LOGFILE_HANDLE,'Fixing '||
   col.table_name||'.'||col.column_name);
 utl_file.fflush(LOGFILE_HANDLE);
 end if;
        curs := dbms_sql.open_cursor;
-- If condition has been added for bug 9769965, COMPILED_VALUE_ATTRIBUTES in
-- FND_FLEX_VALUES should be exempted from replacing newline chars.
 if((col.table_name = 'FND_FLEX_VALUES' or col.table_name = 'FND_FLEX_VALUES#') and col.column_name='COMPILED_VALUE_ATTRIBUTES') then 
   sqlbuf := 'UPDATE '||col.table_name||' SET '||
      col.column_name||' = LTRIM(RTRIM(TRANSLATE('||
      col.column_name||', '''||badchars_nonew||''', '||
      'rpad('' '', length('''||badchars_nonew||''')))))';
 else
   sqlbuf := 'UPDATE '||col.table_name||' SET '||
      col.column_name||' = LTRIM(RTRIM(TRANSLATE('||
      col.column_name||', '''||badchars||''', '||
      'rpad('' '', length('''||badchars||''')))))';
 end if;
       
        dbms_sql.parse(curs, sqlbuf, dbms_sql.v7);
        ignore := dbms_sql.execute(curs);
        dbms_sql.close_cursor(curs);
      end if;
    exception
      when others then
 if LOGTOFILE then
  utl_file.put_line(LOGFILE_HANDLE,'***'||sqlerrm);
  utl_file.fflush(LOGFILE_HANDLE);
 end if;
    end;
  end loop;
  if UTL_FILE.IS_OPEN(LOGFILE_HANDLE) then
     utl_file.fclose(LOGFILE_HANDLE); 
  end if;
exception
  when others then
    if (dbms_sql.is_open(curs)) then
      dbms_sql.close_cursor(curs);
    end if;
    if UTL_FILE.IS_OPEN(LOGFILE_HANDLE) then
      utl_file.fclose(LOGFILE_HANDLE);
    end if;
    raise;
end;
/