dbms常用包之utl_file包小结

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博客 ” ，链接：http://blog.itpub.net/70612/viewspace-1019378/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/70612/viewspace-1019378/