ANBOB™

前天有网友问题这个问题，今天测试一把，需求应该是如果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]$ 

打赏

微信扫一扫，打赏作者吧～