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

Regrettably, Madhu is absolutely right
PL/SQL ignores roles. All grants to dba_views are either to a role, or to PUBLIC, which is again indirect.
This will probably get resolved in the next version, though the feature already exists quite some time.
NB: Sql*plus doesn't suffer from thies problem

Thanks,

Sybrand Bakker

Madhu Cherukuri wrote:

> Check if you have the select previlages
> on sys.dba_segements table (If not grant the previlage on that table)
>
> That could be one problem
>
> Madhu Cherukuri
>
> ashesh48_at_my-dejanews.com wrote in message
> <6rcntj$1as$1_at_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 Wed Aug 19 1998 - 11:52:29 CDT