Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle - sqlldr Data comparison

Posted on 2014-04-01
9
565 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 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

856 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