Re: Exporting a table to an delimitered file in UNIX??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 26 Mar 1999 13:28:23 GMT
Message-ID: <37088b0f.9372306@192.86.155.100>

A copy of this was sent to pbk_at_rdsas.com (Poul Kornmod) (if that email address didn't require changing) On Fri, 26 Mar 1999 08:50:47 GMT, you wrote:

>Dear All,
>
>This is an easy one for you all - but I don't know how to export a
>table via sqlplus to a delimitered file in UNIX?
>
>Something like this in Informix (unload to "/tmp/table.txt" delimiter
>"," select * from table)
>
>Brgds
>
>Poul Kornmod - pbk_at_cphzt.rdsas.com

here is a script that creates a pipe delimited file that can easily be sqlloaded back into the database. You would run it like this:

$ 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

Just redirect the output of sqlldr_exp to a file and you can reload it later using

$ sqlldr scott/tiger <that filename>

#!/bin/sh

if [ "$1" = "" ]
then

	cat << EOF
usage:		flat un/pw [tables|views]

example:	flat scott/tiger emp dept

description:	Select over standard out all rows of table or view with 
		columns delimited by tabs.
EOF
	exit

fi

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 Fri Mar 26 1999 - 07:28:23 CST