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

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]$ 
复制