declare
begin
  begin
  execute immediate 
'CREATE TABLE SDO_GEOR_SYSDATA_TABLE
(
   SDO_OWNER                 VARCHAR2(32),
   GEORASTER_TABLE_NAME      VARCHAR2(32) NOT NULL,
   GEORASTER_COLUMN_NAME     VARCHAR2(1024) NOT NULL,
   GEOR_METADATA_COLUMN_NAME VARCHAR2(1024),
   RDT_TABLE_NAME            VARCHAR2(32),
   RASTER_ID                 NUMBER,
   OTHER_TABLE_NAMES         SDO_STRING_ARRAY,
   CONSTRAINT unique_georasters
       PRIMARY KEY (SDO_OWNER, RDT_TABLE_NAME, RASTER_ID)) ';
  exception when others then NULL;
  end;
end;
/
declare
begin
  begin
   execute immediate 
' CREATE INDEX SDO_GEOR_IDX ON SDO_GEOR_SYSDATA_TABLE
(SDO_OWNER, GEORASTER_TABLE_NAME) ';
  exception when others then NULL;
  end;
end;
/
declare
begin
 begin
    execute immediate 'DROP TRIGGER SDO_GEOR_DROP_USER';
    exception when others then NULL;
 end;
end;
/
declare
begin
 begin
    execute immediate 'DROP TRIGGER SDO_GEOR_TRUNC_TABLE';
    exception when others then NULL;
 end;
end;
/
CREATE OR REPLACE  VIEW ALL_SDO_GEOR_SYSDATA AS
SELECT SDO_OWNER                 OWNER,
       GEORASTER_TABLE_NAME      TABLE_NAME,
       GEORASTER_COLUMN_NAME     COLUMN_NAME,
       GEOR_METADATA_COLUMN_NAME METADATA_COLUMN_NAME,
       RDT_TABLE_NAME            RDT_TABLE_NAME,
       RASTER_ID                 RASTER_ID,
       OTHER_TABLE_NAMES         OTHER_TABLE_NAMES
FROM SDO_GEOR_SYSDATA_TABLE
WHERE
(
    (sdo_owner = sys_context('userenv', 'SESSION_USER'))
    or
    exists
    (
     select table_name from all_tables
     where table_name=georaster_table_name
     and owner = sdo_owner
     union all
     select table_name from all_object_tables
     where table_name=georaster_table_name
     and owner = sdo_owner
     union all
     select view_name table_name from all_views
     where  view_name=georaster_table_name
     and owner = sdo_owner
     )
);
CREATE OR REPLACE  VIEW USER_SDO_GEOR_SYSDATA AS
SELECT TABLE_NAME, COLUMN_NAME,METADATA_COLUMN_NAME,
              RDT_TABLE_NAME,RASTER_ID,OTHER_TABLE_NAMES
FROM ALL_SDO_GEOR_SYSDATA
WHERE owner = sys_context('userenv', 'SESSION_USER');
CREATE OR REPLACE TRIGGER SDO_GEOR_TRIG_INS1
INSTEAD OF INSERT ON user_sdo_geor_sysdata
FOR EACH ROW
DECLARE
  owner   VARCHAR2(32);
  valid   VARCHAR2(32);
BEGIN
  owner:=user;
  valid:=SDO_GEOR_DEF.isValidEntry(upper(owner),upper(:new.table_name),upper(:new.column_name),upper(:new.rdt_table_name),:new.raster_id);

  if(valid='FALSE')
  then
     mderr.raise_md_error('MD', 'SDO', -13391, 'The inserted entry is not valid.');
  end if;    
  SDO_GEOR_INT.insertUserSysEntry(user, :new.table_name, :new.column_name,
      :new.metadata_column_name, :new.rdt_table_name, :new.raster_id, 
      :new.other_table_names);
END;
/
show errors;
CREATE OR REPLACE  TRIGGER SDO_GEOR_TRIG_DEL1
INSTEAD OF DELETE ON user_sdo_geor_sysdata
FOR EACH ROW
DECLARE
  owner   VARCHAR2(32);
  valid   VARCHAR2(32);
