REM
REM $Header: sdowin.sql 20-sep-97.07:43:48 gvincent Exp $
REM
REM Copyright (c) Oracle Corporation All Right Reserved.
REM
REM NAME:
REM   sdowin.sql 
REM DESCRIPTION:
REM    This file contains the specification of PL/SQL functions and procedures 
REM    that aid in window queries.
REM PROCEDURES & FUNCTIONS
REM    create_window_layer
REM    build_window
REM    clean_window
REM    gid_to_window_layer
REM    cleanup_gid
REM NOTES
REM    This package must be compiled by the user who has the privileges to 
REM    read, write and create in any users schema. That is the procedures in 
REM    here when executed by a user will run under the privileges to the user 
REM    who compiles this package. This package is meant to aid those users who 
REM    do not have any insert or create privileges but still wish to perform 
REM    queries. The max number of ordinates that can be passed are 250
REM MODIFIED     (DD-MON-YY)  DESCRIPTION
REM GVincent      10-SEP-97   Add procedures gid_to_window_layer
REM                           (created by dgeringe, integrated by gvincent)
REM pvretano      02-AUG-97   Add support for hybrid indexing.
REM pfwang        23-JUL-97   Add cleanup_gid procedure
REM NAgarwal      25-SEP-96   Add comments
REM NAgarwal      23-SEP-96   Removed body specification .
REM NAgarwal      18-SEP-96   Created

CREATE OR REPLACE PACKAGE sdo_window AS

-- NAME:
--    create_window_layer - creates the necessary tables which constitute a
--			    SDO layer
-- DESCRIPTION:
--    This functions creates the four tables of a SDO layer and iniltializes
--    the _SDOLAYER and _SDODIM tables. It also creates a sequence which is 
--    used internally to generate unique gids.
-- NOTES:
--    Since the _SDODIM table is initialized with the dimension and the bound
--    information only those queries which are in the same dimension should be
--    queried against this layer. If a user wishes to issue a query w.r.t a 
--    different dimension, a new layer must be created


  PROCEDURE create_window_layer(
  win_layer varchar2, levels integer, numtiles integer,
  dimnum1 integer, lb1 NUMBER, ub1 NUMBER, toler1 NUMBER, dimname1 varchar2,
  dimnum2 integer, lb2 NUMBER, ub2 NUMBER, toler2 NUMBER, dimname2 varchar2);

  PROCEDURE create_window_layer(win_layer varchar2,
  dimnum1 integer, lb1 NUMBER, ub1 NUMBER, toler1 NUMBER, dimname1 varchar2,
  dimnum2 integer, lb2 NUMBER, ub2 NUMBER, toler2 NUMBER, dimname2 varchar2);


