[Home] [Help]
PACKAGE BODY: APPS.AMV_IMT_PUB
Source
1 PACKAGE BODY AMV_IMT_Pub AS
2 /* $Header: amvpimtb.pls 120.1 2005/06/21 17:48:12 appldev ship $ */
3 -- NAME
4 -- amvpimtb.pls
5 --
6 -- DESCRIPTION
7 -- Package body for AMV_IMT_PUB in support of rebuilding iMT
8 -- indexes on AMV table amv_c_channels_tl. Expected use of this package
9 -- is either through Apps Concurrent Manager or package DBMS_JOBS.
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_AMV_IMT_Indexes
20 -- Type : Public
21 -- Pre-reqs : None
22 -- Function : Package that performs an iMT Optimize on all indexes across
23 -- all AMV 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_AMV_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 = 520;
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/2);
81 END IF;
82 END IF;
83
84 -----------------------
85 -- rebuild the index --
86 -----------------------
87 curs := dbms_sql.open_cursor;
88 stmt := 'alter index '||owner||'.amv_c_channels_name_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||'.amv_c_channels_desc_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 --
102 EXCEPTION
103 WHEN OTHERS THEN
104 ERRBUF := substrb(sqlerrm, 1, 80);
105 RETCODE := 2;
106 END Optimize_AMV_IMT_Indexes;
107
108 --------------------------------------------------------------------------------
109 -- Start of comments
110 -- API name : Sync_AMV_IMT_Indexes
111 -- Type : Public
112 -- Pre-reqs : None
113 -- Function : Package that performs an iMT Sync on all indexes across
114 -- all AMV iMT-indexed columns.
115 --
116 -- Parameters : None.
117 --
118 -- Version : Current version 1.0
119 -- {add comments here}
120 -- Previous version 1.0
121 -- Initial version 1.0
122 --
123 -- End of comments
124 --
125
126 PROCEDURE Sync_AMV_IMT_Indexes
127 (ERRBUF OUT NOCOPY VARCHAR2,
128 RETCODE OUT NOCOPY NUMBER )
129 AS
130
131 owner varchar2(30);
132 stmt varchar2(256);
133 curs integer;
134 rows integer;
135
136 begin
137 ---------------------------
138 -- determine index owner --
139 ---------------------------
140 select oracle_username into owner
141 from fnd_oracle_userid
142 where oracle_id = 520;
143
144 -----------------------
145 -- rebuild the index --
146 -----------------------
147 curs := dbms_sql.open_cursor;
148 stmt := 'alter index '||owner||'.amv_c_channels_name_ctx rebuild online '||
149 'parameters(''sync'')';
150 dbms_sql.parse(curs, stmt, dbms_sql.native);
151 rows := dbms_sql.execute(curs);
152 dbms_sql.close_cursor(curs);
153
154 curs := dbms_sql.open_cursor;
155 stmt := 'alter index '||owner||'.amv_c_channels_desc_ctx rebuild online '||
156 'parameters(''sync'')';
157 dbms_sql.parse(curs, stmt, dbms_sql.native);
158 rows := dbms_sql.execute(curs);
159 dbms_sql.close_cursor(curs);
160
161 --
162 EXCEPTION
163 WHEN OTHERS THEN
164 ERRBUF := substrb(sqlerrm, 1, 80);
165 RETCODE := 2;
166 END Sync_AMV_IMT_Indexes;
167
168 END AMV_IMT_Pub;