BEGIN
  owner:=user;
  valid:=SDO_GEOR_DEF.isValidEntry(upper(owner),upper(:old.table_name),upper(:old.column_name),upper(:old.rdt_table_name),:old.raster_id);
  if(valid='TRUE')
  then
     mderr.raise_md_error('MD', 'SDO', -13391, 'A valid entry cannot be deleted directly.');
  end if;    
  SDO_GEOR_DEF.deleteMetaEntry(user, :old.rdt_table_name, :old.raster_id);
END;
/
show errors;
CREATE  OR REPLACE TRIGGER SDO_GEOR_TRIG_UPD1
INSTEAD OF UPDATE ON user_sdo_geor_sysdata
FOR EACH ROW
DECLARE
 stmt  varchar2(2048);
 cnt   NUMBER;
 owner VARCHAR2(32);
 rdt   VARCHAR2(32);
 valid   VARCHAR2(32);
BEGIN
  owner:=user;
  valid:=SDO_GEOR_DEF.isValidEntry(upper(owner),upper(:old.table_name),upper(:old.column_name),upper(:old.rdt_table_name),:old.raster_id);
  if(valid='TRUE')
  then
     mderr.raise_md_error('MD', 'SDO', -13391, 'A valid entry cannot be updated directly.');
  end if;    
  valid:=SDO_GEOR_DEF.isValidEntry(upper(owner),upper(:new.table_name),upper(:new.column_name),upper(:new.rdt_table_name),:new.raster_id);
  if(valid='FALSE')
  then
     mderr.raise_md_error('MD', 'SDO', -13391, 'The updated entry is not valid.');
  end if;    
  
  
  SDO_GEOR_DEF.deleteMetaEntry(user, :old.rdt_table_name, :old.raster_id);

  
  SDO_GEOR_INT.insertUserSysEntry(user, :new.table_name, :new.column_name,
      :new.metadata_column_name, :new.rdt_table_name, :new.raster_id,
      :new.other_table_names);
END;
/
show errors;
declare
begin
  
  begin
    execute immediate
     ' drop trigger sdo_geor_bddl_trigger ';
    exception when others then NULL;
  end;
  begin
    execute immediate
     ' drop trigger sdo_geor_err_trigger ';
    exception when others then NULL;
  end;
  begin
    execute immediate
    ' DROP TABLE sdo_geor_ddl__table$$ cascade constraint PURGE ';
    exception when others then NULL;
  end;
end;
/
CREATE GLOBAL TEMPORARY TABLE sdo_geor_ddl__table$$ (id number) ON COMMIT PRESERVE ROWS;
declare
  m_stmt  varchar2(512);
begin
   m_stmt:='drop trigger sdo_geor_err_trigger';
   EXECUTE IMMEDIATE m_stmt; 
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
end;
/
create or replace trigger sdo_geor_bddl_trigger
before ddl on database
declare
 TYPE attrs_cur IS REF CURSOR;
 m_cur       attrs_cur;
 m_event varchar2(512);
 m_user  varchar2(512);
 m_owner varchar2(512);
 m_user1 varchar2(512);
 m_type  varchar2(512);
 m_stmt  varchar2(512);
 m_name  varchar2(5120);
 m_column varchar2(5120);
 m_cnt   NUMBER;
 m_stmt1 varchar2(512);  
 m_var   varchar2(512);
 m_o_stmt VARCHAR2(5120);
