--eseguire come utente DBCGO

spool updateDBM.log;

--TABLE
-------------------------------------------------------------------------
CREATE TABLE DBCGO_ANAG_CLU_B
(
  COD_CLU   VARCHAR2(2 BYTE),
  DESC_CLU  VARCHAR2(40 BYTE),
  COD_MAR   VARCHAR2(2 BYTE)
)
TABLESPACE DBCGO_DATA_SMALL;

CREATE UNIQUE INDEX PK_DBCGO_ANAG_CLU_B ON DBCGO_ANAG_CLU_B
(COD_CLU)
NOLOGGING
TABLESPACE DBCGO_IDX_SMALL;

ALTER TABLE DBCGO_ANAG_CLU_B ADD (
  CONSTRAINT PK_DBCGO_ANAG_CLU_B PRIMARY KEY (COD_CLU)
    USING INDEX 
    TABLESPACE DBCGO_IDX_SMALL); 
    
-------------------------------------------------------------------------
CREATE TABLE DBCGO_ANAG_MAR_B
(
  COD_MAR   VARCHAR2(2 BYTE),
  DESC_MAR  VARCHAR2(40 BYTE)
)
TABLESPACE DBCGO_DATA_SMALL;

CREATE UNIQUE INDEX PK_DBCGO_ANAG_MAR_B ON DBCGO_ANAG_MAR_B
(COD_MAR)
NOLOGGING
TABLESPACE DBCGO_IDX_SMALL;

ALTER TABLE DBCGO_ANAG_MAR_B ADD (
  CONSTRAINT PK_DBCGO_ANAG_MAR_B PRIMARY KEY (COD_MAR)
    USING INDEX 
    TABLESPACE DBCGO_IDX_SMALL); 

--SYNONYM
-------------------------------------------------------------------------
DROP SYNONYM DBM_ANAG_CLU;
DROP SYNONYM DBM_ANAG_MAR;
CREATE SYNONYM DBM_ANAG_CLU_SYN FOR DBM_ANAG_CLU0;
CREATE SYNONYM DBM_ANAG_MAR_SYN FOR DBM_ANAG_MAR0;


--VIEW
-------------------------------------------------------------------------
CREATE OR REPLACE VIEW DBM_ANAG_CLU (COD_CLU, DESC_CLU, COD_MAR)
AS (
select COD_CLU, DESC_CLU, COD_MAR
from DBM_ANAG_CLU_SYN
union
select COD_CLU, DESC_CLU, COD_MAR
from DBCGO_ANAG_CLU_B);

CREATE OR REPLACE VIEW DBM_ANAG_MAR (COD_MAR, DESC_MAR)
AS (
select COD_MAR, DESC_MAR
from DBM_ANAG_MAR_SYN
union
select COD_MAR, DESC_MAR
from DBCGO_ANAG_MAR_B);

--DATA
-------------------------------------------------------------------------
insert into DBCGO_ANAG_MAR_B(COD_MAR, DESC_MAR)
values('T', 'TOP');
insert into DBCGO_ANAG_MAR_B(COD_MAR, DESC_MAR)
values('X', 'Medium Top');
insert into DBCGO_ANAG_MAR_B(COD_MAR, DESC_MAR)
values('M', 'Medium');
commit;

insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('a', 'Privilege PMI','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('b', 'Platinum PMI','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('c', 'Platinum Soho','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('d', 'Personal Soho','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('e', 'Standard PMI','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('f', 'Standard Soho','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('g', 'Privilege New','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('H', 'High','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('i', 'Platinum New','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('j', 'Personal New','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('k', 'Standard New','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('L', 'Low','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('M', 'Medium','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('n', 'Consip','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('o', 'Progetto A','M'); 
insert into DBCGO_ANAG_CLU_B(COD_CLU, DESC_CLU, COD_MAR)
values('p', 'Progetto B','M');
commit;

@@load_dbm_anag_clu.prc
show err
@@load_dbm_anag_mar.prc
show err

spool off;