前几天数据提取,发现SPOOL出来的数据是没有HEAD的.
环境: LINUX AIX + ORA 10G ,
脚本如下:
------------------------------------------------
#! /bin/sh
. /home/oracle/.bash_profile
sqlplus -s itrade/aaaaaa <set echo off
set linesize 10000
set long 2000
set term off verify off feedback off
set pagesize 0
spool /tmp/report.txt ;
select count(*) as aa from tab;
spool off ;
exit
EOF
exit 0
---------------------------------------------------
而我们知道sqlplus 中,HEADING 默认是ON的.
一时郁闷之下,人肉进行了调试.
发现是" set pagesize 0 "在搞鬼.
在10G,如果在SQLPLUS设置pagesize=0,那么自动把HEAD内容也被取消了.
=======================================================================
select">sys@VOUCHER>select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
set">sys@VOUCHER>set pagesize 100 ;
select">sys@VOUCHER>select count(*) as total from tab ;
TOTAL
----------
3525
set">sys@VOUCHER>set pagesize 0
select">sys@VOUCHER>select count(*) from tab ;
3525
========================================================================
也许这也算是10G的"新特性"吧.
所以,当你又不想分页, 又想显示HEAD,那么你只能把PAGESIZE设大一点.
而随带的问题是: PAGESIZE最大只能是50000.
那么如果我的结果集超过50000了而且又不想分页,那怎么办?
大家拍砖!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/703656/viewspace-1018940/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/703656/viewspace-1018940/