CODEWITHSUNDEEP
sqloracle-database
Kristy Welsh
Kristy Welsh

Reputation: 8392

PLS-00201: identifier UTIL_FILE must be declared

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?

Upvotes: 8

Views: 45768

Answers (5)

kumar kislay
kumar kislay

Reputation: 1

We can do this via cmd, with these steps:

  1. Login as sysdba (connect sys as sysdba + enter password as sys_password)
  2. grant execute on utl_file to <user_name>.
  3. now we can check by query :'SELECT * FROM all_tab_privs WHERE grantee = 'PUBLIC' AND table_name = 'UTL_FILE';'

Upvotes: -1

guillaume guerin
guillaume guerin

Reputation: 367

As user: h_djebli pointed out in his comment you need to be connected as SYS user in the first place.

To do that, you have to be in your oracle home directory :

cd $ORACLE_HOME

Then execute :

sqlplus / as sysdba

sqlplus will start in your terminal and you'll be connected as the SYS user.

You can finally write the GRANT command in your sqlplus console :

GRANT EXECUTE ON SYS.utl_file TO your_db_username;

Upvotes: 1

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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.

Upvotes: 13

Alex Poole
Alex Poole

Reputation: 191570

Aside from the possible lack of permissions that other answers have covered, your question says the error you get is:

PLS-00201: identifier UTIL_FILE must be declared

That suggests you've referenced UTIL_FILE, rather than the built-in package UTL_FILE, in your function. It might be an error you've introduced writing the question, of course, but you used it in the text too so maybe you have got the package name wrong in your code, if you didn't just copy-and-paste Tom's code.

You'll still need execute privileges on UTL_FILE anyway, if you don't have them already.

Upvotes: 1

Alen Oblak
Alen Oblak

Reputation: 3325

Users do not have execute permission on UTL_FILE by default. To use UTL_FILE, an ADMIN user or instance administrator must explicitly GRANT EXECUTE permission on it, such as in the following example:

GRANT EXECUTE ON SYS.UTL_FILE TO scott;

Upvotes: 2

Related Questions