-- NAME:
--    build_window - This function builds the window for the query and returns
--                   a gid which serves as a handle       
-- DESCRIPTION:
--    This function inserts the coordinates passed by the user into the _SDOGEOM
--    table, tessellates the geometry (creates the index) and returns a unqiue 
--    gid corresponding to the geometry
-- ARGUMENTS:
--    comp_name - The name of the user who ccompiled this package
--    win_layer - name of the window layer into which the query info should be 
--		  inserted into
--    etype     - ETYPE of the geometry which is being inserted
--    num_tiles - the number of tiles into which the window should be 
--                tessellated into (if NULL, assume 734 layer structure)
--    ord1 thru ord250 - the ordinates of the query
-- NOTES:
--    This function always tessellaates the geometry into 4 tiles
--

  FUNCTION build_window(
       comp_name varchar2,win_layer varchar2,etype integer,num_tiles integer,
       ord1  NUMBER,       ord2  NUMBER,       ord3  NUMBER:=NULL, 
       ord4  NUMBER:=NULL, ord5  NUMBER:=NULL, ord6  NUMBER:=NULL, 
       ord7  NUMBER:=NULL, ord8  NUMBER:=NULL, ord9  NUMBER:=NULL, 
       ord10 NUMBER:=NULL, ord11 NUMBER:=NULL, ord12 NUMBER:=NULL, 
       ord13 NUMBER:=NULL, ord14 NUMBER:=NULL, ord15 NUMBER:=NULL, 
       ord16 NUMBER:=NULL, ord17 NUMBER:=NULL, ord18 NUMBER:=NULL, 
       ord19 NUMBER:=NULL, ord20 NUMBER:=NULL, ord21 NUMBER:=NULL, 
       ord22 NUMBER:=NULL, ord23 NUMBER:=NULL, ord24 NUMBER:=NULL, 
       ord25 NUMBER:=NULL, ord26 NUMBER:=NULL, ord27 NUMBER:=NULL, 
       ord28 NUMBER:=NULL, ord29 NUMBER:=NULL, ord30 NUMBER:=NULL, 
       ord31 NUMBER:=NULL, ord32 NUMBER:=NULL, ord33 NUMBER:=NULL, 
       ord34 NUMBER:=NULL, ord35 NUMBER:=NULL, ord36 NUMBER:=NULL, 
       ord37 NUMBER:=NULL, ord38 NUMBER:=NULL, ord39 NUMBER:=NULL, 
       ord40 NUMBER:=NULL, ord41 NUMBER:=NULL, ord42 NUMBER:=NULL, 
       ord43 NUMBER:=NULL, ord44 NUMBER:=NULL, ord45 NUMBER:=NULL, 
       ord46 NUMBER:=NULL, ord47 NUMBER:=NULL, ord48 NUMBER:=NULL,
       ord49 NUMBER:=NULL, ord50 NUMBER:=NULL, ord51 NUMBER:=NULL, 
       ord52 NUMBER:=NULL, ord53 NUMBER:=NULL, ord54 NUMBER:=NULL, 
       ord55 NUMBER:=NULL, ord56 NUMBER:=NULL, ord57 NUMBER:=NULL, 
       ord58 NUMBER:=NULL, ord59 NUMBER:=NULL, ord60 NUMBER:=NULL, 
       ord61 NUMBER:=NULL, ord62 NUMBER:=NULL, ord63 NUMBER:=NULL, 
       ord64 NUMBER:=NULL, ord65 NUMBER:=NULL, ord66 NUMBER:=NULL, 
       ord67 NUMBER:=NULL, ord68 NUMBER:=NULL, ord69 NUMBER:=NULL, 
       ord70 NUMBER:=NULL, ord71 NUMBER:=NULL, ord72 NUMBER:=NULL, 
       ord73 NUMBER:=NULL, ord74 NUMBER:=NULL, ord75 NUMBER:=NULL, 
       ord76 NUMBER:=NULL, ord77 NUMBER:=NULL, ord78 NUMBER:=NULL, 
       ord79 NUMBER:=NULL, ord80 NUMBER:=NULL, ord81 NUMBER:=NULL, 
       ord82 NUMBER:=NULL, ord83 NUMBER:=NULL, ord84 NUMBER:=NULL, 
       ord85 NUMBER:=NULL, ord86 NUMBER:=NULL, ord87 NUMBER:=NULL, 
       ord88 NUMBER:=NULL, ord89 NUMBER:=NULL, ord90 NUMBER:=NULL, 
       ord91 NUMBER:=NULL, ord92 NUMBER:=NULL, ord93 NUMBER:=NULL, 
       ord94 NUMBER:=NULL, ord95 NUMBER:=NULL, ord96 NUMBER:=NULL, 
       ord97 NUMBER:=NULL, ord98 NUMBER:=NULL, ord99 NUMBER:=NULL, 
       ord100 NUMBER:=NULL, ord101 NUMBER:=NULL, ord102 NUMBER:=NULL, 
       ord103 NUMBER:=NULL, ord104 NUMBER:=NULL, ord105 NUMBER:=NULL, 
       ord106 NUMBER:=NULL, ord107 NUMBER:=NULL, ord108 NUMBER:=NULL, 
       ord109 NUMBER:=NULL, ord110 NUMBER:=NULL, ord111 NUMBER:=NULL, 
       ord112 NUMBER:=NULL, ord113 NUMBER:=NULL, ord114 NUMBER:=NULL, 
       ord115 NUMBER:=NULL, ord116 NUMBER:=NULL, ord117 NUMBER:=NULL, 
       ord118 NUMBER:=NULL, ord119 NUMBER:=NULL, ord120 NUMBER:=NULL, 
       ord121 NUMBER:=NULL, ord122 NUMBER:=NULL, ord123 NUMBER:=NULL, 
       ord124 NUMBER:=NULL,
       ord125 NUMBER:=NULL, ord126 NUMBER:=NULL, ord127 NUMBER:=NULL, 
       ord128 NUMBER:=NULL, ord129 NUMBER:=NULL, ord130 NUMBER:=NULL, 
       ord131 NUMBER:=NULL, ord132 NUMBER:=NULL, ord133 NUMBER:=NULL, 
       ord134 NUMBER:=NULL, ord135 NUMBER:=NULL, ord136 NUMBER:=NULL, 
       ord137 NUMBER:=NULL, ord138 NUMBER:=NULL, ord139 NUMBER:=NULL, 
       ord140 NUMBER:=NULL, ord141 NUMBER:=NULL, ord142 NUMBER:=NULL, 
       ord143 NUMBER:=NULL, ord144 NUMBER:=NULL, ord145 NUMBER:=NULL, 
       ord146 NUMBER:=NULL, ord147 NUMBER:=NULL, ord148 NUMBER:=NULL,
       ord149 NUMBER:=NULL, ord150 NUMBER:=NULL, ord151 NUMBER:=NULL, 
       ord152 NUMBER:=NULL, ord153 NUMBER:=NULL, ord154 NUMBER:=NULL, 
       ord155 NUMBER:=NULL, ord156 NUMBER:=NULL, ord157 NUMBER:=NULL, 
       ord158 NUMBER:=NULL, ord159 NUMBER:=NULL, ord160 NUMBER:=NULL, 
       ord161 NUMBER:=NULL, ord162 NUMBER:=NULL, ord163 NUMBER:=NULL, 
       ord164 NUMBER:=NULL, ord165 NUMBER:=NULL, ord166 NUMBER:=NULL, 
       ord167 NUMBER:=NULL, ord168 NUMBER:=NULL, ord169 NUMBER:=NULL, 
       ord170 NUMBER:=NULL, ord171 NUMBER:=NULL, ord172 NUMBER:=NULL, 
       ord173 NUMBER:=NULL, ord174 NUMBER:=NULL, ord175 NUMBER:=NULL, 
       ord176 NUMBER:=NULL, ord177 NUMBER:=NULL, ord178 NUMBER:=NULL, 
       ord179 NUMBER:=NULL, ord180 NUMBER:=NULL, ord181 NUMBER:=NULL, 
       ord182 NUMBER:=NULL, ord183 NUMBER:=NULL, ord184 NUMBER:=NULL, 
       ord185 NUMBER:=NULL, ord186 NUMBER:=NULL, ord187 NUMBER:=NULL, 
       ord188 NUMBER:=NULL, ord189 NUMBER:=NULL, ord190 NUMBER:=NULL, 
       ord191 NUMBER:=NULL, ord192 NUMBER:=NULL, ord193 NUMBER:=NULL, 
       ord194 NUMBER:=NULL, ord195 NUMBER:=NULL, ord196 NUMBER:=NULL, 
       ord197 NUMBER:=NULL, ord198 NUMBER:=NULL, ord199 NUMBER:=NULL, 
       ord200 NUMBER:=NULL, ord201 NUMBER:=NULL, ord202 NUMBER:=NULL, 
       ord203 NUMBER:=NULL, ord204 NUMBER:=NULL, ord205 NUMBER:=NULL, 
       ord206 NUMBER:=NULL, ord207 NUMBER:=NULL, ord208 NUMBER:=NULL,
       ord209 NUMBER:=NULL, ord210 NUMBER:=NULL, ord211 NUMBER:=NULL, 
       ord212 NUMBER:=NULL, ord213 NUMBER:=NULL, ord214 NUMBER:=NULL, 
       ord215 NUMBER:=NULL, ord216 NUMBER:=NULL, ord217 NUMBER:=NULL, 
       ord218 NUMBER:=NULL, ord219 NUMBER:=NULL, ord220 NUMBER:=NULL, 
       ord221 NUMBER:=NULL, ord222 NUMBER:=NULL, ord223 NUMBER:=NULL, 
       ord224 NUMBER:=NULL, ord225 NUMBER:=NULL, ord226 NUMBER:=NULL, 
       ord227 NUMBER:=NULL, ord228 NUMBER:=NULL, ord229 NUMBER:=NULL, 
       ord230 NUMBER:=NULL, ord231 NUMBER:=NULL, ord232 NUMBER:=NULL, 
       ord233 NUMBER:=NULL, ord234 NUMBER:=NULL, ord235 NUMBER:=NULL, 
       ord236 NUMBER:=NULL, ord237 NUMBER:=NULL, ord238 NUMBER:=NULL, 
       ord239 NUMBER:=NULL, ord240 NUMBER:=NULL, ord241 NUMBER:=NULL, 
       ord242 NUMBER:=NULL, ord243 NUMBER:=NULL, ord244 NUMBER:=NULL, 
       ord245 NUMBER:=NULL, ord246 NUMBER:=NULL, ord247 NUMBER:=NULL, 
       ord248 NUMBER:=NULL, ord249 NUMBER:=NULL, ord250 NUMBER:=NULL)
       return integer;

  FUNCTION build_window_fixed(
       comp_name varchar2,
       win_layer varchar2,
       etype integer,
       tile_size integer,
       ord1 NUMBER, ord2 NUMBER, ord3 NUMBER:=NULL, 
       ord4 NUMBER:=NULL, ord5 NUMBER:=NULL, ord6 NUMBER:=NULL, 
       ord7 NUMBER:=NULL, ord8 NUMBER:=NULL, ord9 NUMBER:=NULL, 
       ord10 NUMBER:=NULL, ord11 NUMBER:=NULL, ord12 NUMBER:=NULL, 
       ord13 NUMBER:=NULL, ord14 NUMBER:=NULL, ord15 NUMBER:=NULL, 
       ord16 NUMBER:=NULL, ord17 NUMBER:=NULL, ord18 NUMBER:=NULL, 
       ord19 NUMBER:=NULL, ord20 NUMBER:=NULL, ord21 NUMBER:=NULL, 
       ord22 NUMBER:=NULL, ord23 NUMBER:=NULL, ord24 NUMBER:=NULL, 
       ord25 NUMBER:=NULL, ord26 NUMBER:=NULL, ord27 NUMBER:=NULL, 
       ord28 NUMBER:=NULL, ord29 NUMBER:=NULL, ord30 NUMBER:=NULL, 
       ord31 NUMBER:=NULL, ord32 NUMBER:=NULL, ord33 NUMBER:=NULL, 
       ord34 NUMBER:=NULL, ord35 NUMBER:=NULL, ord36 NUMBER:=NULL, 
       ord37 NUMBER:=NULL, ord38 NUMBER:=NULL, ord39 NUMBER:=NULL, 
       ord40 NUMBER:=NULL, ord41 NUMBER:=NULL, ord42 NUMBER:=NULL, 
       ord43 NUMBER:=NULL, ord44 NUMBER:=NULL, ord45 NUMBER:=NULL, 
       ord46 NUMBER:=NULL, ord47 NUMBER:=NULL, ord48 NUMBER:=NULL,
       ord49 NUMBER:=NULL, ord50 NUMBER:=NULL, ord51 NUMBER:=NULL, 
       ord52 NUMBER:=NULL, ord53 NUMBER:=NULL, ord54 NUMBER:=NULL, 
       ord55 NUMBER:=NULL, ord56 NUMBER:=NULL, ord57 NUMBER:=NULL, 
       ord58 NUMBER:=NULL, ord59 NUMBER:=NULL, ord60 NUMBER:=NULL, 
       ord61 NUMBER:=NULL, ord62 NUMBER:=NULL, ord63 NUMBER:=NULL, 
       ord64 NUMBER:=NULL, ord65 NUMBER:=NULL, ord66 NUMBER:=NULL, 
       ord67 NUMBER:=NULL, ord68 NUMBER:=NULL, ord69 NUMBER:=NULL, 
       ord70 NUMBER:=NULL, ord71 NUMBER:=NULL, ord72 NUMBER:=NULL, 
       ord73 NUMBER:=NULL, ord74 NUMBER:=NULL, ord75 NUMBER:=NULL, 
       ord76 NUMBER:=NULL, ord77 NUMBER:=NULL, ord78 NUMBER:=NULL, 
       ord79 NUMBER:=NULL, ord80 NUMBER:=NULL, ord81 NUMBER:=NULL, 
       ord82 NUMBER:=NULL, ord83 NUMBER:=NULL, ord84 NUMBER:=NULL, 
       ord85 NUMBER:=NULL, ord86 NUMBER:=NULL, ord87 NUMBER:=NULL, 
       ord88 NUMBER:=NULL, ord89 NUMBER:=NULL, ord90 NUMBER:=NULL, 
       ord91 NUMBER:=NULL, ord92 NUMBER:=NULL, ord93 NUMBER:=NULL, 
       ord94 NUMBER:=NULL, ord95 NUMBER:=NULL, ord96 NUMBER:=NULL, 
       ord97 NUMBER:=NULL, ord98 NUMBER:=NULL, ord99 NUMBER:=NULL, 
       ord100 NUMBER:=NULL, ord101 NUMBER:=NULL, ord102 NUMBER:=NULL, 
       ord103 NUMBER:=NULL, ord104 NUMBER:=NULL, ord105 NUMBER:=NULL, 
       ord106 NUMBER:=NULL, ord107 NUMBER:=NULL, ord108 NUMBER:=NULL, 
       ord109 NUMBER:=NULL, ord110 NUMBER:=NULL, ord111 NUMBER:=NULL, 
       ord112 NUMBER:=NULL, ord113 NUMBER:=NULL, ord114 NUMBER:=NULL, 
       ord115 NUMBER:=NULL, ord116 NUMBER:=NULL, ord117 NUMBER:=NULL, 
       ord118 NUMBER:=NULL, ord119 NUMBER:=NULL, ord120 NUMBER:=NULL, 
       ord121 NUMBER:=NULL, ord122 NUMBER:=NULL, ord123 NUMBER:=NULL, 
       ord124 NUMBER:=NULL,
       ord125 NUMBER:=NULL, ord126 NUMBER:=NULL, ord127 NUMBER:=NULL, 
       ord128 NUMBER:=NULL, ord129 NUMBER:=NULL, ord130 NUMBER:=NULL, 
       ord131 NUMBER:=NULL, ord132 NUMBER:=NULL, ord133 NUMBER:=NULL, 
       ord134 NUMBER:=NULL, ord135 NUMBER:=NULL, ord136 NUMBER:=NULL, 
       ord137 NUMBER:=NULL, ord138 NUMBER:=NULL, ord139 NUMBER:=NULL, 
       ord140 NUMBER:=NULL, ord141 NUMBER:=NULL, ord142 NUMBER:=NULL, 
       ord143 NUMBER:=NULL, ord144 NUMBER:=NULL, ord145 NUMBER:=NULL, 
       ord146 NUMBER:=NULL, ord147 NUMBER:=NULL, ord148 NUMBER:=NULL,
       ord149 NUMBER:=NULL, ord150 NUMBER:=NULL, ord151 NUMBER:=NULL, 
       ord152 NUMBER:=NULL, ord153 NUMBER:=NULL, ord154 NUMBER:=NULL, 
       ord155 NUMBER:=NULL, ord156 NUMBER:=NULL, ord157 NUMBER:=NULL, 
       ord158 NUMBER:=NULL, ord159 NUMBER:=NULL, ord160 NUMBER:=NULL, 
       ord161 NUMBER:=NULL, ord162 NUMBER:=NULL, ord163 NUMBER:=NULL, 
       ord164 NUMBER:=NULL, ord165 NUMBER:=NULL, ord166 NUMBER:=NULL, 
       ord167 NUMBER:=NULL, ord168 NUMBER:=NULL, ord169 NUMBER:=NULL, 
       ord170 NUMBER:=NULL, ord171 NUMBER:=NULL, ord172 NUMBER:=NULL, 
       ord173 NUMBER:=NULL, ord174 NUMBER:=NULL, ord175 NUMBER:=NULL, 
       ord176 NUMBER:=NULL, ord177 NUMBER:=NULL, ord178 NUMBER:=NULL, 
       ord179 NUMBER:=NULL, ord180 NUMBER:=NULL, ord181 NUMBER:=NULL, 
       ord182 NUMBER:=NULL, ord183 NUMBER:=NULL, ord184 NUMBER:=NULL, 
       ord185 NUMBER:=NULL, ord186 NUMBER:=NULL, ord187 NUMBER:=NULL, 
       ord188 NUMBER:=NULL, ord189 NUMBER:=NULL, ord190 NUMBER:=NULL, 
       ord191 NUMBER:=NULL, ord192 NUMBER:=NULL, ord193 NUMBER:=NULL, 
       ord194 NUMBER:=NULL, ord195 NUMBER:=NULL, ord196 NUMBER:=NULL, 
       ord197 NUMBER:=NULL, ord198 NUMBER:=NULL, ord199 NUMBER:=NULL, 
       ord200 NUMBER:=NULL, ord201 NUMBER:=NULL, ord202 NUMBER:=NULL, 
       ord203 NUMBER:=NULL, ord204 NUMBER:=NULL, ord205 NUMBER:=NULL, 
       ord206 NUMBER:=NULL, ord207 NUMBER:=NULL, ord208 NUMBER:=NULL,
       ord209 NUMBER:=NULL, ord210 NUMBER:=NULL, ord211 NUMBER:=NULL, 
       ord212 NUMBER:=NULL, ord213 NUMBER:=NULL, ord214 NUMBER:=NULL, 
       ord215 NUMBER:=NULL, ord216 NUMBER:=NULL, ord217 NUMBER:=NULL, 
       ord218 NUMBER:=NULL, ord219 NUMBER:=NULL, ord220 NUMBER:=NULL, 
       ord221 NUMBER:=NULL, ord222 NUMBER:=NULL, ord223 NUMBER:=NULL, 
       ord224 NUMBER:=NULL, ord225 NUMBER:=NULL, ord226 NUMBER:=NULL, 
       ord227 NUMBER:=NULL, ord228 NUMBER:=NULL, ord229 NUMBER:=NULL, 
       ord230 NUMBER:=NULL, ord231 NUMBER:=NULL, ord232 NUMBER:=NULL, 
       ord233 NUMBER:=NULL, ord234 NUMBER:=NULL, ord235 NUMBER:=NULL, 
       ord236 NUMBER:=NULL, ord237 NUMBER:=NULL, ord238 NUMBER:=NULL, 
       ord239 NUMBER:=NULL, ord240 NUMBER:=NULL, ord241 NUMBER:=NULL, 
       ord242 NUMBER:=NULL, ord243 NUMBER:=NULL, ord244 NUMBER:=NULL, 
       ord245 NUMBER:=NULL, ord246 NUMBER:=NULL, ord247 NUMBER:=NULL, 
       ord248 NUMBER:=NULL, ord249 NUMBER:=NULL, ord250 NUMBER:=NULL)
       return integer;


