Shell Script for updating an Oracle table through a file (from a remote Server)

a simple Script for updating an Oracle table through a file (from a remote Server)
we will b using a shell script for all this
process is described step by step as

1. first Of all create a virtual directory and external table use this page http://eaziweb.blogspot.com/2012/03/oracle-create-external-table.html
2. get file from remote server using ncftpget
3. create Oracle Procedure for data insertions
4. Oracle Procedure uses  UTL_FILE utility for logging different file events like success and failure

Summary

Serrver : server IP like 192.168.168.0
directory Object Name : DIR_3352
directory Object Path : /home/xyz/3352
External File : xtern_3352.txt
Upload File Name Format : add_20120612.csv
Upload File Format : 03007236317,09-MAR-12,C2676M0749
External Table :
create table xtern_3352 ( mobile varchar2(100),dateCol varchar2(20),id varchar2(20))
organization external (
type oracle_loader
default directory DIR_3352
access parameters (
records delimited by newline
FIELDS TERMINATED BY ','
)
location ('xtern_3352.txt')
)
reject limit unlimited;
Procedure : TEST_PARAMETER

Shell Script :

######################################################################
##                                              USAGE                                                                         ##
##      call it as 3355.sh action-parameter                                                                          ##
##      eg. source 3355.sh add                                                                                          ##
##      current action parameters are                                                                                  ##
##      1.add                                                                                                                     ##
##      2.delete                                                                                                                  ##
######################################################################
#!/bin/sh
action=$1
day=1
date_var=`date -d "-$day days" +%Y-%m-%d`
#backup old files
mv -f /home/xyz/3352/xtern_3352.txt /home/xyz/3352/xtern_3352_`echo $date_var`.txt
#get the file from remote Server and put in /path/to/local/dir/ directory
ncftpget -u ftp-user-name -p paSSword rem.ote.Ser.ver.IP /path/to/local/dir/ /file`echo $date_var`_sub.csv
mv -f  /home/xyz/file`echo $date_var`_sub.csv  /home/xyz/new.txt
cat /home/xyz/new.txt | cut -d ' ' -f1> /home/xyz/xtern_3352.txt
source /file_validation_script.sh 3352/xtern_3352.txt
ORACLE_HOME=/Oracle/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
ORACLE_SID=orcl
export ORACLE_SID
/Oracle/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /NOLOG << EOF

connect db_user/db_pwd
set serveroutput on
exec TEST_PARAMETER('`echo $action`');
EXIT;
echo "Now making file"
EOF

Oracle Procedure : TEST_PARAMETER

 ( action IN varchar2 )
  AS CURSOR C_CONTENT IS SELECT * from xtern_3352 ;
--============================================================
-- --
-- Initialization --
--===========================================================--
datecheck number;
v_message varchar2(100);
ERROR_mobile_FORMAT exception;
ERROR_mobile_VALUE exception;
ERROR_DATE_VALUE exception;
ERROR_DUPLICATE_mobile exception;
ERROR_NO_mobile_FOUND  exception;
v_file  UTL_FILE.FILE_TYPE;
s_file  UTL_FILE.FILE_TYPE;
cur_date varchar2(100);
file_Name varchar2(100);
success_File_Name varchar2(100);
var_query varchar2(100);
--=============================================================--
-- --
--                        Start of Logic -- --                                                                   --
--=============================================================--
BEGIN

    -------------------------------------------------------------------------------------------
    --                       File details               --
    -------------------------------------------------------------------------------------------
  SELECT  to_char(sysdate, 'DDMonYYYY_HH24MISS') into cur_date from dual;
  file_Name := '' ;
  success_File_Name := '' ;
  if ( action = 'add') then
    dbms_output.put_line('add Action');
    file_Name := 'add_error_'||cur_date||'.txt' ;
    success_File_Name := 'add_success_'||cur_date||'.txt' ;
    --var_query := 'insert into WHITE_LIST_auto values('||wl_DATA.mobile||','||wl_DATA.dateCol||','||wl_DATA.ID||')' ;
    var_query := 'insert into WHITE_LIST_auto values(wl_DATA.mobile,wl_DATA.dateCol,wl_DATA.ID)' ;
  else if ( action = 'delete') then
    dbms_output.put_line('delete Action');
    file_Name := 'delete_error_'||cur_date||'.txt' ;
    success_File_Name := 'delete_success_'||cur_date||'.txt' ;
    var_query := 'delete from WHITE_LIST_auto where mobile=wl_DATA.mobile' ;
  else
    dbms_output.put_line('What do u want to do ??? Please Select an action (add or delete)');
    return;
 
    dbms_output.put_line('else Action');
    --return 'Please Select an action (add or delete)';
  end if;
  end if;

  SELECT  to_char(sysdate, 'DDMonYYYY_HH24MISS') into cur_date from dual;
  --file_Name := 'error_'||cur_date||'.txt' ;
  dbms_output.put_line('error is '|| file_Name);
  v_file := UTL_FILE.FOPEN(location     => 'dir_3352',
                           filename     => file_Name,
                           open_mode    => 'w',
                           max_linesize => 32767);
  --success_File_Name := 'success_'||cur_date||'.txt' ;
  dbms_output.put_line('success_File_Name is '|| success_File_Name);
  s_file := UTL_FILE.FOPEN(location     => 'dir_3352',
                           filename     => success_File_Name,
                           open_mode    => 'w',
                           max_linesize => 32767);

