11 December 2006

SQL Developer, Migrations and Third Party Databases

A week or so ago, I popped over to Ireland to see some of the developers who are based there. I have mentioned before that the team based in Dublin are the Migration Technology Group. They are a busy team; building the Migration Workbench as an extension to SQL Developer, are also working on SQL Developer and have just released the Oracle Application Express Application Migration Workshop. Take a look at Donal's blog for a bit more on that.

There is lots to tell you about the trip, the work they are doing and have already released and I'll come back and tell you more another time, right now I want to give you a dip into something you can test in SQL Developer.

Part of the Migration Workbench functionality is the ability to connect to and review the objects in a third party database, such as MySQL, SQL Server and Access. In fact these are the three third party databases the team are targeting for their first release. So the Migration Workbench extension to SQL Developer, will allow you to browse your third party database, and then migrate all or some of these objects and data to Oracle. Very exciting! Well, the full Workbench is only due for next year. What we've added to SQL Developer 1.1 is the ability to connect to and browse a third party database. Here's How:

1. JDBC Drivers: Assuming you have an MySQL or SQL Server database, verify you have the correct jdbc drivers. This Getting Started document on SQL Developer 1.1 has links for you to download the drivers you will need.
2. SQL Developer 1.1: This functionality will only be available in SQL Developer Release 1.1. You can test the functionality with the current evaluation release, 1.1.0.22.71, available from here.
3. In SQL Developer, navigate to the Tools menu: Preferences >Database > ThirdParty JDBC Drivers.
4. Click Add Entry and add your specific jar file.


The jars on this preference panel are used for all third party databases.


5. Now you can add your connections. Navigate to the Connections dialog and add a new connection.
My example is for a MySQL Database. As for Oracle, be sure your third party database is up and running!


6. Test and save the connection. Note, in my example I have an ACCESS connection tab too. This will be available in the SQL Developer releases post Evaluation Release 3 (1.1.0.22.71.)

7. You should now see the third party connection in the navigator. Remember this is read only access you have. However, the appropriate tabs and details are available for you to browse and review your database objects.
Have fun!



14 comments:

Anonymous said...

This is really really cool!
I can't personally test it (I still owe you TimesTen re-test), but I think you could also post about it in the forum and ask for specific beta testing of this functionality

Anonymous said...

I run Linux on my desktops and I work with many cusotmers who use Linux throughout their organization on desktops as well. I also use SQL Developer frequently and love the tool!

If I want to run SQL Developer with the Migration Workbench plugin, will you force me to do this under Windows and if so, how long will we have to wait to see SQL Developer with the Migration Workbench extension released for Linux?

Sue said...

You can use SQL Developer on any platform, as you have seen. So the ability to access the non-Oracle databases will be available for Windows, Linux and Mac OS X too. The Migration Workbench Extension, when it's available, will also be available for all those platforms, when it is released.

Regards
Sue

Vidya Balasubramanian said...

Sue,
This is great and will let us stay on one UI while connecting to multiple databases. Something that I encountered last week and not sure if I should bring it up here. I am a big Toad user and have found it hard to switch to another UI. Oracle Consultants were here last week, helping us with a BI setup - they convinced me in switching to SQL Developer - so while they were here I used SQL Developer - there was one issue I ran into frequently was seeing the DDL scripts behind tables , I would go to scripts tab and nothing would come up - the consultants here as well noticed the same behavior - I don't remember the version off the top of my head but can that for you. May be I am just hitting a bug from an older version?

Sue said...

We did find some objects do not have the DDL displayed in the evaluation releases, but this has been fixed for the later builds.

There is alos the chnace that you don't have access to the DBMS_metadata packages. Take a look at the FAQ on the Exchange: http://htmldb.oracle.com/pls/otn/f?p=42626:18

Sue

Anonymous said...

Hello Sue,
not quite related to your blog entry, so answer at your own discretion:
In the Whats New section of the release notes for v.1.1 the first entry under subsection 1.4 General is:
"Import data from spreadsheet (CSV or XLS format) into a table using SQL Developer".
I have however been unable to find documentation on how to do this. Nor have I been able to find any button or menu item for this. Can you supply a quick pointer on how to do this?
Really appreciate it!
Jens

Anonymous said...

Sue,
With regards to my request for help on Excel import, one of my colleagues found the way to do this, by precreating the table, right-click it and select Import.
So you can basically just ignore the comment, and just go celebrate the Christmas holidays instead.
Hope to see some more of your great photos in the other blog, maybe with a christmas theme, though that might be difficult with the current weather situation.

Ok, enugh ranting, wish all the best for you and your family for the holiday season.

Jens from Norway..

nickG said...

Thanks for the info, I like the ability to connect to other databases, I work as a DBA in an ISV and such a tool will only help mind share go Oracle's way. If the developers can have a single point of RDBMS interaction then it will help them. Our product was first developed on sqlserver because it was easier to install and get off the ground, MS are still ahead in this respect.

From my limited experience, Aqua Data Studio is the best for the "all RDBMS" job, but it's not free.

So back to SQL Developer, couple of issues.

I've not managed to connect to a named SQLserver instance, i.e. hostname\sql2005. The driver supplier say it should be possible, and Aqua Studio manages this.

In sqlserver I also get no tables (very few) when I've logged on without selecting a default DB, this is a bit counter-intuitive and not what other MSSQL accessing tools do. Once logged as sa you should really be able to see everything.

Can't see indexes.

I may be being a bit early on this and it appears that MSQL support has only just appeared and will only get better.

Who knows it may replace TOAD in my affections, but only once it does ORCL and MSSQL, thanks for the driver info and keep up the good work.

Anonymous said...

Will SQL Developer ever support SQL Server to do more than just browse? Our product is 85% Oracle, but we support SQL Server for smaller clients. I'm still looking for a JDBC tool that supports both databases nicely.

Sue said...

Hi,

The main intention is for SQL Developer to support read only connections to third party tools. This is the initial phase for those folk wanting to migrate from other databases to Oracle and can browse what they have before the migration. However, the SQL Worksheet is available and so limited updating is possible. The current focus is not on extending the browsing capabilities, just yet.

Regards
Sue

Unknown said...

Hi, any of you have found a way to connect to named instance in MS SQL server like "server\instance" ?
Thanks, Pablo

Sue said...

Pablo,

You are best asking this quetion on the main SQL Developer forum.(http://forums.oracle.com/forums/forum.jspa?forumID=260) Both your question and answer can get lost on the blog.

Regards
Sue

Anonymous said...

Sue:

I am using Oracle SQL Developer to play around with MySQL Version 1.5.1. I can't seem to get the dates displayed as "date" or "date-time-stamp". For example, a date and time portion from a MySQL table is displayed as follows:

oracle.sql.TIMESTAMP@1d08edf

What is amiss?

Please help.

-- Rex

Sue said...

Please post queries on the SQL Developer forum.
http://forums.oracle.com/forums/forum.jspa?forumID=260



Thanks
Sue