1、文件只能放在db server端，客户端的不能读写

2、directory 名称在程序中必须大写。

3、UTL_FILE.FILE_TYPE是包内定义好的file handle

4、使用directory；而非直接的路径名。同时要对directory有读写权限

grant read,write on directory utl to scott;

create or replace directory utl as 'c:';

declare
num number;
j number :=0;
sname varchar2(200);
numfile UTL_FILE.FILE_TYPE;
temp varchar2(1000);
cursor curemp is select empno,ename from oracleemp;
begin
numfile := utl_file.fopen('UTL','121.txt','w',2000);
for i in curemp loop
j :=j+1;
temp := j || ' : '||i.empno || ' '||i.ename;
utl_file.put_line(numfile,temp);
--utl_file.new_line(numfile);
end loop;
utl_file.fclose(numfile);
end;

5、exception。

Table 167-1 UTL_FILE Package Exceptions

Exception Name	Description
`INVALID_PATH`	File location is invalid.
`INVALID_MODE`	The `open_mode` parameter in `FOPEN` is invalid.
`INVALID_FILEHANDLE`	File handle is invalid.
`INVALID_OPERATION`	File could not be opened or operated on as requested.
`READ_ERROR`	Operating system error occurred during the read operation.
`WRITE_ERROR`	Operating system error occurred during the write operation.
`INTERNAL_ERROR`	Unspecified PL/SQL error
`CHARSETMISMATCH`	A file is opened using `FOPEN_NCHAR`, but later I/O operations use nonchar functions such as `PUTF` or `GET_LINE.`
`FILE_OPEN`	The requested operation failed because the file is open.
`INVALID_MAXLINESIZE`	The `MAX_LINESIZE` value for `FOPEN()` is invalid; it should be within the range 1 to 32767.
`INVALID_FILENAME`	The filename parameter is invalid.
`ACCESS_DENIED`	Permission to access to the file location is denied.
`INVALID_OFFSET`	Causes of the `INVALID_OFFSET` exception: `ABSOLUTE_OFFSET` = `NULL` and `RELATIVE_OFFSET = NULL`, or `ABSOLUTE_OFFSET` < 0, or Either offset caused a seek past the end of the file
`DELETE_FAILED`	The requested file delete operation failed.
`RENAME_FAILED`	The requested file rename operation failed.

[@more@]

ITPub博客

dbms常用包之utl_file包小结

最新文章