Q

Error on UTL_FILE

When I run a stored procedure this errors comes out: "PLS-00905: object RPT.UTL_FILE is invalid." Do you know what utl_file package is, and how I can fix the above problem in Oracle? Do you know how to determine if it is a privilege issue, fix a utl_file privilege issue or reinstall utl_file if not?

When I run a stored procedure called "Refer attachment" this error comes out: "PLS-00905: object RPT.UTL_FILE is invalid." "Refer attachment" runs the code stack on this line:
file_id UTL_FILE.FILE_TYPE;
Do you know what the UTL_FILE package is, and how I can fix the above problem in Oracle? Do you know how to determine if it is a privilege issue, fix a UTL_FILE privilege issue or reinstall UTL_FILE if not?
To determine the privilege for SYS.UTL_FILE, run the following SQL statement. But, you must have select privilege on sys.dba_tab_privs view. By default, PUBLIC has EXECUTE privileges granted on the SYS.UTL_FILE package:
select owner,table_name,grantee,privilege 
from dba_tab_privs where table_name = 'UTL_FILE';
If you need to change permissions on SYS.UTL_FILE, run the following statement:
grant execute on utl_file to <user or public>;
In order to write to the file system, the Oracle user must have write permissions to the directory that is listed as a value for the UTL_FILE_DIR init.ora parameter.

To reinstall SYS.UTL_FILE run the following scripts:

$ORACLE_HOME/rdbms/admin/utlfile.sql
$ORACLE_HOME/rdbms/admin/prvtfile.plb
For more information on SYS.UTL_FILE see note 315283.1 on Oracle's Metalink. Also see appendix B in "Oracle Database 10g PL/SQL Programming."
This was first published in July 2006

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close