RE: Analyze schema1.mytable from schema2 in a stored procedure

From: Miller, Jay <JayMiller_at_tdwaterhouse.com>
Date: Thu, 10 May 2001 07:49:45 -0700
Message-ID: <F001.002FF4F2.20010510073126@fatcity.com>

Did you grant the privileges directly or through a role? You need to grant them directly in order to have access to them in PL/SQL.

Jay Miller
x48355

-----Original Message-----
[mailto:Sherrie.Kubis_at_swfwmd.state.fl.us] Sent: Thursday, May 10, 2001 9:56 AM
To: Multiple recipients of list ORACLE-L

This problem is probably related to trying to do something from within a stored procedure that can't be done, however I can't find anything in the docs that say that. I've opened an iTar but the response has been WORTHLESS, and that goofy person keeps asking me in six different ways what grants are given to the object. I think they are stalling because they don't know and it at least looks like they are responding. So, I'll ask the people who really know stuff.

I have two schemas: schema1 and schema2. I need to analyze schema1.mytable from a stored procedure owned by schema2. Schema1 has granted ALL on mytable to Schema2. Logged into SQLPLUS as Schema2, these both work:

     ANALYZE TABLE SCHEMA1.MYTABLE COMPUTE STATISTICS;.
     EXECUTE DBMS_DDL.ANALYZE_OBJECT

('TABLE','SCHEMA1','MYTABLE','COMPUTE'); However, when schema2 executes a stored procedure with this line

DBMS_DDL.ANALYZE_OBJECT('TABLE','SCHEMA1','MYTABLE','COMPUTE') I get an insufficient privilege error.

Does this mean that privileges are interpreted differently within stored procedures that from SQLPLUS?

Any insights would be appreciated.

++++++++++++++++++++++++++++++++++++++++++++++++++

Sherrie Kubis
Southwest Florida Water Management District 2379 Broad Street
Brooksville FL 34604-6899

Phone: (352) 796-7211, Ext. 4033
Fax: (352) 754-6776
Email: Mailto:Sherrie.Kubis_at_swfwmd.state.fl.us

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Sherrie.Kubis_at_swfwmd.state.fl.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Miller, Jay
INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------