set serveroutput ondeclare v_date varchar2(80); begin select to_char(sysdate,'yyyy-mm-dd') into v_date from dual; dbms_output.put_line(v_date); end; /
楼上,你的做法只是输出到控制台,并不是文件。在pl/sql中输出到文件可以用UTL_FILE。 例子: set serveroutput on DECLARE filehndl UTL_FILE.FILE_TYPE; filedata varchar2(100);
CURSOR cur_table1 IS SELECT column1,column2 FROM table1 WHERE column1 = 'condition1';
BEGIN -- Write Example filehndl := UTL_FILE.FOPEN('c:\temp','aaa.txt','w'); UTL_FILE.PUT_LINE(filehndl,'write example!'); FOR rec_table1 IN LOOP UTL_FILE.PUT_LINE(filehndl,rec_table1.column1 || ',' || rec_table1.column2); END LOOP; UTL_FILE.FCLOSE(filehndl);
-- Read Example filehndl := UTL_FILE.FOPEN('c:\temp','aaa.txt','r'); UTL_FILE.GET_LINE(filehndl,filedata); DBMS_OUTPUT.PUT_LINE(filedata); UTL_FILE.FCLOSE(filehndl);EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('INVALID_PATH'); WHEN UTL_FILE.INVALID_MODE THEN DBMS_OUTPUT.PUT_LINE('INVALID_MODE'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN DBMS_OUTPUT.PUT_LINE('INVALID_FILEHANDLE'); WHEN UTL_FILE.INVALID_OPERATION THEN DBMS_OUTPUT.PUT_LINE('INVALID_OPERATION'); WHEN UTL_FILE.WRITE_ERROR THEN DBMS_OUTPUT.PUT_LINE('WRITE_ERROR'); WHEN UTL_FILE.INTERNAL_ERROR THEN DBMS_OUTPUT.PUT_LINE('INTERNAL_ERROR'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,80)); END; /在执行上面程序前 1,在init.ora中添加你要访问的目录,重启oracle. UTL_FILE_DIR = <directory name> 2,一般来说UTL_FILE只能访问DB server上的目录。不能访问客户端的目录。 (经过设置,在特定情况下也可访问客户机,没试过,感兴趣的话,自己google一下吧)
b.spool 完整文件名
c.查询语句
……
d.spool off
set line 1000;
set tab on;
set trims on;
spool c:\emp.txt;
select * from scott.emp;
spool off;数据量不大的时候可以用
spool c:\emp.txt;select * from table;spool off;
SQL> set feedback off
SQL> spool c:\a.txt
SQL> @c:\sql.txt (或执行你的pl/sql)
SQL> spool off在硬盘C 下已有文件a.txt
v_date varchar2(80);
begin
select to_char(sysdate,'yyyy-mm-dd') into v_date from dual;
dbms_output.put_line(v_date);
end;
/
例子:
set serveroutput on
DECLARE
filehndl UTL_FILE.FILE_TYPE;
filedata varchar2(100);
CURSOR cur_table1 IS
SELECT column1,column2
FROM table1
WHERE column1 = 'condition1';
BEGIN -- Write Example
filehndl := UTL_FILE.FOPEN('c:\temp','aaa.txt','w');
UTL_FILE.PUT_LINE(filehndl,'write example!');
FOR rec_table1 IN LOOP
UTL_FILE.PUT_LINE(filehndl,rec_table1.column1 || ',' || rec_table1.column2);
END LOOP;
UTL_FILE.FCLOSE(filehndl);
-- Read Example
filehndl := UTL_FILE.FOPEN('c:\temp','aaa.txt','r');
UTL_FILE.GET_LINE(filehndl,filedata);
DBMS_OUTPUT.PUT_LINE(filedata);
UTL_FILE.FCLOSE(filehndl);EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('INVALID_PATH');
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('INVALID_MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('INVALID_FILEHANDLE');
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('INVALID_OPERATION');
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('WRITE_ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('INTERNAL_ERROR');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,80));
END;
/在执行上面程序前
1,在init.ora中添加你要访问的目录,重启oracle.
UTL_FILE_DIR = <directory name>
2,一般来说UTL_FILE只能访问DB server上的目录。不能访问客户端的目录。
(经过设置,在特定情况下也可访问客户机,没试过,感兴趣的话,自己google一下吧)