Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201 (10.2.0.2.0 ,windows)
PLS-00201 [message #437373] Mon, 04 January 2010 02:19 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Hi all,

when a user execute the procedure which does not have DBA role/priv

exec man.proce_dd_seal('manual_dd_real','Y','Y');

he is getting below error

exec man.proce_dd_seal('manual_dd_real','Y','Y');


ORA-06550: line 2, column 2:
PLS-00201: identifier 'man.proce_dd_seal' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

when i execute the same procedure using my schema (which has DBA role/privileges),
it is successfully executed.

The difference between user and my schema is


Can someone give me solution for this error.

Thanks,
Re: PLS-00201 [message #437374 is a reply to message #437373] Mon, 04 January 2010 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The difference between user and my schema is

is what?

The first user has not the privilege to execute the procedure.

Regards
Michel
Re: PLS-00201 [message #437377 is a reply to message #437373] Mon, 04 January 2010 02:48 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
PLS-00201:identifier 'string' must be declared
Cause: You tried to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.

Action:
1) Check your spelling and declaration of the referenced name.
2) Verify that the declaration for the referenced item is placed correctly in the block structure.
3) If the referenced item is indeed declared but you don't have privileges to refer to that item, for security reasons, you will be notified only that the item is not declared.
4) If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior. Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.


More over you shold get

PLS-00904: insufficient privilege to access object user.object_name


sriram Smile

[Updated on: Mon, 04 January 2010 03:11] by Moderator

Report message to a moderator

Re: PLS-00201 [message #437384 is a reply to message #437377] Mon, 04 January 2010 03:27 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

I received this error.

ORA-06550: line 2, column 2:
PLS-00201: identifier 'man.proce_dd_seal' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored


based on your explanation , i think the user does not have sufficient privilege to execute the procedure.


Thanks

Re: PLS-00201 [message #437385 is a reply to message #437374] Mon, 04 January 2010 03:30 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
"The first user has not the privilege to execute the procedure."

Yes michel,

The first user does not have DBA role to execute the procedure whereas my schema
has the role to execute it.

Regards,

Re: PLS-00201 [message #437386 is a reply to message #437384] Mon, 04 January 2010 03:33 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,

See, the explanation given is proper. If it has to do with priveleges as you mentioned, then the error would have been 'Insufficient Privileges'. Oracle does specify that explicitly.
Try to recomplie your proc once more and then execute it.

Regds
Soni
Re: PLS-00201 [message #437387 is a reply to message #437386] Mon, 04 January 2010 03:38 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Hi soni,

I have also mentioned clearly that the procedure was executed successfully under my schema
which has sufficient privilege whereas the other user who does not have DBA role received such error.

I suspect it could be due to insufficient privilege on object.

Regards,
Re: PLS-00201 [message #437391 is a reply to message #437387] Mon, 04 January 2010 03:45 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member

Tried 'grant execute on object to user;'?

Re: PLS-00201 [message #437393 is a reply to message #437391] Mon, 04 January 2010 03:53 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Thanks to soni and others who helped me on this.


Regards,

[Updated on: Mon, 04 January 2010 03:54]

Report message to a moderator

Re: PLS-00201 [message #437396 is a reply to message #437386] Mon, 04 January 2010 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then the error would have been 'Insufficient Privileges'.

Wrong, it depends on version and context.
In this case it is a privilege issue.

MICHEL> create procedure p is begin null; end;
  2  /

Procedure created.

MICHEL> exec p;

PL/SQL procedure successfully completed.

MICHEL> connect scott/tiger
Connected.
SCOTT> exec michel.p
BEGIN michel.p; END;

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


Regards
Michel
Re: PLS-00201 [message #437397 is a reply to message #437396] Mon, 04 January 2010 04:01 Go to previous message
soni_7
Messages: 33
Registered: July 2005
Member
Yep right Michael, Thanks
Previous Topic: Derived SQL
Next Topic: cursor
Goto Forum:
  


Current Time: Sun Sep 25 19:41:52 CDT 2016

Total time taken to generate the page: 0.04201 seconds