UTL_FILE 包

1.获取足够权限(创建目录,授权)

conn / as sysdba

2.建立目录

create directory my_dir as 'K:\tmp\oracle_my_dir';

3.授予用户 对该目录的相关读写权限

grant read,write on directory my_dir to public,hr;

4.更改系统参数

alter system set utl_file_dir='k:\tmp\oracle_my_dir' scope=spfile;

alter session 仅对当前session有效

alter system 对整个系统有效

scope=spfile 修改配置文件,重启有效 

scope=memory 修改内存,立即生效,重启数据库还原

(此处情况不适合使用memory, 否则:   ORA-02095: 无法修改指定的初始化参数),因为有些参数不能动态修改。

scope=both;（默认）

5.修改完参数后，重启数据库

shutdown immediate;

startup;

6.存储过程定义

create or replace procedure sal_status(
dir in varchar2,filename in varchar2) is
file utl_file.file_type;
cursor empc is
select last_name,salary,department_id from employees order by department_id;
newdeptno employees.department_id%type;
olddeptno employees.department_id%type:=0;
begin
file:=utl_file.fopen(dir,filename,'w');
utl_file.put_line(file,'report:generated on '||sysdate);
utl_file.new_line(file);
for emp_rec in empc loop
  if emp_rec.department_id<>olddeptno then
    utl_file.put_line(file,'department:'||emp_rec.department_id);
  end if;
  utl_file.put_line(file,'employee:'||emp_rec.last_name||'earns:'||emp_rec.salary);
olddeptno:=emp_rec.department_id;
end loop;
utl_file.put_line(file,'***end of report***');
utl_file.fclose(file);
exception
  when utl_file.invalid_filehandle then
  raise_application_error(-20001,'invalid file');
  when utl_file.write_error then
  raise_application_error(-20002,'unable to be write');
end sal_status;
/

7.执行

execute sal_status('MY_DIR','salreport.txt');