PLS-00201: identifier UTIL_FILE must be declared

Question

I'm trying to export data from a query into a csv file from Oracle Enterprise Express installed on a Windows Server 2008 machine. I've found this solution: http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:235814350980 which basically writes a function and uses the UTIL_FILE object to create and write to a file and add delimiters. I receive the follow error when I try and create the function in Oracle SQL Developer: ``` PLS-00201: identifier UTIL_FILE must be declared. ``` When I run the following command: ``` select owner, object_type from all_objects where object_name = 'UTL_FILE' ``` The result is: ``` OWNER Object Type --------- ----------- PUBLIC SYNONYM ``` EDIT: Running: ``` GRANT EXECUTE ON UTL_FILE TO PUBLIC ``` Gives: ``` Error starting at line 2 in command: GRANT EXECUTE ON UTL_FILE TO PUBLIC Error report: SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause: *Action: ``` What is the problem?

Accepted Answer

Seems like lack of privileges to me. Often `PUBLIC` user has `EXECUTE` privilege granted on that package, but the privilege may be revoked.

You can check if `PUBLIC` has that privilege by issuing the following query:

```
SELECT * FROM all_tab_privs WHERE grantee = 'PUBLIC' AND table_name = 'UTL_FILE';
```

If there are no rows returned, try granting the execute privilege to either the user you are logged as, or to `PUBLIC`, as some privileged user, for example `SYS`:

```
GRANT EXECUTE ON SYS.utl_file TO user_name;
```

Edit

You must grant the privilege while being logged as, for example, `SYS` user.

PLS-00201: identifier UTIL_FILE must be declared

Solution

Problem