Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> updating key-preserved table via a joined view

updating key-preserved table via a joined view

From: James Anderson <in_at_onramp.net>
Date: 1997/07/25
Message-ID: <33D91039.7B8F@onramp.net> 







***note**** - this is a duplicate message that was sent to the Oracle-L
listserv.  Please excuse any duplications.



Greetings everyone,  



I have embarked on Bill (Gates) and Larry's (Ellison) Excellent
Adventure and am having some problems.  We run Oracle 7 v. 7.3.3.0.0 on
an NT 4.0 server with a 166Mhz processor and 128MB of RAM.  We develop a
records management application that uses MS Access as a front end and
back end or MS SQL Server as a back end.  Currently we are developing
the application to work with Oracle 7.3 as a back end but are having
some trouble with Oracle's criteria of modifying an underlying base
table through a joined view.



The following is an excerpt I took from my message emailed to Oracle's
Virtual Support Analyst.  I have not heard back from them yet so I
thought I would try this listserv.


Note: I am not using the MS Access front end while encountering this
problem.  I am only using SQL syntax to manipulate the view and
underlying tables.




Unable to modify a base table's columns through a legal joined view.
The primary key of the base table to be modified is selected and
preserved but when attempt to insert records receive 0RA 01779 "error
cannot modify columns of a base table which is non key-preserved".
The following are descriptions of the objects in question:
The following object is a view which that the underlying base table, TBL
CLASSIFICATIONTEST, columns cannot be modified, even though modifiable
criteria has been met.  Here is the select statement of the view:
CREATE VIEW TBLCLASSTEST AS SELECT T1.*, T2.USER_ID

FROM INORA32.TBLCLASSIFICATIONTEST T1, INORA32.TBLSBV_CLASSTEST T2

SQLWKS> DESCRIBE TBLCLASSTEST


Column Name                    Null?    Type


------------------------------ -------- ----

CLASS_CODE                     NOT NULL NUMBER(10)
COMPANY                                 VARCHAR2(20)
OFFICE                                  VARCHAR2(20)
SERIES                         NOT NULL VARCHAR2(10)
FILE_TYPE                      NOT NULL VARCHAR2(25)
DOCUMENT_TYPE                  NOT NULL VARCHAR2(25)
MEDIA                          NOT NULL VARCHAR2(10)
SEE                                     VARCHAR2(30)
OFC_RECORD                              VARCHAR2(10)
OFC_RET                                 VARCHAR2(8)
VITAL                                   VARCHAR2(1)
RET_AUTH                                VARCHAR2(15)
TOTAL_RET                               VARCHAR2(10)
TOTAL_RET_MONTH                         NUMBER(5)
RET_COMMENT                             VARCHAR2(2000)
DESTRUCT_METHOD                         VARCHAR2(1)
LABEL_FORMAT                            VARCHAR2(8)
TAX_CLEARANCE                  NOT NULL NUMBER(1)
CONDITION                      NOT NULL NUMBER(1)
SECURE                         NOT NULL NUMBER(1)
DELETE_FLAG                    NOT NULL NUMBER(1)
CLASS_LABEL                             VARCHAR2(20)
EFFECTIVE_DATE                          DATE
CREATED_TIMESTAMP                       DATE
SPARE_1                                 VARCHAR2(10)
SPARE_2                                 VARCHAR2(10)
SPARE_3                                 VARCHAR2(10)
DOCUMENT                                LONG RAW
USER_ID                        NOT NULL VARCHAR2(30)


The following object is a table with a PK on the CLASS_CODE column and
a FK on COMPANY referencing TBLSBV_CLASSTEST PK COMPANY column
SQLWKS> DESCRIBE TBLCLASSIFICATIONTEST


Column Name                    Null?    Type


------------------------------ -------- ----

CLASS_CODE                     NOT NULL NUMBER(10)
COMPANY                                 VARCHAR2(20)
OFFICE                                  VARCHAR2(20)
SERIES                         NOT NULL VARCHAR2(10)
FILE_TYPE                      NOT NULL VARCHAR2(25)
DOCUMENT_TYPE                  NOT NULL VARCHAR2(25)
MEDIA                          NOT NULL VARCHAR2(10)
SEE                                     VARCHAR2(30)
OFC_RECORD                              VARCHAR2(10)
OFC_RET                                 VARCHAR2(8)
VITAL                                   VARCHAR2(1)
RET_AUTH                                VARCHAR2(15)
TOTAL_RET                               VARCHAR2(10)
TOTAL_RET_MONTH                         NUMBER(5)
RET_COMMENT                             VARCHAR2(2000)
DESTRUCT_METHOD                         VARCHAR2(1)
LABEL_FORMAT                            VARCHAR2(8)
TAX_CLEARANCE                  NOT NULL NUMBER(1)
CONDITION                      NOT NULL NUMBER(1)
SECURE                         NOT NULL NUMBER(1)
DELETE_FLAG                    NOT NULL NUMBER(1)
CLASS_LABEL                             VARCHAR2(20)
EFFECTIVE_DATE                          DATE
CREATED_TIMESTAMP                       DATE
SPARE_1                                 VARCHAR2(10)
SPARE_2                                 VARCHAR2(10)
SPARE_3                                 VARCHAR2(10)
DOCUMENT                                LONG RAW


The following object is the other table in the join that does not need
to be modifiable. It has a PK on the COMPANY column.
SQLWKS> DESCRIBE TBLSBV_CLASSTEST


Column Name                    Null?    Type


------------------------------ -------- ----

USER_ID                        NOT NULL VARCHAR2(30)
COMPANY                        NOT NULL VARCHAR2(20)
OFFICE                         NOT NULL VARCHAR2(20)


When the following query is run:


SQLWKS> SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME =

'TBLCLASSTEST'



 All columns of this view show a "no" in the UPDATABLE column when the
columns representing TBLCLASSIFICATIONTEST should be updatable.





I would greatly appreciate any comments anyone might have on this
subject. Also, if anyone knows where one could obtain information on
migrating MS SQL Server applications to Oracle would be of great
assistance also.  Feel free to reply to the list or my company email
address.  Donka-shin, Muchos Garcias, and for the other languages I dont
know how to say thanks in, THANKS!!



James Anderson


Information Network


Houston TX  USA



company phone:  713.869.8756


company email:  in_at_onramp.net


company URL:    http://rampages.onramp.net/~in
Received on Fri Jul 25 1997 - 00:00:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US