Rem
Rem $Header: sdotnpc.sql 14-feb-2008.11:17:48 sravada Exp $
Rem
Rem sdotin.sql
Rem
Rem Copyright (c) 2006, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdotin.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sravada     02/14/08 - upgrade diffs
Rem    rkothuri    05/23/06 - add tin read and clip functions 
Rem    rkothuri    05/03/06 - add synonym 
Rem    rkothuri    01/31/06 - TIN functionality 
Rem    rkothuri    01/31/06 - TIN functionality 
Rem    rkothuri    01/31/06 - Created
Rem

declare
begin
   begin
   execute immediate
  ' CREATE  TYPE SDO_ORGSCL_TYPE
    TIMESTAMP ''2006-02-16:11:08:14''
    OID ''0CEDBB8FA104A787E040578CB3050404''
    AS OBJECT (
       extent           SDO_MBR,	
       scale 		SDO_VPOINT_TYPE, 
       ord_cmp_type	SDO_VPOINT_TYPE)'; 
   exception when others then NULL;
 end;
end;
/

show errors;
grant execute on sdo_orgscl_type to public with grant option;
create or replace  public synonym sdo_orgscl_type for mdsys.sdo_orgscl_type;


-- block table same for pc
declare
begin
  begin
   execute immediate
   ' CREATE  TYPE SDO_PC_BLK 
    TIMESTAMP ''2006-02-16:10:48:17''
    OID ''0CEDBB8FA101A787E040578CB3050404''
    AS OBJECT (
       obj_id           NUMBER ,
       blk_id           NUMBER,
       blk_extent       sdo_geometry,
       blk_domain       sdo_orgscl_type,
       pcblk_min_res    NUMBER,
       pcblk_max_res    NUMBER,
       num_points       NUMBER,
       num_unsorted_points NUMBER,
       pt_sort_dim      NUMBER,
       points           BLOB)';
   exception when others then NULL;
 end;
end;
/
show errors;

grant execute on mdsys.sdo_pc_blk to public with grant option;


declare
begin
  begin
    execute immediate 
  'CREATE TABLE SDO_PC_BLK_TABLE of mdsys.SDO_PC_BLK';
   exception when others then NULL;
  end;
end;
/

grant select on mdsys.sdo_pc_blk_table to public;

create or replace public synonym sdo_pc_blk for mdsys.sdo_pc_blk;

declare
begin
  begin 
   execute immediate
   'CREATE TYPE SDO_PC_BLK_TYPE
    TIMESTAMP ''2006-08-06:16:01:15''
    OID ''1A6256B2DC759CAFE040578CB3053028''
    AS TABLE of MDSYS.SDO_PC_BLK'; 
   exception when others then NULL;
 end;
end;
/
show errors;

grant execute on mdsys.sdo_pc_blk_type to public with grant option;



declare
begin
   begin
   execute immediate
   ' CREATE  TYPE SDO_PC  
    TIMESTAMP ''2006-02-16:10:51:03''
    OID ''0CEDBB8FA102A787E040578CB3050404''
    AS OBJECT (
       base_table           VARCHAR2(70),
       base_table_col       VARCHAR2(1024),
       pc_id       	    NUMBER,
       blk_table            VARCHAR2(70),  
       ptn_params           VARCHAR2(1024),
       pc_extent            sdo_geometry,
       pc_tol		    NUMBER,	
       pc_tot_dimensions    NUMBER,
       pc_domain       	    sdo_orgscl_type, 
       pc_val_attr_tables   SDO_STRING_ARRAY,
       pc_other_attrs       SYS.XMLTYPE)';
   exception when others then NULL;
 end;
end;
/
show errors;
grant execute on sdo_pc to public with grant option;
create or replace public synonym sdo_pc for mdsys.sdo_pc;


-- block table for tins 
declare
begin
  begin
   execute immediate
   ' CREATE  TYPE SDO_TIN_BLK
    TIMESTAMP ''2006-05-31:09:18:22''
    OID ''1517E67D6019D5FAE040578CB30565D0''
    AS OBJECT (
       obj_id           NUMBER ,
       blk_id           NUMBER,
       blk_extent       sdo_geometry,
       blk_domain       sdo_orgscl_type,
       pcblk_min_res    NUMBER,
       pcblk_max_res    NUMBER,
       num_points       NUMBER,
       num_unsorted_points NUMBER,
       pt_sort_dim      NUMBER,
       points           BLOB,
       tr_lvl	        NUMBER,
       tr_res           NUMBER,
       num_triangles    NUMBER,
       tr_sort_dim      NUMBER,
       triangles        BLOB)';
   exception when others then NULL;
 end;
end;
/
show errors;
grant execute on mdsys.sdo_tin_blk to public with grant option;
                                                                      
declare
begin
  begin
    execute immediate 
      'CREATE TABLE SDO_TIN_BLK_TABLE of mdsys.SDO_TIN_BLK';
    exception when others then NULL;
  end;
end;
/

grant select on mdsys.sdo_tin_blk_table to public;
                                                                  
create or replace  public synonym sdo_tin_blk for mdsys.sdo_tin_blk;


declare
begin
  begin
   execute immediate
   'CREATE TYPE SDO_TIN_BLK_TYPE
    TIMESTAMP ''2006-08-06:16:05:19''
    OID ''1A6256B2DC769CAFE040578CB3053028''
    AS TABLE of MDSYS.SDO_TIN_BLK';
   exception when others then NULL;
 end;
