PROCEDURE: APPS.EDW_DROP_BTREE_IND

Source

1 PROCEDURE edw_drop_btree_ind (owner VARCHAR2, table_name VARCHAR2) AUTHID CURRENT_USER AS
2 /* $Header: EDWDRIND.pls 115.1.310.3 2001/08/31 18:52:16 pkm ship    $*/
3 
4 x_index_name	varchar(30);
5 sql_stmt	varchar(2000);
6 cur_stmt	varchar2(2000);
7 x_table_name	varchar2(30);
8 x_owner		varchar2(30);
9 
10 TYPE IndexCurType is REF CURSOR;
11 ind_cv	IndexCurType;
12 
13 BEGIN
14 
15 x_table_name := UPPER(table_name);
16 x_owner := UPPER(owner);
17 
18 cur_stmt := 'SELECT index_name FROM dba_indexes
19 where index_type = ''NORMAL''
20 and uniqueness = ''NONUNIQUE''
21 and owner = :x_owner
22 and table_name =:x_table_name';
23 
24 OPEN ind_cv FOR cur_stmt USING x_owner, x_table_name;
25 
26 LOOP
27 	FETCH ind_cv INTO x_index_name;
28 	EXIT WHEN ind_cv%NOTFOUND;
29 	sql_stmt := 'drop index '|| x_owner ||'.'|| x_index_name ;
30 	execute immediate sql_stmt;
31 END LOOP;
32 
33 CLOSE ind_cv;
34 
35 EXCEPTION
36 	WHEN OTHERS THEN NULL;
37 
38 END edw_drop_btree_ind;