PL/SQL problem.... (Rebuild Indexes)

From: <ashesh48_at_my-dejanews.com>
Date: Tue, 18 Aug 1998 20:24:19 GMT
Message-ID: <6rcntj$1as$1@nnrp1.dejanews.com>

Hi All, I am trying to write a query for rebuilding the Indexes but it gives me the following errors..The code and error is as below. I don't get where i am making mistake...? this would be a grate help.

SQL> create or replace Procedure CID.REBUILD_INDEXES  2  IS  3	3  --select


'Fragmented Indexes rebuilt on	'||  4	--to_char(sysdate,'mm/dd/yyyy')||' at

'||to_char(sysdate,'hh:mi:ss AM') Comments 5 --from dual ; 6 6
v_cursorid integer; 7 v_dummy integer; 8 v_index_name varchar2(50); 9

 v_drop_stmt  varchar2(100);  10  10  cursor c1  is select segment_name from 
sys.dba_segments  11  where owner = 'CID'  12  and  extents > 1  13  and 
segment_type = 'INDEX' ;  14  14  BEGIN  15  15  -- Open Cursor  16 
v_cursorid := dbms_sql.open_cursor ;  17  open c1 ;  18  18  loop  19  fetch
c1 into v_index_name ;	20  exit when c1%notfound ;  21  21  insert into

cid.index_history(index_seq_num, process_name,action,process_date) 22 values (rebuild_index_seq.nextval,v_index_name,'Index Rebuilt',sysdate) ; 23  23 -- Statement for the rebuilding the indexes. 24 v_drop_stmt := 'alter index '||v_index_name||' rebuild tablespace cid_index' ; 25 25 -- Parse the Statement 26 dbms_sql.parse(v_cursorid,v_drop_stmt,dbms_sql.v7); 27 27 -- Execute the statement. 28 v_dummy := dbms_sql.execute(v_cursorid);

29  29	end loop ;  30	30  -- Close cursor.  31 
dbms_sql.close_cursor(v_cursorid);  32	commit ;  33  33  END; -- Procedure 

34 /

---

LINE/COL ERROR

-------- -----------------------------------------------------------------

10/15 PL/SQL: SQL Statement ignored
10/22 PLS-00320: the declaration of the type of this expression is

         incomplete or malformed

10/41 PLS-00201: identifier 'SYS.DBA_SEGMENTS' must be declared 19/5 PL/SQL: SQL Statement ignored SQL> ************* here Index_history is the local table which will keep info about the rebuilt indexes...

Thanks you for your help in advance.

Ashesh

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Aug 18 1998 - 15:24:19 CDT