[julla27]

Hello All!
I want to create a PROCEDURE deleting some records of the different TABLES.

PHP Code:

CREATE OR REPLACE PROCEDURE PRC_DEL_ALL_DAEMON_DATA
(
       pId VIEW_RESERVE_DET.DAEMON%TYPE
)
AS
       LIC       NUMBER;
BEGIN      

        DELETE FROM VIEW_..
        IF SQL%ROWCOUNT = 0 THEN ..
        ELSE
                 DELETE FROM VIEW_FEATURE WHERE DAEMON = pId;
            IF SQL%ROWCOUNT = 0 THEN ...
            ELSE
                DELETE FROM VIEW_FEATURE_BORROW_OPTIONS WHERE DAEMON = pId;
...
                DELETE FROM VIEW_FEATURE_AVAIL WHERE DAEMON = pId;
...        
                ...            END IF;
            
            DELETE FROM VIEW_FEATURE_BASE WHERE DAEMON = pId;
..        
        END IF;
        
END;
/ 


This procedure works till VIEW_FEATURE and VIEW_FEATURE_AVAIL.
At this both views i have the same problem:

PHP Code:

DELETE FROM VIEW_FEATURE WHERE DAEMON = ''; 


ORA-01752: cannot delete from view without exactly one key-preserved table


or

PHP Code:

DELETE FROM VIEW_FEATURE_AVAIL WHERE DAEMON = ''; 


ORA-01752: cannot delete from view without exactly one key-preserved table



I don't know what how can i do to solve this problem without making many changes on the Database-structure..


It would be great if anyone could help me!

Thanks in advance!



Maybe the codes of the both Tables & views can help:

VIEW_FEATURE:

PHP Code:

CREATE OR REPLACE FORCE VIEW VIEW_FEATURE
(ID_FEATURE, FEATURE_KEY, ID_FEATURE_BASE, FEATURE_NAME, DAEMON, 
 VERSION, EXPIRE_DATE, NUMLIC, NUM_AVAIL, CREATED_ON, 
 CREATED_BY, CHANGED_ON, CHANGED_BY, SIGN, VENDOR_STRING, 
 HOSTID, HOST_BASED, ISSUED, SUPERSEDE_ISSUED, ISSUER, 
 SUPERSEDE_ISSUER, DUPGROUP, SUPERSEDE_DUP_GR, SN, CK, 
 START_DATE, USER_BASED, TS_OK, BORROW, FLOAT_OK, 
 SUITE_DUPGROUP, LINGER, NOTICE, OVERDRAFT, PLATFORMS)
AS 
SELECT 
       F.ID_FEATURE, F.FEATURE_KEY, FB.ID_FEATURE_BASE
       , FB.FEATURE_NAME , FB.DAEMON  
     , F.VERSION, F.EXPIRE_DATE, F.NUMLIC, FA.NUM_AVAIL
     , F.CREATED_ON, F.CREATED_BY, F.CHANGED_ON, F.CHANGED_BY
     , F.SIGN, F.VENDOR_STRING, F.HOSTID, F.HOST_BASED
     , F.ISSUED, F.SUPERSEDE_ISSUED, F.ISSUER, F.SUPERSEDE_ISSUER, F.DUPGROUP, F.SUPERSEDE_DUP_GR
     , F.SN, F.CK, F.START_DATE, F.USER_BASED, F.TS_OK, F.BORROW, F.FLOAT_OK
     , F.SUITE_DUPGROUP, F.LINGER, F.NOTICE, F.OVERDRAFT, F.PLATFORMS
FROM FEATURE F, VIEW_FEATURE_BASE FB, FEATURE_AVAILABLE FA
WHERE F.ID_FEATURE_BASE = FB.ID_FEATURE_BASE
   AND FA.ID_FEATURE = F.ID_FEATURE 
  AND FA.ID_FEATURE_BASE = FB.ID_FEATURE_BASE; 


TABLE FEATURE:

PHP Code:

