Improve company productivity with a Business Account.Sign Up


Oracle: Inserting to a view, using Trigger

Posted on 2009-05-06
Medium Priority
Last Modified: 2013-12-18
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:

select (to_number(to_char(mydate, 'J'))-to_number(to_char(to_date('12/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-mm-dd')+39937;

The above all works fine and is how I want to do selecting,inserting,updating 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, 'J'))-to_number(to_char(to_date('12/31/1899', 'MM/DD/YYYY'),'J'))) 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-mm-dd')+THEVALUE
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?


Question by:KarenNewton
  • 6
  • 6
  • 2
LVL 40

Expert Comment

ID: 24317866
Hi Karen, as I pointed out in the other question, you have too many conversions (to_date, to_char, to_number), so first off, lets simplify again, To convert a date in oracle to a number, as you requested, using the test table, is simply:

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.

Author Comment

ID: 24317962
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,
LVL 40

Expert Comment

ID: 24318047
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.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.


Author Comment

ID: 24318161
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/DD/YYYY') + :NEW.mydate

Thanks again,
LVL 48

Expert Comment

ID: 24318279
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.

Author Comment

ID: 24324473
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/DD/YYYY') + :NEW.mydate

How then, after doing the above, does one execute the insert?

LVL 40

Expert Comment

ID: 24325018
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.
LVL 48

Expert Comment

ID: 24325585
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, 'J'))-to_number(to_char(to_date('12/31/1899', 'MM/DD/YYYY'),'J')))

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/DD/YYYY') + :NEW.mydate);
update  orig_owner.origtable1 set  mydate =  to_date('12/31/1899','MM/DD/YYYY') + :NEW.mydate);

LVL 40

Accepted Solution

mrjoltcola earned 2000 total points
ID: 24325793
To the original problem, which is allowing the legacy code to make use of an Informix 1899 based date.

Here is a quick sample without a view, that just uses a trigger updates back and forth, depending on which column you use. Since this is a shared INSERT + UPDATE trigger, to really make it robust you can also add



to handle odd conditions when both columns are provided, etc. I did not test with all combinations, etc.

drop table t;
create table t (
  id integer,
  orig_date integer,    -- number of days since 12/31/1899
  ora_date date         -- real DATE, we will update by trigger
-- To get fancy, you can put reverse trigger on ora_date column, but be aware of what will
-- happen if you update / insert both values
-- When updating ora_date
create or replace trigger t_updins
before insert or update on t
for each row
when (new.ora_date is not null or new.orig_date is not null)
  if :new.orig_date is not null and :new.ora_date is null then
     dbms_output.put_line('Updating ora_date from orig_date ' || :new.orig_date);
     :new.ora_date := to_date('12/31/1899','MM/DD/YYYY') + :new.orig_date;
  elsif :new.ora_date is not null then
     dbms_output.put_line('Updating orig_date from ora_date');
     :new.orig_date := :new.ora_date - to_date('12/31/1899','MM/DD/YYYY');
  end if;
-- Some test data
alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY';
set serveroutput on
insert into t values(1, 0, null);
insert into t(id, orig_date) values(2, 1);
insert into t(id, orig_date) values(3, 39937);
insert into t(id, ora_date) values(4, sysdate);
insert into t(id, ora_date) values(5, sysdate-365);
select * from t;
update t set ora_date = sysdate where id = 1;
select * from t where id = 1;

Open in new window


Author Comment

ID: 24329865
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.


Author Closing Comment

ID: 31578595
Thank you again for your detailed replies that were spot on to what I was asking for.
LVL 40

Expert Comment

ID: 24330093
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.
LVL 40

Expert Comment

ID: 24330129
I meant: "or predicates on the column that make use of the function will not use indexes."

Author Comment

ID: 24331377
Thanks once again I'll definitely pass that advice along to my dba group so they are aware.

Thank you,

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

606 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