Solved

Oracle - sqlldr Data comparison

Posted on 2014-04-01
9
554 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 76

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 15

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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now