PLSQL UTL_FILE

Oracle进行文件夹读写配置

1.设定UTL_FILE_DIR

Oracle 8i 以前

oracle\product\10.2.0\admin\AWF\pfile下的intOracleSid.ora

UTL_FILE_DIR='C:/LOG','C:/LOG2'

 

Oracle 9i以后

 

ALTER SYSTEM SET

  UTL_FILE_DIR='C:/Log','C:/LOG2'

  SCOPE = SPFILE ;

×需要重启服务

查看设置结果

SELECT NAME, VALUE FROM V$PARAMETER2 WHERE NAME='utl_file_dir' ;

 

 

TEST

declare

 

fHandle UTL_FILE.FILE_TYPE;

fbuffer varchar2(4000);

BEGIN

 

fHandle := UTL_FILE.fopen(' C:/Log ','up.csv','R');

 

UTL_FILE.get_line(fHandle,fbuffer);

dbms_output.put_line(fbuffer);

UTL_FILE.fclose(fHandle);

 

END;

 

2.使用DIRECTORY 

 

Oracle 9i以后

 

create or replace directory LOGPATH as ' C:/Log ';

 

Grant read,write on directory  LOGPATH  to 用户;

 

查看DIRECTORY 

select * from dba_directories

 

 

TEST

declare

 

fHandle UTL_FILE.FILE_TYPE;

fbuffer varchar2(4000);

BEGIN

 

fHandle := UTL_FILE.fopen(' LOGPATH ','up.csv','R');

 

UTL_FILE.get_line(fHandle,fbuffer);

dbms_output.put_line(fbuffer);

UTL_FILE.fclose(fHandle);

 

END;