[Home] [Help]
PACKAGE BODY: APPS.RRS_ELOCATION_PKG
Source
1 PACKAGE BODY rrs_elocation_pkg AS
2 /*$Header: RRSELOCB.pls 120.0 2006/01/19 07:51:53 swbhatna noship $*/
3
4 --------------------------------------
5 -- PUBLIC PROCEDURE rebuild_spatial_indexes
6 -- DESCRIPTION
7 -- Rebuilds the spatial index on RRS_SITE_TMP.GEOMETRY and RRS_TRADE_AREAS.GEOMETRY.
8 -- Rebuilding the spatial index is required so that the index performs adequately,
9 -- queries can accurately extract the spatial data and Spatial functions can be called
10 -- on these columns
11 -- ARGUMENTS
12 -- OUT:
13 -- errbuf Standard AOL concurrent program error buffer.
14 -- retcode Standard AOL concurrent program return code.
15 -- MODIFICATION HISTORY
16 -- 18/01/2006 swbhatna Created.
17 --------------------------------------
18 PROCEDURE rebuild_spatial_indexes (
19 errbuf OUT NOCOPY VARCHAR2,
20 retcode OUT NOCOPY VARCHAR2
21 )
22 IS
23 CURSOR c_index(x_index_name IN VARCHAR2) IS
24 SELECT status, domidx_opstatus
25 FROM sys.all_indexes
26 WHERE owner = 'RRS' and index_name = x_index_name;
27 l_status sys.all_indexes.status%type;
28 l_domidx_opstatus sys.all_indexes.DOMIDX_OPSTATUS%type;
29 l_index_name varchar2(50);
30 l_commit_interval varchar2(5) := '1000';
31 x_drop_index varchar2(255);
32 x_rebuild_string VARCHAR2(100);
33 BEGIN
34 l_index_name := 'RRS_SITE_TMP_N1';
35 open c_index(l_index_name);
36 FETCH c_index into l_status,l_domidx_opstatus;
37 IF c_index%NOTFOUND THEN /* Index is Missing */
38 -- Delete existing and Insert new metadata
39 Update_Index_Metadata(l_index_name);
40 -- Create Index
41 Create_Index(l_index_name);
42 ELSIF c_index%FOUND THEN
43 IF nvl(l_status,'NONE') <> 'VALID' OR nvl(l_domidx_opstatus,'NONE') <> 'VALID' THEN /* Index Is Invalid */
44 -- Drop Index
45 x_drop_index := 'drop index RRS.'||l_index_name||' force';
46 EXECUTE IMMEDIATE x_drop_index;
47 -- Delete existing and Insert new metadata
48 Update_Index_Metadata(l_index_name);
49 -- Create Index
50 Create_Index(l_index_name);
51 ELSE /* Index Exists and is Valid */
52 -- Initialize the return code
53 retcode := '0';
54 x_rebuild_string := 'ALTER INDEX RRS.' || l_index_name || ' REBUILD ' ||
55 'PARAMETERS(''sdo_commit_interval=' ||
56 l_commit_interval || ''')';
57 EXECUTE IMMEDIATE x_rebuild_string;
58 END IF; /* Index Is Invalid */
59 END IF; /* Index is Missing */
60 close c_index;
61 l_index_name := 'RRS_TRADE_AREAS_N1';
62 open c_index(l_index_name);
63 FETCH c_index into l_status,l_domidx_opstatus;
64 IF c_index%NOTFOUND THEN /* Index is Missing */
65 -- Delete existing and Insert new metadata
66 Update_Index_Metadata(l_index_name);
67 -- Create Index
68 Create_Index(l_index_name);
69 ELSIF c_index%FOUND THEN
70 IF nvl(l_status,'') <> 'VALID' OR nvl(l_domidx_opstatus,'') <> 'VALID' THEN /* Index Is Invalid */
71 -- Drop Index
72 x_drop_index := 'drop index RRS.'||l_index_name||' force';
73 EXECUTE IMMEDIATE x_drop_index;
74 -- Delete existing and Insert new metadata
75 Update_Index_Metadata(l_index_name);
76 -- Create Index
77 Create_Index(l_index_name);
78 ELSE /* Index Exists and is Valid */
79 -- Initialize the return code
80 retcode := '0';
81 x_rebuild_string := 'ALTER INDEX RRS.' || l_index_name || ' REBUILD ' ||
82 'PARAMETERS(''sdo_commit_interval=' ||
83 l_commit_interval || ''')';
84 EXECUTE IMMEDIATE x_rebuild_string;
85 END IF; /* Index Is Invalid */
86 END IF; /* Index is Missing */
87 close c_index;
88 EXCEPTION
89 WHEN OTHERS THEN
90 retcode := '1';
91 errbuf := SQLERRM;
92 END rebuild_spatial_indexes;
93
94 PROCEDURE Update_Index_Metadata (
95 p_index_name IN VARCHAR2
96 )
97 IS
98 x_del_meta varchar2(255);
99 x_ins_meta varchar2(2000);
100 l_table_name varchar2(100);
101 BEGIN
102 IF p_index_name= 'RRS_SITE_TMP_N1' THEN
103 l_table_name := 'RRS_SITE_TMP';
104 ELSIF p_index_name= 'RRS_TRADE_AREAS_N1' THEN
105 l_table_name := 'RRS_TRADE_AREAS';
106 END IF;
107 -- Delete Meta Data
108 x_del_meta := 'Delete from user_sdo_geom_metadata
109 Where table_name = ''' || l_table_name ||
110 ''' and column_name= ''GEOMETRY''';
111 EXECUTE IMMEDIATE x_del_meta;
112 -- Create Meta Data
113 x_ins_meta := 'INSERT INTO user_sdo_geom_metadata (
114 table_name, column_name, diminfo, srid ) VALUES (
115 '''|| l_table_name ||''', ''GEOMETRY'',
116 mdsys.sdo_dim_array(
117 mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
118 mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
119 EXECUTE IMMEDIATE x_ins_meta;
120 END Update_Index_Metadata;
121
122 PROCEDURE Create_Index (
123 p_index_name IN VARCHAR2
124 )
125 IS
126 object_exists EXCEPTION;
127 column_not_found EXCEPTION;
128 domainobj_exists EXCEPTION;
129 no_metadata_found EXCEPTION;
130
131 PRAGMA EXCEPTION_INIT(object_exists, -955);
132 PRAGMA EXCEPTION_INIT(column_not_found, -904);
133 PRAGMA EXCEPTION_INIT(domainobj_exists, -29879);
134 PRAGMA EXCEPTION_INIT(no_metadata_found, -13203);
135
136 l_exec_string VARCHAR2(1000);
137 l_table_name varchar2(100);
138 x_dummy BOOLEAN;
139 x_status varchar2(30);
140 x_ind varchar2(30);
141 x_index_owner varchar2(50);
142 check_tspace_exist varchar2(100);
143 physical_tspace_name varchar2(100);
144
145 BEGIN
146 x_dummy := fnd_installation.GET_APP_INFO('RRS',x_status,x_ind,x_index_owner);
147 AD_TSPACE_UTIL.get_tablespace_name('RRS','TRANSACTION_INDEXES','Y',check_tspace_exist,physical_tspace_name);
148
149 IF p_index_name= 'RRS_SITE_TMP_N1' THEN
150 l_table_name := 'RRS_SITE_TMP';
151 ELSIF p_index_name= 'RRS_TRADE_AREAS_N1' THEN
152 l_table_name := 'RRS_TRADE_AREAS';
153 END IF;
154 l_exec_string := 'CREATE INDEX RRS.' || p_index_name ||' ON RRS.'||
155 l_table_name ||'(geometry) INDEXTYPE IS mdsys.spatial_index parameters(''TABLESPACE='||
156 physical_tspace_name||''')';
157 -- create the index
158 IF(check_tspace_exist = 'Y') THEN
159 EXECUTE IMMEDIATE l_exec_string;
160 END IF;
161 EXCEPTION
162 WHEN column_not_found THEN
163 NULL;
164 WHEN object_exists THEN
165 NULL;
166 WHEN domainobj_exists THEN
167 NULL;
168 WHEN no_metadata_found THEN
169 NULL;
170 END Create_Index;
171
172 END rrs_elocation_pkg;