I am using SQL query to generate an Excel sheet to export data from my database, the header is being duplicated every 10000 lines and I need it not to be duplicated.
I am using the below setup. When I set page size more than 10000, the duplication appears every 15 lines.
SET MARKUP
HTML ON
SPOOL ON
HEAD "<TITLE>SQL*PLUS REPORT</title> - <STYLE TYPE='TEXT/CSS'><BODY {bgcolor: ffffc6} ></STYLE>"
SET ECHO OFF
SET PAGESIZE 10000
FEEDBACK OFF
SPOOL file_name.xls
I am answering my own question after I contacted a friend and he gave me the solution.
The setup should be as below:
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
<STYLE TYPE ='TEXT/CSS'><BODY {bgcolor: ffffc6} ></STYLE>"
SET ECHO OFF
SET PAGESIZE 0 FEEDBACK OFF
SPOOL file_name.xls
--header format
SELECT
'filed_name_1',
'filed_name_2',
'filed_name_3',
'filed_name_4',
'filed_name_5'
FROM DUAL
UNION ALL
--end of header format
SELECT
to_char (filed_name_1) as filed_name_1,
to_char (filed_name_2) as filed_name_2,
to_char (filed_name_3) as filed_name_3,
to_char (filed_name_4) as filed_name_4,
to_char (filed_name_5) as filed_name_5
FROM table_name
QUIT;
Kindly be aware that in your header format fields must be ordered the same as your SELECT order