Re: Stored Function problem

sminkeyk_at_interramp.com (Mark Sminkey) wrote:

>Hello,
 

>I'm using Oracle 7.1.6.2 on an HPUX machine running HPUX version 10.
 

>My question:
 

>I have written a stored function which returns the maximum oracle
>date. I have called this function SF_MAXDATE.
 

>I can do the following when logged in as the owner of the function:
 

>SELECT SF_MAXDATE
>FROM dual;
 

>and it correctly returns the maximum date (31-DEC-4097)
 

>I then granted execute permission to this function to a role and
>granted this role to a particular user. I also created a public
>synonym for this function.

>However when I log in as a user having this role and attempt to run
>the SELECT statement above, I get the following error:
 

>ERROR:
>ORA-06550: line 1, column 12:
>PLS-00201: identifier 'SF_MAXDATE' must be declared
>ORA-06550: line 1, column 7:
>PL/SQL: Statement ignored
 

>This is strange because I can do the following:
 

>SQL> desc SF_MAXDATE
>FUNCTION SF_MAXDATE RETURNS DATE
 
>Does anyone know what is going on here and how I can fix this problem?

I have discovered that when I grant execute permission on this function to the specific user it works fine.

Also, it works okay in a PL/SQL program when granted to the role.

It appears that this may be a SQL*Plus error.

-Mark Received on Wed Mar 20 1996 - 00:00:00 CET