[Home] [Help]
PACKAGE BODY: APPS.CSF_SPATIAL_TTSP_PUB
Source
1 PACKAGE BODY CSF_SPATIAL_TTSP_PUB AS
2 /* $Header: CSFPTTSPB.pls 120.1 2009/08/25 10:53:53 vpalle noship $ */
3
4 /* This procedure is called from Spatial Dataset TTSP Plugin concurrent program.
5 It plugins the TTSP containg the spatial data with the help of DATAPUMP. It also imports
6 the table statistics of all spatial tables and initializes all spatial indexes in a tablespace
7 that was transported (APPS user ).
8
9 Note :
10 Initialization all spatial indexes in a tablespace that was transported for CSF user
11 can be done as follows :
12
13 Connect csf/csf pwd
14
15 and run " begin SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS; end; "
16
17 Parameters :
18
19 p_directory_path : Directory path where the dump file is located. A database directory
20 'TTS_NAVTEQ_2008' for the given path and log files created at the same location.
21
22 p_dmp_file : Name of the dump file.
23
24 p_data_file_dir : Transport data files directory
25
26 p_data_file_str : List of transport datafiles with absolute path separated by ','.
27
28 p_data_set_name : Spatial data set name.
29 */
30 PROCEDURE TTSP_PLUGIN(
31 errbuf OUT nocopy VARCHAR2,
32 retcode OUT nocopy VARCHAR2,
33 p_directory_path IN VARCHAR2,
34 p_dmp_file IN VARCHAR2,
35 p_data_file_dir IN VARCHAR2,
36 p_data_file_str IN VARCHAR2,
37 p_data_set_name IN VARCHAR2 )
38 IS
39 -- predefined error codes for concurrent programs
40 l_rc_succ CONSTANT NUMBER := 0;
41 l_rc_warn CONSTANT NUMBER := 1;
42 l_rc_err CONSTANT NUMBER := 2;
43 -- predefined error buffer output strings (replaced by translated messages)
44 l_msg_succ VARCHAR2 (80);
45 l_msg_warn VARCHAR2 (80);
46 l_msg_err VARCHAR2 (80);
47
48 BEGIN
49
50 -- Initialize message list
51 fnd_msg_pub.initialize;
52 -- get termination messages
53 fnd_message.set_name ('CSF', 'CSF_GST_DONE_SUCC');
54 l_msg_succ := fnd_message.get;
55 fnd_message.set_name ('CSF', 'CSF_GST_DONE_WARN');
56 l_msg_warn := fnd_message.get;
57 fnd_message.set_name ('CSF', 'CSF_GST_DONE_ERR');
58 l_msg_err := fnd_message.get;
59 -- Initialize API return status to success
60 retcode := l_rc_succ;
61
62 errbuf := l_msg_succ;
63
64 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, ' ' );
65
66 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, 'Start of Procedure TTSP_PLUGIN for ' || p_data_set_name );
67
68 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, '================================================ ' );
69
70 -- Create a DB directory for the directory path provided.
71 CSF_SPATIAL_TTSP_PVT.CREATE_DIRECTORY(
72 p_directory_path => p_directory_path,
73 errbuf => errbuf,
74 retcode => retcode );
75
76 -- Import the TTSP using DATA PUMP.
77 CSF_SPATIAL_TTSP_PVT.DATAPUMP_IMPORT(
78 p_dmp_file => p_dmp_file,
79 p_data_file_dir => p_data_file_dir,
80 p_data_file_str => p_data_file_str,
81 p_data_set_name => p_data_set_name,
82 errbuf => errbuf,
83 retcode => retcode );
84
85 -- Run the post TTSP import for Spatial Indexes of APPS schema.
86 CSF_SPATIAL_TTSP_PVT.INITIALIZE_INDEXES_FOR_TTS(
87 errbuf => errbuf,
88 retcode => retcode );
89
90 --Run the statistics import for TTSP objects.
91 CSF_SPATIAL_TTSP_PVT.IMPORT_TABLE_STATS(
92 p_data_set_name => p_data_set_name,
93 errbuf => errbuf,
94 retcode => retcode );
95
96 -- Run the recreate synonyms
97 CSF_SPATIAL_TTSP_PVT.CREATE_SYNONYMS(
98 p_data_set_name => p_data_set_name,
99 errbuf => errbuf,
100 retcode => retcode );
101
102 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, ' ' );
103
104 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, 'End of Procedure TTSP_PLUGIN for ' || p_data_set_name );
105
106 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, '================================================ ' );
107
108 END TTSP_PLUGIN;
109
110
111 /* This procedure is called from Spatial Dataset TTSP Plugout concurrent program.
112 It drops all the objects of TTSP being plugged out.
113 Parameter :
114 p_data_set_name : Spatial data set name.
115 */
116 PROCEDURE TTSP_PLUGOUT(
117 errbuf OUT nocopy VARCHAR2,
118 retcode OUT nocopy VARCHAR2,
119 p_data_set_name IN VARCHAR2 )
120 IS
121 -- predefined error codes for concurrent programs
122 l_rc_succ CONSTANT NUMBER := 0;
123 l_rc_warn CONSTANT NUMBER := 1;
124 l_rc_err CONSTANT NUMBER := 2;
125 -- predefined error buffer output strings (replaced by translated messages)
126 l_msg_succ VARCHAR2 (80);
127 l_msg_warn VARCHAR2 (80);
128 l_msg_err VARCHAR2 (80);
129
130 BEGIN
131
132 -- Initialize message list
133 fnd_msg_pub.initialize;
134 -- get termination messages
135 fnd_message.set_name ('CSF', 'CSF_GST_DONE_SUCC');
136 l_msg_succ := fnd_message.get;
137 fnd_message.set_name ('CSF', 'CSF_GST_DONE_WARN');
138 l_msg_warn := fnd_message.get;
139 fnd_message.set_name ('CSF', 'CSF_GST_DONE_ERR');
140 l_msg_err := fnd_message.get;
141 -- Initialize API return status to success
142 retcode := l_rc_succ;
143 errbuf := l_msg_succ;
144
145 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, ' ' );
146
147 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, 'Start of Procedure TTSP_PLUGOUT for ' || p_data_set_name );
148
149 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, '================================================ ' );
150
151 CSF_SPATIAL_TTSP_PVT.DROP_SPATIAL_TABLES(
152 p_data_set_name => p_data_set_name,
153 errbuf => errbuf,
154 retcode => retcode );
155
156 CSF_SPATIAL_TTSP_PVT.DROP_MATERIALIZED_VIEWS(
157 p_data_set_name => p_data_set_name,
158 errbuf => errbuf,
159 retcode => retcode );
160
161 CSF_SPATIAL_TTSP_PVT.DROP_TTSP_STATS(
162 p_data_set_name => p_data_set_name,
163 errbuf => errbuf,
164 retcode => retcode );
165
166 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, ' ' );
167
168 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, 'End of Procedure TTSP_PLUGOUT for ' || p_data_set_name );
169
170 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, '================================================ ' );
171
172
173 EXCEPTION
174 WHEN others THEN
175
176 retcode := 1;
177
178 errbuf := sqlerrm;
179
180 CSF_SPATIAL_TTSP_PVT.put_stream(g_output, 'TTSP_PLUGOUT PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
181
182 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, 'TTSP_PLUGOUT PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
183
184 RAISE FND_API.G_EXC_ERROR;
185
186 END TTSP_PLUGOUT;
187
188 PROCEDURE DATAPUMP_EXPORT(
189 p_dmp_file IN VARCHAR2,
190 p_table_space IN VARCHAR2,
191 p_data_set_name IN VARCHAR2,
192 errbuf OUT nocopy VARCHAR2,
193 retcode OUT nocopy VARCHAR2)
194 IS
195 BEGIN
196
197 CSF_SPATIAL_TTSP_PVT.DATAPUMP_EXPORT(
198 P_DMP_FILE => P_DMP_FILE,
199 P_TABLE_SPACE => P_TABLE_SPACE,
200 P_DATA_SET_NAME => P_DATA_SET_NAME,
201 ERRBUF => ERRBUF,
202 RETCODE => RETCODE );
203
204 EXCEPTION
205
206 WHEN others THEN
207
208 retcode := 1;
209
210 errbuf := sqlerrm;
211
212 CSF_SPATIAL_TTSP_PVT.put_stream(g_output, 'CSF_SPATIAL_TTSP_PUB.DATAPUMP_EXPORT PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
213
214 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, 'CSF_SPATIAL_TTSP_PUB.DATAPUMP_EXPORT PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
215 END DATAPUMP_EXPORT;
216
217 PROCEDURE PREPARE_INDEXES_FOR_TTS(
218 p_table_space IN VARCHAR2,
219 errbuf OUT nocopy VARCHAR2,
220 retcode OUT nocopy VARCHAR2)
221 IS
222 BEGIN
223
224 CSF_SPATIAL_TTSP_PVT.PREPARE_INDEXES_FOR_TTS(
225 p_table_space => p_table_space,
226 ERRBUF => ERRBUF,
227 RETCODE => RETCODE );
228
229 EXCEPTION
230
231 WHEN others THEN
232
233 retcode := 1;
234
235 errbuf := sqlerrm;
236
237 CSF_SPATIAL_TTSP_PVT.put_stream(g_output, 'CSF_SPATIAL_TTSP_PUB.PREPARE_INDEXES_FOR_TTS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
238
239 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, 'CSF_SPATIAL_TTSP_PUB.PREPARE_INDEXES_FOR_TTS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
240 END PREPARE_INDEXES_FOR_TTS;
241
242 /* DBMS_STATS.EXPORT_TABLE_STATS Procedure
243 ==================================
244 This procedure retrieves statistics for all tables of table space being exported and stores them in the user stat table. */
245 PROCEDURE EXPORT_TABLE_STATS(
246 p_data_set_name IN VARCHAR2,
247 errbuf OUT nocopy VARCHAR2,
248 retcode OUT nocopy VARCHAR2)
249 IS
250 BEGIN
251
252 CSF_SPATIAL_TTSP_PVT.EXPORT_TABLE_STATS(
253 P_DATA_SET_NAME => P_DATA_SET_NAME,
254 ERRBUF => ERRBUF,
255 RETCODE => RETCODE );
256
257 EXCEPTION
258
259 WHEN others THEN
260
261 retcode := 1;
262
263 errbuf := sqlerrm;
264
265 CSF_SPATIAL_TTSP_PVT.put_stream(g_output, 'CSF_SPATIAL_TTSP_PUB.EXPORT_TABLE_STATS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
266
267 CSF_SPATIAL_TTSP_PVT.put_stream(g_log, 'CSF_SPATIAL_TTSP_PUB.EXPORT_TABLE_STATS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
268 END EXPORT_TABLE_STATS;
269
270
271 END CSF_SPATIAL_TTSP_PUB;
272