PLS-00201: UTIL_FILE identifier must be declared

Question

I am trying to export data from a request to a csv file from Oracle Enterprise Express installed on a computer running Windows Server 2008.

I found this solution:

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:235814350980

which basically writes the function and uses the UTIL_FILE object to create and write to the file and add delimiters.

I get the following error when trying to create a 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' 

Result:

 OWNER Object Type --------- ----------- PUBLIC SYNONYM 

EDIT:

Launch:

 GRANT EXECUTE ON UTL_FILE TO PUBLIC 

It 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?

Answer 1

It seems like the lack of privileges for me. Often a PUBLIC user has an EXECUTE privilege on this package, but the privilege can be revoked.

You can check if PUBLIC this privilege by running the following query:

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

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

 GRANT EXECUTE ON SYS.utl_file TO user_name; 

Edit

You must grant this privilege during registration, for example, SYS user.

Answer 2

In addition to the possible lack of permissions that were affected by the other answers, your question indicates that you are receiving a message:

 PLS-00201: identifier UTIL_FILE must be declared 

This means that in your function you specified UTIL_FILE , and not the built-in package UTL_FILE . It may have been a mistake you entered to write the question, but you used it in the text, so maybe you have the wrong package name in your code if you did not just copy and paste the Tom code.

In any case, you will need execution rights to UTL_FILE if you do not already have them.

Answer 3

Users by default do not have permission to execute on UTL_FILE. To use UTL_FILE, the administrator or administrator of ADMIN must explicitly specify EXECUTE permission on it, for example, in the following example:

 GRANT EXECUTE ON SYS.UTL_FILE TO scott; 

Answer 4

As user h_djebli pointed out in his comment, you should be logged in as a SYS user first.

To do this, you must be in the oracle home directory:

 cd $ORACLE_HOME 

Then do:

 sqlplus / as sysdba 

sqlplus will start in your terminal and you will be connected as a SYS user.

Finally, you can write the GRANT command in the sqlplus console:

 GRANT EXECUTE ON SYS.utl_file TO your_db_username;