What will need to change when we convert our Lawson financial tables from Informix to Oracle? I've already run into reserved words for files and fields where Oracle will prefix with an r_. I've also seen my date formats change from YYMD to HYYMDS. How can I easily handle that in coding where I look for 20080403 in a WHERE or DEFINE? What other gems can I expect?

This message has been edited. Last edited by: FP Mod Chuck,

ENGINE SQLORA SET VARCHAR OFF

Make sure you specify this in edasprof.prf so that when you regen your synonyms, all of your alpha fiels don't have formats like this: AnV. Trust me, having variable formats will screw up a lot of programs.

Thanks for the info - I definitely needed that. Here are other things I've found...
1. Dates are DD-MMM-YYYY in Oracle. I had to add
ENGINE SQLORA SET DATETIME OFF
to edasprof.prf to get the dates back to the Informix format of YYYYMMDD.
2. Lots of our code is written in SQL which produces HOLD files to focus code. SQL has lots of changes...
a) SQLINF to SQLORA
b) SQL SQLINF SET ISOLATION TO DIRTY READ; not recognized
c) SQL SQLINF SET PDQPRIORITY 24; not recognized
d) SELECT {+FULL(appayment), +FULL(apdistrib)} not recognized
e) 'today' becomes 'sysdate'
f) OUTER JOINS are totally different
g) substrings change from aroihdr.invoice[1,2] to substr(aroihdr.invoice,1,2)
h) I'm having trouble with stored procedures (mostly date routines)
i) fieldnames outputted from the SQL HOLD file were lower case; ORACLE puts out upper case and doesn't recognize lower case; therefore I've had to copy/paste all of my code into WORD, change the case and copy/paste it back.
j) referencing the file name is different - we used to just type the filename or have PROD:lawson.filename, now it MUST be lawprod.filename

I'm now investigating functions such as TRIM, UPPER, etc. to see if there is a difference.

How would we do a Dirty Read using a TABLE FILE request, not SQL Passthru?

... in 8201+

Thanks, Doug

quote:

Originally posted by sys1165a:
2. Lots of our code is written in SQL which produces HOLD files to focus code. SQL has lots of changes...
e) 'today' becomes 'sysdate'

You may be able to create an alias for that in the database.

quote:

f) OUTER JOINS are totally different

Of old, Oracle has had their own syntax for outer joins, but these days they understand standard SQL outer joins just fine. Or is Informix using something different?

quote:

h) I'm having trouble with stored procedures (mostly date routines)

There is no standard for stored procedures, so that's to be expected.

quote:

i) fieldnames outputted from the SQL HOLD file were lower case; ORACLE puts out upper case and doesn't recognize lower case; therefore I've had to copy/paste all of my code into WORD, change the case and copy/paste it back.

You can configure that in the adapter settings. SQL is case-insensitive (even in Oracle), the case-folding happens in the adapter.