Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle - sqlldr Data comparison

Posted on 2014-04-01
9
Medium Priority
?
595 Views
Last Modified: 2014-04-08
Hi,

I have a .csv file which contains 88672802 lines like this:

.
.
"556593136109","724056910751069"
"556593136129","724056910751077"
"556593136239","724056910751103"
.
.

And I have a Oracle DB with a table that has the "same" data like this:

                                  089724051901464402
                                  089724053900623352
558791424791          089724058301058478
552174612635          089724052805529543

So, I want to compare the two outputs and I think the easiest way to do it is to use the SQL Loader and load the .csv file info into the DB and then compare the two tables...

Is this the best way to do it?

Tks,
Joao
0
Comment
Question by:joaotelles
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39970322
If the file is on the database server then I would create an External Table (it uses sql loader syntax but never loads the data physically into the database).

If the file is on a remote client, then I would probably load it into a physical table and do the compare.
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39970377
If you can have the file on the database server, the external table is the best approach.
Other than that, use sqlloader script to load the file into another table and then compare.
0
 

Author Comment

by:joaotelles
ID: 39970524
sqlldr qworked just fine!

I created the table and imported it using a control file.

But now I have two tables:

SQL> desc HLR_DUMP_2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DPSN_DESTINATIONADDRESS                            VARCHAR2(21)
 DPSN_IMSI                                          VARCHAR2(18)


SQL> desc dp_subscription;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DPSN_IMSI                                          VARCHAR2(18)
 DPSN_DESTINATIONADDRESS                            VARCHAR2(21)

That I want to compre and have a table with the rows that are different from each other...

Is this possible?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39970643
>>Is this possible?

Easiest is to run two sql commands:
select DPSN_IMSI, DPSN_DESTINATIONADDRESS from dp_subscription
minus
select DPSN_IMSI, DPSN_DESTINATIONADDRESS from  HLR_DUMP_2;


Then reverse it:
select DPSN_IMSI, DPSN_DESTINATIONADDRESS from  HLR_DUMP_2
minus
select DPSN_IMSI, DPSN_DESTINATIONADDRESS from dp_subscription;

The reason for the two runs is the minus will not only return differences in rows but also rows missing from one.  You need to run it twice to catch rows missing from either table.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39970867
Another solution is to save your result in txt file and use OS utility to compare two files. i.e. in Unix use diff command. Not sure which OS version are you using.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39971382
One of the alternative solution  is there which is GUI Based,

If you have tools like Toad, Beyond Compare etc.,

You just have to open the two files using the utilities and click on Compare button, it will automatically list out the differences and will also color them so as to identify them easily.
0
 

Author Comment

by:joaotelles
ID: 39972014
I ewill try them all....

One thing is that in the HLR_DUMP_2 in the DPSN_IMSI Im missing the '089' in the start of it...

Sry for the newbie question but what would be the easiest way to add it (I mean the fastest because the table has 80M rows)..

I have this:
select DPSN_IMSI, DPSN_DESTINATIONADDRESS from  HLR_DUMP_2;
.
558791424791          724058301058478
552174612635          724052805529543
.

And need to transform to this:
.
558791424791          089724058301058478
552174612635          089724052805529543
.

====

For the minus solution, do I need to add a order by statment in the select´s?
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39972818
seems that you are trying to compare cdr records from two sub sytems(hlr and switch or some other bss)

no need for an order by clause for a minus statement

as you just want to do a comparision, you can do it in select statement itself

select dpsn_imsi, '089'||dpsn_destinationaddress from hlr_dump_2
0
 

Author Closing Comment

by:joaotelles
ID: 39985789
Tks.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question