Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201
PLS-00201 [message #260739] Mon, 20 August 2007 17:06 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
RHEL4 Ora10.2
There is a stored procedure that's part of a package:

procedure getPROCs_sp
( out_PROCs_rc OUT refcursor) AS

begin
open out_PROCs_rc for

SELECT h.PROCId,
h.Description,
h.optincopytypeid,
h.optincreativetypeid,
hu.username,
hu.updatetime
FROM PROC h,
PROCUpdates hu,
(SELECT PROCid, max(updatetime) uptime
FROM PROCupdates GROUP by PROCid) t
WHERE h.PROCId = hu.PROCid AND
hu.updatetime = t.uptime
ORDER BY h.PROCId;

end getPROCs_sp;

I am attempting to execute the stored procedure as the schema owner. Since this is the case, I assume that since the package was created in the schema owner's schema, the schema owner must have execute privileges on this stored procedure. However, I get the below error when attempting to execute the stored procedure:

SQL> exec esave.getPROCs_sp
BEGIN esave.getPROCs_sp; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PROCS_SP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Every post I've read about this error indicates that the user executing the stored procedure does not have execute privileges on the stored procedure. How can that be the case in this instance when the stored procedure is being executed by the schema owner?
Thanks.
Re: PLS-00201 [message #260740 is a reply to message #260739] Mon, 20 August 2007 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Every post I've read about this error indicates that the user executing the stored procedure does not have execute privileges on the stored procedure
However it appears you are incapable or unwilling to follow posting guidelines as stated in URL from the STICKY post at top of this forum.
http://www.orafaq.com/forum/t/85038/74940/

Please use CUT & PASTE to show us that procedure getPROCs_sp exists & was successfully compiled.

[Updated on: Mon, 20 August 2007 17:12] by Moderator

Report message to a moderator

Re: PLS-00201 [message #260741 is a reply to message #260739] Mon, 20 August 2007 17:21 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The error that you have posted does not match the procedure that you have posted. There is no "procs_sp" identifier in the "getprocs_sp" that you have posted. Is esave a package name or schema name or both? In general, all required privileges, not just on the procedure, but on the objects (tables) referenced in the procedure must be granted directly, not through a role. Procedures ignore roles and only recognize explicitly granted privileges.
Previous Topic: ORDER BY
Next Topic: WHICH ONE IS FASTER?
Goto Forum:
  


Current Time: Sat Dec 10 01:14:13 CST 2016

Total time taken to generate the page: 0.09212 seconds