Wednesday, 23 April 2008

SQL Dev 1.5 Migration Tip

Sybase and SQL Server have an option to called "is quoted identifier on".
When ON, double quotes can be used to reference identifiers (tables,columns,....).
When OFF, double quotes means string literals ("Hello World").

In the past this caused no end of problems when translating to PL/SQL , as our translator had to guess if a double quote was referencing a identifier or a string.
For Example what does this mean ?

SELECT "col1" from table1

Is "col1" a column in table1 or is it string? In PL/SQL strings are defined using single quotes and identifiers are convert so that they are case insensitive (without double quotes).

Pre 1.5 the translator would look at the context the double quotes where used and make a best guess.

In 1.5 we changed this so that the translator can be more accurate.
There is a new preference

By Default (like the SQL Server and Sybase) it is on. So double quotes are recognized in T-SQL as identifiers.

If the database your migrating uses double quotes as strings, then unclick this option before converting or attempting a quick migrate.

You will run into convert issues if this option is not set correctly. I have added a note to the doc but as this is a bit of a gotcha in 1.5 I thought Id blog it.

Note, that Alias can still be defined using double quotes and the translator recognizes them correctly.



Ref:
http://msdn2.microsoft.com/en-us/library/aa259228(SQL.80).aspx
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/@Generic__BookTextView/25659;pt=26021