PRAGMA AUTONOMOUS_TRANSACTION;
begin

 m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
 execute immediate m_stmt into m_type;
 if(not (m_type='TABLE' or m_type='TRIGGER' or m_type='USER' or m_type='TABLESPACE'))
 then
   return;
 end if;

 m_stmt:='select sys.dbms_standard.sysevent from dual';
 execute immediate m_stmt into m_event;

 m_stmt:='select SYS_CONTEXT(''USERENV'',''SESSION_USER'') from dual';
 execute immediate m_stmt into m_user;

 m_stmt:='select SYS_CONTEXT(''USERENV'',''CURRENT_USER'') from dual';
 execute immediate m_stmt into m_user1;

 m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
 execute immediate m_stmt into m_owner;

 m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
 execute immediate m_stmt into m_name;

 m_stmt:='select sdo_geor_def.getSqlText from dual';
 execute immediate m_stmt into m_o_stmt;

 
 




  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')
  then
    m_stmt:='select column_name from dba_tab_columns where owner=:1 and table_name=:2';
    open m_cur for m_stmt using m_owner,m_name;
    loop
      fetch m_cur into m_column;
      exit when m_cur%NOTFOUND;
      m_stmt:='select sdo_geor_def.isDropColumn(:1) from dual';
      execute immediate m_stmt into m_stmt1 using SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
      if (trim(m_stmt1)='TRUE')
      then
        m_stmt:='begin sdo_geor_def.doAlterDropColumn(:1,:2,:3); end;';
        execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
      end if;
    end loop;
  end if;

 





 if ((m_event='DROP' and m_type='USER') or (m_event='DROP' and m_type='TABLESPACE'))
 then
   m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
   EXECUTE IMMEDIATE m_stmt;  
   commit; 
 end if;

 if (m_event='DROP' and m_type='TABLE')
 then
   m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2';
   EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;
   if(m_cnt!=0)
   then
     m_stmt:='select count(*) from sdo_geor_ddl__table$$';
     EXECUTE IMMEDIATE m_stmt into m_cnt;  
     if((m_cnt=0)and (m_user!='SYS' and m_user!='SYSTEM' and m_user!='MDSYS'and m_owner!='MDSYS' and m_owner!='SYS'))
     then 
        m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''The referenced raster data table(RDT) cannot be dropped.'')';
        execute immediate m_stmt;
     end if;
   end if;

   m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
   EXECUTE IMMEDIATE m_stmt;  
   commit; 
 end if;

 if(m_user='SYS' or m_user='SYSTEM' or m_user='MDSYS' 
   or m_owner='MDSYS' or m_owner='SYS')
 then 
   return;
 end if;   


 if (m_event='RENAME' and m_type='TABLE')
 then

   m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2';
   EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;
   if(m_cnt!=0)
   then
     m_stmt:='select count(*) from sdo_geor_ddl__table$$';
     EXECUTE IMMEDIATE m_stmt into m_cnt;  
     if(m_cnt=0)
     then 
        m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''The referenced raster data table(RDT) cannot be renamed directly.'')';
        execute immediate m_stmt;
     end if;
   end if;

 end if;

 if (m_type='TRIGGER' and m_event='DROP')  
 then
   m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';
   EXECUTE IMMEDIATE m_stmt into m_var using m_name;
   if(m_var is null)
   then
     return;
   end if;
   m_stmt:='select count(*) from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt into m_cnt;  
   if(m_cnt=0)
   then 
     m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster DML triggers cannot be dropped.'')';
     execute immediate m_stmt;
   end if;
 end if;

 if (m_type='TRIGGER' and m_event='ALTER')  
 then
   m_o_stmt:=upper(trim(m_o_stmt));
   if(instr(m_o_stmt,' COMPILE ')>0 or instr(m_o_stmt,' ENABLE ')>0
     or substr(m_o_stmt,length(m_o_stmt)-8,8)=' COMPILE' or substr(m_o_stmt,length(m_o_stmt)-7,7)=' ENABLE' )
   then
     return;
   end if;
   m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';
   EXECUTE IMMEDIATE m_stmt into m_var using m_name;
   if(m_var is null)
   then
     return;
   end if;
   m_stmt:='select count(*) from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt into m_cnt;  
   if(m_cnt=0)
   then 
     m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster DML triggers cannot be altered.'')';
     execute immediate m_stmt;
   end if;
 end if;

 if (m_type='TRIGGER' and m_event='CREATE')  
 then
   m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';
   EXECUTE IMMEDIATE m_stmt into m_var using m_name;
   if(m_var is null)
   then
     return;
   end if;
   m_stmt:='select count(*) from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt into m_cnt;  
   if(m_cnt=0)
   then 
     m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster reserved names cannot be used to create regular triggers.'')';
     execute immediate m_stmt;
   end if;
 end if;

 Exception
   when others then
     if(sqlcode=-13391)
     then
       m_stmt:=sqlerrm;
       m_stmt:=substr(m_stmt,11);
       m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391,'''||m_stmt||''')';
       execute immediate m_stmt;
     end if;
end;
/
create or replace trigger sdo_geor_addl_trigger
after ddl on database
declare
 TYPE attrs_cur IS REF CURSOR;
 m_cur       attrs_cur;
 m_event   varchar2(512);
 m_user    varchar2(512);
 m_owner   varchar2(512);
 m_user1   varchar2(512);
 m_type    varchar2(512);
 m_name    varchar2(5120);
 m_column  varchar2(5120);
 m_cnt     NUMBER;
 m_stmt    varchar2(512);
 m_ret     varchar2(3000);
 m_ret1     varchar2(512);
 m_o_stmt VARCHAR2(5120);
