Rem
Rem $Header: sdo/admin/sdotxth.sql /main/7 2010/01/08 14:15:41 sravada Exp $
Rem
Rem sdotxth.sql
Rem
Rem Copyright (c) 2004, 2010, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdotxth.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     01/08/10 - bug 9242098
Rem    sravada     06/11/07 - add MAP_BASE_SCALE
Rem    sravada     05/12/05 - sravada_sdo_text_object
Rem    sravada     04/29/05 - use XMLType 
Rem    sravada     09/28/04 - Created
Rem


declare
begin
  begin
   execute immediate
   ' CREATE TYPE ST_ANNOTATIONTEXTELEMENT 
    TIMESTAMP ''2007-04-09:14:32:18''
    OID ''2DB35096FA255AAAE040578CB2054232''
    AS OBJECT ( 
      privateValue  VARCHAR2(4000),
      privateLocation SDO_GEOMETRY,
      privateLeaderLine SDO_GEOMETRY,
      privateTextAttributes VARCHAR2(4000),
      MEMBER FUNCTION Value Return VARCHAR2,
    MEMBER FUNCTION Value(Value VARCHAR2)  RETURN ST_ANNOTATIONTEXTELEMENT,
    MEMBER FUNCTION Location Return SDO_GEOMETRY,
    MEMBER FUNCTION Location(Location SDO_GEOMETRY)
                  RETURN ST_ANNOTATIONTEXTELEMENT,
    MEMBER FUNCTION LeaderLine Return SDO_GEOMETRY,
    MEMBER FUNCTION LeaderLine(LeaderLine SDO_GEOMETRY)
         RETURN ST_ANNOTATIONTEXTELEMENT,
    MEMBER FUNCTION TextAttributes Return VARCHAR2,
    MEMBER FUNCTION TextAttributes(TextAttributes VARCHAR2)
       RETURN ST_ANNOTATIONTEXTELEMENT)  ';
   exception when others then NULL;
 end;
end;
/

declare
begin
  begin
    execute immediate 
    ' CREATE TYPE ST_ANNOT_TEXTELEMENT_ARRAY
      TIMESTAMP ''2007-05-02:14:39:39'' 
      OID ''2F80B03E6FDAE823E040578CB20532C8'' 
      AS VARRAY (100000000) OF ST_ANNOTATIONTEXTELEMENT ';
     exception when others then NULL;
  end;

  begin
   execute immediate
   ' CREATE TYPE ST_ANNOTATIONTEXTELEMENT_ARRAY 
    TIMESTAMP ''2007-04-09:14:30:52''
    OID ''2DB33C751BDEB376E040578CB2054050''
    AS OBJECT (
     privateArray  ST_ANNOT_TEXTELEMENT_ARRAY ,
    MEMBER FUNCTION ElementN ( aposition INTEGER)
          RETURN ST_ANNOTATIONTEXTELEMENT,
    MEMBER FUNCTION ElementN ( element ST_ANNOTATIONTEXTELEMENT, aposition INTEGER)  RETURN ST_ANNOTATIONTEXTELEMENT_ARRAY )  ';
 exception when others then NULL;
 end;
end;
/



declare
begin
   begin
   execute immediate
  ' CREATE  TYPE ST_ANNOTATION_TEXT
    TIMESTAMP ''2007-04-09:14:17:08''
    OID ''2DB30B30F84E6D01E040578CB2053BB0''
    AS OBJECT (
       PrivateEnvelope     SDO_GEOMETRY,
       PrivateElement_Array    ST_ANNOTATIONTEXTELEMENT_ARRAY,
       CONSTRUCTOR FUNCTION ST_ANNOTATION_TEXT(anTextArray ST_ANNOTATIONTEXTELEMENT_ARRAY ) RETURN SELF AS RESULT,
       MEMBER FUNCTION CONCAT (anText ST_ANNOTATION_TEXT)
             RETURN ST_ANNOTATION_TEXT,
      MEMBER FUNCTION Envelope RETURN SDO_GEOMETRY,
      MEMBER FUNCTION Element_Array RETURN ST_ANNOTATIONTEXTELEMENT_ARRAY)  ';
   exception when others then NULL;
 end;
