Well, it has to do with KEY Preserved tables, see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:548422757486 <code>
to read about that concept.
Basically -- in the first view -- it understands that each row in myTable will (can) appear in the view AT MOST ONCE. A given row in myTable will be selected at most ONCE in the view. (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. EMP is "key preserved". DEPT on the other hand -- well, each row in DEPT might be returned 0, 1 or more times -- DEPT is not key preserved).
We only allow modifications to objects in the key preserved table.
Using the IN assures us (because there is only ONE table in the FROM clause of the view) that the rows are in fact key preserved.
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. Consider:
ops$tkyte@ORA920.US.ORACLE.COM> create table myTable
2 ( cny# int, record# int, status int, timeUnit int, location# int );
Table created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table mySlb ( cny# int, locationkey int, sessionkey int );
Table created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table myuserprof ( cny# int, session# int );
Table created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
2 SELECT
3 A.CNY#,
4 A.RECORD#,
5 A.STATUS,
6 A.TIMEUnit
7 FROM myTable A, mySlb slb, myUSERPROF u1
8 where a.cny# = slb.cny#
9 and nvl(a.location#,0) = slb.locationkey
10 and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
11 and slb.CNY# = U1.CNY#
12 and slb.SESSIONKEY = U1.SESSION# ;
View created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
2 insertable,deletable
3 from user_updatable_columns
4 where table_name = 'V_MYVIEW'
5 /
COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
CNY# NO NO NO
RECORD# NO NO NO
STATUS NO NO NO
TIMEUNIT NO NO NO
that is what you are seeing now...ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter table myslb
2 add constraint myslb_pk primary key(cny#,locationkey);
Table altered.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter table myuserprof
2 add constraint myuserprof_pk primary key(cny#,session#);
Table altered.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
2 SELECT
3 A.CNY#,
4 A.RECORD#,
5 A.STATUS,
6 A.TIMEUnit
7 FROM myTable A, mySlb slb, myUSERPROF u1
8 where a.cny# = slb.cny#
9 and nvl(a.location#,0) = slb.locationkey
10 and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
11 and slb.CNY# = U1.CNY#
12 and slb.SESSIONKEY = U1.SESSION# ;
View created.
ops$tkyte@ORA920.US.ORACLE.COM> /
View created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
2 insertable,deletable
3 from user_updatable_columns
4 where table_name = 'V_MYVIEW'
5 /
COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
CNY# NO NO NO
RECORD# NO NO NO
STATUS NO NO NO
TIMEUNIT NO NO NO
same thing as before, it is because of the NVL on the table we are trying to keypreserveops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
2 SELECT
3 A.CNY#,
4 A.RECORD#,
5 A.STATUS,
6 A.TIMEUnit
7 FROM myTable A, mySlb slb, myUSERPROF u1
8 where a.cny# = slb.cny#
9 and a.location# = slb.locationkey
10 -- and nvl(a.location#,0) = slb.locationkey
11 and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
12 and slb.CNY# = U1.CNY#
13 and slb.SESSIONKEY = U1.SESSION# ;
View created.
ops$tkyte@ORA920.US.ORACLE.COM> /
View created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
2 insertable,deletable
3 from user_updatable_columns
4 where table_name = 'V_MYVIEW'
5 /
COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
CNY# YES YES YES
RECORD# YES YES YES
STATUS YES YES YES
TIMEUNIT YES YES YES
ops$tkyte@ORA920.US.ORACLE.COM>
and there you go -- no it is the same as your IN viewIs there another workaround? Yes, namely INSTEAD of triggers. You can code the logic behind the INSERT yourself. Consider:
ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
2 SELECT
3 A.CNY#,
4 A.RECORD#,
5 A.STATUS,
6 A.TIMEUnit
7 FROM myTable A, mySlb slb, myUSERPROF u1
8 where a.cny# = slb.cny#
9 and nvl(a.location#,0) = slb.locationkey
10 and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
11 and slb.CNY# = U1.CNY#
12 and slb.SESSIONKEY = U1.SESSION# ;
View created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
2 insertable,deletable
3 from user_updatable_columns
4 where table_name = 'V_MYVIEW'
5 /
COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
CNY# NO YES NO
RECORD# NO YES NO
STATUS NO YES NO
TIMEUNIT NO YES NO
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into v_myView values ( 1,1,1,1 );
insert into v_myView values ( 1,1,1,1 )
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
ops$tkyte@ORA920.US.ORACLE.COM> create or replace trigger v_myView_IOI
2 instead of insert on v_myView
3 begin
4 insert into myTable ( cny#, record#, status, timeUnit )
5 values ( :new.cny#, :new.record#, :new.status, :new.timeUnit );
6 end;
7 /
Trigger created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into v_myView values ( 1,1,1,1 );
1 row created.