2

I'm trying to create an updatable view.

I understand that the db must be able to identify one single row that I'm trying to update.

I believe I satisfied this requirement. There are no aggregates. Each row in the view has a 1-to-1 relationship with rows in the source table which I'm trying to update (MY_CFG_TABLE below).

But my view is not updatable. Why not? Does it have something to do with the composite key?

CFG_VALUE is the column I want to update.

I want a join-view, rather than subquery, so that i can see, in the view, rows in ALL_TAB_COLUMNS for which there's no corresponding row in MY_CFG_TABLE (for that, it will have to be changed to outer join, but for now just trying to make the update work with an inner.)

In the end, I may resort to an INSTEAD OF trigger, but for now I would like to make an updatable join-view-- or understand why I cannot.

Here's the update statement which fails:

update VBA_CFG_JOIN_V
SET CFG_VALUE = 'TRUE'
WHERE TABLE_NAME = 'APN_GR'
AND COLUMN_ID = 1
AND CFG_NAME = 'PART_ID';

Here's the error message:

cannot modify a column which maps to a non key-preserved table

How can I make my source table, MY_CFG_TABLE, a Key Preserved Table?

View:

CREATE VIEW VBA_CFG_JOIN_V
AS SELECT F.TABLE_NAME, T.COLUMN_NAME, F.COLUMN_ID, F.CFG_NAME, F.CFG_VALUE 
FROM ALL_TAB_COLUMNS T, MY_CFG_TABLE F 
WHERE F.TABLE_NAME = T.TABLE_NAME AND F.COLUMN_ID = T.COLUMN_ID;

Source tables:

