Rem
Rem $Header: sdo/admin/sdotptyp.sql /main/27 2009/03/20 09:02:03 sravada Exp $
Rem
Rem sdotptyp.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdotptyp.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     03/19/09 - increase sdo_topo_object_array to 10000000
Rem    sravada     06/05/07 - increate the sdo_topo_object_array limit
Rem    bgouslin    01/07/07 - New version to fix Windoze format issue caused by
Rem                           some ADE bug
Rem    sravada     01/11/06 - support user specified TG_IDs 
Rem    sravada     07/29/04 - add exception handlers 
Rem    sravada     05/27/04 - fix alter type 
Rem    sravada     04/26/04 - add get_tg_objects 
Rem    sravada     12/19/03 - add get_topo_elements 
Rem    ningan      11/21/03 - add SDO_TOPO_NSTD_TBL
Rem    sravada     08/12/03 - add hierarchy level 
Rem    fjlee       07/28/03 - Add non-persistent type SDO_EDGE_ARRAY
Rem    sravada     07/22/03 - define default constructor
Rem    sravada     06/10/03 - increase the sdo_list_tye size
Rem    sravada     06/05/03 - add constructors with add/delete topo_ids 
Rem    sravada     04/15/03 - add tg_layer name sig for topo_goem constructor
Rem    rkothuri    01/04/03 - grant execute with grant option
Rem    sravada     11/18/02 - update type
Rem    sravada     10/31/02 - change feature to topo_geometry
Rem    sravada     10/03/02 - move body to prvtptyp
Rem    sravada     08/06/02 - add OID for topo_object_array
Rem    sravada     08/01/02 - add OID for list type
Rem    sravada     07/29/02 - add layer feature type
Rem    sravada     07/23/02 - add get_geometry
Rem    sravada     07/19/02 - sravada_topo_metadata_views
Rem    sravada     07/17/02 - Created
Rem


Rem select to_char(systimestamp, 'YYYY-MM-DD:HH24:MI:SS') from dual;
Rem select sys_op_guid() from dual;

declare
begin
  begin
  execute immediate
' Create or Replace Type SDO_Topo_Geometry_Layer 
TIMESTAMP ''2002-07-17:16:42:35'' 
OID ''A5FEBA741CE21F61E034080020F7892A''
as object (
  OWNER        varchar2(64),
  Table_Name   varchar2(64),
  Column_Name  varchar2(32),
  Layer_id     number,
  Layer_Type   VARCHAR2(10),
  Layer_Level  number,
  Child_Layer_id number) ';
 exception when others then NULL;
  end;
end;
/


grant execute on MDSYS.SDO_Topo_Geometry_Layer to public with grant option;
create or replace  public synonym SDO_Topo_Geometry_Layer for 
MDSYS.SDO_Topo_Geometry_Layer;


declare
begin
  begin
  execute immediate
'Create or Replace Type SDO_Topo_Geometry_Layer_Array
TIMESTAMP ''2002-07-17:16:45:41''
OID ''A5FEBA741CE71F61E034080020F7892A''
as VARRAY (1000) of SDO_Topo_Geometry_Layer ';
   exception when others then NULL;
  end;
end;
/



grant execute on MDSYS.SDO_Topo_Geometry_Layer_Array 
to public with grant option;
create or replace  public synonym SDO_Topo_Geometry_Layer_Array for 
                   MDSYS.SDO_Topo_Geometry_Layer_Array;

-- this type is  persistent, so  OID required
declare
begin
  begin
  execute immediate
'Create or replace type SDO_List_Type
TIMESTAMP ''2002-08-01:09:48:24''
OID ''A726BA71528E371AE034080020F7892A''
as VARRAY (2147483647) of Number ';
    exception when others then NULL;
  end;
end;
/


grant execute on MDSYS.SDO_List_Type to public with grant option;
create or replace  public synonym SDO_List_Type for MDSYS.SDO_List_Type;

-- this object is not persistent object, so no OID required
declare
begin
  begin
  execute immediate
' Create or replace type SDO_TOPO_OBJECT 
as Object (topo_id number, topo_type number) ';
    exception when others then NULL;
  end;
end;
/

