Need help with V$PARAMETER

From: Aziz <azizbr_at_rogers.com>
Date: Sat, 21 Sep 2002 18:50:42 GMT
Message-ID: <6c3j9.12047$q41.138@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 - 13:50:42 CDT