6 Replies Latest reply on May 16, 2014 11:08 AM by Dermot ONeill-Oracle

SQL Server to Oracle migration : column semantics

user12017111 May 13, 2014 9:00 AM

I am testing a migration from SQL Server 2012 to Oracle 12c using migration wizard of SQL Developer 4.0.0.13. The problem that I am facing is all the generated table creation scripts have CHARACTER semantics for character columns. I would like the semantics to be BYTES. As the generated tables and columns are several thousand its not possible to manually change the semantics.

CREATE TABLE account_dod (

account_id VARCHAR2(16 CHAR) NOT NULL,

I cant seem to find any option to change the semantics during generation. Does anyone know of any option that I can set so that migration wizard will generate all columns as BYTES ?

Many Thanks

1. Re: SQL Server to Oracle migration : column semantics

710129 May 13, 2014 10:51 AM (in response to user12017111)

Is it picking it up from your NLS preferences under Tools -> Preferences -> Database -> NLS
You can swap the length between CHAR and BYTE

Trotty
Like Show 0 Likes(0)

Actions
2. Re: SQL Server to Oracle migration : column semantics

user12017111 May 13, 2014 11:06 AM (in response to 710129)

Thanks. NLS preference under Tools -> Preferences -> Database -> NLS is set to BYTE.
So doesn't appear to be picking it from there.
Like Show 0 Likes(0)

Actions
3. Re: SQL Server to Oracle migration : column semantics

thatJeffSmith-Oracle May 13, 2014 12:33 PM (in response to user12017111)

Why would you want BYTES over CHAR?

With BYTES you risk not having columns 'wide' enough to store all of your data based on your characterset.

With CHAR you're guaranteed to always be able to fit that many characters in your column.
Like Show 0 Likes(0)

Actions
4. Re: SQL Server to Oracle migration : column semantics

user12017111 May 13, 2014 1:15 PM (in response to thatJeffSmith-Oracle)

Agree with your point, however we need it to be BYTE for following reasons:

BYTE is the default in Oracle and our datamodel uses the default option.With CHAR columns all character columns could become up to 4 times wider.

We are not worried about data not fitting in BYTE column as our application is designed with BYTE columns.

It would be a major re-testing exercise for us to test our application, which is already in production use at several customer sites.
Like Show 0 Likes(0)

Actions
5. Re: SQL Server to Oracle migration : column semantics

thatJeffSmith-Oracle May 13, 2014 1:35 PM (in response to user12017111)

I think we can get a 'fix' out to you, stay tuned.
Like Show 0 Likes(0)

Actions
6. Re: SQL Server to Oracle migration : column semantics

Dermot ONeill-Oracle May 16, 2014 11:08 AM (in response to user12017111)

HI,

If you email me I can provide a modified extension to use the BYTE unit in CHAR and VARCHAR2 columns.

My email address is my firstname.lastname@oracle.com

Regards,
Dermot ONeill
SQL Developer Team
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.