ALL_TAB_COLUMNS is a built-in Oracle view. (Note, I'm not trying to update ALL_TAB_COLUMNS). https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm

MY_CFG_TABLE:

CREATE TABLE "GSM_OWNER"."MY_CFG_TABLE" 
   (    "TABLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "COLUMN_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "CFG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "CFG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
     CONSTRAINT "VBA_FIELDS_PK" PRIMARY KEY ("TABLE_NAME", "COLUMN_ID", "CFG_NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS" ;
2

The problem is that we know that the join will produce only 1 row (and no more) in the view for every row in MY_CFG_TABLE but Oracle doesn't. As described in OraFAQ: Updatable Join Views (emphasis mine):

Clearly the view cannot contain DISTINCT, GROUP BY, CONNECT BY, aggregate functions, UNION, INTERSECT, or MINUS clauses: simple joins are all that is allowed. There is one other restriction: the view must be key preserved. This means that the join must meet the following restrictions:

  • The join criteria must be equals (=) joins.
  • Tables other than the table that is to be updated must be joined on a primary or unique key.

If these conditions are violated, then Oracle cannot guarantee that the view will return one row only for each row in the base table. If two or more rows in the secondary table mapped to each row in the base table, then the update would be ambiguous. An attempt to update a non-key-preserved view will result in an Oracle error.

I assume that either the joined table (which is actually a system view) does not have a UNIQUE or PRIMARY KEY constraint or one of the underlying tables (when the whole view is expanded) is not joined on its unique/primary key. This might be causing the errors when we try to update the view.

The problem may be more complicated as we don't know the exact definition of the underlying ALL_TAB_COLUMNS view. It might have some non-equality joins or non-inner joins or unions that make the new view non-updatable.

  • This might be the issue: the fields in ALL_TAB_COLUMNS might not be primary or unique. How can we determine? However, the rule sounds like the non-updating table should be joined to a key which primary/unique in the updating table-- not itself. (I think whether ALL_TAB_COLUMNS view is non-updatable is not relevant, since i'm not trying to update it. The Oracle doc says only the table i'm updating must fulfill requirements.) I'll give this a try with some sample tables. – johny why Sep 22 '16 at 21:26
  • No, the PK/UNIQUE key of the not-updated table should be used for the join. That ensures that we don't get multiple rows (for 1 row in the updated table) in the result set. – ypercubeᵀᴹ Sep 22 '16 at 21:28
  • I agree that whether the ALL_TAB_COLUMNS is updatable or not is irrelevant, I hope my answer doesn't seem to imply that. What is relevant is whether it has a unique key. Since it is a view it may not even have one. – ypercubeᵀᴹ Sep 22 '16 at 21:29
  • Is it correct to say that no view can have a primary key-- rather, it's underlying tables do. – johny why Sep 22 '16 at 21:30
  • Yes, I was about to add that. Actually you can define PRIMARY, UNIQUE and FOREIGN KEY constraints in views in Oracle. But a small test I did indicates that they are ignored for the "key-preservation" issue. Only the underlying tables are checked. – ypercubeᵀᴹ Sep 22 '16 at 21:48
-2

ALL_TAB_COLUMNS is a built-in view, that is not updatable:

select table_name, column_name, updatable, insertable, deletable from dba_updatable_columns where table_name = 'ALL_TAB_COLS';

TABLE_NAME   COLUMN_NAME                    UPD INS DEL
------------ ------------------------------ --- --- ---
ALL_TAB_COLS CHAR_LENGTH                    NO  NO  NO 
ALL_TAB_COLS CHAR_USED                      NO  NO  NO 
ALL_TAB_COLS V80_FMT_IMAGE                  NO  NO  NO 
ALL_TAB_COLS DATA_UPGRADED                  NO  NO  NO 
ALL_TAB_COLS HIDDEN_COLUMN                  NO  NO  NO 
ALL_TAB_COLS VIRTUAL_COLUMN                 NO  NO  NO 
ALL_TAB_COLS SEGMENT_COLUMN_ID              NO  NO  NO 
ALL_TAB_COLS INTERNAL_COLUMN_ID             NO  NO  NO 
ALL_TAB_COLS HISTOGRAM                      NO  NO  NO 
ALL_TAB_COLS QUALIFIED_COL_NAME             NO  NO  NO 
ALL_TAB_COLS USER_GENERATED                 NO  NO  NO 
ALL_TAB_COLS DEFAULT_ON_NULL                NO  NO  NO 
ALL_TAB_COLS IDENTITY_COLUMN                NO  NO  NO 
ALL_TAB_COLS EVALUATION_EDITION             NO  NO  NO 
ALL_TAB_COLS UNUSABLE_BEFORE                NO  NO  NO 
ALL_TAB_COLS UNUSABLE_BEGINNING             NO  NO  NO 
ALL_TAB_COLS OWNER                          NO  NO  NO 
ALL_TAB_COLS TABLE_NAME                     NO  NO  NO 
ALL_TAB_COLS COLUMN_NAME                    NO  NO  NO 
ALL_TAB_COLS DATA_TYPE                      NO  NO  NO 
ALL_TAB_COLS DATA_TYPE_MOD                  NO  NO  NO 
ALL_TAB_COLS DATA_TYPE_OWNER                NO  NO  NO 
ALL_TAB_COLS DATA_LENGTH                    NO  NO  NO 
ALL_TAB_COLS DATA_PRECISION                 NO  NO  NO 
ALL_TAB_COLS DATA_SCALE                     NO  NO  NO 
ALL_TAB_COLS NULLABLE                       NO  NO  NO 
ALL_TAB_COLS COLUMN_ID                      NO  NO  NO 
ALL_TAB_COLS DEFAULT_LENGTH                 NO  NO  NO 
ALL_TAB_COLS DATA_DEFAULT                   NO  NO  NO 
ALL_TAB_COLS NUM_DISTINCT                   NO  NO  NO 
ALL_TAB_COLS LOW_VALUE                      NO  NO  NO 
ALL_TAB_COLS HIGH_VALUE                     NO  NO  NO 
ALL_TAB_COLS DENSITY                        NO  NO  NO 
ALL_TAB_COLS NUM_NULLS                      NO  NO  NO 
ALL_TAB_COLS NUM_BUCKETS                    NO  NO  NO 
ALL_TAB_COLS LAST_ANALYZED                  NO  NO  NO 
ALL_TAB_COLS SAMPLE_SIZE                    NO  NO  NO 
ALL_TAB_COLS CHARACTER_SET_NAME             NO  NO  NO 
ALL_TAB_COLS CHAR_COL_DECL_LENGTH           NO  NO  NO 
ALL_TAB_COLS GLOBAL_STATS                   NO  NO  NO 
ALL_TAB_COLS USER_STATS                     NO  NO  NO 
ALL_TAB_COLS AVG_COL_LEN                    NO  NO  NO 

Also, there are more restrictions:

Notes on Updatable Views

  • I know-- i'm not trying to update ALL_TAB_COLUMNS. – johny why Sep 22 '16 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.