Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1517
  • Last Modified:

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
0
KarenNewton
Asked:
KarenNewton
  • 5
  • 5
2 Solutions
 
mrjoltcolaCommented:
First, to your overall problem, porting. I recommend investing 3k in a tool, Embarcadero ER/Studio, or CA ERwin. I've used these for migrating between many heterogenous combinations. ER/Studio will also allow you to actually maintain both physical schemas against a single logical model. For the time it will take you to convert all the DDL by hand, the tool will save you way more $$ than it will cost.

Concerning nulls, traditionally I always used indicator variables, but you can use NVL() to substitute 0 in the query, if that is what you choose.

Now, as to date and equivalent C bind variables you have options:

Oracle DATE can be converted to "unixtime" or to the 1899 based value you use, in the same way, by subtracting a date from a date inside the query. The result will be numeric. Try these inside SQLPLUS, you will see they return numeric types.

select (sysdate - to_date('01011970', 'MMDDYYYY')) from dual;  -- unix epoch time
select (sysdate - to_date('12311899', 'MMDDYYYY')) from dual;  -- your turn of the 19th century based time

If you want to handle date's without calculations in your C program, you just bind using char[7] which will produce a raw value (I think, its been a few years), or with timestamp it is longer, but if you want to do calculations in the C program, you may need to convert to unix time your own format with the SQL above, but I recommend just doing the date manipulations inside the SQL, so your C program deals with days, seconds or intervals. As I recall, binding the char[] value in this way does not give you a formatted output, it gives the actual date value. To convert you should use TO_CHAR/TO_DATE if you want formatted output. I will verify this shortly if I can make myself a ProC makefile, I have not done ProC for a few years and don't have the demo ProC stuff handy. If I get time to check this, I will post back a sample. I seem to recall that I did select values into a C long on 64-bit, but Oracle docs says to use char[]. In the meantime...

Pro*C reference for datatype mapping:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_04dat.htm

OCI reference for datatypes:
http://download-uk.oracle.com/docs/cd/B12037_01/appdev.101/b10779/oci03typ.htm#443569

0
 
KarenNewtonAuthor Commented:
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
0
 
KarenNewtonAuthor Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mrjoltcolaCommented:
No, you do not need that many levels of conversion. A simple date + number of days is enough

  select to_date('1899-12-31', 'yyyy-mm-dd') + 39936 from dual;

It is better to think in terms of Oracle's date functions than to try to convert everything to a long value. Oracle has a lot of date functions to use. Just Google: Oracle date functions

See sample below. Adding an integer to a date will result in a date.




SQL> create table t ( id int, mydate date );
 
Table created.
 
SQL> insert into t
  2  values(1, to_date('1899-12-31', 'yyyy-mm-dd') )
  3  ;
 
1 row created.
 
SQL> insert into t
  2  values(1, to_date('1899-12-31', 'yyyy-mm-dd') + 1 )
  3  ;
 
1 row created.
 
SQL> insert into t
  2  values(1, to_date('1899-12-31', 'yyyy-mm-dd') + 39936 )
  3  ;
 
1 row created.
 
SQL> select * from t;
 
        ID MYDATE
---------- ------------------
         1 31-DEC-99
         1 01-JAN-00
         1 04-MAY-09
 
SQL>

Open in new window

0
 
KarenNewtonAuthor Commented:
Thank you mrjoltcola for your detailed responses!
0
 
KarenNewtonAuthor Commented:
Thanks again I really appreciate you taking the time to respond in such detail. This has greatly helped me.

Karen
0
 
mrjoltcolaCommented:
Glad to help, always!
0
 
KarenNewtonAuthor Commented:
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.
0
 
mrjoltcolaCommented:
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.
0
 
mrjoltcolaCommented:
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now