[Home] [Help]
PACKAGE BODY: APPS.IEM_DBLINK_PVT
Source
1 PACKAGE BODY IEM_DBLINK_PVT as
2 /* $Header: iemvdblb.pls 115.38 2004/04/08 21:12:05 chtang shipped $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_DBLINK_PVT ';
5
6 -- Start of Comments
7 -- API name : create_link
8 -- Type : Private
9 -- Function : This procedure create a record in the table IEM_DB_CONNECTIONS
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- IN
13 -- p_api_version_number IN NUMBER Required
14 -- p_init_msg_list IN VARCHAR2
15 -- p_commit IN VARCHAR2
16 -- p_db_glname IN VARCHAR2,
17 -- p_db_username IN VARCHAR2,
18 -- p_db_password IN VARCHAR2,
19 -- p_db_server_id IN NUMBER,
20 -- p_is_admin IN VARCHAR2
21 --
22 -- OUT
23 -- x_return_status OUT VARCHAR2
24 -- x_msg_count OUT NUMBER
25 -- x_msg_data OUT VARCHAR2
26 --
27 -- Version : 1.0
28 -- Notes :
29 --
30 -- End of comments
31 -- **********************************************************
32
33
34 PROCEDURE create_link (p_api_version_number IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_db_server_id IN NUMBER,
38 p_db_glname IN VARCHAR2,
39 p_db_username IN VARCHAR2,
40 p_db_password IN VARCHAR2,
41 p_is_admin IN VARCHAR2,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2
45 ) is
46 TYPE LinkCur Is REF CURSOR;
47 l_api_name VARCHAR2(255):='create_link';
48 l_api_version_number NUMBER:=1.0;
49 l_v_id NUMBER;
50 l_num NUMBER;
51 l_count NUMBER;
52 l_dblink_count number;
53 l_is_admin VARCHAR2(20);
54 l_iem_server_rec IEM_DB_SERVERS%ROWTYPE;
55 l_statement VARCHAR2(2000);
56 l_statement1 VARCHAR2(2000) := 'none';
57 l_global_name VARCHAR2(240);
58 l_glname VARCHAR2(240);
59 l_search_string VARCHAR2(240);
60 l_grp_cnt NUMBER;
61 l_link_count NUMBER;
62 l_link_cur LinkCur;
63 l_db_password VARCHAR2(255);
64 l_schema_owner VARCHAR2(30);
65 oes_not_found EXCEPTION;
66 link_not_correct EXCEPTION;
67 db_name_invalid_a EXCEPTION;
68 db_name_invalid_spc EXCEPTION;
69 db_name_invalid EXCEPTION;
70 glname_invalid EXCEPTION;
71 login_denied EXCEPTION;
72 password_invalid EXCEPTION;
73 user_invalid EXCEPTION;
74 duplicate_db_link EXCEPTION;
75 protocol_invalid EXCEPTION;
76 sid_invalid EXCEPTION;
77 tns_no_listener EXCEPTION;
78 host_invalid EXCEPTION;
79 host_invalid1 EXCEPTION;
80 db_conn_desc_invalid EXCEPTION;
81 db_link_exist EXCEPTION;
82
83 PRAGMA EXCEPTION_INIT(db_name_invalid_a , -002083);
84 PRAGMA EXCEPTION_INIT(db_name_invalid_spc , -00933);
85 PRAGMA EXCEPTION_INIT(db_name_invalid , -0911);
86 PRAGMA EXCEPTION_INIT(glname_invalid, -01729);
87 PRAGMA EXCEPTION_INIT(login_denied , -01017);
88 PRAGMA EXCEPTION_INIT(password_invalid , -00988);
89 PRAGMA EXCEPTION_INIT(user_invalid , -06561);
90 PRAGMA EXCEPTION_INIT(duplicate_db_link , -02011);
91 PRAGMA EXCEPTION_INIT(protocol_invalid , -12538);
92 PRAGMA EXCEPTION_INIT(sid_invalid , -12505);
93 PRAGMA EXCEPTION_INIT(tns_no_listener , -12541);
94 PRAGMA EXCEPTION_INIT(host_invalid , -12535);
95 PRAGMA EXCEPTION_INIT(host_invalid1 , -12545);
96 PRAGMA EXCEPTION_INIT(db_conn_desc_invalid , -02019);
97
98 BEGIN
99 -- Standard Start of API savepoint
100 -- SAVEPOINT CREATE_LINK_PVT;
101 -- Standard call to check for call compatibility.
102 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
103 p_api_version_number,
104 l_api_name,
105 G_PKG_NAME)
106 THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109 -- Initialize message list if p_init_msg_list is set to TRUE.
110 IF FND_API.to_Boolean( p_init_msg_list )
111 THEN
112 FND_MSG_PUB.initialize;
113 END IF;
114 -- Initialize API return status to SUCCESS
115 x_return_status := FND_API.G_RET_STS_SUCCESS;
116
117 IF (p_db_server_id = 0 or p_db_server_id = NULL) THEN
118 raise oes_not_found;
119 END IF;
120
121 select oracle_username into l_schema_owner from fnd_oracle_userid where read_only_flag = 'U';
122
123 select count(*) into l_dblink_count from iem_db_connections where db_username=p_db_username and db_server_id=p_db_server_id;
124
125 if (l_dblink_count <> 0) then
126 raise db_link_exist;
127 end if;
128
129 IF lower(p_db_username) <> 'apps' THEN
130 select * INTO l_iem_server_rec from IEM_DB_SERVERS where DB_SERVER_ID = p_db_server_id;
131
132 l_v_id := DBMS_SQL.OPEN_CURSOR;
133
134 IF (p_db_username = 'oo') THEN
135
136 select CONCAT(l_iem_server_rec.service_name, '@appsto_oo') into l_glname from DUAL;
137
138 l_search_string := l_iem_server_rec.service_name || '%' || '@appsto_oo';
139 select count(*)into l_link_count from all_db_links where upper(owner)=upper(l_schema_owner) and db_link like UPPER(l_search_string);
140
141 if (l_link_count <> 0) then
142 l_statement1 := 'DROP DATABASE LINK ' || l_glname;
143 end if;
144
145 l_statement := 'CREATE DATABASE LINK '||l_glname ||
146 ' CONNECT TO '||p_db_username||' IDENTIFIED BY '||p_db_password||
147 ' USING ''(DESCRIPTION=(ADDRESS=(PROTOCOL='||l_iem_server_rec.protocol||
148 ')(HOST='||l_iem_server_rec.hostname||
149 ')(PORT='||l_iem_server_rec.port ||
150 '))(CONNECT_DATA=(SID='||l_iem_server_rec.sid ||
151 ')))''';
152
153 ELSIF (p_db_username = 'oraoffice') THEN
154
155 select CONCAT(l_iem_server_rec.service_name, '@appsto_ora') into l_glname from DUAL;
156
157 l_search_string := l_iem_server_rec.service_name || '%' || '@appsto_ora';
158 select count(*)into l_link_count from all_db_links where upper(owner)=upper(l_schema_owner) and db_link like UPPER(l_search_string);
159
160 if (l_link_count <> 0) then
161 l_statement1 := 'DROP DATABASE LINK ' || l_glname;
162 end if;
163
164 l_statement := 'CREATE DATABASE LINK '||l_glname ||
165 ' CONNECT TO '||p_db_username||' IDENTIFIED BY '||p_db_password||
166 ' USING ''(DESCRIPTION=(ADDRESS=(PROTOCOL='||l_iem_server_rec.protocol||
167 ')(HOST='||l_iem_server_rec.hostname||
168 ')(PORT='||l_iem_server_rec.port ||
169 '))(CONNECT_DATA=(SID='||l_iem_server_rec.sid ||
170 ')))''';
171 END IF;
172
173 if (l_statement1 <> 'none') then
174 DBMS_SQL.PARSE(l_v_id, l_statement1, DBMS_SQL.native);
175 l_num := DBMS_SQL.EXECUTE(l_v_id);
176 end if;
177
178 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
179 l_num := DBMS_SQL.EXECUTE(l_v_id);
180 DBMS_SQL.CLOSE_CURSOR(l_v_id);
181
182 -- check if db link created successfully oherwise drop the link
183 -- l_v_id := DBMS_SQL.OPEN_CURSOR;
184 l_statement := 'SELECT global_name FROM global_name@'||l_glname;
185 OPEN l_link_cur for l_statement;
186 LOOP
187 FETCH l_link_cur INTO l_global_name;
188 EXIT WHEN l_link_cur%notfound;
189 END LOOP;
190 close l_link_cur;
191 END IF;
192
193 IF (p_db_username = 'oo') then
194 l_is_admin :='A';
195 l_db_password := p_db_password;
196 ELSIF (p_db_username = 'oraoffice') then
197 l_is_admin :='P';
198 l_db_password := p_db_password;
199 else
200 l_is_admin :='O';
201 l_db_password := 'welcome';
202 end if;
203
204 IF (l_glname IS NULL) THEN
205 l_glname:=p_db_glname;
206 END IF;
207
208 -- Standard Start of API savepoint
209 SAVEPOINT CREATE_LINK_PVT;
210
211 select count(*) into l_count from iem_db_connections where UPPER(db_link) = UPPER(l_glname) and db_server_id = p_db_server_id;
212
213 if lower(l_global_name) <> lower(l_iem_server_rec.service_name) then
214 raise link_not_correct;
215 elsif l_count > 0 then
216 raise duplicate_db_link;
217 else
218 IEM_DB_CONNECTIONS_PVT.create_item(
219 p_api_version_number => 1.0,
220 p_db_link => l_glname,
221 p_db_username => p_db_username,
222 p_db_password => l_db_password,
223 p_db_server_id => p_db_server_id,
224 p_is_admin => l_is_admin,
225 x_msg_count => x_msg_count,
226 x_return_status => x_return_status,
227 x_msg_data => x_msg_data);
228 end if;
229
230 -- Standard Check Of p_commit.
231 IF FND_API.To_Boolean(p_commit) THEN
232 COMMIT WORK;
233 END IF;
234 -- Standard callto get message count and if count is 1, get message info.
235 FND_MSG_PUB.Count_And_Get
236 ( p_count => x_msg_count,
237 p_data => x_msg_data
238 );
239 EXCEPTION
240 WHEN oes_not_found THEN
241 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_OES_NOT_FOUND');
242 FND_MSG_PUB.Add;
243 x_return_status := FND_API.G_RET_STS_ERROR ;
244 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
245 -- database link creation cannot be rolled back, it must be dropped manually
246 WHEN link_not_correct THEN
247
248 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
249
250 if l_count <> 0 then
251 l_v_id := DBMS_SQL.OPEN_CURSOR;
252 l_statement := 'DROP DATABASE LINK '||l_glname;
253 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
254 l_num := DBMS_SQL.EXECUTE(l_v_id);
255 DBMS_SQL.CLOSE_CURSOR(l_v_id);
256 end if;
257 -- ROLLBACK TO CREATE_LINK_PVT;
258 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_GLNAME_INVALID');
259 FND_MSG_PUB.Add;
260 x_return_status := FND_API.G_RET_STS_ERROR ;
261 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
262
263 WHEN db_name_invalid_a THEN
264
265 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
266
267 if l_count <> 0 then
268 l_v_id := DBMS_SQL.OPEN_CURSOR;
269 l_statement := 'DROP DATABASE LINK '||l_glname;
270 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
271 l_num := DBMS_SQL.EXECUTE(l_v_id);
272 DBMS_SQL.CLOSE_CURSOR(l_v_id);
273 end if;
274
275 -- ROLLBACK TO CREATE_LINK_PVT;
276 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_GLNAME_INVALID');
277 FND_MSG_PUB.Add;
278 x_return_status := FND_API.G_RET_STS_ERROR ;
279 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
280 WHEN db_name_invalid THEN
281 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
282
283 if l_count <> 0 then
284 l_v_id := DBMS_SQL.OPEN_CURSOR;
285 l_statement := 'DROP DATABASE LINK '||l_glname;
286 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
287 l_num := DBMS_SQL.EXECUTE(l_v_id);
288 DBMS_SQL.CLOSE_CURSOR(l_v_id);
289 end if;
290
291 -- ROLLBACK TO CREATE_LINK_PVT;
292 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_PSWD_GLNAME_INVALID');
293 FND_MSG_PUB.Add;
294 x_return_status := FND_API.G_RET_STS_ERROR ;
295 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
296 WHEN db_name_invalid_spc THEN
297 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
298
299 if l_count <> 0 then
300 l_v_id := DBMS_SQL.OPEN_CURSOR;
301 l_statement := 'DROP DATABASE LINK '||l_glname;
302 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
303 l_num := DBMS_SQL.EXECUTE(l_v_id);
304 DBMS_SQL.CLOSE_CURSOR(l_v_id);
305 end if;
306
307 -- ROLLBACK TO CREATE_LINK_PVT;
308 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_GLNAME_INVALID');
309 FND_MSG_PUB.Add;
310 x_return_status := FND_API.G_RET_STS_ERROR ;
311 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
312
313 WHEN glname_invalid THEN
314
315 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
316
317 if l_count <> 0 then
318 l_v_id := DBMS_SQL.OPEN_CURSOR;
319 l_statement := 'DROP DATABASE LINK '||l_glname;
320 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
321 l_num := DBMS_SQL.EXECUTE(l_v_id);
322 DBMS_SQL.CLOSE_CURSOR(l_v_id);
323 end if;
324
325 -- ROLLBACK TO CREATE_LINK_PVT;
326 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_GLNAME_INVALID');
327 FND_MSG_PUB.Add;
328 x_return_status := FND_API.G_RET_STS_ERROR ;
329 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
330 WHEN login_denied THEN
331 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
332
333 if l_count <> 0 then
334 l_v_id := DBMS_SQL.OPEN_CURSOR;
335 l_statement := 'DROP DATABASE LINK '||l_glname;
336 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
337 l_num := DBMS_SQL.EXECUTE(l_v_id);
338 DBMS_SQL.CLOSE_CURSOR(l_v_id);
339 end if;
340
341 -- ROLLBACK TO CREATE_LINK_PVT;
342 FND_MESSAGE.SET_NAME('IEM','IEM_DBLINK_LOGIN_DENIED');
343 FND_MSG_PUB.Add;
344 x_return_status := FND_API.G_RET_STS_ERROR ;
345 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
346
347 WHEN password_invalid THEN
348 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
349
350 if l_count <> 0 then
351 l_v_id := DBMS_SQL.OPEN_CURSOR;
352 l_statement := 'DROP DATABASE LINK '||l_glname;
353 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
354 l_num := DBMS_SQL.EXECUTE(l_v_id);
355 DBMS_SQL.CLOSE_CURSOR(l_v_id);
356 end if;
357
358 -- ROLLBACK TO CREATE_LINK_PVT;
359 FND_MESSAGE.SET_NAME('IEM','IEM_DBLINK_LOGIN_DENIED');
360 FND_MSG_PUB.Add;
361 x_return_status := FND_API.G_RET_STS_ERROR ;
362 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
363
364 WHEN user_invalid THEN
365 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
366
367 if l_count <> 0 then
368 l_v_id := DBMS_SQL.OPEN_CURSOR;
369 l_statement := 'DROP DATABASE LINK '||l_glname;
370 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
371 l_num := DBMS_SQL.EXECUTE(l_v_id);
372 DBMS_SQL.CLOSE_CURSOR(l_v_id);
373 end if;
374
375 -- ROLLBACK TO CREATE_LINK_PVT;
376 FND_MESSAGE.SET_NAME('IEM','IEM_DBLINK_LOGIN_DENIED');
377 FND_MSG_PUB.Add;
378 x_return_status := FND_API.G_RET_STS_ERROR ;
379 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
380
381 WHEN duplicate_db_link THEN
382
383 -- don't drop database link as rollback as this drop the links for the existing link.
384 -- ROLLBACK TO CREATE_LINK_PVT;
385 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_DB_LINK');
386 FND_MSG_PUB.Add;
387 x_return_status := FND_API.G_RET_STS_ERROR ;
388 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
389
390 WHEN db_link_exist THEN
391
392 -- don't drop database link as rollback as this drop the links for the existing link.
393 -- ROLLBACK TO CREATE_LINK_PVT;
394 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_DB_LINK');
395 FND_MSG_PUB.Add;
396 x_return_status := FND_API.G_RET_STS_ERROR ;
397 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
398
399 WHEN protocol_invalid THEN
400 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
401
402 if l_count <> 0 then
403 l_v_id := DBMS_SQL.OPEN_CURSOR;
404 l_statement := 'DROP DATABASE LINK '||l_glname;
405 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
406 l_num := DBMS_SQL.EXECUTE(l_v_id);
407 DBMS_SQL.CLOSE_CURSOR(l_v_id);
408 end if;
409
410 -- ROLLBACK TO CREATE_LINK_PVT;
411 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_PROTOCOL_NOT_VALID');
412 FND_MSG_PUB.Add;
413 x_return_status := FND_API.G_RET_STS_ERROR ;
414 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
415 WHEN sid_invalid THEN
416 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
417
418 if l_count <> 0 then
419 l_v_id := DBMS_SQL.OPEN_CURSOR;
420 l_statement := 'DROP DATABASE LINK '||l_glname;
421 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
422 l_num := DBMS_SQL.EXECUTE(l_v_id);
423 DBMS_SQL.CLOSE_CURSOR(l_v_id);
424 end if;
425
426 -- ROLLBACK TO CREATE_LINK_PVT;
427 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_SID_NOT_VALID');
428 FND_MSG_PUB.Add;
429 x_return_status := FND_API.G_RET_STS_ERROR ;
430 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
431 WHEN tns_no_listener THEN
432 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
433
434 if l_count <> 0 then
435 l_v_id := DBMS_SQL.OPEN_CURSOR;
436 l_statement := 'DROP DATABASE LINK '||l_glname;
437 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
438 l_num := DBMS_SQL.EXECUTE(l_v_id);
439 DBMS_SQL.CLOSE_CURSOR(l_v_id);
440 end if;
441
442 -- ROLLBACK TO CREATE_LINK_PVT;
443 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_TNS_NO_LISTENER');
444 FND_MSG_PUB.Add;
445 x_return_status := FND_API.G_RET_STS_ERROR ;
446 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
447 WHEN host_invalid THEN
448 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
449
450 if l_count <> 0 then
451 l_v_id := DBMS_SQL.OPEN_CURSOR;
452 l_statement := 'DROP DATABASE LINK '||l_glname;
453 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
454 l_num := DBMS_SQL.EXECUTE(l_v_id);
455 DBMS_SQL.CLOSE_CURSOR(l_v_id);
456 end if;
457
458 -- ROLLBACK TO CREATE_LINK_PVT;
459 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_HOST_INVALID');
460 FND_MSG_PUB.Add;
461 x_return_status := FND_API.G_RET_STS_ERROR ;
462 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
463
464 WHEN host_invalid1 THEN
465 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
466
467 if l_count <> 0 then
468 l_v_id := DBMS_SQL.OPEN_CURSOR;
469 l_statement := 'DROP DATABASE LINK '||l_glname;
470 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
471 l_num := DBMS_SQL.EXECUTE(l_v_id);
472 DBMS_SQL.CLOSE_CURSOR(l_v_id);
473 end if;
474
475 -- ROLLBACK TO CREATE_LINK_PVT;
476 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_HOST_INVALID');
477 FND_MSG_PUB.Add;
478 x_return_status := FND_API.G_RET_STS_ERROR ;
479 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
480
481 WHEN db_conn_desc_invalid THEN
482 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
483
484 if l_count <> 0 then
485 l_v_id := DBMS_SQL.OPEN_CURSOR;
486 l_statement := 'DROP DATABASE LINK '||l_glname;
487 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
488 l_num := DBMS_SQL.EXECUTE(l_v_id);
489 DBMS_SQL.CLOSE_CURSOR(l_v_id);
490 end if;
491
492 -- ROLLBACK TO CREATE_LINK_PVT;
493 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_DBCONN_DESC_INVALID');
494 FND_MSG_PUB.Add;
495 x_return_status := FND_API.G_RET_STS_ERROR ;
496 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
497
498 WHEN FND_API.G_EXC_ERROR THEN
499 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
500
501 if l_count <> 0 then
502 l_v_id := DBMS_SQL.OPEN_CURSOR;
503 l_statement := 'DROP DATABASE LINK '||l_glname;
504 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
505 l_num := DBMS_SQL.EXECUTE(l_v_id);
506 DBMS_SQL.CLOSE_CURSOR(l_v_id);
507 end if;
508
509 ROLLBACK TO CREATE_LINK_PVT;
510 x_return_status := FND_API.G_RET_STS_ERROR ;
511 FND_MSG_PUB.Count_And_Get
512 ( p_count => x_msg_count,
513 p_data => x_msg_data
514 );
515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
516 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
517
518 if l_count <> 0 then
519 l_v_id := DBMS_SQL.OPEN_CURSOR;
520 l_statement := 'DROP DATABASE LINK '||l_glname;
521 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
522 l_num := DBMS_SQL.EXECUTE(l_v_id);
523 DBMS_SQL.CLOSE_CURSOR(l_v_id);
524 end if;
525
526 ROLLBACK TO CREATE_LINK_PVT;
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528 FND_MSG_PUB.Count_And_Get
529 ( p_count => x_msg_count,
530 p_data => x_msg_data
531 );
532 WHEN OTHERS THEN
533 -- bugfixed for #1921152 Aug 6, 2001
534
535 -- if SQLCODE <> -06561 then -- invalid user and password
536
537 select count(*) into l_count from all_db_links where upper(owner)=upper(l_schema_owner) and UPPER(db_link) = UPPER(l_glname);
538
539 if l_count <> 0 then
540 l_v_id := DBMS_SQL.OPEN_CURSOR;
541 l_statement := 'DROP DATABASE LINK '||l_glname;
542 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
543 l_num := DBMS_SQL.EXECUTE(l_v_id);
544 DBMS_SQL.CLOSE_CURSOR(l_v_id);
545 end if;
546
547 -- end if;
548 -- ROLLBACK TO CREATE_LINK_PVT;
549 -- FND_MESSAGE.SET_NAME('IEM','IEM_LINK_LOGIN_DENIED');
550 -- FND_MSG_PUB.Add;
551 x_return_status := FND_API.G_RET_STS_ERROR ;
552 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
553 IF FND_MSG_PUB.Check_Msg_Level
554 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
555 THEN
556 FND_MSG_PUB.Add_Exc_Msg
557 ( G_PKG_NAME,
558 l_api_name
559 );
560 END IF;
561 FND_MSG_PUB.Count_And_Get
562 ( p_count => x_msg_count,
563 p_data => x_msg_data
564 );
565
566 END create_link;
567
568 -- Start of Comments
569 -- API name : delete_link
570 -- Type : Private
571 -- Function : This procedure delete a record in the table IEM_DB_CONNECTIONS
572 -- Pre-reqs : None.
573 -- Parameters :
574 -- IN
575 -- p_api_version_number IN NUMBER Required
576 -- p_init_msg_list IN VARCHAR2
577 -- p_commit IN VARCHAR2
578 -- p_db_connection_id in numbe
579 --
580 -- OUT
581 -- x_return_status OUT VARCHAR2
582 -- x_msg_count OUT NUMBER
583 -- x_msg_data OUT VARCHAR2
584 --
585 -- Version : 1.0
586 -- Notes :
587 --
588 -- End of comments
589 -- **********************************************************
590
591 PROCEDURE delete_link (p_api_version_number IN NUMBER,
592 p_init_msg_list IN VARCHAR2,
593 p_commit IN VARCHAR2,
594 p_db_connection_id IN NUMBER,
595 x_return_status OUT NOCOPY VARCHAR2,
596 x_msg_count OUT NOCOPY NUMBER,
597 x_msg_data OUT NOCOPY VARCHAR2
598 ) is
599 TYPE LinkCur Is REF CURSOR;
600 l_api_name VARCHAR2(255):='delete_link';
601 l_api_version_number NUMBER:=1.0;
602 l_dblink VARCHAR2(128);
603 l_iem_dbconn_rec IEM_DB_CONNECTIONS%ROWTYPE;
604 l_v_id NUMBER;
605 l_num NUMBER;
606 l_statement VARCHAR2(2000);
607 link_does_not_exist EXCEPTION;
608 l_grp_cnt NUMBER;
609 l_link_cur LinkCur;
610 PRAGMA EXCEPTION_INIT(link_does_not_exist , -02024);
611 BEGIN
612 -- Standard Start of API savepoint
613 --SAVEPOINT delete_link_pvt;
614 -- Standard call to check for call compatibility.
615 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
616 p_api_version_number,
617 l_api_name,
618 G_PKG_NAME)
619 THEN
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621 END IF;
622 -- Initialize message list if p_init_msg_list is set to TRUE.
623 IF FND_API.to_Boolean( p_init_msg_list )
624 THEN
625 FND_MSG_PUB.initialize;
626 END IF;
627 -- Initialize API return status to SUCCESS
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
630 -- Standard Start of API savepoint
631 -- SAVEPOINT delete_link_pvt;
632
633 select * INTO l_iem_dbconn_rec from IEM_DB_CONNECTIONS where DB_CONNECTION_ID = p_db_connection_id;
634 -- select db_link into l_dblink from iem_db_connections where db_connection_id = p_db_connection_id;
635
636 IF (l_iem_dbconn_rec.is_admin <> 'O') then
637
638 l_v_id := DBMS_SQL.OPEN_CURSOR;
639
640 l_statement := 'DROP DATABASE LINK '||l_iem_dbconn_rec.db_link;
641
642 DBMS_SQL.PARSE(l_v_id, l_statement, DBMS_SQL.native);
643
644 l_num := DBMS_SQL.EXECUTE(l_v_id);
645
646 DBMS_SQL.CLOSE_CURSOR(l_v_id);
647
648 END IF;
649 -- Standard Start of API savepoint
650 SAVEPOINT delete_link_pvt;
651 -- check if db link dropped successfully
652 IEM_DB_CONNECTIONS_PVT.delete_item(
653 p_api_version_number => 1.0,
654 p_db_conn_id => p_db_connection_id,
655 x_msg_count => x_msg_count,
656 x_return_status => x_return_status,
657 x_msg_data => x_msg_data);
658
659 -- Standard Check Of p_commit.
660 IF FND_API.To_Boolean(p_commit) THEN
661 COMMIT WORK;
662 END IF;
663 -- Standard callto get message count and if count is 1, get message info.
664 FND_MSG_PUB.Count_And_Get
665 ( p_count => x_msg_count,
666 p_data => x_msg_data
667 );
668 EXCEPTION
669 WHEN link_does_not_exist THEN
670 --bugfix 1944746-----------
671 -- If the link does not exist, remove from iem_db_connection directly to avoid garbage data
672
673 IEM_DB_CONNECTIONS_PVT.delete_item(
674 p_api_version_number => 1.0,
675 p_db_conn_id => p_db_connection_id,
676 x_msg_count => x_msg_count,
677 x_return_status => x_return_status,
678 x_msg_data => x_msg_data);
679
680 -- Standard Check Of p_commit.
681 IF FND_API.To_Boolean(p_commit) THEN
682 COMMIT WORK;
683 END IF;
684 --end bugfix1944746---------------------
685 WHEN FND_API.G_EXC_ERROR THEN
686 ROLLBACK TO delete_link_pvt;
687 x_return_status := FND_API.G_RET_STS_ERROR ;
688 FND_MSG_PUB.Count_And_Get
689 ( p_count => x_msg_count,
690 p_data => x_msg_data
691 );
692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
693 ROLLBACK TO delete_link_pvt;
694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
695 FND_MSG_PUB.Count_And_Get
696 ( p_count => x_msg_count,
697 p_data => x_msg_data
698 );
699 WHEN OTHERS THEN
700 FND_MESSAGE.SET_NAME('IEM','IEM_LINK_DOES_NOT_EXIST');
701 FND_MSG_PUB.Add;
702 x_return_status := FND_API.G_RET_STS_ERROR ;
703 --FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
704 --ROLLBACK TO delete_link_pvt;
705 --x_return_status := FND_API.G_RET_STS_ERROR;
706 IF FND_MSG_PUB.Check_Msg_Level
707 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
708 THEN
709 FND_MSG_PUB.Add_Exc_Msg
710 ( G_PKG_NAME ,
711 l_api_name
712 );
713 END IF;
714 FND_MSG_PUB.Count_And_Get
715 ( p_count => x_msg_count ,
716 p_data => x_msg_data
717 );
718
719 END delete_link;
720
721 END IEM_DBLINK_PVT;