Shell Programming and Scripting

 
extn="perl"
ls -1 | while read file
do
echo "$file,$extn" > a.csv
done

update file_list set filename=$1 where extn=$2;

 
while read line
file=`cut -d "," -f 1`
extn=`cut -d "," -f 2`
do
sqlplus <<END
username/password@connectstring
@update.sql $file $extn
commit;
END
done< a.csv

-- Create oracle external table linked to a flat file(i.e. a.csv)
create table schema.table ( 
  account_num                 varchar2( 16 ),
  account_id                  varchar2( 10 ),
  rpt_num                     varchar2(  6 ),
  report_file_name            varchar2( 64 ) )
organization external (
  type              oracle_loader
  default directory TMP
  access parameters (
    records delimited by newline
    badfile      'accounts.bad'
    discardfile  'accounts.dis'
    logfile      'accounts.log'
    fields  terminated  by '-'
    missing field values are null
    ( account_num,
      account_id,
      rpt_num,
      report_file_name ) )
    location ( 'a.csv' ) )