?
Solved

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

Posted on 2009-05-05
10
Medium Priority
?
1,511 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 2000 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

752 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