[Home] [Help]
PACKAGE BODY: APPS.JTF_AMVIMT_PUB
Source
1 PACKAGE BODY JTF_AMVIMT_Pub AS
2 /* $Header: jtfpimtb.pls 115.8 2002/11/26 22:30:42 stopiwal ship $ */
3 -- NAME
4 -- jtfpimtb.pls
5 --
6 -- DESCRIPTION
7 -- Package body for JTF_AMVIMT_PUB in support of rebuilding iMT
8 -- indexes on JTF table jtf_amv_items_b and _tl.
9 -- Expected use of this package is either through Apps Concurrent Manager.
10 --
11 -- NOTES
12 --
13 -- HISTORY
14 -- 12/14/99 J Ray Created.
15 -- 02/17/00 slkrishn Updated.
16 --
17 --------------------------------------------------------------------------------
18 -- Start of comments
19 -- API name : Optimize_JTF_IMT_Indexes
20 -- Type : Public
21 -- Pre-reqs : None
22 -- Function : Package that performs an iMT Optimize on all indexes across
23 -- all JTF 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 AMV
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_JTF_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 owner varchar2(30);
59 stmt varchar2(256);
60 curs integer;
61 rows integer;
62
63 --
64 BEGIN
65 --
66 ---------------------------
67 -- determine index owner --
68 ---------------------------
69 select oracle_username into owner
70 from fnd_oracle_userid
71 where oracle_id = 690;
72
73 IF p_optimize_level = 'FAST' THEN
74 l_maxtime := null;
75 ELSE
76 IF p_runtime IS NULL OR p_runtime = ctx_ddl.maxtime_unlimited THEN
77 l_maxtime := ' maxtime unlimited';
78 ELSE
79 -- Two indexes so split time in half.
80 l_maxtime := ' maxtime '||TRUNC(p_runtime/4);
81 END IF;
82 END IF;
83
84 -----------------------
85 -- rebuild the index --
86 -----------------------
87 curs := dbms_sql.open_cursor;
88 stmt := 'alter index '||owner||'.jtf_amv_items_url_ctx rebuild online '||
89 'parameters(''optimize '|| p_optimize_level || l_maxtime ||''')';
90 dbms_sql.parse(curs, stmt, dbms_sql.native);
91 rows := dbms_sql.execute(curs);
92 dbms_sql.close_cursor(curs);
93
94 curs := dbms_sql.open_cursor;
95 stmt := 'alter index '||owner||'.jtf_amv_items_name_ctx rebuild online '||
96 'parameters(''optimize '|| p_optimize_level || l_maxtime ||''')';
97 dbms_sql.parse(curs, stmt, dbms_sql.native);
98 rows := dbms_sql.execute(curs);
99 dbms_sql.close_cursor(curs);
100
101 curs := dbms_sql.open_cursor;
102 stmt := 'alter index '||owner||'.jtf_amv_items_desc_ctx rebuild online '||
103 'parameters(''optimize '|| p_optimize_level || l_maxtime ||''')';
104 dbms_sql.parse(curs, stmt, dbms_sql.native);
105 rows := dbms_sql.execute(curs);
106 dbms_sql.close_cursor(curs);
107
108 curs := dbms_sql.open_cursor;
109 stmt := 'alter index '||owner||'.jtf_amv_items_text_ctx rebuild online '||
110 'parameters(''optimize '|| p_optimize_level || l_maxtime ||''')';
111 dbms_sql.parse(curs, stmt, dbms_sql.native);
112 rows := dbms_sql.execute(curs);
113 dbms_sql.close_cursor(curs);
114
115 --
116 EXCEPTION
117 WHEN OTHERS THEN
118 ERRBUF := substr(sqlerrm, 1, 80);
119 RETCODE := 2;
120 END Optimize_JTF_IMT_Indexes;
121
122 --------------------------------------------------------------------------------
123 -- Start of comments
124 -- API name : Sync_JTF_IMT_Indexes
125 -- Type : Public
126 -- Pre-reqs : None
127 -- Function : Package that performs an iMT Sync on all indexes across
128 -- all JTF iMT-indexed columns.
129 --
130 -- Parameters : None.
131 --
132 -- Version : Current version 1.0
133 -- {add comments here}
134 -- Previous version 1.0
135 -- Initial version 1.0
136 --
137 -- End of comments
138 --
139
140 PROCEDURE Sync_JTF_IMT_Indexes
141 (ERRBUF OUT NOCOPY VARCHAR2,
142 RETCODE OUT NOCOPY NUMBER )
143
144 AS
145
146 owner varchar2(30);
147 stmt varchar2(256);
148 curs integer;
149 rows integer;
150
151 begin
152 ---------------------------
153 -- determine index owner --
154 ---------------------------
155 select oracle_username into owner
156 from fnd_oracle_userid
157 where oracle_id = 690;
158
159 -----------------------
160 -- rebuild the index --
161 -----------------------
162 curs := dbms_sql.open_cursor;
163 stmt := 'alter index '||owner||'.jtf_amv_items_url_ctx rebuild online '||
164 'parameters(''sync'')';
165 dbms_sql.parse(curs, stmt, dbms_sql.native);
166 rows := dbms_sql.execute(curs);
167 dbms_sql.close_cursor(curs);
168
169 curs := dbms_sql.open_cursor;
170 stmt := 'alter index '||owner||'.jtf_amv_items_name_ctx rebuild online '||
171 'parameters(''sync'')';
172 dbms_sql.parse(curs, stmt, dbms_sql.native);
173 rows := dbms_sql.execute(curs);
174 dbms_sql.close_cursor(curs);
175
176 curs := dbms_sql.open_cursor;
177 stmt := 'alter index '||owner||'.jtf_amv_items_desc_ctx rebuild online '||
178 'parameters(''sync'')';
179 dbms_sql.parse(curs, stmt, dbms_sql.native);
180 rows := dbms_sql.execute(curs);
181 dbms_sql.close_cursor(curs);
182
183 curs := dbms_sql.open_cursor;
184 stmt := 'alter index '||owner||'.jtf_amv_items_text_ctx rebuild online '||
185 'parameters(''sync'')';
186 dbms_sql.parse(curs, stmt, dbms_sql.native);
187 rows := dbms_sql.execute(curs);
188 dbms_sql.close_cursor(curs);
189
190 --
191 EXCEPTION
192 WHEN OTHERS THEN
193 ERRBUF := substr(sqlerrm, 1, 80);
194 RETCODE := 2;
195 END Sync_JTF_IMT_Indexes;
196
197 END JTF_AMVIMT_Pub;