asked on

Informix to Oracle Migration: Dealing with Dates and Nulls and....

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!
Thank you,
Karen

ASKER CERTIFIED SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KarenNewton

ASKER

Thank you for your detailed reply. Could you help explain once my C program has the date in long format, and let's say I perform manipulations with the date such as subtracting days etc., how do I properly update a table with my new date?

I'm assuming I'd have to add back the # of days since 12/31/1899, just can't wrap my head around this.

I found that this works for selecting a date in long format:

select to_number (to_char (mydate, 'J')) - to_number(to_char(to_date('12/31/1899', 'MM/DD/YYYY'), 'J')) from mytable;

Now I would store that into a long variable and let's say I want to update the table again:

update mytable set mydate = ...?

Thank you,
Karen

KarenNewton

ASKER

I figured out how to update my table with the long value, would appreciate if you could confirm if this is the best way or is there a simpler way?

update mytable set mydate = to_date( to_char(to_date('1899-12-31','yyyy-mm-dd') + 39936, 'mm/dd/yyyy'), 'mm/dd/yyyy')';

(where 39936 is the long value)

Thanks!
Karen

SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KarenNewton

ASKER

Thank you mrjoltcola for your detailed responses!

KarenNewton

ASKER

Thanks again I really appreciate you taking the time to respond in such detail. This has greatly helped me.

Karen

mrjoltcola

Glad to help, always!

KarenNewton

ASKER

mrjoltcola - I have another question which I want to detail, should I post it in this thread or create a new one? It is related to this thread but a separate problem.

mrjoltcola

Generally if the topic differs, or the question has been closed, a new question is in order. You can post a link in the new question referring to this question, if you like, in order to give context to the experts reading the new one.

mrjoltcola

Also, remember, for premium service members, there is no question limit or point limit, but just be careful not to post duplicate questions, and also be careful not to close your questions too soon if you don't get a satisfactory solution.