PACKAGE BODY: APPS.ICX_ITEM_IMT_INDEX_CREATE

Source

1 package body icx_item_imt_index_create as
2 /* $Header: ICXIMTXB.pls 115.0 2000/05/02 17:43:41 pkm ship       $ */
3 
4 type ErrorStackType is table of varchar2(1000) index by binary_integer;
5 g_error_stack ErrorStackType;
6 
7 g_exception EXCEPTION;
8 
9 procedure Debug(p_msg in varchar2) is
10 begin
11 --  dbms_output.put_line(p_msg);
12     null;
13 end Debug;
14 
15 procedure PushError(p_msg in varchar2) is
16 begin
17   if (p_msg is not null) then
18     g_error_stack(g_error_stack.COUNT + 1) := p_msg;
19   end if;
20 end PushError;
21 
22 procedure PrintStackTrace is
23   l_index binary_integer;
24 begin
25   if (g_error_stack.COUNT > 0) then
26     Debug('### Error Stack');
27     l_index := g_error_stack.FIRST;
28     while (l_index is not null) loop
29       Debug('###   '||g_error_stack(l_index));
30       l_index := g_error_stack.NEXT(l_index);
31     end loop;
32     Debug('### End of Stack');
33     g_error_stack.DELETE;
34   end if;
35 end PrintStackTrace;
36 
37 
38 /*===========================================================================
39 
40   PROCEDURE NAME: isOracle8i
41 
42 ==========================================================================*/
43   PROCEDURE isOracle8i(oracle8i out varchar2) is
44 
45   l_version       varchar2(20);
46   l_compatibility varchar2(20);
47   l_majorVersion  number;
48   l_minorVersion  number;
49 
50   BEGIN
51 
52     oracle8i := 'N';
53 
54     DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
55 --    dbms_output.put_line(l_version || ' ' || l_compatibility);
56     l_majorVersion := to_number(substr(l_version, 1, instr(l_version, '.')-1));
57     l_minorVersion := to_number(substr(l_version, instr(l_version, '.')+1,
58                                      instr(l_version, '.')-1));
59 --    dbms_output.put_line(to_char(l_majorVersion)||'-'||
60 --                         to_char(l_minorVersion));
61 
62     if ((l_majorVersion > 8) or
63         (l_majorVersion = 8 and l_minorVersion > 0)) then
64       oracle8i := 'Y';
65     end if;
66 
67   EXCEPTION
68     WHEN OTHERS THEN
69       oracle8i := 'N';
70   END isOracle8i;
71 
72 
73 /*===========================================================================
74 
75   PROCEDURE NAME : rebuild_index
76 
77 ==========================================================================*/
78   PROCEDURE rebuild_index is
79 
80    l_progress varchar2(10) := '000';
81    cursor_name INTEGER;
82    ret INTEGER;
83    l_8i varchar2(1) := 'N';
84 
85    BEGIN
86 
87      l_progress := '001';
88      isOracle8i(l_8i);
89      l_progress := '002';
90 
91      if (l_8i = 'Y') then
92 
93        l_progress := '002';
94 
95        cursor_name := DBMS_SQL.OPEN_CURSOR;
96 
97        -- drop the ctx index
98        begin
99          l_progress := '008';
100          DBMS_SQL.PARSE(cursor_name,
101            'DROP INDEX ICX.ICX_POR_ITEMS_TL_CTXIDX',
102            DBMS_SQL.NATIVE);
103          l_progress := '009';
104          ret := DBMS_SQL.EXECUTE(cursor_name);
105 
106        exception
107          when others then Debug('rebuild_index-'||l_progress||' '||SQLERRM);
108        end;
109 
110 
111        l_progress := '010';
112 
113         -- create the index
114 
115        DBMS_SQL.PARSE(cursor_name,
116           'CREATE INDEX ICX.ICX_POR_ITEMS_TL_CTXIDX ' ||
117           ' on ICX_POR_ITEMS_TL(CTX_DESC) ' ||
118           ' INDEXTYPE IS ctxsys.context',
119           DBMS_SQL.NATIVE);
120        l_progress := '011';
121        ret := DBMS_SQL.EXECUTE(cursor_name);
122 
123        DBMS_SQL.CLOSE_CURSOR(cursor_name);
124 
125      end if;
126 
127      l_progress := '012';
128 
129   exception
130     when others then
131       PushError('rebuild_index-'||l_progress||' '||SQLERRM);
132       raise g_exception;
133 
134   end rebuild_index;
135 
136 END icx_item_imt_index_create;
137