Thursday, May 17, 2007

Updated Oracle SQL Developer Migration Workbench Early Adopter Release

We have updated the early adopter release of Oracle SQL Developer Migration Workbench today on OTN. You can get it from here. This is our final preview release, before we go production. We are now functionally complete for this initial production release and are now focused on fixing our final "show stopper" bugs. We have had good feedback from our user community via our feedback application and also via the Workbench forum. They have uncovered a number of bugs, most of which we have now addressed (Oracle9i as a repository and Access data migration issues for example) and the remaining ones we will resolve prior to production. I encourage everybody to update to this latest release and continue to provide us with feedback.

I have been working extensively with different builds of the Migration Workbench these past couple of weeks as we closed in on our goal to refresh the early adopter version. In my "biased" opinion is it looking much stronger and I would like to outline some of the new features in this updated early adopter release.

Quick Migrate
In the orginal Migration Workbench we had a wizard driven approach to simplify migrations and I felt it was important to bring this functionality back. With our Quick Migrate wizard, I believe we have improved from the original wizard, since we will leverage our least priviliege migration capabilities, assume sensible defaults and create/remove our migration repository.



So if you have a schema on SQL Server or a single Access mdb file to migrate to an existing Oracle schema, this should be the easiest and quickest migration option for you. Another nice feature, if you are doing an access migration, is that we have added command line support to our exporter so, we will automatically launch the correct Access exporter for the Access connection that you specify.

Offline Capture
This was a popular feature with our consultants and partner technical services folks, with the original Workbench, as it allowed them to work remote from the customer/partner. We have now added back in that feature.

Migration Reports
We have added in some initial migration reports available under Reports->Shared Reports. This will be an area we will add to into the future, as we can mine our rich metadata repository to provide you with useful information. If you have suggestions for additional reports let me know. I will also publish more details about our repository, so you can develop your own migration reports as well. Maybe we should have a competition for the best contributed report? I think we have a couple of 1GB USB keys left over from our Database Developer Day in Dublin I could use as prizes.

Translation Scratch Editor
We have reworked this feature extensively. I originally wanted to add a feature that would enable you to validate our translated SQL. As we worked through different iterations of how best to implement this feature, we came up with the idea about leveraging our existing Worksheet capabilities, which I think is very cool and I am very pleased with how this turned out.


We have also done a lot of work to improve incremental capture and improve our filtering capabilities from our early adopter release. We have integrated our MySQL parser from the original Migration Workbench and will extend its capabilities in subsequent releases to be as functional as our new TSQL parser and also support SQL statement level translation. (workaround for now, within the scratch editor, is just wrap the SQL statement in a procedure). We have also implemented the ability to update your Access mdb file, to create link tables to point to your newly migrated schema. This was also a feature of the original workbench. We hope to add some additional usability tweaks to create an ODBC OSN on the fly and provide a select list of known Oracle DSN. Hopefully that will make it in before production as well.

We have made fixes to ensure correct generation order for pl/sql procedures to resolve dependencies, so more pl/sql procedures should compile correctly first time. We made improvement to handle inline DDL statements correctly. Temporary tables, normal tables and other DDL are lifted out of the body of the procedure/function and are created separately.

Now for the final bug fix push by the development teams in Dublin and Bangalore. Our QA team, have been doing a good job verifying our fixes and closing off our bugs. The teams have been working hard on this for many months now and I believe we are in touching distance of reaching our goal. It will be very exciting for me personally to see this second generation migration tool reach production. We'll all need some time off when this is done to recharge...

4 comments:

ImwatIm said...

hi ,

I am using Sql Developer for porting my t-sql code from pl/sql..
i have ported most of my code but am stuck in a few stored procedures involving xml..hope u can help me in this regard..

Here is my exact problem..
How do i map the xml handling code in t-sql to pl/sql??
for example , here is the t-sql code :

CREATE PROCEDURE [dbo].[usp_LIBRARYHideUnhideTreeNodes]
-- Add the parameters for the stored procedure here
@paramMyXml text,
@XmlHandle int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

/*Update statements*/
EXEC sp_xml_preparedocument @XmlHandle output,@paramMyXml

UPDATE LIBRARYTree SET IsVisible = ~IsVisible WHERE NodeId in(
SELECT xNodeID FROM OPENXML (@XmlHandle, 'HiddenNodes/Node',1)
WITH ( xNodeID int '@id'))

EXEC sp_xml_removedocument @XmlHandle


END

The corresponding code in pl/sql by sql developer is :


CREATE OR REPLACE PROCEDURE usp_LIBRARYHideUnhideTreeNodes

(
-- Add the parameters for the stored procedure here
v_paramMyXml IN CLOB DEFAULT NULL ,
v_XmlHandle OUT NUMBER
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
NULL/*TODO:SET NOCOUNT ON*/;
/*Update statements*/
sp_xml_preparedocument(v_XmlHandle,
v_paramMyXml);
UPDATE LIBRARYTree
SET IsVisible = UTL_RAW.BIT_COMPLEMENT(IsVisible)
WHERE NodeId IN ( SELECT xNodeID
FROM DUAL/*TODO:OPENXML (@XmlHandle, 'HiddenNodes/Node',1)
WITH ( xNodeID int '@id'))*/ );
sp_xml_removedocument(v_XmlHandle);
END;

This on compilation for obvious reasons gives errors.

how is sp_xml_preparedocument,openxml,sp_xml_removedocument handled in pl/sql??

any help in this direction will be appreciated..

Cheers,
Shishir.

Unknown said...

Shishir,

Posting the question as you have to the OTN forum is the best place to get an answer.
Currently the parser does not handle these statements. You need to convert the manually to an Oracle equivalent.

Donal

Mindbees said...

The post looks awesome
Many of my problems got solution

Mark said...

The blog seems to be nice
Helpful and creative one
hats off to you Donal