使用spool SQL> spool c:\text.txt; Stopped spooling to c:\text.txt Started spooling to c:\text.txtSQL> select count(*) from dual; COUNT(*) ---------- 1SQL> spool off; Stopped spooling to c:\text.txtSQL>
然后去查看你的c:\text.txt文件就可以了
spool '路径\文件名称' select * from tb spool off
写一个main.sql, 如下: set linesize 200 set term off verify off feedback off pagesize 999 spool on spool tables.txt select * from dept order by 2; spool off exit然后在sqlplus里@main.sql 这样会将dept表中的内容输出到tables.txt 里头
如果想实现定时执行的话,在存储过程里调用utl_file包,比如 CREATE OR REPLACE PROCEDURE TEST1(o_date varchar2,n_date varchar2) IS F_handle utl_file.file_type; file_name varchar2(50);--文件名 content varchar2(2000);--查询的结果信息 begin file_name:='table_name'||to_char(sysdate,'yyyy-mm-dd 24hh:mm:ss')||'.txt';---这个看实际需要是要放在一个文档还是多个文档 /* SQL STATEMENT */F_handle:=utl_file.fopen('UTL_FILE_DIR',content,'w');--w为写模式,a为追加模式 UTL_FILE_DIR为虚拟路径 create directory utl_file_dir as 'txt所在的路径' utl_file.fclose(F_handle);--关闭文件 end;再加个job 定时执行存储过程
谢谢各位! 问题解决了!把脚本贡献给大家!while [ 1 -eq 1 ] do sqlplus -s smsg/smsg@infox103 <<! >>1.txt SET PAGESIZE 0; set heading off; select count(*) from sms_ppsusertable ; exit; ! date +%r>>time.txt sleep 10; done
Stopped spooling to c:\text.txt
Started spooling to c:\text.txtSQL> select count(*) from dual; COUNT(*)
----------
1SQL> spool off;
Stopped spooling to c:\text.txtSQL>
select * from tb
spool off
set linesize 200
set term off verify off feedback off pagesize 999
spool on
spool tables.txt
select * from dept order by 2;
spool off
exit然后在sqlplus里@main.sql
这样会将dept表中的内容输出到tables.txt 里头
CREATE OR REPLACE PROCEDURE TEST1(o_date varchar2,n_date varchar2)
IS
F_handle utl_file.file_type;
file_name varchar2(50);--文件名
content varchar2(2000);--查询的结果信息
begin
file_name:='table_name'||to_char(sysdate,'yyyy-mm-dd 24hh:mm:ss')||'.txt';---这个看实际需要是要放在一个文档还是多个文档
/*
SQL STATEMENT
*/F_handle:=utl_file.fopen('UTL_FILE_DIR',content,'w');--w为写模式,a为追加模式 UTL_FILE_DIR为虚拟路径 create directory utl_file_dir as 'txt所在的路径'
utl_file.fclose(F_handle);--关闭文件
end;再加个job 定时执行存储过程
问题解决了!把脚本贡献给大家!while [ 1 -eq 1 ]
do
sqlplus -s smsg/smsg@infox103 <<! >>1.txt
SET PAGESIZE 0;
set heading off;
select count(*) from sms_ppsusertable ;
exit;
!
date +%r>>time.txt
sleep 10;
done