Unanswered: how to Compare 2 tables using cursors??
I have 2 tables that have the same fields and the same data. However when I counted the records, I found that one table contain more records than the other. Now I need to delete the extra records and make the table identical. How can I do this using cursor?
The problem that it's not that easy. One table is in Oracle Platform and the other is in as400 (IBM platform) but I took care of that. The other problem is that the oracle table contain around 87000 records and as400 table has 84000 although we are running a batch file to copy the same records to both tables. The normal way of doing this will take at least 1 hour to run the query. So I tought cursors will make it faster.
Yes this is exactly what I need and note that there are 4 PK for the tables. What I found out that sometimes when the user delete a record from the as400 table it's not deleted in oracle. that's why we have more records in oracle.
the system we have is basically like this
as400 master table : store original data
as400 intermediate file : takes the data from the master table and store them
oracle intermediate file: take the information from the as400 intermediate file and store them
oracle master table: takes the information from oracle intermediate table.
so at the end the as400 master table and the oracle master table must be identical however they are not.
Originally posted by moza
The normal way of doing this will take at least 1 hour to run the query. So I tought cursors will make it faster.
Now that I've got your answers, let's get back to your original problem.
1. Of course you can do it using cursors (read from as400, see if it exists on Oracle, ...). This is quite straightforward to implement, but it will not be faster than copying the data directly (see point 2). Moreover, you need to write code (and we don't like that, do we...)
2. My current project also involves an AS400 (source) and an Oracle database (destination). I cannot imagine that those 87000 rows are giving you a hard time. I don't know the size of a row, nor do I know about the number of indexes, etc. that are linked to the destination table, but I can simply not imagine that it takes about 1 hour. Did you try using the (Sql*Plus) command COPY FROM ?
I know that there are some simple ways of doing this, but it's not dicision to make. Moreover, these table are so active and users are accessing it every seconds so there should be no chance for any mistake or loss or data. What my boss suggested is to run cursors for both tables and store as oracle record in local variables, search for it in as400, if not exists then delete it. But It's not working.
The tables doesn't use indexes.
What exactly do you mean by "The tables doesn't use indexes" ?
Anyway, I'm a getting out of ideas here. Knowing a little bit about the AS400, and considering the fact that you don't want to have mistakes/loss or data during your patch, my guess is you need to run your patch using a very high commitment control level (on the AS400) like REPEATABLE READ, which gets your users locked anyway.