SQL PLS-00201：必须声明标识符 UTIL_FILE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20106162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLS-00201: identifier UTIL_FILE must be declared
提问by Kristy Welsh
I'm trying to export data from a query into a csv file from Oracle Enterprise Express installed on a Windows Server 2008 machine.
我正在尝试将查询中的数据从安装在 Windows Server 2008 机器上的 Oracle Enterprise Express 导出到 csv 文件中。
I've found this solution:
我找到了这个解决方案：
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:235814350980
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.
它基本上编写了一个函数并使用 UTIL_FILE 对象来创建和写入文件并添加分隔符。
I receive the follow error when I try and create the function in Oracle SQL Developer:
当我尝试在 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?
问题是什么？
回答by Przemyslaw Kruglej
Seems like lack of privileges to me. Often PUBLICuser has EXECUTEprivilege granted on that package, but the privilege may be revoked.
对我来说似乎缺乏特权。通常，PUBLIC用户已EXECUTE授予对该包的特权，但该特权可能会被撤销。
You can check if PUBLIChas that privilege by issuing the following query:
您可以PUBLIC通过发出以下查询来检查是否具有该权限：
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:
如果没有返回任何行，请尝试将执行权限授予您登录的用户，或授予PUBLIC某些特权用户执行权限，例如SYS：
GRANT EXECUTE ON SYS.utl_file TO user_name;
Edit
编辑
You must grant the privilege while being logged as, for example, SYSuser.
您必须在以SYS用户身份登录时授予权限。
回答by Alen Oblak
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:
默认情况下，用户对 UTL_FILE 没有执行权限。要使用 UTL_FILE，ADMIN 用户或实例管理员必须对其显式授予执行权限，例如在以下示例中：
GRANT EXECUTE ON SYS.UTL_FILE TO scott;
回答by Alex Poole
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.
这表明您在函数中引用了UTIL_FILE，而不是内置包UTL_FILE。当然，这可能是您在编写问题时引入的错误，但是您也在文本中使用了它，所以如果您不只是复制并粘贴 Tom 的代码，那么您的代码中的包名称可能是错误的.
You'll still need execute privileges on UTL_FILEanyway, if you don't have them already.
UTL_FILE无论如何，如果您还没有执行权限，您仍然需要执行权限。
回答by guillaume guerin
As user: h_djebli pointed out in his comment you need to be connected as SYS user in the first place.
作为用户：h_djebli 在他的评论中指出，您首先需要以 SYS 用户身份连接。
To do that, you have to be in your oracle home directory :
为此，您必须位于 oracle 主目录中：
cd $ORACLE_HOME
Then execute :
然后执行：
sqlplus / as sysdba
sqlplus will start in your terminal and you'll be connected as the SYS user.
sqlplus 将在您的终端中启动，您将以 SYS 用户身份连接。
You can finally write the GRANT command in your sqlplus console :
您终于可以在 sqlplus 控制台中编写 GRANT 命令：
GRANT EXECUTE ON SYS.utl_file TO your_db_username;
回答by kumar kislay
We can do this via cmd, with these steps:
我们可以通过 cmd 执行此操作，步骤如下：
- Login as
sysdba(connect sys as sysdba+ enter password as sys_password) grant execute on utl_file to <user_name>.- now we can check by query :
'SELECT * FROM all_tab_privs WHERE grantee = 'PUBLIC' AND table_name = 'UTL_FILE';'
- 登录为
sysdba(connect sys as sysdba+ 输入密码为 sys_password) grant execute on utl_file to <user_name>.- 现在我们可以通过查询来检查：
'SELECT * FROM all_tab_privs WHERE grantee = 'PUBLIC' AND table_name = 'UTL_FILE';'
