Informix to Oracle Migration: Dealing with Dates and Nulls and....
Posted on 2009-05-05
A bit of background first. My company is evaluating whether or not we will migrate our Informix database to Oracle 10g. We have several ESQL/C programs. I've run some through the Oracle Migration workbench and have been muddling through some testing. Now I've come to realize a few things.
First, we have dynamic sql statements that are not handling null values at all. From what I've read, I either have to manually modify the queries to utilize the nvl( ) function or implement indicator variables. Can someone confirm if manual modifications are necessary? The least amount of manual changes we have to make to our converted ESQL/C programs, the better.
Second, we have several queries which pull dates from various tables etc., and in Informix dates are treated as type long, the # of days since Dec 31st, 1899.
In Pro*C, what format is a date being selected as? I know it's not numeric because I tried selecting date field into my long variable and get Oracle error stating "expected NUMBER but got a DATE". So I'm assuming we'd have to modify how we are selecting date fields - either select a date field in a converted manner so it becomes a long (ie, # of days since 12/31/1899), or change the host variable to match what Oracle is returning (what is that, string?).
Feedback is appreciated!