grant execute on MDSYS.SDO_TOPO_OBJECT to public with grant option;
create or replace  public synonym SDO_TOPO_OBJECT for MDSYS.SDO_TOPO_OBJECT;

-- making it persistent
declare
begin
  begin
  execute immediate
' CREATE OR REPLACE TYPE SDO_TOPO_NSTD_TBL 
TIMESTAMP ''2003-12-25:09:11:08''
OID ''CF5072A2CF3A0E54E034080020B7ADF6''
AS TABLE OF NUMBER '; 
    exception when others then NULL;
  end;
end;
/


GRANT EXECUTE ON MDSYS.SDO_TOPO_NSTD_TBL TO PUBLIC WITH GRANT OPTION;
CREATE OR REPLACE PUBLIC SYNONYM SDO_TOPO_NSTD_TBL FOR MDSYS.SDO_TOPO_NSTD_TBL;

-- this object is not persistent object, so no OID required
declare
begin
  begin
  execute immediate
'create type SDO_TGL_OBJECT
as Object (tgl_id number, tg_id number) ';
    exception when others then NULL;
  end;
end;
/
grant execute on MDSYS.SDO_TGL_OBJECT to public with grant option;
create or replace public synonym SDO_TGL_OBJECT for MDSYS.SDO_TGL_OBJECT;


-- this object is not persistent object, so no OID required
declare
begin
  begin
  execute immediate
'Create or replace type SDO_EDGE_ARRAY 
as VARRAY(1000000) of SDO_NUMBER_ARRAY ';
    exception when others then NULL;
  end;
end;
/
grant execute on MDSYS.SDO_EDGE_ARRAY to public with grant option;
create  or replace public synonym SDO_EDGE_ARRAY for MDSYS.SDO_EDGE_ARRAY;

declare
begin
  begin
  execute immediate
' Create or replace type SDO_TOPO_OBJECT_ARRAY
OID ''A6EAE6CBDFFC1E41E034080020F7892A''
 as VARRAY (1000000) of SDO_TOPO_OBJECT ';
    exception when others then NULL;
  end;
  begin
   execute immediate
    ' Alter type SDO_TOPO_OBJECT_ARRAY modify limit 10000000';
    exception when others then NULL;
  end;
end;
/

grant execute on MDSYS.SDO_TOPO_OBJECT_ARRAY to public with grant option;
create  or replace public synonym SDO_TOPO_OBJECT_ARRAY for 
MDSYS.SDO_TOPO_OBJECT_ARRAY;

declare
begin
  begin
  execute immediate
' Create or replace type SDO_TGL_OBJECT_ARRAY
OID ''C4B7F989E25A1414E034080020F7892A''
 as VARRAY (1000000) of SDO_TGL_OBJECT ';
    exception when others then NULL;
  end;
end;
/

declare
begin
   begin
     execute immediate
      ' alter type SDO_TGL_OBJECT_ARRAY modify limit 10000000 cascade';
    exception when others then NULL;
  end;
end;
/


grant execute on MDSYS.SDO_TGL_OBJECT_ARRAY to public with grant option;
create or replace  public synonym SDO_TGL_OBJECT_ARRAY for 
MDSYS.SDO_TGL_OBJECT_ARRAY;

-- the default type constructor returns a valid object only
-- the fid is already there in the topology_relation table
-- user defined constructor has only topolgy name as the input
-- the feature id is generated as part of the constructor by reading
-- the topo metadata and getting the sequence name

declare
begin
  begin
  execute immediate
