Re: How to copy table to a file in a specific format?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/30
Message-ID: <36127fa2.10481311@192.86.155.100>#1/1

A copy of this was sent to badstreetboy_at_my-dejanews.com (if that email address didn't require changing) On Tue, 29 Sep 1998 20:12:36 GMT, you wrote:

>I want to copy the entire table to a unix file in this format:
>f1|f2|f3
>...
>How to do that? Thanks!
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

If you have access to sqlplus, the following will do it:

#!/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	select
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'))
/
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 Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Wed Sep 30 1998 - 00:00:00 CDT