输出oracle 对象源码
发表于:2007-07-02来源:作者:点击数:
标签:
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 = @#TEST CC B@#; BEGIN OPEN cur; LOOP FETC
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 = @#TEST
CCB@#;
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;
/
原文转自:http://www.ltesting.net