Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLS-00201: UTL_FILE identifier must be declared

chijarNov 30 2007 — edited Nov 30 2007
dear friends
i have a problem with utl_file package.

- Oracle 10gr2
- CENTos 4.0

The folder permissions are:
drwxrwx--- 2 oracle oinstall 4096 nov 29 17:40 test_UtlFileDir
...with that permisssion on the folder, the programmer done that file:
-rw-r--r-- 1 oracle oinstall 904 nov 29 17:40 example3.txt
everything is ok.

but....

I was done that yesterday in the night...
- i created a role and i assigned to that role execute utl_file
- then, i assigned this role to user TRAMITE
- revoke sys.utl_file from public.
Today
- i assigned to the same role the priv execute utl_smtp
- revoke sys.utl_smtp from public.

Here the actions:
*****************************************************************************************
SQL> SHO USER
USER es "SYS"

SQL> GRANT EXECUTE ON SYS.UTL_FILE TO ROLE_EXEC_UTL_FILE;

Concesión terminada correctamente.

SQL> GRANT ROLE_EXEC_UTL_FILE TO TRAMITE;

Concesión terminada correctamente.

SQL> REVOKE EXECUTE ON SYS.UTL_FILE FROM PUBLIC

Revocación terminada correctamente.

*****************************************************************************************

When my friend want to execute the package, oracle presents the next error:
=====================================================

0/0 PL/SQL: Compilation unit analysis terminated
5/19 PLS-00201: el identificador 'UTL_FILE' se debe declarar
5/19 PL/SQL: Item ignored
8/22 PL/SQL: SQL Statement ignored
8/53 PL/SQL: ORA-00942: la tabla o vista no existe
11/5 PLS-00320: la declaración de tipo de esta expresión está
incompleta o tiene un formato incorrecto
0/0 PL/SQL: Compilation unit analysis terminated
5/19 PLS-00201: el identificador 'UTL_FILE' se debe declarar
5/19 PL/SQL: Item ignored
8/22 PL/SQL: SQL Statement ignored
8/53 PL/SQL: ORA-00942: la tabla o vista no existe
11/5 PLS-00320: la declaración de tipo de esta expresión está
incompleta o tiene un formato incorrecto
=====================================================

When i looked for solution i saw this documento un metalink:Note:315283.1

...but i disagree about that, 'cause if i grant execute utl_file, utl_smtp to public, everyone can execute that Package.

Are there any other ways to assign "execute this packages" through role to users that i want without have any risk??

thanks for your answers, and apologize my english...

Cesar
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 28 2007
Added on Nov 30 2007
2 comments
1,153 views