Solved

Oracle - sqlldr Data comparison

Posted on 2014-04-01
9
569 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
[X]
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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 77

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 77

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

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!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

733 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