Re: Need help with V$PARAMETER

Thanks Anton ,

Yes, I logged as SYS/CHANGE_ON_INSTALL_at_AZIZ and issued the command

  GRANT ON SELECT TO SYS.V_$PARAMETER TO MY_DBA; Where MY_DBA is a role and AZIZ user has the DBA role granted and user AZIZ own the function, ie. function is compiled in AZIZ schema.

"Anton Buijs" <aammbuijs_at_xs4all.nl> wrote in message news:amijoo$2t8$1_at_news1.xs4all.nl...
> I tested your function but it works fine with me (after eliminating the
> errors). Are you sure you granted select to the user who owns the function
> when you where connected as SYS? Only SYS can give those grants. Because
you
> use stored PL/SQL you can't grant via role, you must grant directly.
> Query DBA_TAB_PRIVS to check it.
>
> But I think a much easier solution is to call the function
> DBMS_UTILITY.GET_PARAMETER_VALUE
> In my database execute priv is granted to public but mayby I did that
> myself, it's my playground db.
> It is described in the book Oracle8i Supplied PL/SQL Packages Reference.
>
> Aziz <azizbr_at_rogers.com> schreef in berichtnieuws
> 6c3j9.12047$q41.138_at_news02.bloor.is.net.cable.rogers.com...
> | 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
> |
> |
> |
>
Received on Sun Sep 22 2002 - 08:25:29 CDT