'Create or Replace Type SDO_Topo_Geometry 
TIMESTAMP ''2002-07-17:16:54:47''
OID ''A5FEBA741CEB1F61E034080020F7892A''
AUTHID current_user as Object 
  (TG_Type  NUMBER,
   TG_ID NUMBER,
   TG_Layer_ID NUMBER,
   Topology_ID   NUMBER,
   Constructor Function SDO_Topo_Geometry(TG_Type number,
                                    TG_id NUMBER,
                                    TG_layer_id NUMBER,
                                    Topology_ID NUMBER)
     return SELF as RESULT DETERMINISTIC,
   Constructor Function SDO_Topo_Geometry(topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    Topo_ids SDO_TGL_OBJECT_ARRAY) 
     return SELF as RESULT DETERMINISTIC,
   Constructor Function SDO_Topo_Geometry(topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    Topo_ids SDO_TGL_OBJECT_ARRAY) 
     return SELF as RESULT DETERMINISTIC,
   Constructor Function SDO_Topo_Geometry(topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    add_Topo_ids SDO_TGL_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TGL_OBJECT_ARRAY) 
     return SELF as RESULT DETERMINISTIC,
   Constructor Function SDO_Topo_Geometry(topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    add_Topo_ids SDO_TGL_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TGL_OBJECT_ARRAY) 
     return SELF as RESULT DETERMINISTIC,

   Member Function Get_Geometry
     return SDO_GEOMETRY DETERMINISTIC )';
   exception when others then NULL;
  end;

 begin
   execute immediate 
 ' alter type sdo_topo_geometry add 
   Constructor Function SDO_Topo_Geometry(topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    Topo_ids SDO_TOPO_OBJECT_ARRAY) 
     return SELF as RESULT DETERMINISTIC,
   add Constructor Function SDO_Topo_Geometry(topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    add_Topo_ids SDO_TOPO_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TOPO_OBJECT_ARRAY) 
     return SELF as RESULT DETERMINISTIC,
   add Constructor Function SDO_Topo_Geometry(topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    Topo_ids SDO_TOPO_OBJECT_ARRAY) 
     return SELF as RESULT DETERMINISTIC,
   add Constructor Function SDO_Topo_Geometry(topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    add_Topo_ids SDO_TOPO_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TOPO_OBJECT_ARRAY) 
     return SELF as RESULT DETERMINISTIC,
 add   Member Function Get_Topo_Elements
     return SDO_TOPO_OBJECT_ARRAY DETERMINISTIC  CASCADE';

   exception when others then NULL;
  end;
end;
/


declare
begin
  begin
    execute immediate
   ' alter type SDO_Topo_Geometry ' ||
   'add member function Get_TGL_Objects ' ||
   ' return SDO_TGL_OBJECT_ARRAY DETERMINISTIC CASCADE ';
    exception when others then
    return;
   end;
end;
/


declare
begin
  begin
    execute immediate
  ' alter type sdo_topo_geometry add ' ||
  '  Constructor Function SDO_Topo_Geometry(tg_id number, topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

    execute immediate
  ' alter type sdo_topo_geometry add ' ||
  ' Constructor Function SDO_Topo_Geometry(tg_id number, topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    add_Topo_ids SDO_TOPO_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

    execute immediate
  ' alter type sdo_topo_geometry add ' ||
  ' Constructor Function SDO_Topo_Geometry(tg_id number, topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

    execute immediate
  ' alter type sdo_topo_geometry add ' ||
  ' Constructor Function SDO_Topo_Geometry(tg_id number, topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    add_Topo_ids SDO_TOPO_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

    execute immediate
  ' alter type sdo_topo_geometry add ' ||
  ' Constructor Function SDO_Topo_Geometry(tg_id number, topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    Topo_ids SDO_TGL_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

    execute immediate
  ' alter type sdo_topo_geometry add ' ||
  ' Constructor Function SDO_Topo_Geometry(tg_id number, topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    Topo_ids SDO_TGL_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

    execute immediate
  ' alter type sdo_topo_geometry add ' ||
  ' Constructor Function SDO_Topo_Geometry(tg_id number, topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    add_Topo_ids SDO_TGL_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TGL_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE ';

    execute immediate
  ' alter type sdo_topo_geometry add ' ||
  ' Constructor Function SDO_Topo_Geometry(tg_id number, topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    add_Topo_ids SDO_TGL_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TGL_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE ';
    exception when others then
    return;
 end;
end;
/

declare
begin
  begin
   execute immediate
    ' alter type sdo_topo_geometry add ' ||
    ' MAP MEMBER FUNCTION to_string RETURN VARCHAR2 CASCADE ';
   exception when others then 
     return;
  end;
end;
/

show errors;

grant execute on MDSYS.SDO_Topo_Geometry to public with grant option;
declare
begin
  begin
    execute immediate
             'create or replace public synonym SDO_Topo_Geometry ' ||
             '  for MDSYS.SDO_Topo_Geometry';
    exception when others then NULL;
   end;
end;
/