declare v_text varchar2(1000); --i number(3):=0; cursor cur is select text from sys.dba_source where owner = 'scott' and name = 'testccb'; begin open cur; loop fetch cur into v_text; --dbms_output.put_line(' cityname = ' ||v_name||' count='|| i); dbms_output.put_line(v_text); --i := i+ 1; exit when cur%notfound;
end loop;
close cur; end; / spool off
way2: 利用 utl_file 包直接寫出到文件 要求oracle用戶對os文件系統有操作權限
declare v_text varchar2(1000); v_dir varchar2(256); v_owner varchar2(128); v_obj varchar2(128); l_output utl_file.file_type; cursor cur is select text from dba_source where owner = v_owner and name = v_obj; begin v_owner := 'scott'; v_obj := 'emp'; select t.directory_path into v_dir from all_directories t; l_output := utl_file.fopen(v_dir, 'tab.txt', 'w'); utl_file.new_line(l_output); utl_file.put_line(l_output, '-- output owner :' || v_owner || ' object: ' || v_obj);
open cur; loop fetch cur into v_text; exit when cur%notfound; utl_file.new_line(l_output); utl_file.put_line(l_output, v_text); end loop; utl_file.new_line(l_output); utl_file.put_line(l_output, '-- output finished! ');
