1 PACKAGE ICX_POR_CTX_SQL_PKG AUTHID CURRENT_USER AS
2 /* $Header: ICXCTXS.pls 115.1 2004/03/31 21:56:19 vkartik ship $*/
3
4 -- OEX_IP_PORTING
5 TYPE DescriptorInfo IS RECORD (
6 descriptor_id NUMBER,
7 descriptor_key ICX_CAT_DESCRIPTORS_TL.key%TYPE,
8 descriptor_index NUMBER,
9 descriptor_type NUMBER,
10 descriptor_length NUMBER,
11 section_tag NUMBER,
12 stored_in_column ICX_CAT_DESCRIPTORS_TL.stored_in_column%TYPE,
13 stored_in_table ICX_CAT_DESCRIPTORS_TL.stored_in_table%TYPE
14 );
15
16 TYPE DescriptorInfoTab IS TABLE OF DescriptorInfo INDEX BY
17 BINARY_INTEGER;
18
19 TYPE SQLTab IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
20
21 -- Use this where clause for rowid based operations
22 ROWID_WHERE_CLAUSE CONSTANT VARCHAR2(40) := ' AND tl.rowid = :p_rowid';
23 -- Use this where clause for itemid based operations
24 ITEMID_WHERE_CLAUSE CONSTANT VARCHAR2(40) := ' AND tl.rt_item_id = :p_item_id';
25 ITEMS_B_PREFIX CONSTANT VARCHAR2(40) := 'i';
26 ITEMS_TLP_PREFIX CONSTANT VARCHAR2(40) := 'tl';
27
28 -- Default max length for each row in icx_por_ctx_tl, set to 3600, leaving
29 -- 400 bytes for category name, section tags etc
30 DEFAULT_MAX_LENGTH CONSTANT NUMBER := 3600;
31
32 /* Constructs the sql required to populate icx_por_ctx_tl
33 - pCategoryId - Category Id, pass in 0 if constructing for the root attributes
34 - pDescriptors - Table containing info about searchable descriptors
35 - pWhereClause - Where clause to be appended to the end of the sql statements
36 - pLanguage - Language to use
37 - pMaxLength - The max length each ctx row should hold. This is used in
38 cases when we know each attribute is much shorter than it's
39 max length and we want to pack more attributes into each row
40 - pInsertSQL - Table containing SQL for processing new items for the loader
41 - pUpdateSQL - Table containing SQL for processing existing items
42 (Existing means it exists in icx_por_items_tl)
43 */
44 PROCEDURE build_ctx_sql(pCategoryId IN NUMBER,
45 pDescriptors IN DescriptorInfoTab,
46 pWhereClause IN VARCHAR2,
47 pLanguage IN VARCHAR2,
48 pMaxLength IN NUMBER,
49 pInsertSQL OUT NOCOPY SQLTab,
50 pUpdateSQL OUT NOCOPY SQLTab);
51
52 /* Constructs the sql required to populate icx_por_ctx_tl
53 - This will load the descriptor info for pCategoryId and then call the
54 version of build_ctx_sql that takes in a DescriptorInfoTab
55 - pCategoryId - Category Id, pass in 0 if constructing for the root attributes
56 - pWhereClause - Where clause to be appended to the end of the sql statements
57 - pLanguage - Language to use
58 - pMaxLength - The max length each ctx row should hold. This is used in
59 cases when we know each attribute is much shorter than it's
60 max length and we want to pack more attributes into each row
61 - pInsertSQL - Table containing SQL for processing new items for the loader
62 - pUpdateSQL - Table containing SQL for processing existing items
63 (Existing means it exists in icx_por_items_tl)
64 */
65 PROCEDURE build_ctx_sql(pCategoryId IN NUMBER,
66 pWhereClause IN VARCHAR2,
67 pLanguage IN VARCHAR2,
68 pMaxLength IN NUMBER,
69 pInsertSQL OUT NOCOPY SQLTab,
70 pUpdateSQL OUT NOCOPY SQLTab);
71
72
73 /* Constructs the sql required to populate icx_por_ctx_tl
74 - pCategoryId - Category Id, pass in 0 if constructing for the root attributes
75 - pWhereClause - Where clause to be appended to the end of the sql statements
76 - pLanguage - Language to use
77 - pInsertSQL - Table containing SQL for processing new items for the loader
78 - pUpdateSQL - Table containing SQL for processing existing items
79 (Existing means it exists in icx_por_items_tl)
80 */
81 PROCEDURE build_ctx_sql(pCategoryId IN NUMBER,
82 pWhereClause IN VARCHAR2,
83 pLanguage IN VARCHAR2,
84 pInsertSQL OUT NOCOPY SQLTab,
85 pUpdateSQL OUT NOCOPY SQLTab);
86
87 /* Constructs the sql required to populate icx_por_ctx_tl. Use this if you
88 don't want the sql to include a filter on the language
89 - pCategoryId - Category Id, pass in 0 if constructing for the root attributes
90 - pWhereClause - Where clause to be appended to the end of the sql statements
91 - pInsertSQL - Table containing SQL for processing new items for the loader
92 - pUpdateSQL - Table containing SQL for processing existing items
93 (Existing means it exists in icx_por_items_tl)
94 */
95 PROCEDURE build_ctx_sql(pCategoryId IN NUMBER,
96 pWhereClause IN VARCHAR2,
97 pInsertSQL OUT NOCOPY SQLTab,
98 pUpdateSQL OUT NOCOPY SQLTab);
99
100
101 END ICX_POR_CTX_SQL_PKG;
[Home] [Help]