compare tables record using UI tools on Oracle and MariaDB.

marrowyung
marrowyung used Ask the Experts™
on
hi,

anyone know how to use Oracle SQL developer to show a report and export as an excel for number of record information of all table belongs to a schema ?


I want to compare the same tables in MariaDB once I migrate from Oracle.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

This is fairly easy. In SQL Developer you Just execute your query on the table that you want to sample data from and right click on the result and choose export. The rest is just next-next-finish along with your choices in each step. ;)
Same goes for MariaDB database which you could connect to from the SQL Developer using the MySQL connector/connection that you have created.

Regards,
    Tomas Helgi
marrowyungSenior Technical architecture (Data)

Author

Commented:
" from the SQL Developer using the MySQL connector/connection that you have created."

you mean I can connect to MariaDB using Oracle SQL developer ? I tried that and it seems that SQL developer can only connect to Oracle.

you can do me a screenshot

"?This is fairly easy. In SQL Developer you Just execute your query on the table that you want to sample data from and right click on the result and choose export. "

you mean still needs to run a script, not by the feature of the tools ? then export the result to CSV ?

any step by step guide on this ?
johnsoneSenior Oracle DBA

Commented:
The documentation is your friend.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

>you mean I can connect to MariaDB using Oracle SQL developer ? I tried that and it seems that SQL developer can only connect to Oracle.
Yes, as I mentioned ( the connect word/link ) in the earlier comment then the link provides you with step by step setup to configure SQL Developer to connect to MySQL/MariaDB.
And as jonsone mentioned it is in the Oracle SQL Developer documentation as well.

Regards,
    Tomas Helgi
marrowyungSenior Technical architecture (Data)

Author

Commented:
it is funny that the product manager of SQL developer said it can't connect to MySQL/MariaDB.

"Setup
Download the latest "JDBC Driver for MySQL (Connector/J)" from here. Click the "Download" link next to the "JDBC Driver for MySQL (Connector/J)", then select the platform independent version and download the zip file."

oh, still need connector for MySQL ?

but that one seems only for linux, but not for windows... if for SQL developer is for windows we need to run the MySQL installer ?

now I try connector/J on it but do I need to restart SQL developer?

also, anyone here used Toad for MySQL before to show the same thing for all table ?
johnsoneSenior Oracle DBA

Commented:
Where do you see that those instructions are for Linux?  There is no mention of an operating system in either of the 2 links that I see posted.  And in fact "select the platform independent version", would clearly indicate, that operating system shouldn't matter.
Geert GOracle dba
Top Expert 2009

Commented:
he didn't have his glasses on ... ? again ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
johnsone,

As the migration tools I am going to use can only migrate 10 rows of data to each of the table, can we tell oracle developer to compare only first n rows only ?

I am trying to compare using SQL developer diff wizard, it seems mySQL connection is not shown, any idea?

when comparing
johnsoneSenior Oracle DBA

Commented:
I don't use SQL Developer, so haven't used that wizard.  Based on the documentation, for which a link has been given and I suggest you read it, that wizard is not for comparing data.  Based on the options, I don't see how a non-Oracle database could be used for a compare target, there would always be major differences.
marrowyungSenior Technical architecture (Data)

Author

Commented:
johnsone,

actually the product manager of SQL developer said that diff wizard is between oracle to oracle.

The SQL developer is by design not to let people leave Oracle but go to oracle ! that's why by default no third party DB connection by default for Oracle SQL developer.

that connector just allow SQL developer query other DB.

"I don't use SQL Developer"

What you use ?

Tomas,

what should I do ?

the answer you all give means to me that I can use SQL developer for it but not I found out can't ! what should be it ?
johnsoneSenior Oracle DBA

Commented:
I'm confused, why can't you use SQL*Developer?  As you stated, it allows you to connect to another database.  Connected to another database allows you to run queries, doesn't it?  Use SQL*Developer to run your verification queries, which should allow you to easily dump the results to a file and then compare.  That is what was originally suggested and by your own research would work.  Just because you don't like the method doesn't mean it won't work.

Personally, I use SQL*Plus.  I can open SQL*Plus and do half my work before you even get SQL*Developer started.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"I'm confused, why can't you use SQL*Developer?  As you stated, it allows you to connect to another database.  Connected to another database allows you to run queries, doesn't it?"

yeah, but initially I want to compare DIRECTLY using tools instead of dump to a CSV files.

"Use SQL*Developer to run your verification queries, which should allow you to easily dump the results to a file and then compare."

this is the most important part but this is not my wish .

may be I need to say compare DIRECTLY.

today I tried today I found out way to export as csv from toad for MySQL, so now need a script for MySQL, please help.

" I don't see how a non-Oracle database could be used for a compare target, there would always be major differences."

that's what this question is about, I am considering way to compare it and as we all know schema can't be compare, so only DATA is ok. aqua data studio is under consideration.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial