Skip to Main Content
  • Questions

  • Oracle dump into csv file have a empty line at start.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, krishnendu.

Asked: November 16, 2022 - 7:33 am UTC

Last updated: November 28, 2022 - 5:56 am UTC

Version: XE

Viewed 1000+ times

You Asked

I am executing a sql-plus session to dump a table's data into a .csv file. I need header details along with the data in the dumped csv file. When I use the "set heading on" option, I am getting header details along with the data plus one empty line at the start. I tried multiple options like "set newpage NONE" and others, but first empty file is still coming. Can u pls help to solve this issue? My code snippet is mentioned at below

sqlplus -s /nolog << !EOF!
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
connect sys/${DB_PASSWORD}@//${CONN_STR}:${CONN_PORT}/XE as ${DB_USERNAME}

--/*************************/--

set echo off
set feedback off
set linesize 4000
set newpage NONE
set pagesize 0
set serveroutput off
set termout off
set flush off
SET NUMWIDTH 5
SET COLSEP ","
set markup csv on
set sqlprompt ''
set trimspool on


SET VERIFY OFF
SET TERM OFF
--/*******************************/--
--/**** Clear Screen & Buffer ****/--
--/*******************************/--
clear screen
clear buffer
--/*****************************/--
--/**** Start spooling file ****/--
--/*****************************/--

SPOOL $FILE
set heading on
set pagesize 0 embedded on
select * from $table_name;
SPOOL OFF

EXIT
!EOF!


Output
sh-4.2$ cat aprm_mirrordb_persons_2022_11_16.csv

"PERSONID","LASTNAME"
1,"das"
2,"das2"
3,"abc"
4,"def"
5,"testdata only temporary purpose"
6,"testdata only temporary purpose"
7,"testdata only temporary purpose"


and Connor said...

I suspect that's a bug. An easy fix would be run the script with SQLcl, eg

test.sql
================
set termout off
set feedback off
set headsep off
set linesize 4000
set pagesize 999
set newpage none
set termout off
set trimspool on
spool test.dat
select /*csv*/ * from scott.emp;
spool off
set termout on
host cat x:\temp\test.dat


gave me

SQL> @x:\temp\test.sql
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17/DEC/80,800,,20
7499,"ALLEN","SALESMAN",7698,20/FEB/81,1600,300,30
7521,"WARD","SALESMAN",7698,22/FEB/81,1250,500,30
7566,"JONES","MANAGER",7839,02/APR/81,2975,,20
7654,"MARTIN","SALESMAN",7698,28/SEP/81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/MAY/81,2850,,30
7782,"CLARK","MANAGER",7839,09/JUN/81,2450,,10
7788,"SCOTT","ANALYST",7566,09/DEC/82,3000,,20
7839,"KING","PRESIDENT",,17/NOV/81,5000,,10
7844,"TURNER","SALESMAN",7698,08/SEP/81,1500,,30
7876,"ADAMS","CLERK",7788,12/JAN/83,1100,,20
7900,"JAMES","CLERK",7698,03/DEC/81,950,,30
7902,"FORD","ANALYST",7566,03/DEC/81,3000,,20
7934,"MILLER","CLERK",7782,23/JAN/82,1300,,10




Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

krishnendu Das, November 25, 2022 - 6:38 am UTC

Thanks for the suggestion
Connor McDonald
November 28, 2022 - 5:56 am UTC

Or perhaps some simple python (this from Chris Jones the cx_oracle PM)

import os
import csv  # See https://docs.python.org/3/library/csv.html
import oracledb
​
un = os.environ.get("PYTHON_USERNAME")
pw = os.environ.get("PYTHON_PASSWORD")
cs = os.environ.get("PYTHON_CONNECTSTRING")
​
connection = oracledb.connect(user=un, password=pw, dsn=cs)
​
with connection.cursor() as cursor:
    cursor.arraysize = 1000  # tune this for large queries
    f = open("emp.csv", "w")
    writer = csv.writer(f, lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)
    cursor.execute("""select * from emp""")
    col_names = [row[0] for row in cursor.description]
    writer.writerow(col_names)
    for row in cursor:
        writer.writerow(row)
    f.close()

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.