1 PACKAGE BODY IBC_IMT_PUB AS
2 /* $Header: ibcpimtb.pls 120.1 2005/05/31 23:22:29 appldev $ */
3 -- NAME
4 -- ibcpimtb.pls
5 --
6 -- DESCRIPTION
7 -- Package body for IBC_IMT_PUB in support of rebuilding iMT
8 -- indexes on IBC table IBC_Attribute_bundles. Expected use of this package
9 -- is either through Apps Concurrent Manager or package DBMS_JOBS.
10 --
11 -- NOTES
12 --
13 -- HISTORY
14 -- Marzia Usman and Sri Rangarajan Created 11/07/2003
15 -- Siva Devaki Declared IN and OUT as NOCOPY 05/31/2005
16 --
17 --------------------------------------------------------------------------------
18 -- Start of comments
19 -- API name : Optimize_IBC_IMT_Indexes
20 -- Type : Public
21 -- Pre-reqs : None
22 -- Function : Package that performs an iMT Optimize on all indexes across
23 -- all IBC iMT-indexed columns in a time-distributed fashion.
24 -- Parameters :
25 --
26 -- IN : p_optimize_level IN VARCHAR2 Optional
27 -- Specifies the type of iMT index optimization to perform.
28 -- Valid values are 'FAST','FULL', ctx_ddl.optlevel_fast or
29 -- ctx_ddl.optlevel_full.
30 --
31 -- Default is ctx_ddl.optlevel_full.
32 --
33 -- : p_runtime IN NUMBER Optional
34 -- Integer that indicates the total run-time (in seconds) of
35 -- this optimization function call. This time will be
36 -- divided equally amongst all indexes within the IBC
37 -- subsystem. A null value implies execution until
38 -- completion of the task.
39 --
40 -- Default is ctx_ddl.maxtime_unlimited.
41 --
42 -- Version : Current version 1.0
43 -- {add comments here}
44 -- Previous version 1.0
45 -- Initial version 1.0
46 --
47 -- End of comments
48 --
49
50 PROCEDURE OPTIMIZE_IBC_IMT_INDEXES
51 (ERRBUF OUT NOCOPY VARCHAR2,
52 RETCODE OUT NOCOPY NUMBER,
53 p_optimize_level IN VARCHAR2 := ctx_ddl.optlevel_full,
54 p_runtime IN NUMBER := ctx_ddl.maxtime_unlimited)
55 AS
56 --
57 l_maxtime VARCHAR2(30);
58 l_user_name VARCHAR2(30);
59 stmt VARCHAR2(256);
60 l_index_name VARCHAR2(50);
61 curs INTEGER;
62 ROWS INTEGER;
63
64 CURSOR c_index_name IS
65 SELECT index_name FROM all_indexes
66 WHERE index_name = 'IBC_ATTRIBUTE_BUNDLES_CTX'
67 AND owner = l_user_name;
68
69 --
70 BEGIN
71 --
72 ---------------------------
73 -- determine index owner --
74 ---------------------------
75 SELECT oracle_username INTO l_user_name
76 FROM fnd_oracle_userid
77 WHERE oracle_id = 549;
78
79 IF p_optimize_level = 'FAST' THEN
80 l_maxtime := NULL;
81 ELSE
82 IF p_runtime IS NULL
83 OR p_runtime = ctx_ddl.maxtime_unlimited
84 THEN
85 l_maxtime := ' maxtime unlimited';
86 ELSE
87 -- Two indexes so split time in half.
88 l_maxtime := ' maxtime '||TRUNC(p_runtime/2);
89 END IF;
90 END IF;
91
92 -----------------------
93 -- rebuild the index --
94 -----------------------
95 OPEN c_index_name;
96 FETCH c_index_name INTO l_index_name;
97 IF c_index_name%FOUND THEN
98 ad_ctx_ddl.OPTIMIZE_INDEX(l_user_name||'.'||l_index_name,p_optimize_level,p_runtime);
99 END IF;
100 CLOSE c_index_name;
101
102 /*
103 curs := dbms_sql.open_cursor;
104 stmt := 'alter index '||owner||'.IBC_ATTRIBUTE_BUNDLES_CTX rebuild online '||
105 'parameters(''optimize '|| p_optimize_level || l_maxtime ||''')';
106 dbms_sql.parse(curs, stmt, dbms_sql.native);
107 ROWS := dbms_sql.EXECUTE(curs);
108 dbms_sql.close_cursor(curs);
109
110 curs := dbms_sql.open_cursor;
111 stmt := 'alter index '||owner||'.IBC_ATTRIBUTE_BUNDLES_CTX rebuild online '||
112 'parameters(''optimize '|| p_optimize_level || l_maxtime ||''')';
113 dbms_sql.parse(curs, stmt, dbms_sql.native);
114 ROWS := dbms_sql.EXECUTE(curs);
115 dbms_sql.close_cursor(curs);
116 */
117 --
118 EXCEPTION
119 WHEN OTHERS THEN
120 ERRBUF := SUBSTRB(SQLERRM, 1, 80);
121 RETCODE := 2;
122 END Optimize_IBC_IMT_Indexes;
123
124 --------------------------------------------------------------------------------
125 -- Start of comments
126 -- API name : SYNC_IBC_IMT_INDEXES
127 -- Type : Public
128 -- Pre-reqs : None
129 -- Function : Package that performs an IMT Sync on all indexes across
130 -- all IBC IMT-indexed columns.
131 --
132 -- Parameters : None.
133 --
134 -- Version : Current version 1.0
135 -- {add comments here}
136 -- Previous version 1.0
137 -- Initial version 1.0
138 --
139 -- End of comments
140 --
141
142 PROCEDURE SYNC_IBC_IMT_INDEXES
143 (ERRBUF OUT NOCOPY VARCHAR2,
144 RETCODE OUT NOCOPY NUMBER )
145 AS
146
147 owner VARCHAR2(30);
148 stmt VARCHAR2(256);
149 l_owner_name VARCHAR2(30);
150 l_index_name VARCHAR2(50);
151
152 curs INTEGER;
153 ROWS INTEGER;
154
155 CURSOR c_index_name IS
156 SELECT index_name FROM all_indexes
157 WHERE index_name = 'IBC_ATTRIBUTE_BUNDLES_CTX'
158 AND owner = l_owner_name;
159
160 BEGIN
161 ---------------------------
162 -- determine index owner --
163 ---------------------------
164 SELECT oracle_username INTO l_owner_name
165 FROM fnd_oracle_userid
166 WHERE oracle_id = 549;
167
168 -----------------------
169 -- rebuild the index --
170 -----------------------
171 OPEN c_index_name;
172 FETCH c_index_name INTO l_index_name;
173 IF c_index_name%FOUND
174 THEN
175 ad_ctx_ddl.SYNC_INDEX(l_owner_name||'.'||l_index_name);
176 END IF;
177 CLOSE c_index_name;
178 /*
179 curs := dbms_sql.open_cursor;
180 stmt := 'alter index '||owner||'.IBC_ATTRIBUTE_BUNDLES_CTX rebuild online '||
181 'parameters(''sync'')';
182 dbms_sql.parse(curs, stmt, dbms_sql.native);
183 ROWS := dbms_sql.EXECUTE(curs);
184 dbms_sql.close_cursor(curs);
185
186 curs := dbms_sql.open_cursor;
187 stmt := 'alter index '||owner||'.IBC_ATTRIBUTE_BUNDLES_CTX rebuild online '||
188 'parameters(''sync'')';
189 dbms_sql.parse(curs, stmt, dbms_sql.native);
190 ROWS := dbms_sql.EXECUTE(curs);
191 dbms_sql.close_cursor(curs);
192 */
193 --
194 EXCEPTION
195 WHEN OTHERS THEN
196 ERRBUF := SUBSTRB(SQLERRM, 1, 80);
197 RETCODE := 2;
198 END SYNC_IBC_IMT_INDEXES;
199
200 END IBC_IMT_PUB;
[Home] [Help]