SQL Developer is built by Oracle, for Oracle. It’s the database IDE. That’s my current elevator pitch, at least. But, SQL Developer is also a fully featured data modeling solution (have you been enjoying my data modeling posts?) AND a 3rd party database migration platform.
The migration piece is the Migration Workbench. Since we support migrations from these other database platforms, wouldn’t it be neat if SQL Developer could connect and query those 3rd party databases too? Surprise, surprise – that’s exactly what we DO do today.
What ‘Support’ means
You can connect to, browse, and query (SELECTs only) SQL Server, DB2, Access, MySQL, and Teradata.
January 2020 Update: As of version 18.1, we also now support Amazon Redshift migrations, as well as Azure SQL databases.
What ‘Support’ does NOT mean
- providing debuggers
- delivering database administration features
- write access to the database
Sound a little harsh or spartan? Going much further in any of these areas would just open a big ole can of worms, and we’re committed to providing an optimal experience for the Oracle database. But, since we already support these limited features, there’s no real reason to hide it.
So what’s it good for outside of migration projects?
You own one of those fancy-dancy MacBook Pros? Fall in love with Ubuntu? Need browse some SQL Server data? You sir/madam, are what we call a rebel! Instead of installing a VirtualBox Windows VM just so you can run SQL Server Management Studio (SSMS) or some other Windows-based database IDE, you can run Oracle SQL Developer natively on your machine and connect to that server.
This is pretty old news – here’s a nice blog post from 2007 talking about how to use SQL Developer to connect to SQL Server. But since the product name is ‘Oracle SQL Developer’, it’s probably a good idea to remind folks every now and then that SQL Developer is more than just a tool for Oracle developers 🙂
Download and Go
You will need the JDBC drivers for the database you want to connect to. These are conveniently indexed here. Couple of notes –
- You need to be a registered IBM customer to access their downloads
- The Teradata download manager page seems to be ‘missing’ – go grab the driver here
Once you have extracted the packages, you need to point SQL Developer to the .jar file
Be sure to point to the actual file, not just the directory containing the jar.
Once you’ve done this, you can open the connection dialog and navigate to the appropriate database panel.
87 Comments
Hi Jeff, where can I find the supported jdbc drivers:
The link that included in this page:
http://www.oracle.com/technetwork/database/migration/omwb-getstarted-093461.html#conf
doesn’t work anymore.
Good job with this blog.
Which one(s) do you need?
Hi Jeff,
Is there a way we can connect SQLcl to other databases eg mySQL or PostgreSQL
No
Hi Jeff,
For the sake of completeness in case anyone else out that is trying to do the same thing, I can confirm that I’ve tried all 4 of these JDBC drivers for MS Access in the latest version of SQL Developer (v19.4), both 32-bit and 64-bit:
1 – https://www.easysoft.com/products/data_access/jdbc-access-gateway/index.html
2 – https://www.cdata.com/drivers/access/
3 – http://ucanaccess.sourceforge.net/site.html
4 – https://www.progress.com/jdbc/sequelink
For each, I specified its driver jar file in the SQL Developer Preferences for “Database: Third Party JDBC Drivers” and stopped/started SQL Dev, but in each case it did not show up as a new option in the Database Type dropdown on the Connections dialog.
That last one from Progress Software, named “Sequelink”, seemed promising because they referenced this blog article from someone else at Oracle that got it to work with the ODI (Oracle Data Integrator) tool:
https://blogs.oracle.com/dataintegration/how-to-load-data-from-excel-using-odi-1221-and-progress-datadirect-sequelink
Have you had any luck making any JDBC driver for Access work? How can I convince you to certify a JDBC driver for Access? I know in my case I run across many instances where the company’s “business” personnel (not IT) have rolled out departmental applications based on Access and invariably outgrow it. I would like to show them an easy way to convert it to Oracle (and to APEX) and see SQL Developer as just the ticket for doing this.
I can understand why Oracle’s focus is on migrating from the big competitor’s offerings (SQL Server, DB2, Teradata, etc), but I think you’re missing an opportunity to convert the “business” users over to Oracle’s camp. If they had a self serve way (no IT help needed) to easily convert their MS Access (and Excel) data to Oracle (and then run some APEX wizards to create web-based applications), then I think tons of them would migrate over.
Thanks,
Jack
p.s. I’ve had an Oracle Support case open on this for about a week now in case you’re interested, “SR 3-22917841471 : Unable to migrate a Microsoft Access database to Oracle”. It’s not looking too promising right now, but I’ll post any solution they might come up with here on your site.
Access connectivity and migration support was dropped with version 4.0 of SQL Developer. There is no driver that will let you connect to Acccess today.
You’ll need to get your hands on a copy of SQL Developer 3.0, 3.1, or 3.2 to in order to get help with Access to Oracle migration
Now, in terms of building it back into the product, I agree it’s something we should do. But there’s a long list of things we need to do…
Hi,
I was able to connect from Oracle SQL Developer to SQL Server 2008 r2, but I cannot connect to SQL Server 2016. I have the jtds-1.3.1 driver. I get the error “provider code 18456”.
The same happens to me, when I want to connect from SQL Server Data Modeler to SQL Server 2016. I get the error “Status: Failure: Test failed: Login failed for user ‘Est_Museos.update’. ClientConnectionId: 73821b60-ab82-4a9e- 9cb6-139243515da7 “. However, I do connect to SQL Server 2008 R2. Here I use the mssql-jdbc-6.4.0 driver.
Thanks.
Hi Jeff,
I was able to Move a SQLServer Database to Oracle SQL Developer, I was wondering if there was a way to query two tables (one from oracle and one from SQLServer) in one database. any idea how i can create a DB link in oracle to queries both databases in one connection?
Yes, check out our Database Gateway for SQL Server feature – it’s not free though.
What is the syntax of calling a DB2 stored procedure from SQL DEVELOPER, that has IN and OUT parameters?
No idea. We’re not a IDE for DB2. We’re an IDE for Oracle that you can use to move your DB2 objects/data over to Oracle.
Hi Jeff,
Most helpful article and comments I’ve come across so far. I am trying to migrate an Access database (.accdb) and have downloaded various versions of SQL Developer but get stuck somewhere in the process with all of them. The last was sqldeveloper-4.0.0.13.80-x64. I can connect to the access databse in SQL Dev. When I do an online migrate, it stops at the Capture step – no databases in the source or destination. If I try offline (create database capture scripts), I cannot see Access in the platforms dropdown. Any suggestions welcome. Does the latest version of SQL Dev perhaps allow ms access migration?
Thanks,
Marianne
Best thing for you to do would be to select 1 or more Access tables in the tree, right click, and ‘Copy to Oracle.’
Thanks for the reply. I have done that successfully, but cannot do that with the views. Any suggestions?
Hi Jeff
Given jTDS is no longer actively developed (https://sourceforge.net/p/jtds/discussion/129584/thread/050f9892/?limit=25#c37d) and it has always had issues with SSL connections (https://sourceforge.net/p/jtds/bugs/725/), is there a chance that Microsoft JDBC driver for SQL Server would be supported?
Not a great chance – the jTDS driver does what we need it to for BOTH SQL Server and Sybase ASE
Hi Jeff,
Nice article. I especially love that Access tab right next to the Oracle tab in the connections dialog. I have been really surprised how well Oracle SQLDeveloper allowed me to leverage MS Access for any number of scenarios (migration and otherwise). Really very useful.
Sadly, the Access tab doesn’t seem show up with JDK 8 and later lack of support for the JDBC-ODBC bridge.
Any way we can get it back?
We’ve talked about just getting a proper jdbc driver for Access and bringing back the migration feature support for that ‘platform’ – but in the meantime, best to get an older copy of SQLDev (3.0) and use that to work with Access.
Get this back please. It is way to troublesome to do a second SQL Developer. My company needs this, because of all of our requirements.
Thank you
What exactly are your requirements?
Agreed, Microsoft Access won’t go away!!! Jeff, we still need an efficient and quick way to convert them to Oracle (and APEX).
So finally, I convinced my customer to let me convert their multi-GB Access database to APEX, but my experience getting SQL Developer to connect to it was a lot harder than I expected.
I just spent the weekend trying to get v19.4 to read an Access 2013 database. I wasn’t successful. And that help documentation is such a teaser… makes you think you can do it easily but for the life of me I couldn’t figure it out. I tried two different third party JDBC drivers for Access (btw, the “conveniently indexed here” link above is broken so I just did some Googling and might not have been trying the right ones), and also monkeyed around with trying to do it through ODBC, all to no avail.
So I gave up trying with v19.4 and did what you suggested in your comment above and downloaded and older version. I wanted to use v4.0 (because of this article: https://www.dbasolved.com/2014/03/open-and-migrate-microsoft-access-in-oracle-sql-developer-4/) but that version was nowhere to be found because it seems many of the links to older versions on Oracle’s site are broken. Luckily the link for v3.2.2 is still live (https://www.oracle.com/downloads/opensource/sqldev-v322-downloads.html)… whew.
Finally, under the Connections section in v3.2.2, voilà, there’s the Access tab in all its glory! I can connect and query the Access database, and reverse-engineer the DDL. I can also right-click on an Access table successfully run the “Move to Oracle” option. But it’s still a tad unstable… after a few attempts of it seeming to hang on this one particular Access table, I found that several of the column names were over 30 characters long. After I shortened them manually in Access, the “Move to Oracle” function worked fine.
However, for this reason and others I want to used the Migration Workbench Wizard, but it isn’t working… when I get to the Source Database step and select the “Online” option, it won’t show my Access db connection in the dropdown. Any idea why?
And when I try the alternative “offline” approach, and run the builtin exporter tool, choose the “Export for Oracle SQL Developer” option, specify the MDB file and try the export, Access starts running the exporter tool but exits with a popup error “Error #3032 – XMLExporter – Application-defined or object-defined error… Database Schema Export did not complete successfully”. Couldn’t find any hits on this in Google or MOS either.
Should I just give up on trying to use the Migration Workbench for Access databases, and maybe migrate Access to SQL Server, and then try the Migration Workbench with SQL Server?
Thanks,
Jack
Wow you got lucky finding the 3.2 version…i was told they were all taken down by the Web Team.
if you do copy your access database over into SQL Server, yes, you could use the migration feature in SQL Developer to migrate it to oracle. that’s the best way to do it today, assuming you don’t have the older version of SQL Developer that supported Access connections. We used a JDBC-ODBC bridge to connect to Access and Java dropped that. We haven’t taken the time to certify a JDBC driver for Access to officially support access migrations in the newest versions.