Re: Export to CSV

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Mar 1999 12:46:38 GMT
Message-ID: <36e92280.5762035_at_192.86.155.100>

A copy of this was sent to rkennedy_at_rogersds.com (if that email address didn't require changing) On Fri, 05 Mar 1999 21:17:43 GMT, you wrote:

>Hello,
>
>I have a need to export the results of a SQL*Plus query to a CSV file. Is
>there a tool similar to SQL*Loader to do this? Or are there options in
>SQL*Plus to do this using the SPOOL command?
>
>Any help is very much appreciated.
>
>Rick Kennedy
>Rogers Department Store
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Here is a sh script that uses sqlplus to generate SQLLDR control files with embedded data. you use it like:

$ sqlldr_exp scott/tiger dept
LOAD DATA
INFILE *
INTO TABLE dept
REPLACE
FIELDS TERMINATED BY '|'
(
deptno
,dname
,loc
)
BEGINDATA

10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|RESTON
40|OPERATIONS|BOSTON

so just redirect the sqlldr_exp command to a file and you can sqlldr it later. beware of data with a | in it already and data with newlines in it -- the script does not attempt to handle them at all.

---

#!/bin/sh

PW=$1
shift

for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql

set	wrap off
set	feedback off
set	pagesize 0
set	verify off

prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE $X
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (

select	'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from	user_tab_columns
where	table_name = upper('$X') 

/
prompt prompt )
prompt prompt BEGINDATA

prompt	select
select	lower(column_name)||'||chr(124)||'
from	user_tab_columns
where	table_name = upper('$X') and
	column_id != (select max(column_id) from user_tab_columns where
			 table_name = upper('$X'))
/
select	lower(column_name)
from	user_tab_columns
where	table_name = upper('$X') and
	column_id = (select max(column_id) from user_tab_columns where
			 table_name = upper('$X'))
/
prompt	from	$X
prompt	/
prompt	exit

exit
EOF
sqlplus -s $PW << EOF

set	wrap off
set	feedback off
set	pagesize 0
set	verify off

set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done

---

 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Sat Mar 06 1999 - 13:46:38 CET