执行在 Oracle 中生成 csv 文件的过程时无效的目录路径（在 Windows 中）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5152852/
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
Invalid Directory path while executing a procedure that produces a csv file in Oracle (in windows)
提问by Yahoo-Me
I have a procedure
我有一个程序
create or replace procedure dump_table_to_csv( p_tname in varchar2,
2 p_dir in varchar2,
3 p_filename in varchar2 )
4 is
5 l_output utl_file.file_type;
6 l_theCursor integer default dbms_sql.open_cursor;
7 l_columnValue varchar2(4000);
8 l_status integer;
9 l_query varchar2(1000)
10 default 'select * from ' || p_tname;
11 l_colCnt number := 0;
12 l_separator varchar2(1);
13 l_descTbl dbms_sql.desc_tab;
14 begin
15 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
16 execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''
';
17
18 dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
19 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
20
21 for i in 1 .. l_colCnt loop
22 utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"'
);
23 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
24 l_separator := ',';
25 end loop;
26 utl_file.new_line( l_output );
27
28 l_status := dbms_sql.execute(l_theCursor);
29
30 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
31 l_separator := '';
32 for i in 1 .. l_colCnt loop
33 dbms_sql.column_value( l_theCursor, i, l_columnValue );
34 utl_file.put( l_output, l_separator || l_columnValue );
35 l_separator := ',';
36 end loop;
37 utl_file.new_line( l_output );
38 end loop;
39 dbms_sql.close_cursor(l_theCursor);
40 utl_file.fclose( l_output );
41
42 execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
43 exception
44 when others then
45 execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
46 raise;
47 end;
48 /
When I try to execute
当我尝试执行
exec dump_table_to_csv('emp', 'C:\Temp','a.txt')
I am getting the error (Error): Error executing statement: ORA-29280: invalid directory path ORA-06512: at "LCM_EVAL.DUMP_TABLE_TO_CSV", line 59 ORA-06512: at line 2
我收到错误 (Error): Error execution statement: ORA-29280: invalid directory path ORA-06512: at "LCM_EVAL.DUMP_TABLE_TO_CSV", line 59 ORA-06512: at line 2
What is the possible problem?
可能的问题是什么？
回答by Vincent Malgrat
The error message is pretty clear: the path is invalid. Most probably it is just a typo: C:/Tempdoesn't exist, but maybe C:\Tempdoes.
错误信息很清楚：路径无效。很可能这只是一个错字：C:/Temp不存在，但也许C:\Temp存在。
It could also be a problem with grants. In PL/SQL you only have access to the disk if you have been given a specific right. The UTL_FILEdocumentationexplains how to grant access to PL/SQL:
这也可能是赠款的问题。在 PL/SQL 中，如果您被授予特定权限，您只能访问磁盘。该UTL_FILE文档解释了如何授予对 PL/SQL 的访问权限：
- (recommended) Create a directory with a dba account, grant access to user.
- (obsolete) Use the
UTL_FILE_DIRsystem parameter. Be careful, this is a system-wide parameter, allusers will be able to write and read this directory.
- （推荐）使用 dba 帐户创建目录，授予用户访问权限。
- （过时）使用
UTL_FILE_DIR系统参数。注意，这是一个系统范围的参数，所有用户都可以读写这个目录。
回答by Jeffrey Kemp
You should create an Oracle DIRECTORY object first, then refer to that object in the call to fopeninstead of passing the filesystem path.
您应该首先创建一个 Oracle DIRECTORY 对象，然后在调用中引用该对象，fopen而不是传递文件系统路径。
More info: http://www.orafaq.com/wiki/Oracle_directory
回答by Gary Myers
Anything to do with UTL_FILE happens on the database server, not the client. Make sure the directory exists on the server, and that the Oracle user has permission to write to it.
与 UTL_FILE 相关的任何事情都发生在数据库服务器上，而不是客户端上。确保该目录存在于服务器上，并且 Oracle 用户有权写入该目录。