end;
/
 

CREATE OR REPLACE TYPE BODY ST_ANNOTATIONTEXTELEMENT IS
 MEMBER FUNCTION Value Return VARCHAR2 is
 BEGIN
  RETURN SELF.privateValue;
 END;

 MEMBER FUNCTION Value(Value VARCHAR2)  RETURN ST_ANNOTATIONTEXTELEMENT IS
 BEGIN
  RETURN ST_ANNOTATIONTEXTELEMENT(value, SELF.PRIVATELOCATION,
           SELF.PRIVATELEADERLINE, SELF.PRIVATETEXTATTRIBUTES);
 END;
 
 MEMBER FUNCTION Location Return SDO_GEOMETRY is
 BEGIN
  RETURN SELF.privateLocation;
 END;

 MEMBER FUNCTION Location(Location SDO_GEOMETRY) 
    RETURN ST_ANNOTATIONTEXTELEMENT IS
 BEGIN
  RETURN ST_ANNOTATIONTEXTELEMENT(SELF.Value, LOCATION,
           SELF.PRIVATELEADERLINE, SELF.PRIVATETEXTATTRIBUTES);
 END;

 MEMBER FUNCTION LeaderLine Return SDO_GEOMETRY is
 BEGIN
  RETURN SELF.privateLeaderLine;
 END;

 MEMBER FUNCTION LeaderLine(LeaderLine SDO_GEOMETRY)     
     RETURN ST_ANNOTATIONTEXTELEMENT IS
 BEGIN
  RETURN ST_ANNOTATIONTEXTELEMENT(SELF.Value, SELF.PRIVATELOCATION,
           LEADERLINE, SELF.PRIVATETEXTATTRIBUTES);
 END;

 MEMBER FUNCTION TextAttributes Return VARCHAR2 is
 BEGIN
  RETURN SELF.privateTextAttributes;
 END;

 MEMBER FUNCTION TextAttributes(TextAttributes VARCHAR2)  
  RETURN ST_ANNOTATIONTEXTELEMENT IS
 BEGIN
  RETURN ST_ANNOTATIONTEXTELEMENT(SELF.Value, SELF.PRIVATELOCATION,
           SELF.PRIVATELEADERLINE, TEXTATTRIBUTES);
 END;
END;
/

show errors;

CREATE OR REPLACE TYPE BODY ST_ANNOTATIONTEXTELEMENT_ARRAY IS
 MEMBER FUNCTION ElementN ( aposition INTEGER) 
    RETURN ST_ANNOTATIONTEXTELEMENT IS
 BEGIN
   IF (aposition > SELF.PRIVATEARRAY.count) THEN
        RETURN NULL;
   END IF;
   RETURN SELF.PRIVATEARRAY(aposition);
 END;

 MEMBER FUNCTION ElementN (element ST_ANNOTATIONTEXTELEMENT, aposition INTEGER) 
    RETURN ST_ANNOTATIONTEXTELEMENT_ARRAY IS
  tarray ST_ANNOT_TEXTELEMENT_ARRAY;
 BEGIN
   tarray := SELF.PRIVATEARRAY;
   IF (aposition > SELF.PRIVATEARRAY.count) THEN
        RETURN NULL;
   END IF;
   tarray(aposition) := element;
   RETURN ST_ANNOTATIONTEXTELEMENT_ARRAY(tarray);
 END;
END;
/

show errors;

