Re: Extract ASCII file from table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 11 Jun 1999 01:41:40 GMT
Message-ID: <376368fe.4413446@newshost.us.oracle.com>

A copy of this was sent to Interlog User <orita_at_interlog.com> (if that email address didn't require changing) On Thu, 10 Jun 1999 21:30:15 -0400, you wrote:

>Hi,
>
>Is there a method or utility in Oracle 7.3 or 8.0.5 to extract
>table data in ASCII format to a file like the "bcp" utility in Sybase
>???
>
>I tried with sqlplus, but it comes with all kinds of header and other
>information.
>
>Answers appreciated.
>
>
>

In unix, try this (if in nt, let me know, i have .cmd files for NT)

#!/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.
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

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

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

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 10 1999 - 20:41:40 CDT