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