附件中就是我的源代码，创建过程成功，执行也成功，但是没有任何输出（user表中是有数据的），单步调测发现，当程序运行到l_file :=utl_file.fopen('ZJ_TEST_DIRECTORY','test.xls','W'); 时下一步就会调到EXCEPTION，当运行到IF utl_file.is_open(l_file) THEN 是即跳出if语句，查看变量l_file没有值输出，这是不是说明utl_file.fopen没有执行成功？？这是什么原因呢？？是我程序哪里有问题吗？？
注明：
建立这个过程的用户，已经通过grant execute on utl_file to ...授权了UTL_FILE 的操作权的了
------解决方案--------------------
- SQL code
declare -- 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<sid>.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