Re: Exporting data tab delimited to flat ascii file.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 28 Jan 1999 17:40:42 GMT
Message-ID: <36b7a099.18742219_at_192.86.155.100>

A copy of this was sent to "Robert Alexander" <robertalexander_at_cca-audit.com> (if that email address didn't require changing) On Thu, 28 Jan 1999 11:38:56 -0500, you wrote:

>I don't use oracle but need to get data from an oracle system to load into
>our systems.
>
>The people running the oracle system say it is not possible to get a flat
>file of an oracle table delimited.
>
>They can give me fixed length but then they don't have enough space on their
>systems since many fields are varchar(255).
>
>Is it possible to export an oracle table tab delimited? If it is could you
>give me the syntax?
>
>Thanks for your help.
>
>
>Robert Alexander
>robertalexander_at_cca-audit.com
>

Here is a way that uses sqlplus (you don't have to be on the db machine to use this, any machine will work)

---

#!/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)||'||chr(9)||'
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

---

the 'nt' version of this would be:

• flat.cmd --------------------------- _at_echo off

if "%1"=="" goto :usage
if "%2"=="" goto :usage

sqlplus -s %1 _at_flat.sql %2

goto :done

:Usage

echo "usage 		flat un/pw [tables|views]"
echo "example 	flat scott/tiger emp dept"
echo "description 	Select over standard out all rows of table or view with "
echo "        		columns delimited by tabs."

:done

• flat.cmd ---------------------------
• flat.sql --------------------------- set wrap off set feedback off set pagesize 0 set verify off set termout off

spool ytmpy.sql

prompt	select
select	lower(column_name)||'||chr(9)||'
from	user_tab_columns
where	table_name = upper('&1') and
	column_id != (select max(column_id) from user_tab_columns where
			 table_name = upper('&1'))
/
select	lower(column_name)
from	user_tab_columns
where	table_name = upper('&1') and
	column_id = (select max(column_id) from user_tab_columns where
			 table_name = upper('&1'))
/
prompt	from	&1
prompt	/

spool off
set termout on
_at_ytmpy.sql
exit

------------------- flat.sql----------------------
 

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
 
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 Thu Jan 28 1999 - 18:40:42 CET