Home » SQL & PL/SQL » SQL & PL/SQL » grant execute to procedure
grant execute to procedure [message #415393] Mon, 27 July 2009 09:50 Go to next message
Unprez
Messages: 9
Registered: February 2009
Junior Member
Does grant execute on a procedure with a paramater different than a non paramater procedure. Recieving error msg of 'ORA-04042: procedure, function, package, or package body does not exist' When running the following:
PROCEDURE P_ERROR_MSG_DEBUG_INFO (P_Err_Code in Varchar2) IS..

>  grant execute on P_ERROR_MSG_DEBUG_INFO to user;


Thanks.
Re: grant execute to procedure [message #415394 is a reply to message #415393] Mon, 27 July 2009 09:53 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Which user is trying to issue GRANT?
Which user owns P_ERROR_MSG_DEBUG_INFO?
Re: grant execute to procedure [message #415395 is a reply to message #415393] Mon, 27 July 2009 09:54 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Parameters make no difference.
Does this procedure reside in a package?
Re: grant execute to procedure [message #415398 is a reply to message #415394] Mon, 27 July 2009 09:56 Go to previous messageGo to next message
Unprez
Messages: 9
Registered: February 2009
Junior Member
Owned and is issued by DA
Re: grant execute to procedure [message #415401 is a reply to message #415393] Mon, 27 July 2009 10:00 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: grant execute to procedure [message #415403 is a reply to message #415395] Mon, 27 July 2009 10:14 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Mon, 27 July 2009 15:54

Does this procedure reside in a package?

Re: grant execute to procedure [message #415405 is a reply to message #415403] Mon, 27 July 2009 10:23 Go to previous messageGo to next message
Unprez
Messages: 9
Registered: February 2009
Junior Member
Can I be advised how to determine if it is within a package? On another note, I am note sure if it is in da object now as when I run the following i get the 'ORA-00942: table or view does not exist' error msg. That being said, is there a way to search for this stored procedure?

 
SQL> select status from da where object_name='P_ERROR_MSG_DEBUG_INFO';
select status from da where object_name='P_ERROR_MSG_DEBUG_INFO'
Re: grant execute to procedure [message #415407 is a reply to message #415393] Mon, 27 July 2009 10:27 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd like to hope you would know if it was in a package, since you can't call procedures in packages without referencing the package name.

As for this:
select status from da where object_name='P_ERROR_MSG_DEBUG_INFO'


da is not a table_name is it?

presumably that query should be against all_objects.
Re: grant execute to procedure [message #415409 is a reply to message #415393] Mon, 27 July 2009 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Owned and is issued by DA
>select status from da

Is DA a schema name, table name, some other object type?
Re: grant execute to procedure [message #415464 is a reply to message #415393] Mon, 27 July 2009 23:55 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Please post output of these commands :-

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS 
WHERE OBJECT_NAME = 'P_ERROR_MSG_DEBUG_INFO';

SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'P_ERROR_MSG_DEBUG_INFO';
Re: grant execute to procedure [message #415673 is a reply to message #415464] Tue, 28 July 2009 10:42 Go to previous messageGo to next message
Unprez
Messages: 9
Registered: February 2009
Junior Member
Hi,

I am getting no rows selected for both select commands. I attached screenshot of code for that procedure.
  • Attachment: error.JPG
    (Size: 206.13KB, Downloaded 151 times)
Re: grant execute to procedure [message #415674 is a reply to message #415393] Tue, 28 July 2009 11:17 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SELECT NAME, TYPE, COUNT(LINE)
FROM ALL_SOURCE
WHERE OWNER = 'DA'
GROUP BY NAME, TYPE
ORDER BY 1, 2;

post results from SQL above
Re: grant execute to procedure [message #415675 is a reply to message #415393] Tue, 28 July 2009 11:49 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
If I'm reading that rather blurry screen shot correctly then
P_ERROR_MSG_DEBUG_INFO is a forms procedure.

If that is the case then you can't grant execute on it because forms procedures have no such concept.

It would also explain why you can't find it in any of the data dictionary views.
Re: grant execute to procedure [message #415689 is a reply to message #415393] Tue, 28 July 2009 13:23 Go to previous messageGo to next message
Unprez
Messages: 9
Registered: February 2009
Junior Member
I was contemplating that might be the case, but since I had other error msgs such as DA.DBK_SYS_RPT which was a procedure which required grant access I was not sure if I required grant access to these procedures too.
Not having a 'DA.' in front of the procedure name I'm guessing its not part of the DA schema but I'm sure its some grant issue because if my manager logs into oracle forms using DA i do not recieve those declaration error msgs.
Re: grant execute to procedure [message #415692 is a reply to message #415689] Tue, 28 July 2009 13:46 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Unprez wrote on Tue, 28 July 2009 19:23
I was contemplating that might be the case, but since I had other error msgs such as DA.DBK_SYS_RPT which was a procedure which required grant access I was not sure if I required grant access to these procedures too.
Not having a 'DA.' in front of the procedure name I'm guessing its not part of the DA schema but I'm sure its some grant issue because if my manager logs into oracle forms using DA i do not recieve those declaration error msgs.


The fact that the full code was visible in Form Builder was what give it away to me.
You need to learn to tell the difference between forms code and database code.
If it's visible in form builder it's forms code.
If has a schema name prefixed to it it's db code.
If it doesn't have a schema name it could be either - schema names aren't always required to call db code.

You need to have execute permission on all the database procedures that your form is calling granted to the user you're logging into form builder with.
Re: grant execute to procedure [message #415696 is a reply to message #415689] Tue, 28 July 2009 15:24 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Unprez wrote on Tue, 28 July 2009 14:23
I'm sure its some grant issue because if my manager logs into oracle forms using DA i do not recieve those declaration error msgs.


That's because THAT program unit is making calls to other procedures that the DA user can see that you cannot.
Previous Topic: Showing error ora-00923 from keyword not found where expected
Next Topic: analytic function
Goto Forum:
  


Current Time: Mon Dec 05 18:49:46 CST 2016

Total time taken to generate the page: 0.09205 seconds