-- NAME:
--    clean_window - Cleanup the window layer 
-- DESCRIPTION:
--    Drops all the four tables corresponding to the window layer
-- 

  PROCEDURE clean_window(win_layer varchar2);


  FUNCTION table_present(tab_name varchar2) return integer;

-- NAME:
--    gid_to_window_layer - Takes gid from one layer and tiles it to new
--    level in a query window 
-- DESCRIPTION:
--    Given two layers tiled to different levels, take a GID (the area of 
--    interest) from one layer, and tile it (in a window layer) to the level
--    of the other. Then a join from the two layers can be done. 
-- ARGUMENTS:
--    owner - owner of the query window which needs to be tiled to the correct
--    level
--    app_win_layer - application window layer. This was created with 
--    sdo_window.create_window layer.
--    user_win_layer - The layer which contains user_win_gid
--    user_win_gid - GID of the window or area of interest
--    user_query_layer - Layer you will query with new_gid
--    new_gid - GID of the retiled area of interest       
-- NOTES:

   PROCEDURE gid_to_window_layer(owner           varchar2,
                                app_win_layer    varchar2,
                                user_win_layer   varchar2,
                                user_win_gid      integer,
                                user_query_layer varchar2,
                                new_gid      out  integer);

-- Name:
--    cleanup_gid - Clean up a GID from a layer
-- DESCRIPTION
--    Delete rows from the geom and index table associated with gid
--

 PROCEDURE cleanup_gid(gid         integer,
                       layer       varchar2,
                       do_commit   boolean := FALSE);


END sdo_window;
/