Total Pageviews

January 9, 2018

1/09/2018 12:03:00 PM
When you run UTL_FILE, you might encounter these errors  – possible causes and fixes are given below.

1. PLS-00201: identifier ‘UTL_FILE’ must be declared

(a) Check that UTL_FILE package exists and is valid.
SQL> select owner
  2       , object_type
  3       , status
  4  from all_objects
  5  where object_name = 'UTL_FILE';

-------- ---------------- -------
SYS      PACKAGE          VALID
If you get a listing as above(i.e Status is  in valid , all is well – move to check (b)
If the package status is invalid, recompile the package. 

If the package is not listed, run {ORACLE_HOME}/rdbms/admin/utlfile.sql to create it.
(b) Check if the logged in user has execute privilege on UTL_FILE
A missing grant is the most likely cause of the PLS-00201 error.
SQL> select grantee
  2  from all_tab_privs
  3  where table_name = 'UTL_FILE';

For UTL_FILE to work, the grantee should be either the user logged in, or PUBLIC. If this privilege is missing, log in as an admin user (e.g. SYS) and grant EXECUTE on UTL_FILE.
Log back in as the application user, and check the execute privilege on UTL_FILE.
The script should be able to recognize UTL_FILE now, without PLS-00201.

For more information on UTL_FILE What is UTL_FILE?
Related Posts Plugin for WordPress, Blogger...