在 sqlplus 中使用假脱机创建标题时遇到问题 [英] trouble creating headers using spool in sqlplus

问题描述

我有很多数据要假脱机到 csv 文件.我需要 set Heading off 这样标题就不会在每个页面上重复.但是，我仍然需要我生成的文件来包含标题.有没有办法将一行标题(而不是表本身)添加到查询中，在假脱机时实际上不会被视为标题?这是我的代码，当我设置标题关闭时，它只是不包含标题.

I have a lot of data that I want to spool to a csv file. I need to set heading off so the heading will not repeat every page. However, I still need my produced file to contain headers. Is there a way to add a row of headers (not to the table itself) into the query that won't actually be considered a header when spooling? This is my code which works, it just doesn't contain headers when I set heading off.

 select a.col1 as name1, 
        a.col2 as name2, 
        b.col3 as name3
from tab1 a, 
     tab2 b

提前致谢

推荐答案

你总是可以尝试这样的事情:

you could always try something like:

set heading off;

select 'NAME1' name1, 'NAME2' name2, 'NAME3' name3 from dual
union all
select a.col1 as name1, a.col2 as name2, b.col3 as name3
from tab1 a, tab2 b
where <join condition>;

ETA:如果主查询返回的列类型不都是字符串，则必须显式转换它们.下面是一个例子:

ETA: If the column types returned by the main query aren't all strings, you'll have to explicitly convert them. Here is an example:

create table test1 (col1 number,
                    col2 date,
                    col3 varchar2(10),
                    col4 clob);

insert into test1 values (1, sysdate, 'hello', 'hello');

commit;

select 'col1' col1, 'col2' col2, 'col3' col3, 'col4' col4 from dual
union all
select col1, col2, col3, col4
from   test1;
       *
Error at line 1
ORA-01790: expression must have same datatype as corresponding expression

set heading off;

select 'col1' col1, 'col2' col2, 'col3' col3, to_clob('col4') col4 from dual
union all
select to_char(col1), to_char(col2, 'dd/mm/yyyy hh24:mi:ss'), col3, col4
from   test1;

col1                                     col2                col3       col4    
1                                        05/08/2015 11:23:15 hello      hello   

这篇关于在 sqlplus 中使用假脱机创建标题时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！