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 100+ 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)

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()