tech oriented notes to self and lessons learned
Using Oracle SQLDeveloper with MySQL
2012-08-03Posted by on
Oracle SQLDeveloper is a tool I’ve found very valuable in projects where I’m using the Oracle Database. Normally I like using command line tools, but many tasks such as browsing large result sets or data in fat tables, browsing database schema metadata etc. are much faster with SQLDeveloper. SQLDeveloper supports other relational databases and since I’m currently working on a project involving MySQL, I thought I’d give SQLDeveloper (v3.1.07) a little test with MySQL (v5.5).
You can install extensions in SQLDeveloper in a similar fashion as in Eclipse and there’s a MySQL JDBC driver available (Third Party SQLDeveloper extension). For some reason the extension failed to install properly on my Mac: everything looked to be going fine but the installation failed silently for some reason. You can configure JDBC drivers manually in SQLDeveloper, however, so I downloaded the MySQL driver and configured it (preferences / database / third party JDBC drivers). After that, a new tab called “MySQL” appears when creating a new database connection. Here you can specify DB product specific connection parameters.
I was able to successfully connect to my MySQL database but when trying to browse table data on a table containing 5+ M rows, the operation failed with the following error:
Java heap space
I don’t remember running into this problem with SQLDeveloper when connecting to Oracle DB. As a workaround I modified the Java VM heap size argument that SQLDeveloper passes to Java VM at launch (sqldeveloper.conf configuration file).
I also, wanted to test if SQLDeveloper would run with my newly installed Java 7 but that turned out to be a bit more difficult. On Mac OS X, changing the Java path in the SQLDeveloper default configuration files had no effect, as this parameter was overridden in a platform specific configuration file that had to be changed (sqldeveloper-Darwin.conf), in order to use an alternate Java VM. The correct configuration file to change was revealed after starting up SQLDeveloper with –verbose flag from the command line:
SQLDeveloper can help in a number of ways when you’re working with Oracle DB including: provide wizards for creating and editing table definitions, import and export data and allow viewing and changing many aspects of database metadata. The SQL Worksheet can help you when writing SQL statements with the autocompletion feature. SQLDeveloper is a great tool to use with Oracle DB, but you should note that some of its features aren’t available in SQLDeveloper for other database products.