oracle中fopen(),关于utl_file.fopen,该如何处理

SQL codedeclare

-- Create or replace directory UCE_DIR AS 'D:\UCEDATA\FILEDIR\';

-- GRANT ALL ON DIRECTORY UCE_DIR to TESTUSER;

Fileid UTL_FILE.file_type;

l_line VARCHAR2 (32767);

L_EOF BOOLEAN;

BEGIN

---1)Test writing file

-- w means Rewrite the file,A means append the file

fileid := UTL_FILE.fopen ('UCE_DIR', 'TUSER.TXT', 'W');

FOR emprec IN (SELECT rownum,RPAD(userid,12,' ') USERID,name UNAME FROM TUSER)

LOOP

l_line:=RPAD(to_char(emprec.rownum),6,' ')||' '||emprec.userid||' '||emprec.UNAME;

UTL_FILE.putf(fileid,'%s',l_line); --like C language printf ,here f means five stirng parameters

utl_file.new_line(fileid);

--This following row does the same as the two rows upon.

--Utl_File.put_line(fileid,l_line);

END LOOP;

UTL_FILE.fclose (fileid);

--2)Test Reading file

fileid :=utl_file.fopen('UCE_DIR', 'TUSER.TXT', 'R');

begin

LOOP

UTL_FILE.get_line (fileid, l_line);

DBMS_OUTPUT.put_line(l_line);

END LOOP;

exception

WHEN NO_DATA_FOUND THEN

UTL_FILE.fclose (fileid);

end;

--3)Test with clob

end;

------解决方案--------------------

oracle 9i：

alter system set utl_file_dir='e:/work' scope=spfile;

在ini.ora文件里添加：

utl_file=e:/work

GRANT EXECUTE ON utl_file TO system;

oracle 10g :

create or replace directory UTL_FILE_DIR as '/home/oracle/smb';

GRANT EXECUTE ON utl_file TO system;

GRANT READ,WRITE ON OTL_FILE TO SYSTEM;

show parameter utl_file_dir