Today's Client Question: Converting From Oracle

Q: What should we look out for when converting from Oracle to SQL Anywhere? We're switching software for our remote databases so we can use MobiLink to synchronize with our central Oracle database and save a lot of money in maintenance fees.

A: For the most part, it's easier to go from Oracle to SQL Anywhere than the other way. Plus, you'll save more money than just the maintenance fees.

The first thing to look out for are the column data types. You can use SQL Anywhere's "Migrate Database" wizard to create a SQL Anywhere database from Oracle in a few minutes, but then you really should check each and every column to make sure you're happy with the new data type. Here are some of the wizard's choices that deserve your attention:

32-bit BINARY_FLOAT Oracle columns are migrated to the 64-bit DOUBLE data type in SQL Anywhere, whereas you may prefer the 32-bit REAL data type.

DATE columns become TIMESTAMP in SQL Anywhere, whereas you may prefer DATE.

NUMBER columns are migrated to use the 64-bit floating point DOUBLE data type, whereas you may only be using the columns to hold integer or fixed-point values in which case data types like INTEGER, BIGINT or NUMERIC might be more appropriate.

NUMBER ( precision ) columns become NUMERIC ( precision ), whereas you may prefer one of the binary integer data types like INTEGER or BIGINT.

Also, Oracle CHAR columns are fixed-length whereas all SQL Anywhere string data types are varying in length, even if you code CHAR instead of VARCHAR. This doesn't often pose a problem since applications don't usually rely on automatic-padding-to-waste-space.

Another big difference to deal with is that Oracle uses the PL/SQL syntax for stored procedures and triggers, and it is very different from the WATCOM SQL and Transact SQL syntax that SQL Anywhere uses. So different, in fact, that converting procedures and triggers will be a complete rewrite. The good news is that WATCOM SQL's easier to code and debug; e.g., you don't have to deal with problems like Oracle's "mutating tables" limitation...

There are a lot of other minor differences between Oracle and SQL Anywhere; here's a few I've run into on conversion projects:

• REMAINDER becomes MOD
• DECLARE goes after the procedure BEGIN
• TO_NUMBER(USERENV('SESSION_ID')) becomes @@spid
• IN OUT becomes INOUT
• RAISE_APPLICATION_ERROR becomes RAISERROR
• variable := expression; becomes SET variable = expression;
• ELSIF becomes ELSEIF
• SYSDATE becomes CURRENT TIMESTAMP
• TO_CHAR becomes STRING (which is way more powerful)
• FROM DUAL becomes FROM dummy (or just omit the FROM clause)
• WHERE (+) operators must be replaced with OUTER JOIN clauses
• DECODE becomes a CASE or IF expression
• SQL%ROWCOUNT becomes @@ROWCOUNT
• CREATE SEQUENCE becomes DEFAULT AUTOINCREMENT or GLOBAL AUTOINCREMENT
• ROUND requires 2 arguments
• GREATEST becomes GREATER and only works with 2 arguments
• Date arithmetic does not return a fractional time component

Also note that in Oracle the empty string literal '' becomes a NULL value whereas in SQL Anywhere it is treated as a zero-length string.