checking for utl_file_dir

[Muskaan]

Hi All. I am checking for a directory in utl_file_dir from table v$parameter. It compiles with errors(see below). Could anyone please tell me what I am doing wrong?  
 1  CREATE OR REPLACE FUNCTION isvaliddir (dir_in IN VARCHAR2)
 2     RETURN BOOLEAN
 3  IS
 4     v_value v$parameter.VALUE%TYPE;
 5  BEGIN
 6     SELECT VALUE
 7       INTO v_value
 8       FROM v$parameter
 9      WHERE name = 'utl_file_dir'
10        AND (   UPPER (VALUE) LIKE
11                   UPPER (dir_in)
12             OR VALUE = '*'); -- Any directory is OK!
13     RETURN TRUE;
14  EXCEPTION
15     WHEN NO_DATA_FOUND
16     THEN
17        RETURN FALSE;
18     WHEN TOO_MANY_ROWS
19     THEN
20        DBMS_OUTPUT.put_line (
21           'Multiple directories fit criteria. Please narrow.'
22        );
23        RETURN TRUE;
24* END;
25  /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION ISVALIDDIR:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/12     PLS-00201: identifier 'SYS.V_$PARAMETER' must be declared
4/12     PL/SQL: Item ignored
6/4      PL/SQL: SQL Statement ignored
8/11     PLS-00201: identifier 'SYS.V_$PARAMETER' must be declared

 

[rcurva]

SYS have to explicitly grant an object privilege "SELECT" to this v_$parameter table to your user.


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[Muskaan]

Thanks for your help Robbie. I have asked the DBAs to grant select privileges for V_$PARAMETER.

But what still puzzles me is, when I query on v$parameter directly on SQL prompt, it works fine. Then why doesn't the function above compile for the same user ?

 

[rcurva]

This is because in Oracle, all roles acquired are disbled inside the Stored Procedure so you need an explicit grant of the priv. instead of the one acquire through roles.


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War