PLS-00201: UTL_FILE identifier must be declared
chijarNov 30 2007 — edited Nov 30 2007dear 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