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 || '---->' || sqlerrm);
END out_excel_test;

ITPub博客

117

最新文章