Re: Stored Procedure problem.

Hi,

Please check if the privileges to select from dba_indexes, have been granted to
the user-id (schema) that is creating the SP, via a role. If that's the case, the SP shall give this error even though you may be able to select from sys.dba_indexes from SQL * Plus. If this indeed is the case, you'll need to grant SELECT privilege directly to the user-id that owns the SP.

Hope this helps.

Regards
Bhooshan

manish1000_at_hotmail.com (Manish) wrote in message news:<9d90ea9a.0205081449.1a2b56dc_at_posting.google.com>...
> Hi
>
> I want to create a procedure (as follows) which takes the rows from
> dba_indexes view in its cursor. When I compile, It is giving the error
> :
>
> PLS-00201: identifier 'SYS.DBA_INDEXES' must be declared.
>
> When I use user_indexes view, it compiled correctly. The schema,
> containing the procedure, is having DBA previlidges and I can select
> the rows from dba_indexes view in SQL Plus connected thru that schema.
> Why this not allowed in Stored Procedure? Any help will be
> appreciated.
>
> CREATE OR REPLACE PROCEDURE usp_comp_inv_obj111 as
> Cursor c1 is select index_name from dba_indexes
> where table_name not like 'TMP_%';
> .....
>
> Manish
Received on Thu May 09 2002 - 06:18:27 CEST