end;
/
show errors;
                                                                                  
grant execute on mdsys.sdo_tin_blk_type to public with grant option;
                                                                                  


declare
begin
   begin
   execute immediate
   ' CREATE  TYPE SDO_TIN
    TIMESTAMP ''2006-02-16:10:52:24''
    OID ''0CEDBB8FA103A787E040578CB3050404''
    AS OBJECT (
       base_table            VARCHAR2(70),
       base_table_col        VARCHAR2(1024),
       tin_id                NUMBER,
       blk_table             VARCHAR2(70),
       ptn_params            VARCHAR2(1024),
       tin_extent            sdo_geometry,
       tin_tol               NUMBER,
       tin_tot_dimensions    NUMBER,
       tin_domain            sdo_orgscl_type,
       tin_break_lines	     SDO_GEOMETRY,
       tin_stop_lines	     SDO_GEOMETRY,
       tin_void_rgns 	     SDO_GEOMETRY,
       tin_val_attr_tables   SDO_STRING_ARRAY,
       tin_other_attrs       SYS.XMLTYPE)';
   exception when others then NULL;
 end;
end;
/
show errors;
grant execute on sdo_tin to public with grant option;
create or replace  public synonym sdo_tin for mdsys.sdo_tin;

declare
begin
  begin
  execute immediate
'CREATE TABLE SDO_TIN_PC_SEQ
(
   SDO_OWNER                 VARCHAR2(32) NOT NULL,
   TABLE_NAME                VARCHAR2(32) NOT NULL,
   CUR_OBJ_ID                NUMBER,
   CONSTRAINT sdo_pk_seq_tin_pc
       PRIMARY KEY (SDO_OWNER, TABLE_NAME))';
  exception when others then NULL;
  end;
end;
/
SHOW ERRORS;
GRANT SELECT ON sdo_tin_pc_seq TO PUBLIC;
                                                                              


-- Metadata for the tin/pc objects 
declare
begin
  begin
  execute immediate
'CREATE TABLE SDO_TIN_PC_SYSDATA_TABLE
 (
   SDO_OWNER                 VARCHAR2(32) NOT NULL,
   TABLE_NAME                VARCHAR2(32) NOT NULL,
   COLUMN_NAME               VARCHAR2(1024) NOT NULL,
   DEP_TABLE_SCHEMA          VARCHAR2(32) NOT NULL,
   DEP_TABLE_NAME            VARCHAR2(32) NOT NULL,
   CONSTRAINT sdo_pk_tin_pc
       PRIMARY KEY (DEP_TABLE_SCHEMA, DEP_TABLE_NAME),
   CONSTRAINT sdo_unq_pcattr_tin_pc
       UNIQUE (SDO_OWNER, TABLE_NAME, COLUMN_NAME, 
	       DEP_TABLE_SCHEMA, DEP_TABLE_NAME))';
  exception when others then NULL;
  end;
end;
/
SHOW ERRORS;
GRANT SELECT ON sdo_tin_pc_sysdata_table TO PUBLIC;

-- ----------------------------------------------------------------
--      Create an index on the metadata table
-- ----------------------------------------------------------------

declare
begin
  begin
   execute immediate
' CREATE INDEX SDO_TIN_PC_IDX ON SDO_TIN_PC_SYSDATA_TABLE
(SDO_OWNER, TABLE_NAME, COLUMN_NAME) ';
  exception when others then NULL;
  end;
end;
/

-- ----------------------------------------------------------------
--          Create ALL view of SDO_TIN_PC_SYSDATA_TABLE
-- ----------------------------------------------------------------
CREATE OR REPLACE  VIEW ALL_SDO_TIN_PC_SYSDATA AS
SELECT SDO_OWNER                 OWNER,
       TABLE_NAME,
       COLUMN_NAME, 
       DEP_TABLE_SCHEMA,
       DEP_TABLE_NAME
FROM SDO_TIN_PC_SYSDATA_TABLE a
WHERE
(
    exists
    (
     select table_name from all_tables
     where table_name=a.table_name
     and owner = sdo_owner
     union all
     select table_name from all_object_tables
     where table_name=a.table_name
     and owner = sdo_owner
     union all
     select view_name table_name from all_views
     where  view_name=a.table_name
     and owner = sdo_owner
     )
);

GRANT SELECT ON all_sdo_tin_pc_sysdata TO PUBLIC;
CREATE OR REPLACE PUBLIC SYNONYM all_sdo_tin_pc_sysdata FOR
  mdsys.all_sdo_tin_pc_sysdata;
                                                                                 

-- ----------------------------------------------------------------
--      Create USER view of SDO_TIN_PC_SYSDATA_TABLE
-- ----------------------------------------------------------------
CREATE OR REPLACE  VIEW user_sdo_tin_pc_sysdata AS
SELECT *  FROM all_sdo_tin_pc_sysdata
WHERE owner = sys_context('userenv', 'CURRENT_SCHEMA');
                                                                                 
GRANT SELECT ON user_sdo_tin_pc_sysdata TO PUBLIC;
CREATE OR REPLACE PUBLIC SYNONYM user_sdo_tin_pc_sysdata FOR
  mdsys.user_sdo_tin_pc_sysdata;