Informix to Oracle: Dealing with Fetching Null Values

Question

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

Answer 1

Ya. You will need to modify your queries as you described.

long is tripping you up. long has a different meaning in Oracle. There is a specific DATE type. Generally when selecting one uses the TO_DATE function with a format, to get the result as a VARCHAR2, in exactly the format you want.

Answer 2

Probably it didn't hit you yet but be aware that in Oracle empty VARCHAR2 fields are NULLs. I see no logic behind this (probably because I came from Informix land) - just keep it in mind. I think it is stupid - IMHO empty string is meaningful and different from NULL.

Either modify all your VARCHAR2 fields to be NOT NULL DEFAULT '-' or any other arbitrary value, or use indicatores in ALL your queries that return VARCHAR2 fields, or always use NVL().

Answer 3

@Jonathan - There just seems to be no way for me to bypass this error from Oracle when my query doesn't explicitly deal with possible null values in a given field. I also discovered that I cannot use nvl( ) within my dynamic queries and instead must use indicator variables. I'm past actually solving that portion, just wish there was a way I didn't have to do the manual code changes.

@EvilTeach - thanks for your comments. You say that there is a specific lDATE type, are you saying that I don't have to extract a date field as a char (with TO_DATE)? Can anyone point me towards some documentation? I've been practically living on Google lately searching for answers.

@qrdl - I hear you about empty VARCHAR2 fields, thanks for the tip. I agree that it doesn't make any sense to enforce this type of logic...

If anyone can help further me along with the whole date issue I would highly appreciate it.

Thank you! Karen

Answer 4

In order to convert the oracle dates (which are store in Oracle internal format) into a long integer, you will need to alter your queries. Use the following formula for your dates:

to_number (to_char (date_column, 'J')) - to_number(to_char(to_date('12/31/1899', 'MM/DD/YYYY'), 'J'))

The Oracle system 'J' (for Julian date) format is a count of number of days since December 31, 4712BC. If you want to count from a later date, you'll need to subtract off the Julian day count of that later date.

One suggestion: instead of altering all of your queries in your programs (which may create problems and introduce bugs), create a set of views in a different schema. These views would be named the same as all the tables, with all the same columns, but include the NVL() and date() formulas (like above). Then point your application at the view schema rather than the base table schema. Much less testing and fewer places to missing something.

So for example, put all your tables into a schema called "APPS_BASE" (defined by the user "APPS_BASE". Then create another schema/user called "APPS_VIEWS". In the APPS_VIEWS create a view:

CREATE OR REPLACE VIEW EMP AS
SELECT name, birth_date
FROM   APPS_BASE.EMP;