KarenNewton
asked on
Oracle: Inserting to a view, using Trigger
This is a new ticket but this following ticket:
https://www.experts-exchange.com/questions/24381581/Informix-to-Oracle-Migration-Dealing-with-Dates-and-Nulls-and.html
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:
select (to_number(to_char(mydate,
(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
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,
FROM orig_owner.origtable1
(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?
Thanks!
Karen
https://www.experts-exchange.com/questions/24381581/Informix-to-Oracle-Migration-Dealing-with-Dates-and-Nulls-and.html
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:
select (to_number(to_char(mydate,
(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
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,
FROM orig_owner.origtable1
(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?
Thanks!
Karen
ASKER
Hi - yes thanks for showing that to me again! I took some ill advice it seems.. thank goodness it's much simpler to extract the date in the format I want it. I've updated my logic now to use the simplified conversion as you've pointed out.
Looking forward to further replies, might have to leave shortly and not post back here for several hours until I'm online this evening again.
Thanks again,
Karen
Looking forward to further replies, might have to leave shortly and not post back here for several hours until I'm online this evening again.
Thanks again,
Karen
The challenge here is you are trying to avoid porting your app to Oracle, while porting your data to Oracle. I think this is a bad decision, and a waste of money.
If you want to deal with integers, you COULD just use an integer column instead. Then create a read-only view that does the to_date() calculation when needed, but all of your Informix compliant code can use the original column, and you can use the view for the Oracle DATE output.
Do you have ability to use 11g? If this is a new port, why not go with 11.1 right away? It has calculated columns, but will still not allow you to insert the number and convert on the fly as calculated columns are read-only, you can only update them by changing the underlying column. So a trigger may be your best bet.
I or someone can probably do a sample for you, with a before insert trigger on insert or update of an integer column to populate an date column.
If you want to deal with integers, you COULD just use an integer column instead. Then create a read-only view that does the to_date() calculation when needed, but all of your Informix compliant code can use the original column, and you can use the view for the Oracle DATE output.
Do you have ability to use 11g? If this is a new port, why not go with 11.1 right away? It has calculated columns, but will still not allow you to insert the number and convert on the fly as calculated columns are read-only, you can only update them by changing the underlying column. So a trigger may be your best bet.
I or someone can probably do a sample for you, with a before insert trigger on insert or update of an integer column to populate an date column.
ASKER
The issue is we have several C programs that are affected by the migration, so I'm just trying to identify the best solution that would equate to the least amount of changes required. Down the road we will undertake several rewrites of said code, so we want to keep modifications now to a minimum. Unfortunately going to 11g probably isn't viable, as we already have licensing for 10g.
So I guess it all boils down to, can my C programs insert dates as type long? I think with a trigger this should be feasible? Could the sql section of the trigger simply do:
:NEW.mydate := to_date('12/31/1899','MM/D
Thanks again,
Karen
So I guess it all boils down to, can my C programs insert dates as type long? I think with a trigger this should be feasible? Could the sql section of the trigger simply do:
:NEW.mydate := to_date('12/31/1899','MM/D
Thanks again,
Karen
Inserting in views?
This can be done DIRECTLY only in simple views.
If your view is not simple you have programmatically code
HOW will you insert in the base table(s).
Oracle provides INSTEAD OF INSERT triggers that can be used.
You have to figure out the way how should be the base tables inserted.
This is not so easy and some times even imposible.
This can be done DIRECTLY only in simple views.
If your view is not simple you have programmatically code
HOW will you insert in the base table(s).
Oracle provides INSTEAD OF INSERT triggers that can be used.
You have to figure out the way how should be the base tables inserted.
This is not so easy and some times even imposible.
ASKER
Wouldn't my above code snippet work properly with an INSTEAD OF INSERT trigger? Except the wording of that makes me think that instead of inserting do the following...
:NEW.mydate := to_date('12/31/1899','MM/D
How then, after doing the above, does one execute the insert?
:NEW.mydate := to_date('12/31/1899','MM/D
How then, after doing the above, does one execute the insert?
INSTEAD OF TRIGGER is used to override, so in that case you have to explicitly code the INSERT statements inside the trigger, and usually the trigger is used to INSERT into the base tables. Essentially the VIEW is such that makes it non-updatable, so you write INSTEAD OF trigger to perform the logic behind the scenes.
I don't think you need this, I think you can do it without a view, by just storing your original date column as a INTEGER / NUMBER in Oracle, and adding a 2nd column that is real DATE type, and adding a plain BEFORE INSERT OR UPDATE ... FOR EACH ROW trigger that will convert the INTEGER column to the actual DATE. I will post an actual example as soon as I take care of morning things, like coffee and voicemail.
I don't think you need this, I think you can do it without a view, by just storing your original date column as a INTEGER / NUMBER in Oracle, and adding a 2nd column that is real DATE type, and adding a plain BEFORE INSERT OR UPDATE ... FOR EACH ROW trigger that will convert the INTEGER column to the actual DATE. I will post an actual example as soon as I take care of morning things, like coffee and voicemail.
Definitelly you are speaking about updating of views.
If you would like to update (insert) via the view then you should use INSTEAD OF trigger(s).
this is the thing that makes your view nonupdatable:
(to_number(to_char(mydate,
If not - the update the base table directly (no matter where - in the INSTEAD OF trigger or directly):
insert into orig_owner.origtable1 values (1, to_date('12/31/1899','MM/D
update orig_owner.origtable1 set mydate = to_date('12/31/1899','MM/D
If you would like to update (insert) via the view then you should use INSTEAD OF trigger(s).
this is the thing that makes your view nonupdatable:
(to_number(to_char(mydate,
If not - the update the base table directly (no matter where - in the INSTEAD OF trigger or directly):
insert into orig_owner.origtable1 values (1, to_date('12/31/1899','MM/D
update orig_owner.origtable1 set mydate = to_date('12/31/1899','MM/D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for all of the replies. User mrjoltcola has definitely put in a lot of time and details to help me out, so it's only deserving I award the points to him.
I've discussed all of this with my dba staff and they think the best solution is to forego using triggers and instead use a function. Meaning we have to modify our C code to wrap the date fields with the Oracle function, that will in turn return to use the numeric value our C programs are expecting.
Thanks again for all of the help, I certainly learned a great deal.
Karen
I've discussed all of this with my dba staff and they think the best solution is to forego using triggers and instead use a function. Meaning we have to modify our C code to wrap the date fields with the Oracle function, that will in turn return to use the numeric value our C programs are expecting.
Thanks again for all of the help, I certainly learned a great deal.
Karen
ASKER
Thank you again for your detailed replies that were spot on to what I was asking for.
If you go that route, and expect to use indexes on the column, make sure to create a deterministic function, and create a function based index on the table, or predicates on teh column will not use indexes.
I meant: "or predicates on the column that make use of the function will not use indexes."
ASKER
Thanks once again I'll definitely pass that advice along to my dba group so they are aware.
Thank you,
Karen
Thank you,
Karen
select mydate - to_date('12/31/1899', 'MM/DD/YYYY') as informixdate
from orig_owner.origtable1
Date arithmetic results in integers. No need for all those other functions. I am reading the rest of your problem, but wanted to put this at the top, because we discussed this in the other thread.