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 —
— =============================================================

oracle procedure : number validation

if ( (LENGTH(TRIM(TRANSLATE(contentcode, ‘+0123456789′,’ ‘))) is not null) or (LENGTH(TRIM(TRANSLATE(otherfield, ‘+0123456789′,’ ‘))) is not null)) then
dbms_output.put_line(‘contentcode or otherfield is invalid’); 

Oracle : number check in database

if ( (LENGTH(TRIM(TRANSLATE(contentcode, ‘+0123456789′,’ ‘))) is not null) or (LENGTH(TRIM(TRANSLATE(otherfield, ‘+0123456789′,’ ‘))) is not null)) then
dbms_output.put_line(‘contentcode or otherfield is invalid’);

oracle : create external table

 in browser type url ip-Of-Server:1158/em/console/

1. in browser type url ip-Of-Server:1158/em/console/
2. login there
3. go to Schema-> Directory Objects
4. click the “create” Button
5. fill in the form

6. * Name	DIR_WLIST
   * Path	/tmp/test_v_dir

   where Name= name of virtual directory and Path= path to the directory

7. now thru ftp client (or putty ) create a file test.txt in /tmp/test_v_dir/
8. then go to sqlplus
9. type in follwing query

create table XTERN_test( student_name varchar2(100) )
organization external (
type oracle_loader
default directory DIR_WLIST
access parameters (
records delimited by newline
)
location (‘test.txt’)
)
reject limit unlimited;

oracle : external table

1. in browser type url ip-Of-Server:1158/em/console/

1. in browser type url ip-Of-Server:1158/em/console/
2. login there
3. go to Schema-> Directory Objects
4. click the “create” Button
5. fill in the form

6. * Name	DIR_WLIST
   * Path	/tmp/test_v_dir

   where Name= name of virtual directory and Path= path to the directory

7. now thru ftp client (or putty ) create a file test.txt in /tmp/test_v_dir/
8. then go to sqlplus
9. type in follwing query
10. create table XTERN_test( student_name varchar2(100) )
    organization external (
    type oracle_loader
    default directory DIR_WLIST
    access parameters (
    records delimited by newline
    )
    location (‘test.txt’)
    )
    reject limit unlimited;

Time difference between two date columns

Solution 1

SQL> CREATE TABLE dates (date1 DATE, date2 DATE);

Table created.

SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);

1 row created.

SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);

1 row created.

SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);

1 row created.

SQL> SELECT (date1 - date2) FROM dates;

DATE1-DATE2
-----------
          1
 .041666667
 .000694444

Solution 2

SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2 || ' HOURS ' ||
3 floor((((date1-date2)*24*60*60) -
4 floor(((date1-date2)*24*60*60)/3600)*3600)/60)
5 || ' MINUTES ' ||
6 round((((date1-date2)*24*60*60) -
7 floor(((date1-date2)*24*60*60)/3600)*3600 -
8 (floor((((date1-date2)*24*60*60) -
9 floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
10 || ' SECS ' time_difference 11 FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS 0
HOURS 1 MINUTES 0 SECS.

SQL> SELECT (date1 - date2) FROM dates;

DATE1-DATE2
-----------
          1
 .041666667
 .000694444

Solution 3

SQL> SELECT to_number( to_char(to_date('1','J') +
  2         (date1 - date2), 'J') - 1)  days,
  3         to_char(to_date('00:00:00','HH24:MI:SS') +
  4         (date1 - date2), 'HH24:MI:SS') time
  5   FROM dates;

      DAYS TIME
---------- --------
         1 00:00:00
         0 01:00:00
         0 00:01:00

Solution 4

NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are ‘DAY’, ‘HOUR’, ‘MINUTE’ and ‘SECOND’.

This function converts the number x into an INTERVAL DAY TO SECOND datatype.

SQL> select numtodsinterval(date1-date2,'day') time_difference from dates;

TIME_DIFFERENCE
----------------------------------------------------------------
+000000001 00:00:00.000000000
+000000000 01:00:00.000000000
+000000000 00:01:00.000000000

Solution 5

this works for both Date and timestamp columns

CREATE OR REPLACE FUNCTION datediff
( time1 TIMESTAMP,  time2 TIMESTAMP )
-- RETURN NUMBER
RETURN number
AS sec NUMBER;

BEGIN

SELECT (extract(DAY FROM time2-time1)*24*60*60)+
(extract(HOUR FROM time2-time1)*60*60)+
(extract(MINUTE FROM time2-time1)*60)+
extract(SECOND FROM time2-time1)
into sec FROM dual;

RETURN sec;

END;