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! '''');