compare tables record using UI tools on Oracle and MariaDB.

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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:
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 DBACommented:
The documentation is your friend.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:
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 DBACommented:
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 dbaCommented:
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 DBACommented:
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 DBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.