Solved

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

Posted on 2009-05-05
10
1,493 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
  • 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
 
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Syntax 8 42
Clone Oracle 12c Database 5 28
sql query 9 22
scheduler for Procedure in DB with 3 arguments in 10g 7 10
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now