输出oracle 对象源码

2020-03-09 22:53:00

字体：大中小

来源：转载

供稿：网友

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后，经常使用的修改表空间的SQL代码

下一篇：Oracle 数据库的备份与恢复[整理]