| PLS-00201 [message #260739] |
Mon, 20 August 2007 17:06  |
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  |
 |
BlackSwan
Messages: 26766
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 |
 |
Barbara Boehmer
Messages: 9101
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
