输出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;
/

 

上一个：Oracle 常用技巧和脚本
下一个：Oracle 数据库的备份与恢复整理