Solved

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

Posted on 2009-05-05
10
1,508 Views
Last Modified: 2013-12-20

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
Comment
Question by:KarenNewton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24309297
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
 

Author Comment

by:KarenNewton
ID: 24314107
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
 

Author Comment

by:KarenNewton
ID: 24314857
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 500 total points
ID: 24315229
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
 

Author Closing Comment

by:KarenNewton
ID: 31578061
Thank you mrjoltcola for your detailed responses!
0
 

Author Comment

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

Karen
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24315392
Glad to help, always!
0
 

Author Comment

by:KarenNewton
ID: 24317395
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24317446
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24317457
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question