ORA-29280: 目录路径无效
今天项目上提了一个support，一个存储过程报“ORA-29280: 目录路径无效”错误
改存储过程中用到了UTL_FILE.fopen函数v_dat := UTL_FILE.fopen(v_datpath,v_datfile,'w');
UTL_FILE.fopen的作用是打开主机上某一目下的一个文件，向文件里写入内容。
该函数与utl_file_dir有密切关系，查看问题主机该参数的值
SQL> show parameter utl_file_dir
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
utl_file_dir
string
SQL>
该参数为空，导致
在initsid.ora文件中，加入或修改
设置utl_file_dir的要点：
1。
utl_file_dir=* 这表示你能操作任何目录，尽量不要用
2。
utl_file_dir=d:\ 这表示你能操作d:\目录下的文件，但你不能操作d:\目录下的子目录
3。注意在设置
utl_file_dir=路径时，如果路径是长路径名，例如c:\my temp目录，则你必须加上''，例如：
utl_file_dir='c:\my
temp'
4。utl_file_dir可以是多个路径
utl_file_dir=c:\,d:\,d:\temp,'c:\my temp'
5。设置完必须重新启动数据库
Sr上：ID 1063519.6
Cause
This error is caused by not using quotes when setting the UTL_FILE_DIR parameter within the init.ora database parameter file. As a result, the database reads the space as the beginning of a second directory.
Init.ora file setting:
----------------------------
utl_file_dir=d:\Program Files
When the database is started and the UTL_FILE_DIR is reviewed via SQL*Plus is it displayed as the following (notice the ',' instead of space):
SQL> show parameter
utl_file_dir
NAME
TYPE VALUE
----------- ------- -----------------------
utl_file_dir string d:\Program, Files
here are actually 2 solutions for this issue.
Solution 1:
-------------------
Use quotes when adding the directory to the init.ora file.
Init.ora file
setting:
----------------------------
utl_file_dir='d:\Program Files'
When the database is restarted, the database will now view the space as part of the directory name.
SQL> show parameter
utl_file_dir
NAME TYPE
VALUE
----------- ------- -----------------------
utl_file_dir string d:\Program Files
Now when the anonymous block is executed it works.
SQL> set serveroutput
on
SQL> DECLARE
2 file_location VARCHAR2(256) := 'd:\Program
Files';
3 file_name VARCHAR2(256) := 'utlfile.txt';
4 file_text VARCHAR2(256) := 'Hello World';
5 file_id UTL_FILE.file_type;
6 BEGIN
7 file_id := UTL_FILE.fopen(file_Location, file_name,
'W');
8 UTL_FILE.put_line(file_id, file_text);
9 UTL_FILE.fclose(file_id);
10 EXCEPTION
11 WHEN UTL_FILE.INVALID_PATH
12 THEN dbms_output.put_line('Invalid path ' || SQLERRM);
13 WHEN OTHERS
14 THEN dbms_output.put_line('Others '|| SQLCODE || ' ' ||
SQLERRM);
15 END;
16 /
PL/SQL procedure successfully completed.
Reviewing the file from the command prompt should display the following:
D:\Program Files>
type utlfile.txt
Hello World
Solution 2:
-----------------
Don't using the UTL_FILE_DIR database parameter.
Starting with version 9i of the Database the UTL_FILE_DIR has been deprecated. A Directory Alias should be used instead.
Instead of the UTL_FILE_DIR, a Directory Alias should be created. One advantage
to using a Directory Alias is that the database does NOT have to be restarted
before it can be used.
The following SQL illustrates an example. The Database user needs to have CREATE DIRECTORY privileges. Since the directory is passed as a string it would already have quotes which would prevent this error.
SQL> CREATE DIRECTORY
DIR_WITH_SPACES AS 'D:\Program Files';
Directory created.
Once the Directory Alias has been created the code should use the Directory
Alias instead of the actual path. Review the change to the UTL_FILE.fopen
command.
DECLARE
file_name VARCHAR2(256) := 'utlfile.txt';
file_text VARCHAR2(256) := 'Hello World';
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen('DIR_WITH_SPACES', file_name, 'W');
UTL_FILE.put_line(file_id, file_text);
UTL_FILE.fclose(file_id);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN dbms_output.put_line('Invalid path ' || SQLERRM);
WHEN OTHERS
THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM);
END;
/
PL/SQL procedure successfully completed.