CREATE TABLE FEATURE
(
  ID_FEATURE        INTEGER,
  FEATURE_KEY       VARCHAR2(25) CONSTRAINT NN_FEATURE_FKEY NOT NULL,
  ID_FEATURE_BASE   INTEGER CONSTRAINT NN_FEATURE_IDFBASE NOT NULL,
  VERSION           VARCHAR2(10) CONSTRAINT NN_FEATURE_VERSION NOT NULL,
  EXPIRE_DATE       DATE CONSTRAINT NN_FEATURE_EXPDATE NOT NULL,
  NUMLIC            INTEGER CONSTRAINT NN_FEATURE_NUMLIC NOT NULL,
  CREATED_ON        DATE,
  CREATED_BY        VARCHAR2(20),
  CHANGED_ON        DATE,
  CHANGED_BY        VARCHAR2(20),
  SIGN              VARCHAR2(200),
  VENDOR_STRING     VARCHAR2(200),
  HOSTID            VARCHAR2(200),
  HOST_BASED        INTEGER,
  ISSUED            DATE,
  SUPERSEDE_ISSUED  VARCHAR2(1)                 DEFAULT 'N',
  ISSUER            VARCHAR2(50),
  SUPERSEDE_ISSUER  VARCHAR2(1)                 DEFAULT 'N',
  DUPGROUP          VARCHAR2(4),
  SUPERSEDE_DUP_GR  VARCHAR2(1)                 DEFAULT 'N',
  SN                VARCHAR2(25),
  CK                INTEGER,
  START_DATE        DATE,
  USER_BASED        INTEGER,
  TS_OK             VARCHAR2(1),
  BORROW            INTEGER,
  FLOAT_OK          VARCHAR2(10),
  SUITE_DUPGROUP    VARCHAR2(4),
  LINGER            INTEGER,
  NOTICE            VARCHAR2(200),
  OVERDRAFT         INTEGER,
  PLATFORMS         VARCHAR2(200)
);


CREATE UNIQUE INDEX PK_FEATURE ON FEATURE (ID_FEATURE);


CREATE UNIQUE INDEX UK_FEATURE_FKEY ON FEATURE (FEATURE_KEY);


CREATE OR REPLACE TRIGGER TRG_FEATURE_AFT_INS
AFTER INSERT ON FEATURE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
      INSERT INTO FEATURE_AVAILABLE (ID_FEATURE, FEATURE_KEY, ID_FEATURE_BASE
                         , NUM_AVAIL, EXPIRE_DATE
                    , CREATED_ON, CREATED_BY, CHANGED_ON, CHANGED_BY
                 ) 
              VALUES (SEQ_FEATURE.CURRVAL, :NEW.FEATURE_KEY, :NEW.ID_FEATURE_BASE
                         , :NEW.NUMLIC, :NEW.EXPIRE_DATE
                    , SYSDATE, USER, SYSDATE, USER
                 );

END;
/

CREATE OR REPLACE TRIGGER TRG_FEATURE_AFT_UPD 
AFTER UPDATE ON FEATURE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
     UPDATE FEATURE_AVAILABLE 
                              SET FEATURE_KEY = :NEW.FEATURE_KEY
                               , ID_FEATURE_BASE =:NEW.ID_FEATURE_BASE
                                    , NUM_AVAIL =:NEW.NUMLIC
                               , EXPIRE_DATE =:NEW.EXPIRE_DATE
                               , CREATED_ON = :NEW.CREATED_ON
                               , CREATED_BY = :NEW.CHANGED_BY 
                               , CHANGED_ON = SYSDATE
                               , CHANGED_BY = USER
                           WHERE ID_FEATURE = :NEW.ID_FEATURE;
END;
/




CREATE OR REPLACE TRIGGER TRG_FEATURE_BEF_INS 
BEFORE INSERT ON FEATURE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
     tmpVar1 NUMBER;

BEGIN
     tmpVar1 := 0;
     
  SELECT SEQ_FEATURE.NEXTVAL INTO tmpVar1 FROM DUAL;
    :NEW.ID_FEATURE := tmpVar1; 
    :NEW.CREATED_ON := SYSDATE;
    :NEW.CREATED_BY := USER;
    :NEW.CHANGED_ON := SYSDATE;
    :NEW.CHANGED_BY := USER;

END;
/



CREATE OR REPLACE TRIGGER TRG_FEATURE_BEF_UPD 
BEFORE UPDATE ON FEATURE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
     tmpVar1 NUMBER;

BEGIN
     tmpVar1 := 0;
     
  SELECT SEQ_FEATURE.NEXTVAL INTO tmpVar1 FROM DUAL;
    :NEW.CHANGED_ON := SYSDATE;
    :NEW.CHANGED_BY := USER;
END;
/

ALTER TABLE FEATURE ADD (
  CONSTRAINT PK_FEATURE PRIMARY KEY (ID_FEATURE));


ALTER TABLE FEATURE ADD (
  CONSTRAINT FK_FEATURE_FBASE FOREIGN KEY (ID_FEATURE_BASE) 
    REFERENCES FEATURE_BASE (ID_FEATURE_BASE)); 


VIEW_FEATURE_AVAIL:

PHP Code:

