This is a new ticket but this following ticket:
can be referenced as well, it will help provide background for the reasoning behind what I'm doing.
Let's say we have table named origtable1 defined with some fields like:
create table origtable1 ( id int, mydate date );
My programs can select the mydate field and convert to integer by doing this:
31/1899', 'MM/DD/YYYY'),'J'))) from origtable1
(I want to do this because we are migrating Informix to Oracle, and our C programs treat dates like long/int values, # of days since 12/31/1899).
If mydate field contained today's date May 6, 2009, the resulting value would be 39938.
Now my C program will utilize that value and perhaps do some manipulation such as subtract a day from it etc. Let's just say for simplicity sake that we just want to subtract 1 day, thus our new value would be 39937.
To update table origtable1 with this new value I simply type:
update origtable1 set mydate = to_date('1899-12-31','yyyy
The above all works fine and is how I want to do selecting,inserting,updati
ng Oracle tables in order to minimize the amount of changes in our C programs.
But I want to improve this even further. In the above scenario, I would have to modify all of our C programs' queries that use dates, wrapping them with all these Oracle date functions.
So I create another user (vrd_view) so that this user can create a view in it's own schema like this:
create or replace view origtable1 as
select id, (to_number(to_char(mydate,
31/1899', 'MM/DD/YYYY'),'J'))) mydate
(NOTE: take note that the name of my view is the *same* name as the underlying table in another schema, also field name mydate is the same. This is important so our C programs do not have to change)
Now in my C program, if I connect to the db as user vrd_view, and select from origtable1, I am *not* selecting against the actual table orig_owner.origtable1, I'm instead selecting against the view vrd_view.origtable1. Now my queries work fine because I'll be selecting integer date values like they are expecting. I've already tested this and it works beautifully.
My problem now becomes, how do I insert or update the underlying table of the view, with a date being represented as an integer (39938 for eg.)? I cannot update the view with an expression, already tried that:
(as user vrd_view: )
insert into origtable1 values (1, 39938)
ERROR: virtual column not allowed here)
So - can I create a trigger that is attached to my view, so that it converts the # 39938 to the appropriate Oracle date?
Maybe the trigger can perform this type of logic:
- for each of the date type fields in the table being updated/inserted to:
o modify the value being updated/inserted to be: to_date('1899-12-31','yyyy
o (where THEVALUE is the new value of the field from the program)
This way it is completely transparent to the C programs and will greatly help me in reducing the manual changes required.
So is creating a trigger to do the above a) possible and b) the best solution?