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