CREATE OR REPLACE FORCE VIEW VIEW_FEATURE_AVAIL
(DAEMON, ID_FEATURE, FEATURE_KEY, FEATURE_NAME, ID_FEATURE_BASE, 
 VERSION, EXPIRE_DATE, NUMLIC, NUM_AVAIL, CREATED_ON, 
 CREATED_BY, CHANGED_ON, CHANGED_BY, SIGN, VENDOR_STRING, 
 HOSTID, HOST_BASED, ISSUED, SUPERSEDE_ISSUED, ISSUER, 
 SUPERSEDE_ISSUER, DUPGROUP, SUPERSEDE_DUP_GR, SN, CK, 
 START_DATE, USER_BASED, TS_OK, BORROW, FLOAT_OK, 
 SUITE_DUPGROUP, LINGER, NOTICE, OVERDRAFT, PLATFORMS)
AS 
SELECT 
       FB.ID_DAEMON, F.ID_FEATURE, F.FEATURE_KEY
       , FB.FEATURE_NAME , FB.ID_FEATURE_BASE  
     , F.VERSION, F.EXPIRE_DATE, F.NUMLIC, FA.NUM_AVAIL
     , F.CREATED_ON, F.CREATED_BY, F.CHANGED_ON, F.CHANGED_BY
     , F.SIGN, F.VENDOR_STRING, F.HOSTID, F.HOST_BASED
     , F.ISSUED, F.SUPERSEDE_ISSUED, F.ISSUER, F.SUPERSEDE_ISSUER, F.DUPGROUP, F.SUPERSEDE_DUP_GR
     , F.SN, F.CK, F.START_DATE, F.USER_BASED, F.TS_OK, F.BORROW, F.FLOAT_OK
     , F.SUITE_DUPGROUP, F.LINGER, F.NOTICE, F.OVERDRAFT, F.PLATFORMS
FROM FEATURE F, FEATURE_BASE FB, FEATURE_AVAILABLE FA
WHERE F.ID_FEATURE_BASE = FB.ID_FEATURE_BASE
  AND FA.ID_FEATURE = F.ID_FEATURE 
  AND FA.ID_FEATURE = F.ID_FEATURE; 


Table FEATURE_AVAILBLE:

PHP Code:

CREATE TABLE FEATURE_AVAILABLE
(
  ID_FEATURE       INTEGER,
  FEATURE_KEY      VARCHAR2(25),
  ID_FEATURE_BASE  INTEGER,
  NUM_AVAIL        INTEGER,
  EXPIRE_DATE      DATE,
  CREATED_ON       DATE,
  CREATED_BY       VARCHAR2(20),
  CHANGED_ON       DATE,
  CHANGED_BY       VARCHAR2(20),
  ID_ACT           INTEGER
);

CREATE UNIQUE INDEX PK_FEATURE_AVAILABLE ON FEATURE_AVAILABLE (ID_FEATURE);


CREATE OR REPLACE TRIGGER TRG_FEATURE_AVAILABLE_BEF_UPD 
BEFORE UPDATE ON FEATURE_AVAILABLE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
     tmpVar1 NUMBER;

BEGIN
     tmpVar1 := 0;
     
  SELECT SEQ_FEATURE_AVAILABLE.NEXTVAL INTO tmpVar1 FROM DUAL;
    :NEW.CHANGED_ON := SYSDATE;
    :NEW.CHANGED_BY := USER;
END;
/




.....
ALTER TABLE FEATURE_AVAILABLE ADD (
  CONSTRAINT PK_FEATURE_AVAILABLE PRIMARY KEY (ID_FEATURE)   );


ALTER TABLE FEATURE_AVAILABLE ADD (
  CONSTRAINT FK_FAVAIL_FEATURE FOREIGN KEY (ID_FEATURE) 
    REFERENCES FEATURE (ID_FEATURE)
    ON DELETE CASCADE); 


[andrewst]

You need to fully understand the concept of Key Preserved tables. Essentially this is a table whose rows map 1:1 with rows in the view. Take this very simple view:

create view v as
select emp.ename, dept.dname
from emp, dept
where emp.deptno = dept.deptno;

View created.

SQL> select * from v;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

13 rows selected.

In this case, tabel EMP is key-preserved but table DEPT is not - because any EMP row appears just once (or not at all) in the view, but each DEPT row appears many times. So in this case "DELETE FROM v WHERE ename = 'JAMES'" would be transalated to "DELETE FROM emp WHERE ename = 'JAMES'".

Oracle only allows one key-preserved table in an updatable view. If the view contains 2 or more key-preserved tables, then it is not updatable.

For Oracle to determine whether the veiw is key-preserved, the tables must have primary and foreign keys defined - as yours do.

Having said all that, could you not get around this problem by simply deleting from the appropriate base table in your procedure, instead of deleting from the view?

If not, you can always resort to putting INSTEAD OF triggers on the views.