Solved

Oracle - sqlldr Data comparison

Posted on 2014-04-01
9
558 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

895 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

17 Experts available now in Live!

Get 1:1 Help Now