[Home] [Help]
PACKAGE BODY: APPS.CS_KB_R_CTX_PKG
Source
1 package body cs_kb_r_ctx_pkg as
2 /* $Header: cskrctxb.pls 115.3 2004/03/16 00:07:35 hmei noship $ */
3 /*======================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 | FILENAME: cskrctxb.pls |
9 | |
10 | PURPOSE |
11 | Datastore procedure for cs_forum_messages_tl_n4 intermedia index. |
12 | ARGUMENTS |
13 | |
14 | NOTES |
15 | Usage: start cskrctxb.pls apps |
16 | Arguments: |
17 | 1 - un_apps = apps user name |
18 | 2 - CTXSYS = ctxsys user name |
19 | HISTORY |
20 | 05-Mar-2003 klou Created. Copy from ctxsys.cs_kb_f_ctx_pkg (115.5)|
21 | 06-Mar-2003 klou |
22 | 1. Grant execute of this package to CTXSYS. |
23 | 2. Add APPS qualifier in front of schema. |
24 | 15-Mar-2004 hmei Bug 3499204 - remove tags for indexing |
25 +======================================================================*/
26 procedure Get_Forum_Composite_Cols(
27 p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB
28 ) is
29 l_desc_clob CLOB := null;
30 l_message_id number := null;
31 l_lang VARCHAR2(256) := null;
32 l_name VARCHAR2(200) := null;
33 l_data varchar2(2000);
34 l_mesg_number Number := null;
35 l_temp_clob CLOB; -- used in Remove_Tags_Clob
36
37
38 cursor get_data_csr(p_rowid in ROWID) is
39 Select cfmt.message_id, cfmt.language, cfmt.name, cfmt.description, cfmb.message_number
40 from cs_forum_messages_tl cfmt, cs_forum_messages_b cfmb
41 where cfmt.rowid = p_rowid
42 and cfmt.message_id = cfmb.message_id;
43
44
45 begin
46
47 -- temp clob lives for at most the duration of call.
48 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
49
50 open get_data_csr(p_rowid);
51 fetch get_data_csr into l_message_id, l_lang, l_name, l_desc_clob, l_mesg_number;
52 close get_data_csr;
53
54 l_data := '<SUBJECT>'||l_name||'</SUBJECT>';
55 l_data := l_data ||'<NUMBER>'||to_char(l_mesg_number)||'</NUMBER>';
56
57 l_data := l_data||'<BODY>';
58
59 l_data := CS_KB_CTX_PKG.Remove_Tags(l_data);
60
61 dbms_lob.trim(p_clob, 0);
62 dbms_lob.writeappend(p_clob, length(l_data), l_data);
63
64 dbms_lob.open(p_clob, DBMS_LOB.LOB_READWRITE);
65 dbms_lob.open (l_desc_clob, DBMS_LOB.LOB_READONLY);
66 dbms_lob.append(p_clob, CS_KB_CTX_PKG.Remove_Tags_Clob(l_desc_clob,l_temp_clob));
67
68 dbms_lob.close(p_clob);
69 dbms_lob.close(l_desc_clob);
70 dbms_lob.writeappend(p_clob, length('</BODY>'), '</BODY>');
71
72 l_data := '<LANG>'||l_lang||'</LANG>';
73 dbms_lob.writeappend(p_clob, length(l_data), l_data);
74
75 -- explicitly free the clob
76 dbms_lob.freetemporary(l_temp_clob);
77
78 End Get_Forum_Composite_Cols;
79 end cs_kb_r_ctx_pkg;