FOR wl_DATA in C_CONTENT
LOOP
  begin
    begin
    -------------------------------------------------------------------------------------------
    -- Error Validations      --
    -------------------------------------------------------------------------------------------
      if length(wl_DATA.mobile) <> 11 or substr(wl_DATA.mobile,0,2) <> '03' then
raise ERROR_mobile_FORMAT;
      else if  ( (LENGTH(TRIM(TRANSLATE(wl_DATA.mobile, '+0123456789',' '))) is not null)  ) then
raise ERROR_mobile_VALUE;
      else if (is_date(wl_DATA.dateCol, 'dd-mon-yy') = 0) then
raise ERROR_DATE_VALUE;
      else if (CHECK_WHITELIST(wl_DATA.mobile) = 1 and action = 'add') then
raise ERROR_DUPLICATE_mobile;
      else if (CHECK_WHITELIST(wl_DATA.mobile) = 0 and action = 'delete') then
raise ERROR_NO_mobile_FOUND;
      else
          -----------------------------------------------------------------------------
 -- Dumping Valid Data      --
 -----------------------------------------------------------------------------
 if ( action = 'add') then
   insert into WHITE_LIST_auto values(wl_DATA.mobile,wl_DATA.dateCol,wl_DATA.ID);
 else if ( action = 'delete') then
   delete from WHITE_LIST_auto where mobile=wl_DATA.mobile;
 else
   dbms_output.put_line('else Action');
   return;
   --return 'Please Select an action (add or delete)';
   end if;
   end if;
   
--dbms_output.put_line(var_query);
--execute immediate var_query ;
--insert into WHITE_LIST_auto values(wl_DATA.mobile,wl_DATA.dateCol,wl_DATA.ID);
UTL_FILE.PUT_LINE(s_file,wl_DATA.mobile|| ',' ||wl_DATA.dateCol|| ',' ||wl_DATA.ID);
      end if;
      end if;
      end if;
      end if;
      end if;
      exception
          -----------------------------------------------------------------------------
 -- Exception Handling      --
 -----------------------------------------------------------------------------
        when ERROR_mobile_FORMAT then
          v_message:='Mobile number Format is invalid';
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ',' ||wl_DATA.dateCol|| ',' ||wl_DATA.ID|| ',' ||v_message);
        when ERROR_mobile_VALUE then
          v_message:='Mobile number is invalid';
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ',' ||wl_DATA.dateCol|| ',' ||wl_DATA.ID|| ',' ||v_message);
when ERROR_DATE_VALUE then
          v_message:='date is invalid';
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ',' ||wl_DATA.dateCol|| ',' ||wl_DATA.ID|| ',' ||v_message);
        when ERROR_DUPLICATE_mobile then
          v_message:='Duplictae mobile';
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ',' ||wl_DATA.dateCol|| ',' ||wl_DATA.ID|| ',' ||v_message);
        when ERROR_NO_mobile_FOUND then
          v_message:='No mobile Found';
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ',' ||wl_DATA.dateCol|| ',' ||wl_DATA.ID|| ',' ||v_message);
    end;
  end;
  END LOOP;
  UTL_FILE.FCLOSE(v_file);        
  UTL_FILE.FCLOSE(s_file);        
commit;

END;
--=============================================================
-- --
-- End of Logic --
-- =============================================================