Get data from 3 differrnt oracle DB & then compare data

Shell Programming and Scripting
1

Hi All,
I have a requirement to write a shell script for the following...
we have 3 different database.... lets say A, B, C
From these 3 DBs, i need to get data.. all have 3 different table...a,b, c
A.a => Emp_code, count()
B.b => emp_code, count()
C.c => emp_code, copunt()
Once get this information, i need to compare these 3 tables using common emp_code
For example, for emp_code=100....
A.a.count() = B.b.count() & B.b.count() > C.c.count(*) If it is true then no action else write to log_file.
Can you please help me how to do it... all are Oracle DB

2

In addition,

all these 3 table contains more than 100 emp_code...

emp_code Table A.a count Table B.b Count Table C.C count
100 5748 5748 5040
201 2771 2771 1274
202 34 20 13

In this case emp_code 100 & 201 is tru but for 202 it is not tru so that i should be in log file. I also wanted to know how to retrive all information from differenbt table & store it into variable. For one value (emp_code) we can do.. but how about more than 100 emp_code to store in variable & then need to compare?

3

Could you please post what you have tried so far ? :slight_smile:

4

Hi Matrix,

I was thinking about using cursor to get the data from all there tables in 3 different Dbs. But my problem is i am not able to think on how to assign these values to variable & compare using one by one. Also, if i write cursor, i can not open all 3 cursors at a time.

Here i got stuck on thinking about this...

5

I think there are two approaches to do this.

Approach I
To write a complex, costly query that fetches data from multiple db compares and gives the output that is needed - everything in the form of query or to use a stored procedure to do this work. To some extent, after-query processing of data might be needed if everything cannot be achieved via querying from databases ( assuming you are not going for the option of stored procedures). But queries fetching data from multiple dbs don't come for free.

Approach II
Download the required data from each of the db/table and then use a perl/shell script to achieve the same.

The approaches need to be decided purely based on the importance of the application that you are writing. If the databases that are going to be queried are heavily loaded already, then its not worthwhile to use the approach I.

Approach I may not be more suited if the application is like reporting or batch processing that don't have strict timelines ( most of the time ) then its better to switch to approach II.