Re: Generate SQL*Loader Control File

From: <tedchyn_at_yahoo.com>
Date: Wed, 14 Jul 1999 19:47:18 GMT
Message-ID: <7mipg1$q9t$1@nnrp1.deja.com>

Kevin, try following script see whether it meet your need. I got this from one of thomas kyte's posting sometime back.

Ted Chyn
#!/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	dba_tab_columns
where	table_name = upper('$X')

/
prompt prompt )
prompt prompt BEGINDATA

prompt	select
select	lower(column_name)||'||chr(124)||'
from	dba_tab_columns
where	table_name = upper('$X') and
	column_id != (select max(column_id) from dba_tab_columns where
			 table_name = upper('$X'))
/
select	lower(column_name)
from	dba_tab_columns
where	table_name = upper('$X') and
	column_id = (select max(column_id) from dba_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

In article <378CADBE.A2C98877_at_poboxes.com>,   kslcheung_at_hknet.com wrote:
> I tried to write an anonymouse PL/SQL block to dynamically generate
> control file for loading data. DBMS_OUTPUT.PUT_LINE is used to echo
> output but the buffer is only 1000,000 bytes which is not enough for
> large tables, i.e with 200-300 columns. Pro*C is not considered as
this
> product is not installed in our shop. Anyone has any suggestion?
>
> Thanks,
> Kevin
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jul 14 1999 - 14:47:18 CDT