Re: Need help with V$PARAMETER

On Sat, 21 Sep 2002 18:50:42 GMT, "Aziz" <azizbr_at_rogers.com> wrote:

>I need to write a function, which will return me the path for UTL_FILE(see
>bellow). When I compile this function, I am getting error message
>'table/view doesn't exits' and pointing to SYS.V_$PARAMETER. I have granted
>the SELECT privilage to me on this table.
>
>FUNCTION GetUTLFilePath
>RETURN VARCHAR2 AS
>BEGIN
> DECLARE
>
> cPath SYS.V_$PARAMETER.VALUE%TYPE;
>
> CURSOR curVP IS
> SELECT VALUE FROM SYS.V_$PARAMETER
> WHERE UPPER(NAME) = 'UTL_FILE_DIR';
>
> BEGIN
>
> OPEN curVP;
> FETCH curVP INTO cPath;
> IF curVP%NOTFOUND OR cPath IS NULL OR cPath = '*' THEN
> cPath := UTL_FILE_DIR;
> END IF;
> CLOSE curVP;
>
> RETURN cPath ;
>
> END;
>END;
>
>
>But using SQL Plus I can run the query
>
> SELECT VALUE FROM SYS.V_$PARAMETER
> WHERE UPPER(NAME) = 'UTL_FILE_DIR';
>
>and get the correct results without any error message.
>
>Can anybody please tell me if there is any specific restrictions on using of
>V_$PARAMETER view or V$PARAMETER table in store procedure/packages.
>Your help much appreciated.
>
>Regards
>
>Aziz Basharyar
>
>
>

Hi Aziz,

Did you grant the SELECT on SYS.V$PARAMETER through a role ?

You need to grant the SELECT directly to the user when you select via a stored proc.

• Kenneth Koenraadt