begin

 m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
 execute immediate m_stmt into m_type;
 if(not (m_type='TABLE' or m_type='TRIGGER' or m_type='USER' or m_type='TABLESPACE'))
 then
   return;
 end if;

 m_stmt:='select sys.dbms_standard.sysevent from dual';
 execute immediate m_stmt into m_event;

 m_stmt:='select SYS_CONTEXT(''USERENV'',''SESSION_USER'') from dual';
 execute immediate m_stmt into m_user;

 m_stmt:='select sys.dbms_standard.login_user  from dual';
 execute immediate m_stmt into m_user1;

 m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
 execute immediate m_stmt into m_owner;

 m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
 execute immediate m_stmt into m_name;

 if((instr(upper(m_name),'MDRT_')>0) and m_event='DROP')
 then
   return;
 end if;
 
 

 




 if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='CREATE')  
 then
   m_stmt:='select sdo_geor_def.listAllGeoRasterFieldsStr(:1,:2) from dual';
   execute immediate m_stmt  into m_ret  using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name);
   m_ret:=trim(m_ret);
   while (length(m_ret)!=0) loop
     if (instr(m_ret,' $$__## ')!=0) 
     then
       m_ret1:=trim(substr(m_ret,1,instr(m_ret,' $$__## ')-1));
       m_ret:=trim(substr(m_ret,instr(m_ret,' $$__## ')+8));
     else
       m_ret1:=trim(m_ret);
       m_ret:='';
     end if;
     m_stmt:='begin SDO_GEOR_UTL.createDMLTrigger(:1,:2); end;';
    execute immediate m_stmt using m_owner||'.'||m_name,SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME(m_ret1);    
   end loop;
   return;
 end if;

 
 
 
 
 
 










 




  if (m_name!='MDSYS' and m_type='USER' and m_event='DROP')
  then
     m_stmt:='call sdo_geor_def.doDropUserAndTable()';
     execute immediate m_stmt;   
  end if;

  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='DROP')
  then
     m_stmt:='call sdo_geor_def.doDropUserAndTable()';
     execute immediate m_stmt;   
  end if;

  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='TRUNCATE')
  then
     m_stmt:='call sdo_geor_def.doTruncateTable()';
     execute immediate m_stmt;   
  end if;
 
  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')
  then
     m_stmt:='call sdo_geor_def.doAlterRenameTable()';
     execute immediate m_stmt;   
  end if;

  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='RENAME')
  then
     m_stmt:='call sdo_geor_def.doRenameTable()';
     execute immediate m_stmt;  
  end if;

 




 if (m_event='DROP' and m_type='TABLE')  
 then
   m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
   EXECUTE IMMEDIATE m_stmt;    
 end if;

 if ((m_type='USER' and m_event='DROP') or (m_type='TABLESPACE' and m_event='DROP'))
 then
   m_stmt:='delete from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt;     
 end if;

 Exception
   when others then
     if(sqlcode=-13391)
     then
       m_stmt:=sqlerrm;
       m_stmt:=substr(m_stmt,11);
       m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391,'''||m_stmt||''')';
       execute immediate m_stmt;
     end if;
end;
/
show errors;
grant select,insert, delete, update on user_sdo_geor_sysdata to public;
grant select on all_sdo_geor_sysdata to public;
create or replace  public synonym user_sdo_geor_sysdata for
mdsys.user_sdo_geor_sysdata;
create or replace  public synonym all_sdo_geor_sysdata for
mdsys.all_sdo_geor_sysdata;
declare
begin
  begin
  execute immediate
'CREATE SEQUENCE SDO_GEOR_SEQ
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOCYCLE
    CACHE 10 ';
  exception when others then NULL;
  end;
end;
/
grant select on SDO_GEOR_SEQ to public;
declare
begin
  begin
    execute immediate
   ' DROP TABLE SDO_GR_MOSAIC_0 PURGE ';
    exception when others then NULL;
  end;
  begin
   execute immediate
   ' DROP TABLE SDO_GR_MOSAIC_1 PURGE ';
    exception when others then NULL;
  end;
  begin
   execute immediate
   ' DROP TABLE SDO_GR_MOSAIC_2 PURGE ';
    exception when others then NULL;
  end;
  begin
   execute immediate
   ' DROP TABLE SDO_GR_MOSAIC_3 PURGE ';
    exception when others then NULL;
  end;
