oracle 动态生成txt,Script: oracle procedure error 生成txt日志文件

前天有网友问题这个问题，今天测试一把，需求应该是如果producre存储过程运行出错，可以写错误日志到OS的文本文件，这样系统管理员就可以看到，无需分配数据库权限.

eg.

SQL> create directory dir_log as '/oracle10g/log';

Directory created.

SQL> grant read,write on directory dir_log to anbob;

Grant succeeded.

SQL> conn anbob/anbob;

Connected.

SQL> create table test_log (id int,name varchar2(20));

Table created.

SQL> insert into test_log values(1,'anbob');

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace procedure pro_test(p_id number)

2 is

3 v_name varchar2(20);

4 begin

5 select name into v_name from test_log where id=p_id;

6 dbms_output.put_line(v_name);

7 exception

8 when no_data_found then

9 dbms_output.put_line('a');

10 end;

11 /

Procedure created.

SQL> set serveroutput on

SQL> exec pro_test(1);

anbob

PL/SQL procedure successfully completed.

SQL> exec pro_test(2);

a

PL/SQL procedure successfully completed.

SQL> l

1 create or replace procedure pro_test(p_id number)

2 is

3 v_name varchar2(20);

4 v_fil utl_file.file_type;

5 begin

6 select name into v_name from test_log where id=p_id;

7 dbms_output.put_line(v_name);

8 exception

9 when no_data_found then

10 v_fil := utl_file.fopen('DIR_LOG','DB_PRO.LOG','W');

11 utl_file.PUT_LINE(v_fil,'ERROR:'||SYSDATE||'-'||sqlerrm);

12 UTL_FILE.fclose(v_fil);

13* end;

SQL> /

Procedure created.

SQL> exec pro_test(1);

anbob

PL/SQL procedure successfully completed.

SQL> exec pro_test(2);

PL/SQL procedure successfully completed.

SQL> host

[oracle@anbob ~]$ cd /oracle10g/log/

[oracle@anbob log]$ ll

total 4

-rw-r--r-- 1 oracle oinstall 42 Mar 9 05:04 DB_PRO.LOG

[oracle@anbob log]$ cat DB_PRO.LOG

ERROR:09-3 -12-ORA-01403: no data found

[oracle@anbob log]$