I have a comma seperated file with data like this which is from shell script: id , jobname , started, ended , time 1 , A , 1130 , 1200 , 30 2 , A , 1120 , 1130 , 10
I need to load these data to a oracle table and need to check before inserting whether the record already exists ,if exists then update else insert .I have used sqlplus for the connection from shell script to oracle .Any help would be appreciated. Thanks in Advance.
Doing the solution the hardest way
$ cat test.dat
id , jobname , started, ended , time
1 , A , 1130 , 1200 , 30
2 , A , 1120 , 1130 , 10
3 , C , 1120 , 1130 , 10
In Oracle
SQL> create table t ( id number, jobname varchar2(1) , started number, ended number, elapsed number ) ;
Table created.
SQL> insert into t values ( 1 , 'A' , 1130 , 1200 , 30 ) ;
1 row created.
SQL> insert into t values ( 2, 'B' , 1120 , 1130 , 10 ) ;
1 row created.
SQL> select * from t ;
ID J STARTED ENDED ELAPSED
---------- - ---------- ---------- ----------
1 A 1130 1200 30
2 B 1120 1130 10
Under this scenario our program needs to update the record with ID 2 and insert the record with ID 3. This is just a basic shell program to do so, but by far this is the worst way to handle this problem. An external table using the oracle sql loader driver would be much better, easier and faster.
Code
#/bin/bash
records=$(cat /home/ftpcpl/test.dat | tail -n +2 | wc -l)
echo "Number of records in file: $records"
record_exists ()
{
record_id=$1
counter=`sqlplus -S "/ as sysdba" << eof
whenever sqlerror exit 2;
set echo off verify off head off feed off
select count(*) from t where t.id = ${record_id} ;
eof`
if [[ $? -eq 2 ]]; then exit 2; fi
export counter=$(echo ${counter} | tr -d '\n')
}
UpdateRecord ()
{
${ORACLE_HOME}/bin/sqlplus "/ as sysdba" << eof >> $logfile
whenever sqlerror exit failure;
update t set jobname = '${jb}' ,
started = ${st} ,
ended = ${en} ,
elapsed = ${ti}
where id = ${id} ;
commit;
eof
if [[ $? -eq 2 ]]; then exit 2; fi
}
InsertRecord ()
{
${ORACLE_HOME}/bin/sqlplus "/ as sysdba" << eof >> $logfile
whenever sqlerror exit failure;
insert into t values ( ${id} , '${jb}' , ${st} , ${en} , ${ti} );
commit;
eof
if [[ $? -eq 2 ]]; then exit 2; fi
}
logfile=test.log
tail -n +2 /home/ftpcpl/test.dat |
while read -r line
do
echo $line
export id=$(echo $line | awk -F ',' '{print $1}' | xargs echo -n )
export jb=$(echo $line | awk -F ',' '{print $2}' | xargs echo -n )
export st=$(echo $line | awk -F ',' '{print $3}' | xargs echo -n )
export en=$(echo $line | awk -F ',' '{print $4}' | xargs echo -n )
export ti=$(echo $line | awk -F ',' '{print $5}' | xargs echo -n )
record_exists ${id}
if [[ ${counter} -eq 1 ]];
then
UpdateRecord
else
InsertRecord
fi
done
Demo program
./test.sh
Number of records in file: 3
1 , A , 1130 , 1200 , 30
2 , A , 1120 , 1130 , 10
3 , C , 1120 , 1130 , 10
[ftpcpl@scglvdoracd0006 ~]$ cat test.log
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:34 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> SQL> 2 3 4 5
1 row updated.
SQL>
Commit complete.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:36 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> SQL> 2 3 4 5
1 row updated.
SQL>
Commit complete.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:38 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> SQL>
1 row created.
SQL>
Commit complete.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
Final records
SQL> select * from t ;
ID J STARTED ENDED ELAPSED
---------- - ---------- ---------- ----------
1 A 1130 1200 30
2 A 1120 1130 10
3 C 1120 1130 10