oracle的utl_file应用讨论实例

1.在本地目录中建立文件夹,F:\oracle\oracle_dir,注意在WINDOWSXP中进行实验：

2。进行创建ORACLE对应的目录，并赋于权限
SQL> create directory my_dir2 as 'F:\oracle\oracle_dir';

目录已创建。

SQL> grant read,write on directory my_dir2 to hr;

授权成功。

SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      f:\oracle\oracle_dr

3。编写测试UTL_File存储过程，注意与上面授权成功的用户相同HR；
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');--如果该W变为A：表示追加，可以不断改变文件名测试是不是生成不同文件，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(-200002,'unable to be write');
End;

4。调用存储过程，注意是原来定义的ORACLE中的目录名MY_DIR2;文件名随意，就命名白云飞TXT

exec sal_status('my_dir2','baiyunfei.txt');