Re: Need help with V$PARAMETER

Thanks Richard,

I tried the DBMS_UTILITY.GET_PARAMETER_VALUE and it worked perfectly. I think I can not access V_$PARAMETER for some security reason implemented in our development environment.

I will use DBMS_UTILITY.GET_PARAMETER_VALUE

I appreciate your help very much.

Cheers

Aziz

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:Ewjj9.37944$g9.108326_at_newsfeeds.bigpond.com...
> Hi Aziz,
>
> Therefore grant the select *directly* to the required users (ie.
yourself).
>
> Cheers
>
> Richard
> "Aziz" <azizbr_at_rogers.com> wrote in message
> news:dxjj9.18233$q41.8296_at_news02.bloor.is.net.cable.rogers.com...
> > 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:51:15 CDT