Oracle updating through view

_____" id="ctl00_m_m_i_ctl00_gr_ctl03_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="2453822" / you cannot write to a view in Netezza.this has always been a hard-and-fast rule and will never change loading Netezza happens in bulk - you don't want any SQL statements or rules in the way of this activity.Let's look at one simple example.-- Create our data CREATE TABLE Base Table (field1 NUMBER(8), field2 NUMBER(8)); INSERT INTO Base Table (field1, field2) VALUES (5, 10); CREATE OR REPLACE VIEW Update View Yes AS SELECT field1, field2 FROM Base Table; CREATE OR REPLACE VIEW Update View No AS SELECT field1 field2 addfield FROM Base Table;-- This won't work SQL UPDATE Update View No SET addfield = 16; UPDATE Update View No SET addfield = 16 *ERROR at line 1: ORA-01733: virtual column not allowed here-- This should work UPDATE Update View Yes SET field1 = 6 WHERE field1 = 5; SQLIn this example, Update View No.addfield is the addition of two columns.

Can I rewrite the above with join, and still be able to update/insert the view?

All the columns from all_updatable_columns show they are not updatable! Moving onto your other one -- if the right keys are in place AND we drop the NVL() which clouds the issue -- the view can be key preserved.

When you're executing DML operations on a View, Oracle actually executes those DML operations appropriately on the base tables.

And there are some cases where it is impossible for Oracle to do that.

Same goes if you have a collection expression or a subquery in a SELECT list.

--This is updatable CREATE OR REPLACE VIEW V_my View AS SELECT A.

hard-and-fast rule and will never change loading Netezza happens in bulk - you don't want any SQL statements or rules in the way of this activity.

Even if it were supported, it would defeat the data processing mission - i would wave people off this path Views are good to manage consumption, nothing else.

(eg: if you join EMP to DEPT and given that a primary key exists on DEPTNO in DEPT, you know that each row in EMP will be returned AT MOST once. DEPT on the other hand -- well, each row in DEPT might be returned 0, 1 or more times -- DEPT is not key preserved). SESSIONKEY = sys_context('userenv','SESSION_USER') 11 and slb. We attempt to make what we can updateable and report to you how much of it is.

SESSION# ; Note, I tried without the NVL function, as well. A given row in my Table will be selected at most ONCE in the view. TIMEUnit 7 FROM my Table A, my Slb slb, my USERPROF u1 8 where a.cny# = slb.cny# 9 and nvl(a.location#,0) = slb.locationkey 10 and slb. We document the existance of the "updateable" view - but that is about it.

"In some cases, yes you can, and it actually serves as a handy technique to manage security on your data.

Tags: , ,