PACKAGE BODY: APPS.EDW_DROP_INDEX

Source

1 package body EDW_DROP_INDEX AS
2 /* $Header: EDWDRNDB.pls 115.2 2002/12/06 01:49:26 jwen noship $*/
3 
4 procedure edw_drop_btree_ind (owner VARCHAR2, table_name VARCHAR2) IS
5 x_index_name	varchar(30);
6 sql_stmt	varchar(2000);
7 cur_stmt	varchar2(2000);
8 x_table_name	varchar2(30);
9 x_owner		varchar2(30);
10 
11 TYPE IndexCurType is REF CURSOR;
12 ind_cv	IndexCurType;
13 
14 BEGIN
15 
16 x_table_name := UPPER(table_name);
17 x_owner := UPPER(owner);
18 
19 cur_stmt := 'SELECT index_name FROM dba_indexes
20 where index_type = ''NORMAL''
21 and uniqueness = ''NONUNIQUE''
22 and owner = :x_owner
23 and table_name =:x_table_name';
24 
25 OPEN ind_cv FOR cur_stmt USING x_owner, x_table_name;
26 
27 LOOP
28 	FETCH ind_cv INTO x_index_name;
29 	EXIT WHEN ind_cv%NOTFOUND;
30 	sql_stmt := 'drop index '|| x_owner ||'.'|| x_index_name ;
31 	execute immediate sql_stmt;
32 END LOOP;
33 
34 CLOSE ind_cv;
35 
36 EXCEPTION
37 	WHEN OTHERS THEN NULL;
38 
39 END;
40 
41 END EDW_DROP_INDEX;