CREATE OR REPLACE TYPE BODY  ST_ANNOTATION_TEXT IS
 CONSTRUCTOR FUNCTION ST_ANNOTATION_TEXT(anTextArray 
               ST_ANNOTATIONTEXTELEMENT_ARRAY ) RETURN SELF AS RESULT IS
  mbr SDO_GEOMETRY;
  geom SDO_GEOMETRY;
  idx number;
  anText ST_ANNOTATIONTEXTELEMENT;
 BEGIN
   geom := null;
   FOR idx in 1 .. anTextArray.PRIVATEARRAY.count LOOP
     anText := anTextArray.ElementN(idx);
     IF (anText.PRIVATELEADERLINE is NULL) THEN
       mbr := sdo_3gl.sdo_mbr(anText.PRIVATELOCATION,anText.PRIVATELOCATION);
     ELSE
       mbr := sdo_3gl.sdo_mbr(anText.PRIVATELOCATION, anText.PRIVATELEADERLINE);
     END IF;
     geom := sdo_util.append(geom, mbr);
   END LOOP;
   PRIVATEENVELOPE := sdo_geom.sdo_mbr(geom);
   PRIVATEELEMENT_ARRAY := anTextArray; 
   RETURN;
 END;
       
 MEMBER FUNCTION CONCAT (anText ST_ANNOTATION_TEXT)
             RETURN ST_ANNOTATION_TEXT IS
  prvArray ST_ANNOT_TEXTELEMENT_ARRAY;
 BEGIN
    prvArray := ST_ANNOT_TEXTELEMENT_ARRAY();
    prvArray.extend(anText.PRIVATEELEMENT_ARRAY.PRIVATEARRAY.count+
                      SELF.PRIVATEELEMENT_ARRAY.PRIVATEARRAY.count);
    FOR idx in 1 .. SELF.PRIVATEELEMENT_ARRAY.PRIVATEARRAY.count LOOP
     prvArray(idx) := SELF.PRIVATEELEMENT_ARRAY.PRIVATEARRAY(idx);
    END LOOP;
    FOR idx in 1 .. anText.PRIVATEELEMENT_ARRAY.PRIVATEARRAY.count LOOP
     prvArray(idx+anText.PRIVATEELEMENT_ARRAY.PRIVATEARRAY.count) := 
                  anText.PRIVATEELEMENT_ARRAY.PRIVATEARRAY(idx);
    END LOOP;
   RETURN ST_ANNOTATION_TEXT(ST_ANNOTATIONTEXTELEMENT_ARRAY(prvArray));
 END;
 MEMBER FUNCTION Envelope RETURN SDO_GEOMETRY IS
 BEGIN
   RETURN PRIVATEENVELOPE;
 END;
 MEMBER FUNCTION Element_Array RETURN ST_ANNOTATIONTEXTELEMENT_ARRAY  IS
 BEGIN
   RETURN PRIVATEELEMENT_ARRAY;
 END;
END;
/



grant execute on ST_ANNOTATIONTEXTELEMENT to public;
grant execute on ST_ANNOT_TEXTELEMENT_ARRAY to public;
grant execute on ST_ANNOTATIONTEXTELEMENT_ARRAY to public;
grant execute on ST_ANNOTATION_TEXT to public;

create or replace public synonym ST_ANNOTATIONTEXTELEMENT for  MDSYS.ST_ANNOTATIONTEXTELEMENT ;
create or replace public synonym ST_ANNOT_TEXTELEMENT_ARRAY for  MDSYS.ST_ANNOT_TEXTELEMENT_ARRAY ;
create or replace public synonym ST_ANNOTATIONTEXTELEMENT_ARRAY for  MDSYS.ST_ANNOTATIONTEXTELEMENT_ARRAY ;
declare
begin
  begin
    execute immediate
        ' create or replace public synonym ST_ANNOTATION_TEXT for ' ||
        '  MDSYS.ST_ANNOTATION_TEXT ' ;
    exception when others then NULL;
   end;
end;
/



declare
begin
   begin
     EXECUTE IMMEDIATE
      ' CREATE TABLE SDO_ANNOTATION_TEXT_METADATA (
        F_TABLE_CATALOG               VARCHAR2(1000),
        F_TABLE_SCHEMA                VARCHAR2(32),
        F_TABLE_NAME                  VARCHAR2(32),
        F_TEXT_COLUMN                 VARCHAR2(1024),
        MAP_BASE_SCALE                NUMBER,
        TEXT_DEFAULT_EXPRESSION       VARCHAR2(4000),
        TEXT_DEFAULT_ATTRIBUTES       VARCHAR2(4000), 
        CONSTRAINT unique_text_layers
             PRIMARY KEY (F_TABLE_SCHEMA,F_TABLE_NAME,F_TEXT_COLUMN) ) ';
   exception when others then NULL;
   end;
end;
/

