0

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

Thanks in advance

Improve this question
1

3 Answers 3

Reset to default
3

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: 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
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I'll give it a shot, but what does from dual do?
Dual is a special one column, one row table. In oracle, you can't just select things (eg. literals, functions such as sysdate etc) without specifying a table - you must always provide a table. So, in cases where you need to "make up" a row, use the dual table. Later versions of Oracle (10g and above, IIRC) recognise when dual is being used and has certain shortcuts it can do, rather than having to go physically look inside the table.
hey, when using your code, I got an error saying must have datatype in the expression. This error was referring to the first line ie: select 'NAME1' name1, 'NAME2' name2, 'NAME3' name3 from dual
ah, yes - you need to match datatypes across the union'd queries - what are the datatypes of the columns returned by your main query? You may find yourself having to convert them into a string.
sorry, you mean you can't do "desc tablename"?! Really?
|
2

You want to try:

set pages <number of rows you expect>

E.g.

set pages 1000

Another way around could be a UNION like so:

SELECT 'name1', 'name2', 'name3' FROM DUAL UNION select a.col1 as name1, a.col2 as name2, b.col3 as name3 from tab1 a, tab2 b

Improve this answer

3 Comments

I set the page size to max so that doesn't help. I have a lot of data coming from this query so it is multiple pages even at max pagesize
How do you mean max? The set pages command only accepts numbers.
Why would you choose UNION? Surely UNION ALL is better, since it won't cause the query to have to do a distinct?
2

You said you're spooling to a CSV file, so presumably the column spacing doesn't matter (and you have set colsep , already).

If so, you can use the SQL*Plus prompt command to fake a header, without needing a union:

prompt name1,name2,name3

 select a.col1, 
        a.col2, 
        b.col3
from tab1 a, 
     tab2 b

Or a separate query, again without the union:

set feedback off
 select 'name1', 'name2', 'name3' from dual;
set feedback on -- optionally; probably not in this case

 select a.col1, 
        a.col2, 
        b.col3
from tab1 a, 
     tab2 b

Again the values won't align with the real data columns, but for CSV you don't really care. (For CSV I usually explicitly concatenate the values with commas anyway, which removes a load of whitespace and makes the file smaller).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.