117

2.system授权
grant read,write on directory myport to scott;


create or replace procedure out_excel_test is
  v_file utl_file.file_type;
  vDirname Varchar2(250);
  vFilename Varchar2(250);
  vOutput Varchar2(32767);


  cursor empc is
    select ename, sal, deptno from emp order by deptno;
begin
  vDirname := 'MYPORT'; --目录定义名
  vFilename:='zbn.txt';


  v_file := utl_file.fopen(vDirname, vFilename, 'w',32767);


  utl_file.put_line(v_file, 'report: generated on ' || sysdate);
  utl_file.new_line(v_file);
  utl_file.put_line(v_file,
                    'deptno' || chr(9) || 'ename' || chr(9) || 'sal');
  FOR emp_rec IN empc LOOP
    UTL_FILE.PUT_LINE(v_file,
                      nvl(emp_rec.deptno, '') || chr(9) || emp_rec.ename ||
                      chr(9) || emp_rec.sal);
  END LOOP;
  UTL_FILE.PUT_LINE(v_file, '*** END OF REPORT ***');
  UTL_FILE.FCLOSE(v_file);
EXCEPTION
  when others then
    dbms_output.put_line(sqlcode || '----&gt' || sqlerrm);
END out_excel_test;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12961536/viewspace-1061218/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12961536/viewspace-1061218/