[Home] [Help]
PACKAGE BODY: APPS.IEM_TEXT_PVT
Source
1 PACKAGE BODY IEM_TEXT_PVT as
2 /* $Header: iemtextb.pls 120.12.12010000.2 2009/08/10 09:18:18 sanjrao ship $*/
3
4 PROCEDURE GetThemes(p_message_id IN number,
5 p_part_id in number,
6 xbuf OUT NOCOPY iem_text_pvt.theme_Table,
7 errtext OUT NOCOPY VARCHAR2) IS
8 l_part_id number;
9 TYPE theme_type is TABLE OF iem_imt_keywords.keyword%type;
10 TYPE score_type is TABLE OF iem_imt_keywords.weight%type;
11 theme_tbl theme_type:=theme_type();
12 score_tbl score_type:=score_type();
13 -- Below cursor will check for existing data for the message
14 cursor c_weight is select keyword,weight from iem_imt_keywords
15 where message_id=p_message_id and message_type=1 and message_part=l_part_id and message_format=1 ;
16 cursor c1 is select rowid from iem_imt_texts
17 where message_id=p_message_id and message_type=1 and message_part=l_part_id and message_format=1 ;
18 l_theme_buf THEME_TABLE;
19 l_count number:=0;
20 l_status varchar2(10);
21 l_schema varchar2(100);
22 begin
23 l_schema:='IEM';
24 IF p_part_id is null then
25 l_part_id:=9999;
26 ELSE
27 l_part_id:=p_part_id;
28 END IF;
29 -- First check for existing message
30 for v_theme in c_weight loop
31 l_count:=l_count+1;
32 xbuf(l_count).theme:=v_theme.keyword;
33 xbuf(l_count).weight:=v_theme.weight;
34 end loop;
35 IF l_count=0 then -- Need to Generate Themes
36 IEM_TEXT_PVT.IEM_PROCESS_PARTS(p_message_id,1,p_part_id,null,l_status);
37 for v1 in c1 loop
38 CTX_DOC.THEMES(l_schema||'.IEM_IMT_INDEX',
39 CTX_DOC.PKENCODE(v1.rowid), l_theme_buf, full_themes => FALSE);
40 end loop;
41 -- Insert Into iem_imt_keywords Table for Future Use
42 IF l_theme_buf.count>0 THEN
43 for i in l_theme_buf.first..l_theme_buf.last LOOP
44 xbuf(i).theme:=l_theme_buf(i).theme;
45 xbuf(i).weight:=l_theme_buf(i).weight;
46 theme_tbl.extend;
47 theme_tbl(theme_tbl.last):=l_theme_buf(i).theme;
48 score_tbl.extend;
49 score_tbl(score_tbl.last):=l_theme_buf(i).weight;
50 END LOOP;
51 FORALL j in indices of theme_tbl
52 insert into iem_imt_keywords(message_id,message_type,message_part,message_format,keyword,weight)
53 values(p_message_id,1,l_part_id,1,theme_tbl(j),score_tbl(j));
54 END IF;
55 END IF;
56 END GetThemes;
57
58 PROCEDURE GetTokens(p_message_id IN number,
59 p_part_id in number,
60 p_lang in varchar2,
61 xbuf OUT NOCOPY iem_text_pvt.token_table,
62 errtext OUT NOCOPY VARCHAR2) IS
63 l_part_id number;
64 TYPE theme_type is TABLE OF iem_imt_keywords.keyword%type;
65 theme_tbl theme_type:=theme_type();
66 cursor c_weight is select keyword from iem_imt_keywords
67 where message_id=p_message_id and message_type=1 and message_part=l_part_id and message_format=2
68 order by 1;
69 cursor c1 is select rowid from iem_imt_texts where
70 message_id=p_message_id and message_type=1 and message_part=l_part_id and message_format=2;
71 l_token_buf TOKEN_TABLE;
72 l_count number:=0;
73 l_status varchar2(10);
74 l_schema varchar2(10);
75 l_keyword iem_imt_keywords.keyword%type;
76 l_counter number;
77 dml_errors EXCEPTION;
78 PRAGMA exception_init(dml_errors, -24381);
79 l_start number:=1;
80 begin
81 l_schema:='IEM';
82 IF p_part_id is null then
83 l_part_id:=9999;
84 ELSE
85 l_part_id:=p_part_id;
86 END IF;
87 -- First check for existing message
88 for v_theme in c_weight loop
89 l_count:=l_count+1;
90 xbuf(l_count).token:=v_theme.keyword;
91 end loop;
92 IF l_count=0 then -- Need to Generate Tokens
93 IEM_TEXT_PVT.IEM_PROCESS_PARTS(p_message_id,1,p_part_id,p_lang,l_status);
94 for v1 in c1 loop
95 CTX_DOC.TOKENS(l_schema||'.IEM_IMT_INDEX',
96 CTX_DOC.PKENCODE(v1.rowid), l_token_buf);
97 end loop;
98 -- Insert Into iem_imt_keywords Table for Future Use
99 IF l_token_buf.count>0 THEN
100 for i in l_token_buf.first..l_token_buf.last LOOP
101 theme_tbl.extend;
102 theme_tbl(theme_tbl.last):=l_token_buf(i).token;
103 END LOOP;
104 LOOP
105 BEGIN
106 FORALL j in l_start..theme_tbl.count
107 insert into iem_imt_keywords(message_id,message_type,message_part,message_format,keyword,weight)
108 values(p_message_id,1,l_part_id,2,theme_tbl(j),null);
109 EXIT;
110 EXCEPTION WHEN OTHERS THEN
111 l_start := l_start + sql%rowcount + 1;
112 END;
113 END LOOP;
114 END IF;
115 -- populate the out buffer
116 theme_tbl.delete;
117 open c_weight;
118 LOOP
119 fetch c_weight bulk collect into theme_tbl;
120 exit when c_weight%notfound;
121 end loop;
122 close c_weight;
123 l_counter:=1;
124 for i in theme_tbl.first..theme_tbl.last LOOP
125 xbuf(l_counter).token:=theme_tbl(i);
126 l_counter:=l_counter+1;
127 end loop;
128 END IF;
129 exception when others then
130 null;
131 end GetTokens;
132
133 PROCEDURE IEM_INSERT_TEXTS(p_clob in clob,
134 p_lang in varchar2,
135 x_id OUT NOCOPY NUMBER,
136 x_status out nocopy varchar2) IS
137 l_seq number;
138 l_msgformat number;
139 l_imt_format varchar2(100);
140 begin
141 l_imt_format:='TEXT';
142 IF p_lang is null then
143 l_msgformat:=1;
144 ELSE
145 l_msgformat:=2;
146 END IF;
147 select nvl(max(message_id),0)+1 into l_Seq
148 from iem_imt_texts where message_type=2;
149 insert into iem_imt_texts
150 (
151 message_ID,
152 message_TYPE,
153 message_PART,
154 message_format,
155 IMT_FORMAT,
156 IMT_CHARSET,
157 IMT_LANG,
158 message_text)
159 VALUES
160 (l_seq
161 ,2
162 ,9999
163 ,l_msgformat
164 ,l_imt_format
165 ,null -- May be defaulted to database char set.
166 ,p_lang
167 ,p_clob);
168 x_status:='S';
169 x_id:=l_seq;
170 EXCEPTION WHEN OTHERS THEN
171 x_status:='E';
172 end;
173
174
175 -- Api for returning thems/token for Creation of Intent. Only to be used by Email Center
176 procedure iem_get_tokens(p_intent_id in number,
177 p_type in number, -- 1 for theme 2 for token
178 p_lang in varchar2,
179 p_qtext in varchar2,
180 p_rtext in varchar2,
181 x_qtokens OUT NOCOPY jtf_varchar2_Table_2000,
182 x_rtokens OUT NOCOPY jtf_varchar2_Table_2000,
183 x_status OUT NOCOPY varchar2)
184 is
185 l_kw_tbl iem_text_pvt.keyword_Rec_tbl;
186 l_theme_enabled varchar2(1);
187 l_count number:=1;
188 cursor c_query is select keyword,weight from iem_intent_dtls
189 where intent_id=p_intent_id
190 and query_Response='Q';
191 cursor c_resp is select keyword,weight from iem_intent_dtls
192 where intent_id=p_intent_id
193 and query_Response='R';
194 l_flag number;
195 begin
196 select decode(p_type,1,'Y','N') into l_theme_enabled from dual;
197 l_kw_tbl.delete;
198 x_qtokens:=jtf_varchar2_Table_2000();
199 x_rtokens:=jtf_varchar2_Table_2000();
200 IF p_qtext is not null then
201 iem_text_pvt.get_tokens(p_type,p_lang,p_qtext,l_kw_tbl);
202 IF l_kw_tbl.count>0 then
203 for i in l_kw_tbl.first..l_kw_tbl.last LOOP
204 -- Compare with existing intent keyword and donot allow duplicate keyword
205 l_flag:=0;
206 for v1 in c_query LOOP
207 if l_kw_tbl(i).keyword = v1.keyword then
208 l_flag:=1;
209 end if;
210 END LOOP;
211 if l_flag=0 then -- No match new keyword
212 x_qtokens.extend;
213 x_qtokens(l_count):=l_kw_tbl(i).keyword;
214 l_count:=l_count+1;
215 end if;
216 END LOOP;
217 END IF;
218 END IF;
219 l_kw_tbl.delete;
220 l_count:=1;
221 IF p_rtext is not null then
222 iem_text_pvt.get_tokens(p_type,p_lang,p_rtext,l_kw_tbl);
223 IF l_kw_tbl.count>0 then
224 for i in l_kw_tbl.first..l_kw_tbl.last LOOP
225 -- Compare with existing intent keyword and donot allow duplicate keyword
226 l_flag:=0;
227 for v1 in c_resp LOOP
228 if l_kw_tbl(i).keyword = v1.keyword then
229 l_flag:=1;
230 end if;
231 END LOOP;
232 if l_flag=0 then
233 x_rtokens.extend;
234 x_rtokens(l_count):=l_kw_tbl(i).keyword;
235 l_count:=l_count+1;
236 end if;
237 END LOOP;
238 END IF;
239 END IF;
240 x_status:='S';
241 EXCEPTION WHEN OTHERS THEN
242 x_status:='E';
243
244 END;
245
246
247 procedure get_tokens(p_type in number, -- 1 for theme 2 for token
248 p_lang in varchar2,
249 p_text in CLOB,
250 xbuf OUT NOCOPY iem_text_pvt.keyword_rec_tbl) IS
251 l_seq number;
252 l_text raw(32767);
253 l_errortext varchar2(100);
254 l_tokenbuf iem_text_pvt.token_Table;
255 l_token_buf TOKEN_TABLE;
256 l_status varchar2(10);
257 l_theme_buf THEME_TABLE;
258 l_schema varchar2(100);
259 l_message_id number;
260 l_counter number;
261 TYPE theme_type is TABLE OF iem_imt_keywords.keyword%type;
262 theme_tbl theme_type:=theme_type();
263 l_start number:=1;
264 cursor c1 is select rowid from iem_imt_texts
265 where message_id=l_seq and message_type=2 ;
266 cursor c_Weight is select keyword from iem_imt_keywords where message_id=l_message_id;
267 begin
268 -- Insert the content into IEM_IMT_TEXTS
269 iem_insert_texts(p_text,p_lang,l_seq,l_status);
270 l_schema:='IEM';
271 if p_type=1 then
272 for v1 in c1 LOOP
273 CTX_DOC.THEMES(l_schema||'.IEM_IMT_INDEX',
274 CTX_DOC.PKENCODE(v1.rowid), l_theme_buf, full_themes => FALSE);
275 END LOOP;
276 IF l_theme_buf.count>0 THEN
277 for i in l_theme_buf.first..l_theme_buf.last LOOP
278 xbuf(i).keyword:=l_theme_buf(i).theme;
279 xbuf(i).weight:=l_theme_buf(i).weight;
280 end loop;
281 end if;
282 elsif p_type=2 then
283 for v1 in c1 loop
284 CTX_DOC.TOKENS(l_schema||'.IEM_IMT_INDEX',
285 CTX_DOC.PKENCODE(v1.rowid), l_token_buf);
286 end loop;
287 IF l_token_buf.count>0 THEN
288 select nvl(max(message_id),0)+1 into l_message_id from iem_imt_keywords;
289 for i in l_token_buf.first..l_token_buf.last LOOP
290 theme_tbl.extend;
291 theme_tbl(theme_tbl.last):=l_token_buf(i).token;
292 END LOOP;
293 LOOP
294 BEGIN
295 FORALL j in l_start..theme_tbl.count
296 insert into iem_imt_keywords(message_id,message_type,message_part,message_format,keyword,weight)
297 values(l_message_id,1,0,2,theme_tbl(j),null);
298 EXIT;
299 EXCEPTION WHEN OTHERS THEN
300 l_start := l_start + sql%rowcount + 1;
301 END;
302 END LOOP;
303
304 -- populate the out buffer
305 theme_tbl.delete;
306 open c_weight;
307 LOOP
308 fetch c_weight bulk collect into theme_tbl;
309 exit when c_weight%notfound;
310 end loop;
311 close c_weight;
312 l_counter:=1;
313 for i in theme_tbl.first..theme_tbl.last LOOP
314 xbuf(l_counter).keyword:=theme_tbl(i);
315 l_counter:=l_counter+1;
316 end loop;
317 delete from iem_imt_keywords where message_id=l_message_id;
318 END IF;
319 end if;
320 end get_tokens;
321
322 PROCEDURE IEM_PROCESS_PARTS(p_message_id in number,
323 p_message_type in number,
324 p_part_id in number,
325 p_lang in varchar2,
326 x_status out nocopy varchar2) IS
327 l_status varchar2(10);
328 l_error_text varchar2(100);
329 l_count number:=0;
330 l_buf CLOB;
331 l_text CLOB;
332 l_rowid rowid;
333 l_charset varchar2(100);
334 l_msgformat number;
335 l_schema varchar2(10);
336 l_subject iem_ms_base_headers.subject%type;
337 l_blob BLOB;
338 cursor c1 is select mime_msg from iem_ms_mimemsgs where
339 message_id=p_message_id;
340 cursor c2 is select part_type,part_id,part_charset,part_data from iem_ms_msgparts
341 where message_id=p_message_id
342 and part_id=p_part_id;
343 l_imt_format varchar2(100);
344 l_part_id number:=-1;
345
346 BEGIN
347 l_schema:='IEM';
348 IF p_lang is null then
349 l_msgformat:=1; -- Theme
350 ELSE
351 l_msgformat:=2; -- Token
352 END IF;
353 l_imt_format:='TEXT';
354 IF p_part_id is null then -- For all parts
355 -- Create an entry for all parts
356 for v1 in c1 Loop
357 ctx_doc.policy_filter( 'IEM_MAIL_FILTER_POLICY', v1.mime_msg, l_text, false );
358 insert into iem_imt_texts
359 (
360 message_ID,
361 message_TYPE,
362 message_PART,
363 message_format,
364 IMT_FORMAT,
365 IMT_LANG,
366 message_text)
367 VALUES
368 (p_message_id
369 ,p_message_type
370 ,9999
371 ,l_msgformat
372 ,'TEXT'
373 ,p_lang
374 ,l_text
375 );
376 end loop;
377 ELSE
378 for v2 in c2 Loop
379 ctx_doc.policy_filter( 'my_policy', v2.part_data, l_text, false );
380 insert into iem_imt_texts
381 (
382 message_ID,
383 message_TYPE,
384 message_PART,
385 message_format,
386 IMT_FORMAT,
387 IMT_LANG,
388 message_text)
389 VALUES
390 (p_message_id
391 ,p_message_type
392 ,v2.part_id
393 ,l_msgformat
394 ,l_imt_format
395 ,p_lang
396 ,l_text);
397 END LOOP;
398 END IF;
399 x_status:='S';
400 EXCEPTION WHEN OTHERS THEN
401 x_status:='E';
402 END IEM_PROCESS_PARTS;
403 PROCEDURE RETRIEVE_DOC(p_intent_id in varchar2,
404 x_status out nocopy varchar2) IS
405 cursor c1 is
406 select keyword,weight from iem_intent_dtls where intent_id=p_intent_id
407 and query_response='R';
408 l_imt_string varchar2(32767):=' ';
409 l_return_status VARCHAR2(20);
410 l_msg_count NUMBER;
411 l_msg_data VARCHAR2(400);
412 l_rows_returned cs_kb_number_tbl_type :=cs_kb_number_tbl_type();
413 l_next_row_pos cs_kb_number_tbl_type :=cs_kb_number_tbl_type();
414 l_total_row_cnt cs_kb_number_tbl_type :=cs_kb_number_tbl_type();
415 l_logmessage varchar2(500);
416 l_level varchar2(20):='STATEMENT';
417 l_app_id number;
418 l_part number;
419 l_flag number:=1;
420 l_ret number;
421 l_search varchar2(100);
422 l_theme varchar2(200);
423 l_tstr varchar2(2000);
424 l_errtext varchar2(200);
425 l_score number;
426 l_class NUMBER;
427 l_count NUMBER;
428 l_next_row_tbl cs_kb_number_tbl_type:=cs_kb_number_tbl_type();
429 l_total_row_tbl cs_kb_number_tbl_type:=cs_kb_number_tbl_type();
430 l_area_array AMV_SEARCH_PVT.amv_char_varray_type:=null;
431 l_result_array cs_kb_result_varray_type;
432 l_amv_result_array AMV_SEARCH_PVT.amv_searchres_varray_type;
433 l_content_array AMV_SEARCH_PVT.amv_char_varray_type:=null;
434 l_param_array AMV_SEARCH_PVT.amv_searchpar_varray_type;
435 l_rep cs_kb_varchar100_tbl_type ;
436 l_category_id AMV_SEARCH_PVT.amv_number_varray_type:=AMV_SEARCH_PVT.amv_number_varray_type();
437 l_tag1 number;
438 l_cnt number;
439 l_res1 varchar2(10);
440 l_res2 varchar2(10);
441 l_search_repos varchar2(10);
442 l_days number ;
443 l_user_id number ;
444 l_rows_req cs_kb_number_tbl_type ;
445 l_rows number;
446 l_start_row cs_kb_number_tbl_type:=cs_kb_number_tbl_type(1,1);
447 l_sms_string varchar2(255);
448 l_sms_count number;
449 l_counter number:=1;
450 l_cat_map_id number;
451 l_cat_counter number;
452 l_search_type varchar2(100);
453 G_APP_ID number;
454 l_intent_id number;
455 l_number number;
456 begin
457 l_intent_id:=to_number(p_intent_id);
458 for v1 in c1 loop
459 l_imt_string:=l_imt_string||'about ('||v1.keyword||')*'||v1.weight||',';
460 end loop;
461
462 l_imt_string:=substr(l_imt_string,1,length(l_imt_string)-1);
463 l_search_repos:='ALL';
464 l_rows:=10; -- Number of Document Retrieved...
465 l_rows_req :=cs_kb_number_tbl_type(l_rows,l_rows);
466 G_APP_ID:=520;
467 l_area_array := AMV_SEARCH_PVT.amv_char_varray_type();
468 l_area_array.extend;
469 l_area_array(1) := 'ITEM';
470 l_content_array := AMV_SEARCH_PVT.amv_char_varray_type();
471 l_content_array.extend;
472 l_content_array(1) := 'CONTENT';
473 l_content_array.extend;
474 l_param_array := AMV_SEARCH_PVT.amv_searchpar_varray_type();
475 l_rep :=cs_kb_varchar100_tbl_type() ;
476 l_rep :=cs_kb_varchar100_tbl_type('MES') ;
477 cs_knowledge_grp.Specific_Search(
478 p_api_version => 1.0,
479 p_init_msg_list => fnd_api.g_true,
480 --p_validation_level => p_validation_level,
481 x_return_status => l_return_status,
482 x_msg_count => l_msg_count,
483 x_msg_data => l_msg_data,
484 p_repository_tbl => l_rep,
485 p_search_string => l_imt_string,
486 p_updated_in_days => l_days,
487 p_check_login_user => FND_API.G_FALSE,
488 p_application_id => G_APP_ID,
489 p_area_array => l_area_array,
490 p_content_array => l_content_array,
491 p_param_array => l_param_array,
492 p_user_id => l_user_id,
493 p_category_id => l_category_id,
494 p_include_subcats => FND_API.G_FALSE,
495 p_external_contents => FND_API.G_TRUE,
496 p_rows_requested_tbl => l_rows_req,
497 p_start_row_pos_tbl => l_start_row,
498 p_get_total_cnt_flag => 'T',
499 x_rows_returned_tbl => l_rows_returned,
500 x_next_row_pos_tbl => l_next_row_pos,
501 x_total_row_cnt_tbl => l_total_row_cnt,
502 x_result_array => l_result_array);
503
504 -- Insert The Data into IEM_INTENT_DOCUMENTS
505 delete from iem_intent_documents where intent_id=l_intent_id;
506 if l_result_array.count>0 then
507 FOR l_count IN 1..l_result_array.count LOOP
508 insert into iem_intent_documents
509 (intent_id,
510 docname,
511 repos_id,
512 doc_id,
513 url_string,
514 score)
515 values
516 (l_intent_id,
517 l_result_array(l_count).title,
518 1, -- 1 for MES and 2 for SMS
519 l_result_array(l_count).id,
520 l_result_array(l_count).url_string,
521 l_result_array(l_count).score);
522 END LOOP;
523 end if;
524
525 l_rep :=cs_kb_varchar100_tbl_type('SMS') ;
526 IF length(l_imt_string)>255 THEN
527 l_sms_string:=substr(l_imt_string,1,255);
528 l_sms_count:=instr(l_sms_string,',about',-1,1);
529 l_imt_string:=substr(l_sms_string,1,l_sms_count-1);
530 END IF;
531 cs_knowledge_grp.Specific_Search(
532 p_api_version => 1.0,
533 p_init_msg_list => fnd_api.g_true,
534 --p_validation_level => p_validation_level,
535 x_return_status => l_return_status,
536 x_msg_count => l_msg_count,
537 x_msg_data => l_msg_data,
538 p_repository_tbl => l_rep,
539 p_search_string => l_imt_string,
540 p_updated_in_days => l_days,
541 p_check_login_user => FND_API.G_FALSE,
542 p_application_id => G_APP_ID,
543 p_area_array => l_area_array,
544 p_content_array => l_content_array,
545 p_param_array => l_param_array,
546 p_user_id => l_user_id,
547 p_category_id => l_category_id,
548 p_include_subcats => FND_API.G_TRUE,
549 p_external_contents => FND_API.G_TRUE,
550 p_rows_requested_tbl => l_rows_req,
551 p_start_row_pos_tbl => l_start_row,
552 p_get_total_cnt_flag => 'T',
553 x_rows_returned_tbl => l_rows_returned,
554 x_next_row_pos_tbl => l_next_row_pos,
555 x_total_row_cnt_tbl => l_total_row_cnt,
556 x_result_array => l_result_array);
557 if l_result_array.count>0 then
558 FOR l_count IN 1..l_result_array.count LOOP
559 --select set_number into l_number
560 --from CS_KB_SETS_B where set_id=l_result_array(l_count).id;
561 insert into iem_intent_documents
562 (intent_id,
563 docname,
564 repos_id,
565 doc_id,
566 url_string,
567 score)
568 values
569 (l_intent_id,
570 l_result_array(l_count).title,
571 2, -- 1 for MES and 2 for SMS
572 l_result_array(l_count).document_number,
573 -- l_number,
574 l_result_array(l_count).url_string,
575 l_result_array(l_count).score);
576 END LOOP;
577 end if;
578 commit;
579 end RETRIEVE_DOC ;
580 PROCEDURE RETRIEVE_TEXT(p_message_id in number,
581 x_text OUT NOCOPY varchar2,
582 x_status out nocopy varchar2) IS
583 l_data clob;
584 l_text varchar2(32767):=' ';
585 l_text1 varchar2(32767):=' ';
586 cursor c1 is select value,type from iem_ms_msgbodys
587 where message_id=p_message_id
588 order by order_id;
589 l_html_flag number;
590 l_index number;
591 begin
592 dbms_lob.createtemporary(l_data, TRUE);
593 l_html_flag:=0; -- Plain Text
594 for v1 in c1 loop
595 l_text:=l_text||ltrim(v1.value,' ');
596 if v1.type like '%html%' then
597 l_html_flag:=1;
598 end if;
599 exit when length(l_text)>=2000;
600 end loop;
601 l_text:=substr(l_text,1,2000);
602 if l_html_flag=1 then
603 ctx_doc.policy_filter('IEM_HTML_EXTRACT_POLICY',l_text,l_data,TRUE,null,null,null);
604 l_text:=dbms_lob.substr(l_data,dbms_lob.getlength(l_data),1);
605 x_text:=ltrim(ltrim(l_text),fnd_global.local_chr(10));
606 else
607 x_text:=ltrim(l_text,' '); --Return the plain text as is.
608 end if;
609 dbms_lob.freetemporary(l_data);
610 x_status:='S';
611 exception when others then
612 x_status:='E';
613 end RETRIEVE_TEXT;
614 end IEM_TEXT_PVT ;