Bash-Script/getInsertQueriesALL.sh at main · vikassharmapunj/Bash-Script · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
#!/bin/bash

if [ "$#" -ne 4 ]; then
	echo "Please use like below"
	echo "./$0 <SOURCE_IP> <DEST_IP> <SOURCE_SID> <DEST_SID> [SCHEMA_FLAG]"
	echo "SCHEMA FLAG VALUES"
	echo "defalut->All 1->paypro 2->billergw 3->camp"
	exit
fi

SOURCE_DIRECTORY=/home/oracle/masterSync
SOURCEIP=$1
DESTIP=$2
SOURCE_SID=$3
DEST_SID=$4
SCHEMA_FLAG=$5

LOG_FILE=$SOURCE_DIRECTORY/logs/logs.txt
TEMP_FILE=$SOURCE_DIRECTORY/temp.txt
TEMP_FILE1=$SOURCE_DIRECTORY/temp1.txt
MASTER_TABLES_FILE=$SOURCE_DIRECTORY/input/mastertable.txt
SPOOL_FILE_PAYPRO=$SOURCE_DIRECTORY/output/payproMaster.txt
SPOOL_FILE_BILLERGW=$SOURCE_DIRECTORY/output/billergwMaster.txt
SPOOL_FILE_CAMP=$SOURCE_DIRECTORY/output/campMaster.txt

>$TEMP_FILE
>$SPOOL_FILE_PAYPRO
>$SPOOL_FILE_BILLERGW
>$SPOOL_FILE_CAMP

echo "script started at `date` for params:$1,$2,$3,$4" >>$LOG_FILE
############# START - GET INSERT QUERY FOR PAYPRO SCHEMAS ###########

ALL_TABLE=`cat $MASTER_TABLES_FILE | grep -v "^#" | grep "paypro" |awk -F"=" '{printf "\x27"$1"\x27"}' | sed -e "s/''/','/g"`
#echo $ALL_TABLE

/usr/local/bin/sqlplus -s <<EOF> $TEMP_FILE paypro/spice#123@$SOURCEIP/$SOURCE_SID
set echo off
set heading off
set lin 500
set linesize 500
set pagesize 0
set long 1000000
column HEAD format A2000

spool $SPOOL_FILE_PAYPRO
	select 'insert into ' || table_name || ' (' || ( select rtrim(extract(xmlagg(xmlelement(e, t.column_value.getrootelement() || ',')),'//text()'),',') from table(xmlsequence(t.column_value.extract('ROW/*'))) t) || ') values (' || ( select dbms_xmlgen.convert(rtrim(extract(xmlagg(xmlelement(e, '''' || t.column_value.extract('//text()') || ''',')),'//text()'),','),1) from table(xmlsequence(t.column_value.extract('ROW/*')))t) || ');' ins_stmt from user_tables , table(xmlsequence(dbms_xmlgen.getxmltype('select * from ' || table_name).extract('ROWSET/ROW'))) t where table_name IN ($ALL_TABLE);
spool off;

exit 1;
EOF

cat $SPOOL_FILE_PAYPRO | sed -e 's/  //g' | grep -v "^$" |  grep -v "rows selected"  >$TEMP_FILE1
mv $TEMP_FILE1 $SPOOL_FILE_PAYPRO

############# END - GET INSERT QUERY FOR PAYPRO SCHEMAS ###########

############# START - GET INSERT QUERY FOR BILLERGW SCHEMAS ###########

ALL_TABLE=`cat $MASTER_TABLES_FILE | grep -v "^#" | grep "billergw" |awk -F"=" '{printf "\x27"$1"\x27"}' | sed -e "s/''/','/g"`
#echo $ALL_TABLE

/usr/local/bin/sqlplus -s <<EOF> $TEMP_FILE billergw/spice#123@$SOURCEIP/$SOURCE_SID
set echo off
set heading off
set lin 500
set linesize 500
set pagesize 0
set long 1000000
column HEAD format A2000

spool $SPOOL_FILE_BILLERGW
	select 'insert into ' || table_name || ' (' || ( select rtrim(extract(xmlagg(xmlelement(e, t.column_value.getrootelement() || ',')),'//text()'),',') from table(xmlsequence(t.column_value.extract('ROW/*'))) t) || ') values (' || ( select dbms_xmlgen.convert(rtrim(extract(xmlagg(xmlelement(e, '''' || t.column_value.extract('//text()') || ''',')),'//text()'),','),1) from table(xmlsequence(t.column_value.extract('ROW/*')))t) || ');' ins_stmt from user_tables , table(xmlsequence(dbms_xmlgen.getxmltype('select * from ' || table_name).extract('ROWSET/ROW'))) t where table_name IN ($ALL_TABLE);
spool off;

exit 1;
EOF

cat $SPOOL_FILE_BILLERGW | sed -e 's/  //g' | grep -v "^$" | grep -v "rows selected"  >$TEMP_FILE1
mv $TEMP_FILE1 $SPOOL_FILE_BILLERGW

############# END - GET INSERT QUERY FOR BILLERGW SCHEMAS ###########

############# START - GET INSERT QUERY FOR CAMP SCHEMAS ###########

ALL_TABLE=`cat $MASTER_TABLES_FILE | grep -v "^#" | grep "camp" |awk -F"=" '{printf "\x27"$1"\x27"}' | sed -e "s/''/','/g"`
#echo $ALL_TABLE

/usr/local/bin/sqlplus -s <<EOF> $TEMP_FILE camp/spice#123@$SOURCEIP/$SOURCE_SID
set echo off
set heading off
set lin 500
set linesize 500
set pagesize 0
set long 1000000
column HEAD format A2000

spool $SPOOL_FILE_CAMP
	select 'insert into ' || table_name || ' (' || ( select rtrim(extract(xmlagg(xmlelement(e, t.column_value.getrootelement() || ',')),'//text()'),',') from table(xmlsequence(t.column_value.extract('ROW/*'))) t) || ') values (' || ( select dbms_xmlgen.convert(rtrim(extract(xmlagg(xmlelement(e, '''' || t.column_value.extract('//text()') || ''',')),'//text()'),','),1) from table(xmlsequence(t.column_value.extract('ROW/*')))t) || ');' ins_stmt from user_tables , table(xmlsequence(dbms_xmlgen.getxmltype('select * from ' || table_name).extract('ROWSET/ROW'))) t where table_name IN ($ALL_TABLE);
spool off;

exit 1;
EOF

cat $SPOOL_FILE_CAMP | sed -e 's/  //g' | grep -v "^$" | grep -v "rows selected"  >$TEMP_FILE1
mv $TEMP_FILE1 $SPOOL_FILE_CAMP

############# END - GET INSERT QUERY FOR CAMP SCHEMAS ###########
echo "script ended at `date` for params:$1,$2,$3,$4,paypro rows added/updated(`cat $SPOOL_FILE_PAYPRO | wc -l`),billergw rows added/updated(`cat $SPOOL_FILE_BILLERGW | wc -l`), camp rows added/updated(`cat $SPOOL_FILE_CAMP | wc -l`)" >>$LOG_FILE

############# START - INSERT DATA FOR CAMP SCHEMAS ###########
/usr/local/bin/sqlplus -s <<EOF> $TEMP_FILE camp/spice#123@$DESTIP/$DEST_SID @$SPOOL_FILE_CAMP
EOF
############# END - INSERT DATA FOR CAMP SCHEMAS ###########