输出oracle 对象源码

王朝oracle·作者佚名 2006-01-09

宽屏版字体: 小|中|大|超大

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;