way 1:
通过spool输出到文件:
set serveroutput on
spool 'c:\log.txt'
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!
');
utl_file.fclose(l_output);
CLOSE cur;
END;
/
