Updating view which doesn't have keys

[aravindv26]

Hi All,

In my mapping i hv to update a view, but without keys informatica doesn't update the target. Views doesn't have keys, is there any alternate solution for updating the view.

Thanks in Advance.

 

[datamart]

are you sure you want to update a view?
ideally in oracle too, you cannot run an update command on a view.
a view is actually a sql that selects columns from one or more tables joined to each other.
if you update the constituent table, the view will reflect the change accordingly.

 

[aravindv26]

Yes, I am sure i want to update a view.
That view is created using only one table.

 

[datamart]

this is something that i have not heard of. is it even possible in oracle??????

why dont you update the table..
or if u still want to make some conditional changes, modify the sql used to construct the view.

 

[blom0344]

Yes,updatable views do really exist, in ORACLE at least from 8i onwards. But, since the point is to manipulate the underlying base table, there seems little point in writing to a view (instead of directly to the table)

http://www.oracle.com/oramag/oracle/01-mar/o21o8i.html

Ties Blom
Information analyst

 

[datamart]

Thanks for the article, but I have some doubts.

Whenever we use select from a view, Oracle executes the underlying sql (contructing the view) at run time.
If I have updated the view, from where will Oracle get the updated data.
Does this mean that updatable views are physically stored?

 

[blom0344]

Updating the view results in update on the underlying table, which means synchronization of data for the objects.

Last developments also let us use materialized views (mainly to improve query performance) , but in that case data is stored in an intermediate fashion (that is the SQL does not need to be rerun to produce the views results)

Note that updatable views are severly limited. In fact, they can contain little else than a portion of the underlying table (no joins, disctinct, aggregates allowed)

Ties Blom
Information analyst