Oracle Database

#!/bin/ksh
clear
echo "******************************************************************************"
echo -n "Starting LOB check at "
date
echo "******************************************************************************"
sqlplus -S $1/$2@$3  <<EOF
set hea off
set echo off
set feedback off
drop table corrupted_lob_data;
create table corrupted_lob_data (table_name char(2000),column_name char(2000),corrupted_rowid rowid);
EOF

SDS_tab_list="table1 table2 table3 etc"

#echo ${SDS_tab_list} |wc -w
#echo ${SDS_tab_list}

for f in $SDS_tab_list
do
SDS_col_list=$(sqlplus -S $1/$2@$3  <<EOF
set hea off
set echo off
set feedback off
select column_name from all_lobs where owner='$1' and table_name = '$f';
EOF
)

if [[ ${SDS_col_list} = "" ]]
then echo "$f has no LOB columns. Moving onto next table"
else echo "$f has these lob columns: "${SDS_col_list}
fi
  for n in ${SDS_col_list}
  do
  echo "Looking at column: "${n} " on table "${f}
  sqlplus -S $1/$3@$3 <<EOF
  set concat off
  set hea off
  set echo off
  set feedback off
  declare
  error_1555 exception;
  pragma exception_init(error_1555,-1555);
  num number;
  begin
  for cursor_lob in (select rowid r, $n from $1.$f) loop
        begin
        num := dbms_lob.instr (cursor_lob.$n, hextoraw ('889911')) ;
        exception
        when error_1555 then
        insert into corrupted_lob_data(table_name,column_name,corrupted_rowid) values ('$f','$n',cursor_lob.r);
        commit;
  end;
  end loop;
  end;
  /
EOF

  done
done
sqlplus -S $1/$2@$3  <<EOF
select * from corrupted_lob_data;
EOF
date