Using Toad Data Modeler to Convert from Oracle 12c to SQLServer 2012.

I just needed to do a simple conversion from Oracle to SQLServer.  

I didn't have the need to spend time trying to customize anything but I did come across a couple of 'extra' steps that I thought I would share with you.

I opened my Oracle db in the modeler and ran a simple conversion to SQLServer.  After the conversion was completed I generated the DDL script so I could create the objects in my SQLServer instance.

This is just a portion of the overall script that was generated from the conversion:

CREATE TABLE [T_BORROWING]
 (
 [exemplar_id] Int NOT NULL,
 [customer_id] Int NOT NULL,
 [start_date] Datetime2 DEFAULT --sysdate NULL,
 [end_date] Datetime2 NULL,
 [total_price] [Price_Type] NULL,
 [VAT] Decimal(4,2) DEFAULT 19 NULL,
 CONSTRAINT [check_end_after_start] CHECK (--(end_date>start_date) or (end_date is null))
 ).

When I tried to run this script I received three errors. The first one was on Line 5, highlighted in green.  The second one was with the Price_Type UDT (line 7) and the third one was on the constraint.

It appears that the tool puts the comma in as part of the comment (–sysdate NULL,) and SQLServer doesn't like that.

The second problem, which was totally mine at this point, was the UDT was not defined.  Not a big deal and I don't really need it in the new model, but you may.

The third problem is probably mine as well as I have not looked into the constraint structure and syntax on SQLServer versus Oracle.

So I edited the script to accommodate my needs and this is what I came up with:

CREATE TABLE [T_BORROWING]
 (
 [exemplar_id] Int NOT NULL,
 [customer_id] Int NOT NULL,
 [start_date] Datetime2 null,
 [end_date] Datetime2 NULL,
 [total_price] Decimal (4,2) NULL,
 [VAT] Decimal(4,2) DEFAULT 19 NULL,
 /* CONSTRAINT [check_end_after_start] CHECK (--(end_date>start_date) or (end_date is null))*/
 )

I removed the –sysdate from line 5, changed the Price_Type UDT to a simple decimal (4.2) and commented out the Constraint.

Now everything ran smoothly and I was able to create my Oracle table on my SQLServer instance and import the data without any issues.

The original/complete script has the UDT definition in it  – Create Type [Price_Type] as Table () – but this is Oracle syntax and not SQLServer so you will need to use the SQLServer Create Assembly syntax to make sure the UDT is created properly.

I wanted to share this experience with you not because there are any problems with using Data Modeler to do conversions but more to highlight that even a 'simple' conversion may need some massaging when you are moving across platforms.

These are some guidelines and usage parameters that you should be aware of as you move forward with Toad Data Modeler.

• TDM can help users with model conversion, however, database specific items cannot be converted automatically from one platform to another. We can convert tables, attributes, set settings on how data types should be converted, but items defined in Oracle syntax can’t be changed to SQL Server syntax automatically. Such objects must be verified/modified manually. Automatic conversion is out of the scope of the tool.  (Exceptions are Sequences/Identities that can be converted during Model Conversion).

In the case where you use the model conversion from one db platform to another, it makes sense to keep the option “Comment out database specific items” enabled. Many database specific items will be commented out and then you can edit them as needed for the new environment. (See attached screen shot)

If you convert a model from the same platform, just different version, the option should be disabled.

In summary, model conversion can be used for two basic scenarios:

A] When you need to convert a model from oneplatform to another (Oracle 10g model to SQL Server 2012 model for example)

B] When you need to convert a model from one version to another (Oracle 10g model to Oracle 12c model)

Toad Data Modeler can do a lot of the up-front work for you and make the conversion process a lot easier. I hope this helps some of you when you are converting your data models between different brands of databases.

-Mark