sql loader control file+ Unix scripting [message #623614] |
Thu, 11 September 2014 22:01 |
|
f150
Messages: 13 Registered: September 2014
|
Junior Member |
|
|
Hello Friends,
I am completely new to Oracle db and Unix scripting.
I have a task of making a process which can pick up multiple files from specific folder, process them and load them into Oracle db table via SQL-LOADER.
My source files are .csv format excel spreadsheet of up to 700MB size.
(1.) the logic I can not figure out is to compare a column from incoming file to my oracle table partition(column name is partition ID), if it matches that partition then it needs to be dropped . if it does not match than create a new partition based on that column and load the data.
(2.) populate the column "File_Name" from which the data is being loaded in to table.
I looked over in other forums they indicated to make a batch file containing sqlldr+ username/password+ control file+ log file + bad file. and asked to manually write a control file containing my data mapping and script for (1) and (2).
I have been instructed to use Unix shell script to invoke SQL-LOADER (using putty) for the process. but I don't know how to do Unix coding which can process/pass each file one by one to SQL-LOADER.
please help me out on this, let me know I am missing any details or specifications.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: sql loader control file+ Unix scripting [message #623704 is a reply to message #623703] |
Fri, 12 September 2014 13:58 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Something like this. Make it fancy to fit your needs.
##
## read the source file.
## Look for the first value. Process one-by-one.
##
oracle@kanada#cat somescript
awk -F" " '{print$1}' source.dat | sort | uniq | while read pname
do
sq=`sqlplus -s dbadmin/xx@xx <<EOF
set head off
select count(partition_name) from user_tab_partitions where table_name ='T1' and partition_name like (upper('MODEL_$pname'));
exit;
EOF`
if [ "$sq" -eq 1 ]; then
echo "Partition $pname found. Do whatever you want";
else
echo "Partition $pname notfound. ";
fi
done
##
## Sample source file
##
oracle@kanada#cat source.dat
focus 2013 se
focus 2014 s
focus 2012 se
focus 2013 h
something 2013 som
this 2014 that
##sample run
oracle@kanada#./somescript
Partition focus found. Do whatever you want
Partition something notfound.
Partition this notfound.
[Updated on: Fri, 12 September 2014 13:59] Report message to a moderator
|
|
|
|