CREATE OR REPLACE VIEW USER_ANNOTATION_TEXT_METADATA AS
SELECT F_TABLE_NAME TABLE_NAME, F_TEXT_COLUMN COLUMN_NAME,
               MAP_BASE_SCALE,
               TEXT_DEFAULT_EXPRESSION TEXT_EXPRESSION ,
               TEXT_DEFAULT_ATTRIBUTES TEXT_ATTRIBUTES
FROM SDO_ANNOTATION_TEXT_METADATA
WHERE F_TABLE_SCHEMA =  sys_context('userenv', 'CURRENT_SCHEMA');

         
CREATE OR REPLACE VIEW ALL_ANNOTATION_TEXT_METADATA AS
SELECT F_TABLE_SCHEMA OWNER, F_TABLE_NAME TABLE_NAME, 
               F_TEXT_COLUMN COLUMN_NAME,
               MAP_BASE_SCALE,
               TEXT_DEFAULT_EXPRESSION TEXT_EXPRESSION,
               TEXT_DEFAULT_ATTRIBUTES TEXT_ATTRIBUTES
FROM SDO_ANNOTATION_TEXT_METADATA, ALL_OBJECTS a
       where a.object_name = F_TABLE_NAME and a.owner = F_TABLE_SCHEMA 
             and a.object_type in ('TABLE', 'SYNONYM', 'VIEW')
UNION ALL
SELECT F_TABLE_SCHEMA OWNER, F_TABLE_NAME TABLE_NAME, 
               F_TEXT_COLUMN COLUMN_NAME,
               MAP_BASE_SCALE,
               TEXT_DEFAULT_EXPRESSION ,
               TEXT_DEFAULT_ATTRIBUTES 
FROM SDO_ANNOTATION_TEXT_METADATA, ALL_OBJECT_TABLES a
       where a.table_name = F_TABLE_NAME and a.owner = F_TABLE_SCHEMA ;
 

CREATE OR REPLACE TRIGGER SDO_ANNOT_TRIG_INS1
INSTEAD OF INSERT OR DELETE OR UPDATE ON USER_ANNOTATION_TEXT_METADATA
REFERENCING NEW AS n OLD as o
FOR EACH ROW
declare
 tname varchar2(32);
begin

  IF INSERTING THEN 
 EXECUTE IMMEDIATE
  'SELECT user FROM dual' into tname;
      INSERT INTO SDO_ANNOTATION_TEXT_METADATA values(null,tname,
            UPPER(:n.table_name), UPPER(:n.column_name), :n.MAP_BASE_SCALE,
                 :n.TEXT_EXPRESSION,
             :n.TEXT_ATTRIBUTES);
  ELSIF UPDATING THEN
 EXECUTE IMMEDIATE
  'SELECT user FROM dual' into tname;
  UPDATE SDO_ANNOTATION_TEXT_METADATA SET(F_TABLE_SCHEMA,
             F_TABLE_NAME,F_TEXT_COLUMN, MAP_BASE_SCALE, TEXT_DEFAULT_EXPRESSION,
                    TEXT_DEFAULT_ATTRIBUTES) =
   (SELECT tname, :n.table_name, :n.column_name, :n.MAP_BASE_SCALE,
                    :n.TEXT_EXPRESSION,
           :n.TEXT_ATTRIBUTES FROM DUAL)
  WHERE F_TABLE_SCHEMA =  tname AND
     F_TABLE_NAME = :o.table_name AND F_TEXT_COLUMN = :o.column_name;
  ELSE
 EXECUTE IMMEDIATE
  'SELECT user FROM dual' into tname;
   DELETE FROM SDO_ANNOTATION_TEXT_METADATA
    WHERE  F_TABLE_SCHEMA =  tname AND
     F_TABLE_NAME = :o.table_name AND F_TEXT_COLUMN = :o.column_name;
  END IF;
end;
/


grant select on ALL_ANNOTATION_TEXT_METADATA to public;
grant select,insert,update,delete on USER_ANNOTATION_TEXT_METADATA to public;

create or replace public synonym USER_ANNOTATION_TEXT_METADATA 
for MDSYS.USER_ANNOTATION_TEXT_METADATA;

create or replace public synonym ALL_ANNOTATION_TEXT_METADATA 
for MDSYS.ALL_ANNOTATION_TEXT_METADATA;