1 Package Body AMS_DISCOVERER_PVT AS
2 /* $Header: amsvdisb.pls 120.1 2005/12/15 17:48:58 musman noship $ */
3
4 /*==========================================================================+
5 | PROCEDURES. |
6 | Create_Discoverer_Url. |
7 | get_source_type_code |
8 | EUL_TRIGGER$POST_SAVE_DOCUMENT |
9 | Search_SQL_string |
10 +==========================================================================*/
11
12 g_pkg_name CONSTANT VARCHAR2(30):='AMS_DISCOVERER_PVT';
13 g_file_name CONSTANT VARCHAR2(12):='amsvdisb.pls';
14
15
16 --
17 -- Foreward Procedure Declarations
18 --
19 AMS_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
20 AMS_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
21 AMS_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
22
23 PROCEDURE get_source_type_code (
24 p_workbook_name IN VARCHAR2,
25 p_worksheet_name IN VARCHAR2,
26 x_source_type_code OUT NOCOPY VARCHAR2
27 );
28
29
30 --
31 -- Procedure Bodies
32 --
33
34 -----------------------------------------------------------------------------
35 -- Procedure
36 -- Create_Discoverer_Url
37
38 -- PURPOSE
39 -- Creates a URL which will launch Web Discoverer.
40 --
41 -- PARAMETERS
42
43 -- NOTES
44 -- created yxliu 14-Mar-2001
45 -----------------------------------------------------------------------------
46 PROCEDURE Create_Discoverer_Url(p_text IN VARCHAR2,
47 p_application_id IN NUMBER,
48 p_responsibility_id IN NUMBER,
49 p_function_id IN NUMBER,
50 p_target IN VARCHAR2,
51 p_session_id IN NUMBER,
52 x_discoverer_url OUT NOCOPY VARCHAR2
53 )
54 IS
55 l_api_name CONSTANT VARCHAR2(30) := 'Create_Discoverer_Url';
56 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
57
58
59 BEGIN
60
61 IF (AMS_DEBUG_HIGH_ON) THEN
62
63
64
65 AMS_Utility_PVT.debug_message(l_full_name||': start');
66
67 END IF;
68
69 /*
70 vbhandar commented to fix bug 2665683
71 x_discoverer_url := ORACLEAPPS.CREATERFLINK(
72 p_text => p_text
73 ,p_application_id => p_application_id
74 ,p_responsibility_id => p_responsibility_id
75 ,p_security_group_id => 0
76 ,p_function_id => p_function_id
77 ,p_target => p_target
78 ,p_session_id => p_session_id);
79 */
80 x_discoverer_url := ICX_SEC.CREATERFURL (
81 p_function_name => null,
82 p_function_id => p_function_id,
83 p_application_id => p_application_id,
84 p_responsibility_id => p_responsibility_id,
85 p_security_group_id => 0,
86 p_session_id => p_session_id
87 );
88
89
90 IF (AMS_DEBUG_HIGH_ON) THEN
91
92
93
94
95
96 AMS_Utility_PVT.debug_message(l_full_name||': end');
97
98
99 END IF;
100
101 END;
102
103 -----------------------------------------------------------------------------
104 -- Function
105 -- EUL_TRIGGER$POST_SAVE_DOCUMENT
106
107 -- PURPOSE
108 -- 1. This Function is used by Oracle Discoverer to save a WorkSheets SQL
109 -- to The AMS_DISCOVERER_SQL table.
110
111 -- 2. This Function must be Registered as a valid Function using Oracle
112 -- Discoverer's Administration Edition before any WorkBook SQL will be
113 -- saved.
114
115 -- NOTES
116 -- choang - 24-jun-2002 - new logic for post save document
117 -- - get source_type_code for workbook/worksheet
118 -- - if source_type_code exists, use source_type_code for current record
119 -- - else merge sql for workbook/worksheet
120 -- - search for source_type_code in merged sql
121 -- - if source_type_code exists, update all records for workbook/worksheet
122 -- - else continue
123 --
124 -- HISTORY
125 -- 13-Apr-2001 yxliu copied from AMS_DiscovererSQL_PVT
126 -- 24-Jun-2002 choang added logic to populate source_type_code
127 --
128 -- End of Comments
129 -----------------------------------------------------------------------------
130 FUNCTION EUL_TRIGGER$POST_SAVE_DOCUMENT (
131 p_workbookowner IN VARCHAR2,
132 p_workbookname IN VARCHAR2,
133 p_worksheetname IN VARCHAR2,
134 p_sequence IN NUMBER,
135 p_sqlsegment IN VARCHAR2
136 )
137 RETURN NUMBER
138 IS
139
140 l_max_sequence_number number;
141
142 -- Declare program variables
143 l_sqlerrm varchar2(600);
144 l_sqlcode varchar2(100);
145
146 CURSOR c_get_seq IS
147 SELECT ams_discoverer_sql_s.NEXTVAL
148 FROM DUAL;
149
150 l_discoverer_sql_id NUMBER;
151
152 -- choang - 24-jun-2002
153 -- get the start id for this workbook+worksheet
154 -- get the largest sequence_order and source_type_code
155 -- to determine if the requested worksheet is a replacement
156 -- or a continuation, and if source type code is defined
157 CURSOR c_disco (p_workbook_name IN VARCHAR2, p_worksheet_name IN VARCHAR2) IS
158 SELECT MIN (discoverer_sql_id), MAX (sequence_order), MAX (source_type_code)
159 FROM ams_discoverer_sql
160 WHERE workbook_name = p_workbook_name
161 AND worksheet_name = p_worksheet_name
162 ;
163
164 l_source_type_code ams_discoverer_sql.source_type_code%TYPE;
165 BEGIN
166 OPEN c_disco (p_workbookname, p_worksheetname);
167 FETCH c_disco INTO l_discoverer_sql_id, l_max_sequence_number, l_source_type_code;
168 CLOSE c_disco;
169
170 /* choang - 24-jun-2002 - replaced with new logic
171 --checking for an existing set of entries for this workbook and worksheet combination.
172 select max(sequence_order)
173 into l_max_sequence_number
174 from ams_discoverer_sql
175 where workbook_name = P_WorkBookName
176 and WORKSHEET_NAME = P_WorkSheetName;
177 */
178
179 /* choang - 24-jun-2002 - replaced to use one cursor for performance
180 -- checking the start discoverer_sql_id for this workbook and worksheet
181 -- combination
182 select min(discoverer_sql_id)
183 into l_start_id
184 from ams_discoverer_sql
185 where workbook_name = P_WorkBookName
186 and worksheet_name = P_WorkSheetName;
187 */
188
189 --if the workbook and worksheet combination exists in the AMS_DISCOVERER_SQL table
190 --then delete all entries , then we can insert the new records for the newest version
191 --of this workbook - worksheet.
192 IF ((l_max_sequence_number >= p_sequence) OR (l_max_sequence_number = 0)) THEN
193 DELETE FROM ams_discoverer_sql
194 WHERE workbook_name = p_workbookname
195 AND WORKSHEET_NAME = p_worksheetname;
196 END IF;
197
198 -- start new logic to update source type code
199
200 -- if updating the same workbook + worksheet, we
201 -- need to preserve the disco sql id which is
202 -- associated to marketing objects. the logic:
203 -- - new worksheet = p_sequence in 0,1 and disco sql id is null
204 -- - updated worksheet = p_sequence in 0,1 and disco sql id not null
205 -- - wrapped long sql = p_sequence > 1
206 IF p_sequence IN (0, 1) THEN
207 IF l_discoverer_sql_id IS NULL THEN
208 OPEN c_get_seq;
209 FETCH c_get_seq INTO l_discoverer_sql_id;
210 CLOSE c_get_seq;
211 END IF;
212 ELSE
213 OPEN c_get_seq;
214 FETCH c_get_seq INTO l_discoverer_sql_id;
215 CLOSE c_get_seq;
216 END IF;
217
218 INSERT INTO ams_discoverer_sql (
219 discoverer_sql_id,
220 workbook_owner_name,
221 workbook_name,
222 worksheet_name,
223 sequence_order,
224 last_update_date,
225 last_updated_by,
226 creation_date,
227 created_by,
228 sql_string,
229 source_type_code
230 )
231 VALUES (
232 l_discoverer_sql_id,
233 p_workbookowner,
234 p_workbookname,
235 p_worksheetname,
236 p_sequence,
237 SYSDATE,
238 FND_GLOBAL.user_id,
239 SYSDATE,
240 FND_GLOBAL.user_id,
241 p_sqlsegment,
242 l_source_type_code
243 );
244
245 -- update needed when SQL spans multiple
246 -- segments.
247 get_source_type_code (
248 p_workbook_name => p_workbookname,
249 p_worksheet_name => p_worksheetname,
250 x_source_type_code => l_source_type_code
251 );
252
253 IF l_source_type_code IS NOT NULL THEN
254 UPDATE ams_discoverer_sql
255 SET source_type_code = l_source_type_code
256 WHERE workbook_name = p_workbookname
257 AND worksheet_name = p_worksheetname
258 AND (source_type_code <> l_source_type_code OR source_type_code IS NULL)
259 ;
260 END IF;
261
262 RETURN (0);
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 l_sqlerrm := SQLERRM;
267 l_sqlcode := SQLCODE;
268 RETURN (1);
269 END EUL_TRIGGER$POST_SAVE_DOCUMENT;
270
271 -----------------------------------------------------------------------------
272 -- Procedure
273 -- Search_SQL_string
274
275 -- PURPOSE
276 -- 1. Will search for the p_search_string variable in the set of strings
277 -- which compose a workbook SQL statement and are stored in the
278 -- AMS_DISCOVERER_SQL table.
279
280 -- 2. p_found will return FND_API.G_TRUE if the string has been found.
281
282 -- 3. p_found_in_str returns the number of the sql string in which the search
283 -- string was found. each SQL string is 2000 characters in length.
284
285 -- 4. p_position returns the position in the string where the first character
286 -- in the search string was found.
287
288 -- 5. p_overflow wil return the number of characters which contain part of
289 -- the search string in the overflow string if the searched for string
290 -- spans two SQL strings.
291
292 -- 6. p_max_search_len restricts the number of characters to search on from
293 -- the set of sql strings.
294
295 -- NOTES
296
297 -- HISTORY
298 -- 04/13/2001 yxliu copied from AMS_DiscovererSQL_PVT
299 -- End of Comments
300 -----------------------------------------------------------------------------
301 Procedure Search_SQL_string(p_search_string in varchar2,
302 p_workbook_name in varchar2,
303 p_worksheet_name in varchar2,
304 p_max_search_len in number default NULL,
305 x_found out nocopy varchar2 ,
306 x_found_in_str out nocopy number,
307 x_position out nocopy number,
308 x_overflow out nocopy number)
309 IS
310
311 l_sql_table t_SQLtable;
312
313 --the total number of strings for a sql statement.
314 l_str_count number := 0;
315
316 --the current search position in the string.
317 l_str_pos number := 0;
318
319 --the length of the current sql string.
320 l_str_len number := 0;
321
322 --the total number of characters read from the set of sql strings so far.
323 l_total_str_len number := 0;
324
325 --this flag indicates that no more fetched of sql strings are to be made
326 --because the max. number of characters have been read.
327 l_last_fetch varchar2(1);
328
329 --this flag is set to 'Y' when processing on the last sql string has started.
330 l_last_str varchar2(1) := 'N';
331
332 --temporary substring holder used if a p_max_search_len has been reached.
333 l_tmp_str varchar2(2000);
334
335 --if a sub string has to be constructed because of overflow into a second
336 --string then it is stored in this variable.
337 l_substr varchar2(1000);
338
339 --the length of the sub string.
340 l_substr_len number := 0;
341
342 --set to FND_API.G_TRUE when the first character of the searched for string
343 --has been found.
344 l_first_char_found varchar2(1);
345
346
347 --the first character being searched for.
348 l_first_char varchar2(1);
349
350 --the position that the first character was found in the current sql string.
351 l_first_char_pos number := 0;
352
353 --the length of the string being searched for.
354 l_search_str_len number := 0;
355
356 --the search string without its first character.
357 l_search_sub_str varchar2(2000);
358
359
360
361 l_sqlerrm varchar2(600);
362 l_sqlcode varchar2(100);
363
364 Cursor C_SQL_string IS
365 Select Sql_String
366 From Ams_Discoverer_SQL
367 Where Workbook_name = p_workbook_name
368 And Worksheet_name = p_worksheet_name
369 Order by Sequence_Order;
370
371 Begin
372
373 l_first_char := substr(p_search_string,1,1);
374 l_search_str_len := length(p_search_string);
375 l_search_sub_str := substr(p_search_string,2,l_search_str_len);
376
377
378 x_found := FND_API.G_FALSE;
379
380 --getting the total number of strings that compose the sql statement.
381 Select Count(*)
382 into l_str_count
383 From ams_discoverer_sql
384 Where Workbook_name = p_workbook_name
385 And Worksheet_name = p_worksheet_name;
386
387 if (l_str_count = 0) then
388 x_found := FND_API.G_FALSE;
389 --dbms_output.put_line('search sql str : could not find workbook SQL = '||to_char(l_str_count));
390 RETURN;
391 end if;
392
393
394 --Getting the SQL strings.
395 Open C_SQL_string;
399
396
397 --starting from Zero because the sequence in which the strings are stored
398 --start from Zero.
400
401 <<l_fetch_string>>
402 For l_iterator in 0 .. (l_str_count - 1) Loop
403
404 --dbms_output.put_line('search sql str : fetch string iteration '||to_char(l_iterator + 1));
405
406 --Fetching the next string for the discoverer workbook.
407 Fetch C_SQL_string into l_sql_table(l_iterator + 1);
408
409 --initializing the current search position for the string.
410 l_str_pos := 0;
411
412 --checking for the last string.
413 if ( l_iterator = (l_str_count -1) )then
414 --dbms_output.put_line('search sql str : last string detected ');
415 l_last_str := 'Y';
416 end if;
417
418 --getting the length of the current sql string.
419 l_str_len := Length(l_sql_table(l_iterator + 1));
420 --dbms_output.put_line('search sql str : length of current sql string = '||to_char(l_str_len));
421
422 --updating the total number of characters read from the set of sql strings.
423 l_total_str_len := l_total_str_len + l_str_len;
424
425 --dbms_output.put_line('search sql str : total number of chars read = '||to_char(l_total_str_len));
426
427 --if the max. number of characters have been read then set this flag to
428 --break out of the fetch string loop.
429 if (l_total_str_len >= p_max_search_len) then
430
431 --dbms_output.put_line('search sql str : max num of characters have been read');
432
433 l_last_fetch := FND_API.G_TRUE;
434 l_tmp_str := substr(l_sql_table(l_iterator + 1),1,p_max_search_len - 1);
435
436 --dbms_output.put_line('search sql str : l_tmp_str ='||l_tmp_str);
437
438 l_sql_table(l_iterator + 1) := NULL;
439 l_sql_table(l_iterator + 1) := l_tmp_str;
440
441 end if;
442
443 --If First character of search string has been found in the previous string
444 --but the remainder of previous string is too short to contain the rest of
445 --the search string.
446 if (l_first_char_found = FND_API.G_TRUE) then
447 --getting the substring length.
448 l_substr_len := length(l_substr);
449 --dbms_output.put_line('search sql str : overflow detected, length of sub string = '||to_char(l_substr_len));
450
451 --concatenating the substring and the necessary number of characters in
452 --the current string to search for search string minus the first
453 --character of it.
454
455 --dbms_output.put_line('search sql str : overflow match string ='||l_substr||substr(x_sql_table(l_iterator + 1),1,(l_search_str_len-1)-l_substr_len));
456
457 if ( l_substr||substr(l_sql_table(l_iterator + 1),1,((l_search_str_len-1)-nvl(l_substr_len,0)) ) = l_search_sub_str) then
458 --dbms_output.put_line('search sql str : sucessful match after overflow');
459
460 x_found := FND_API.G_TRUE;
461 x_overflow := (l_search_str_len-1)- l_substr_len;
462
463 --dbms_output.put_line('search sql str : overflow length = '||to_char(x_overflow));
464
465 exit l_fetch_string;
466 else
467
468 x_found := FND_API.G_FALSE;
469 --calculating the new search position.
470 l_str_pos := l_str_pos + 1;
471
472 --dbms_output.put_line('search sql str : unsucessful match after overflow, new string position ='||to_char(l_str_pos ));
473
474 end if;
475 end if;
476
477
478 --Loop through the current sql string searching for l_first_char,
479 --If this is found then check that the next length(l_search_sub_str - 1)
480 --characters are part of the search string.
481
482 --If when l_first_char is found there are not enough characters to perform
483 --a match, copy the remaining characters into l_sub_str, read the next
484 --string(this is done before this loop,
485 --after the string is fetched) and then perform a check for a match.
486
487 --If l_first_char is not found then read in the next string and continue
488 --searching.
489
490 <<l_search_string>>
491 Loop
492
493 --dbms_output.put_line('search sql str : looping through current sql string searching for first char');
494
495 --getting the position of l_first_char in the current string.
496 l_first_char_pos := Instr(l_sql_table(l_iterator + 1),l_first_char,l_str_pos+1);
497
498 --the first character of the search string has been found.
499 if ( l_first_char_pos <> 0 ) then
500
501 --dbms_output.put_line('search sql str : getting the position of l_first_char in the current string, found position ='||to_char(l_first_char_pos ));
502 --setting current position in sql string.
503 l_str_pos := l_first_char_pos;
504
505 --indicating that the first character of the searched for string has been found.
506 l_first_char_found := FND_API.G_TRUE;
507
508
509 if (l_str_count = 1) then
510 x_found_in_str := l_iterator;
511 else
512 x_found_in_str := l_iterator + 1;
513 end if;
514
518
515 --dbms_output.put_line('search sql str : first char found in sql string number '||to_char(x_found_in_str));
516
517 x_position := l_first_char_pos;
519 if (l_search_str_len = 1 ) then
520 --dbms_output.put_line('search sql str : Match has been found');
521 x_found := FND_API.G_TRUE;
522 exit l_search_string;
523 --there are enough characters left in the current string to find a match.
524 elsif ( (l_str_len - (l_str_pos + 1)) >= (l_search_str_len - 1) )then
525
526 --dbms_output.put_line('search sql str : enough remaining chars in current string to perform match');
527
528 l_substr := substr(l_sql_table(l_iterator + 1),l_str_pos + 1,l_search_str_len - 1);
529 --dbms_output.put_line('search sql str : Sub string = '||l_substr);
530
531
532 --match has been found.
533 if ( l_substr = l_search_sub_str ) then
534
535 --dbms_output.put_line('search sql str : Match has been found');
536 x_found := FND_API.G_TRUE;
537 exit l_search_string;
538
539 --no match found, update string search position.
540 else
541 --dbms_output.put_line('search sql str : Match has not been found');
542 x_found := FND_API.G_FALSE;
543 l_str_pos := l_str_pos + 1;
544 end if;
545
546 --not enough characters remaining in current sql string to perform a match.
547 --creating a sub string of the remaining characters, which are used with
548 --the next string fetched to perform a check.
549 elsif ((l_str_len - l_str_pos) < (l_search_str_len-1)) then
550 --dbms_output.put_line('search sql str : Not enough chars remaining to perform check');
551 l_substr := substr(l_sql_table(l_iterator + 1),l_str_pos + 1,l_str_len) ;
552
553 --dbms_output.put_line('search sql str : sub string created = '||l_substr);
554 --exit the search string loop and fetch another sql string.
555 exit l_search_string;
556 end if;
557
558 else
559 --dbms_output.put_line('search sql str : first char not found in current sql string');
560 --l_first_char has not been been found in the current string.
561
562
563 --indicating that the first character of the searched for string has not been found.
564 l_first_char_found := FND_API.G_FALSE;
565
566 --indicating that the search string has not been found;
567 x_found := FND_API.G_FALSE;
568
569 --exit the search string loop and fetch another sql string.
570 exit l_search_string;
571 end if;
572
573
574 End Loop l_search_string;--Searching the current strings.
575 --dbms_output.put_line('search sql str : exiting search string loop');
576
577 --the string has been found , no need to fetch any more sql strings.
578 --OR the max. number of characters have been read.
579 If (x_found = FND_API.G_TRUE or l_last_fetch = FND_API.G_TRUE ) then
580 exit l_fetch_string;
581 end If;
582
583 End Loop l_fetch_string;--Fetching the SQL strings.
584 --dbms_output.put_line('search sql str : exiting fetch string loop');
585 Close C_SQL_string;
586
587 EXCEPTION
588 WHEN OTHERS THEN
589
590 if (c_sql_string%ISOPEN) then
591 Close C_SQL_string;
592 end if;
593
594 l_sqlerrm := SQLERRM;
595 l_sqlcode := SQLCODE;
596 --dbms_output.put_line('Search SQL string:'||l_sqlerrm||l_sqlcode);
597
598 End Search_SQL_string;
599
600 -----------------------------------------------------------------------------
601 -- Function
602 -- Encrypt_Param
603
604 -- PURPOSE
605 -- 1. This Function is used by the middle tier java class to get encryption
606 -- of parameters that need to be passed to form function
607
608 -- NOTES
609
610 -- HISTORY
611 -- 05/03/2001 yxliu created
612 -- End of Comments
613 -----------------------------------------------------------------------------
614 Function Encrypt_Param
615 ( P_params IN varchar2,
616 P_session_id IN NUMBER)
617 RETURN varchar2
618
619 IS
620
621 BEGIN
622 RETURN icx_call.encrypt2(P_params, P_session_id);
623
624 END Encrypt_Param;
625
626
627 --
628 -- NOTE
629 -- Need to
630 PROCEDURE get_source_type_code (
631 p_workbook_name IN VARCHAR2,
632 p_worksheet_name IN VARCHAR2,
633 x_source_type_code OUT NOCOPY VARCHAR2
634 )
635 IS
636 -- by musman bug:4655422:fix start
637 l_source_type_code ams_discoverer_sql.source_type_code%TYPE; --VARCHAR2(30);
638 l_search_code VARCHAR2(50);-- VARCHAR2(30);
639 --end
640
641 -- variables used to call search_sql_string
642 l_found VARCHAR2(1);
643 l_found_in_str NUMBER;
644 l_position NUMBER;
645 l_overflow NUMBER;
646
647 CURSOR c_master_types IS
651 AND enabled_flag = 'Y'
648 SELECT source_type_code
649 FROM ams_list_src_types
650 WHERE master_source_type_flag = 'Y'
652 and list_source_type = 'TARGET' -- by musman bug:4655422:fix
653 order by list_source_type_id ; -- by musman bug:4655422:fix
654 BEGIN
655 OPEN c_master_types;
656 LOOP
657 l_source_type_code := NULL;
658
659 FETCH c_master_types INTO l_source_type_code;
660
661 -- Need to encapsulate the source type code
662 -- with single quotes because the saved
663 -- SQL has the search string within quotes.
664 -- Without the quotes, any string with the
665 -- searched string will return regardless if it
666 -- is designated as the source type code.
667 l_search_code := '''' || l_source_type_code || '''';
668
669 search_sql_string (
670 p_search_string => l_search_code,
671 p_workbook_name => p_workbook_name,
672 p_worksheet_name => p_worksheet_name,
673 x_found => l_found,
674 x_found_in_str => l_found_in_str,
675 x_position => l_position,
676 x_overflow => l_overflow
677 );
678
679 EXIT WHEN c_master_types%NOTFOUND OR l_found = FND_API.G_TRUE;
680 END LOOP;
681 CLOSE c_master_types;
682
683 x_source_type_code := l_source_type_code;
684
685 EXCEPTION
686 WHEN OTHERS THEN
687
688 if (c_master_types%ISOPEN) then
689 Close c_master_types;
690 end if;
691
692 END get_source_type_code;
693
694
695 --
696 -- Note
697 -- ======
698 -- 2 main scenarios in the processing flow: 1) all the fragments concatenated
699 -- are not longer than 32k (max PL/SQL string), so instr has to be done to only
700 -- that one str. 2) all the fragments would concatenate to a string longer than
701 -- 32k, so the instr has to be done in batches with a size no bigger than 32k
702 -- at a time.
703 Procedure batch_search_sql (
704 p_workbook_name in varchar2,
705 p_worksheet_name in varchar2,
706 p_source_type_code_tab IN source_type_code_type,
707 x_found_index OUT NOCOPY VARCHAR2
708 )
709 IS
710 MAX_FRAGMENTS CONSTANT NUMBER := 8; -- each SQL fragment is 4k, so the string can only be up to 32k
711
712 TYPE sql_string_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
713 l_sql_string_tab sql_string_type;
714
715 l_str VARCHAR2(32767);
716
717 l_last_str VARCHAR2(4000);
718 l_end_offset NUMBER := MAX_FRAGMENTS;
719
720 CURSOR c_sql_string IS
721 SELECT sql_string
722 FROM ams_discoverer_sql
723 WHERE workbook_name = p_workbook_name
724 AND worksheet_name = p_worksheet_name
725 ORDER BY sequence_order;
726 BEGIN
727 x_found_index := 0;
728
729 OPEN c_sql_string;
730 FETCH c_sql_string BULK COLLECT INTO l_sql_string_tab;
731 CLOSE c_sql_string;
732
733 IF l_sql_string_tab.COUNT > MAX_FRAGMENTS THEN
734 --
735 -- Logic
736 -- =========
737 -- Build a string up to 32k length (max for PL/SQL
738 -- strings). Look for code in the string, and if
739 -- not found, build another 32k string but start
740 -- with the last fragment of the previous string.
741 FOR j IN 1 .. l_sql_string_tab.COUNT LOOP
742 IF j > l_end_offset THEN
743 l_str := l_last_str;
744 l_end_offset := (j - 1) + MAX_FRAGMENTS;
745 ELSE
746 l_str := l_str || l_sql_string_tab(j);
747 IF j = l_end_offset THEN
748 l_last_str := l_sql_string_tab(j);
749 END IF;
750 END IF;
751
752 IF j = l_end_offset OR j = l_sql_string_tab.COUNT THEN
753 --
754 -- Logic
755 -- =======
756 -- Constructing the strings is expensive, so we don't
757 -- want to keep re-doing it. For every string, check
758 -- all the codes for a match.
759 FOR i IN 1 .. p_source_type_code_tab.COUNT LOOP
760 IF INSTR (l_str, '''' || p_source_type_code_tab(i) || '''') <> 0 THEN
761 x_found_index := i;
762 RETURN;
763 END IF;
764 END LOOP;
765 END IF;
766 END LOOP;
767 ELSE
768 -- construct a single string out of all the sql fragments
769 FOR i IN 1 .. l_sql_string_tab.COUNT LOOP
770 l_str := l_str || l_sql_string_tab(i);
771 END LOOP;
772
773 FOR i IN 1 .. p_source_type_code_tab.COUNT LOOP
774 IF INSTR (l_str, '''' || p_source_type_code_tab(i) || '''') <> 0 THEN
775 x_found_index := i;
776 RETURN;
777 END IF;
778 END LOOP;
779 END IF;
780
781
782 END;
783
784 END AMS_DISCOVERER_PVT;
[Home] [Help]