end;
/
CREATE GLOBAL TEMPORARY TABLE SDO_GR_MOSAIC_0
  (
   rid     rowid,
   rdt     VARCHAR2(100),
   rstid   number,
   rstype  number,
   srid    number,
   rctif   VARCHAR2(10),
   r0      number,
   c0      number,
   b0      number,
   r1      number,
   c1      number,
   b1      number,
   bands   number,
   bcv     number,
   ilv     varchar2(5),
   rblksz  number,
   cblksz  number,
   bblksz  number,
   cdl     number,
   cdp     varchar2(50),
   ultr    number,
   ultc    number,
   ultb    number,
   rrows   number,
   cols    number,
   cptype  VARCHAR2(10),
   meta    XMLType
  ) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE SDO_GR_MOSAIC_1
  (
   rid     rowid,
   ultr    number,
   ultc    number,
   rsize   number,
   csize   number,
   r0      number,
   r1      number,
   c0      number,
   c1      number,
   b0      number,
   b1      number
  );
CREATE GLOBAL TEMPORARY TABLE SDO_GR_MOSAIC_2
  as select * from SDO_GR_MOSAIC_1
  where 0=1;
CREATE GLOBAL TEMPORARY TABLE SDO_GR_MOSAIC_3 (p number);
GRANT SELECT,INSERT,UPDATE,DELETE on SDO_GR_MOSAIC_0 TO PUBLIC;
GRANT SELECT,INSERT,UPDATE,DELETE on SDO_GR_MOSAIC_1 TO PUBLIC;
GRANT SELECT,INSERT,UPDATE,DELETE on SDO_GR_MOSAIC_2 TO PUBLIC;
GRANT SELECT,INSERT,UPDATE,DELETE on SDO_GR_MOSAIC_3 TO PUBLIC;
declare
begin
  begin
    execute immediate
   ' DROP TABLE SDO_GR_RDT_1 PURGE ';
    exception when others then NULL;
  end;

  begin
   execute immediate
   ' DROP TABLE SDO_GR_RDT_2 PURGE ';
    exception when others then NULL;
  end;
end;
/
declare
begin
   begin
    execute immediate 
' CREATE GLOBAL TEMPORARY TABLE SDO_GR_RDT_1
  (
    rasterDataTable   varchar2(100),
    rasterId          number,
    pyramidLevel      number,
    bandBlockNumber   number,
    rowBlockNumber    number,
    columnBlockNumber number,
    blockMBR          SDO_GEOMETRY,
    rasterBlock       BLOB,
    constraint sdo_gr_rdt_1_pk primary key
      (rasterDataTable, rasterId, pyramidLevel, bandBlockNumber,
       rowBlockNumber, columnBlockNumber)
  ) ';
     exception when others then NULL;
  end;
end;
/
declare
begin
   begin
    execute immediate
' CREATE GLOBAL TEMPORARY TABLE SDO_GR_RDT_2 OF SDO_RASTER
 (
   constraint sdo_gr_rdt_2_pk primary key
     (rasterId, pyramidLevel, bandBlockNumber,
      rowBlockNumber, columnBlockNumber)
 ) ';
   exception when others then NULL;
  end;
end;
/
declare
begin
   begin
    execute immediate 
      ' GRANT SELECT,INSERT,UPDATE,DELETE on SDO_GR_RDT_1 TO PUBLIC ';
    execute immediate 
      ' GRANT SELECT,INSERT,UPDATE,DELETE on SDO_GR_RDT_2 TO PUBLIC ';
   exception when others then NULL;
  end;
end;
/
declare
begin
  begin
   execute immediate 
'CREATE TABLE SDO_GEOR_PLUGIN_REGISTRY
(  plugin_name    VARCHAR2(32) PRIMARY KEY,
   plugin_type    VARCHAR2(32),
   plugin         VARCHAR2(32),
   company_name   VARCHAR2(1024),
   description    VARCHAR2(1024)) ';
  exception when others then NULL;
 end;
end;
/
GRANT SELECT ON SDO_GEOR_PLUGIN_REGISTRY TO PUBLIC;