分隔符要与ctl文件中fields terminated by指定的一致,这个例子中为"|"
ctl和dat文件就绪后可以执行上载,命令为:
sqlldr user/passwd@oracle_sid control=emp.ctl
*sqlplus
*.config内容如下:
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set termout off;
set trimout on;
set trimspool on;
spool ..\*.txt
select ASYSAREACODE||','||COLUMN_59||','||VAVILLAGE from TSUCCORLSBASIC;
spool off;
在sql plus 中不行,用plsql或pb都挺爽的。
在sql plus中可以将select 的结果保存到文件
set echo off
set feedback off
set heading off
set linesize 你想要的长度
set pagesize 一页的行数
spool c:\文件名
select *
from aaa where ur_condition;
spool off
示例如下:
1.main.sql脚本:
[oracle@jumper utl_file]$ more main.sql
set linesize 200
set term off verify off feedback off pagesize 999
set markup html on entmap ON spool on preformat off
spool tables.xls
@get_tables.sql
spool off
exit
2.get_tables.sql脚本:
[oracle@jumper utl_file]$ more get_tables.sql
select owner,table_name,tablespace_name,blocks,last_analyzed
from all_tables order by 1,2;
3.执行并获得输出:
[oracle@jumper utl_file]$ sqlplus "/ as sysdba" @main
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Apr 25 10:30:11 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[oracle@jumper utl_file]$ ls -l tables.xls
-rw-r--r-- 1 oracle dba 69539 Apr 25 10:30 tables.xls