Re: Need help with V$PARAMETER

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 Sat Sep 21 2002 - 15:10:27 CDT