输出oracle 对象源码

领测软件测试网
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;
/

 

文章来源于领测软件测试网 https://www.ltesting.net/