Re: select/insert into/from a file ?

From: Hakan YUKSEL <hyuksel_at_tekstilbank.com.tr>
Date: Wed, 13 Sep 2000 08:17:58 GMT
Message-ID: <8pnd7b$ssh$1@nnrp1.deja.com>

hi lisa,
if you are on unix, you will love these... have a good day.
btw, i hope i am not too late...

unload.bat
---

if [ $# -lt 3 ]
then
 echo "Usage : unload <username> <password> <table> <query>"  echo "Example : unload system manager sube \"where sube=1\""  exit 99
fi
sqlplus -s $1/$2 << EOF > unload.tmp

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('$3') and
	column_id != (select max(column_id) from user_tab_columns where
			 table_name = upper('$3'))
/
select	lower(column_name)||'||''|'''
from	user_tab_columns
where	table_name = upper('$3') and
	column_id = (select max(column_id) from user_tab_columns where
			 table_name = upper('$3'))

/
exit
EOF
echo "from $3" >> unload.tmp
if [ "$4hakan" != "hakan" ]
then
  echo "$4" >> unload.tmp
fi
echo "/" >> unload.tmp
echo "exit" >> unload.tmp

sqlplus -s $1/$2 << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start unload.tmp
exit
EOF
rm unload.tmp
---

special thanks to thomas kyte for unload...

load.bat
---

if [ $# -lt 6 ]
then
 echo "Usage : sqlloader <username> <password> <table> <filename> <tnsentry> <delimeter>"
 echo "Example : sqlloader system manager sube sube.txt develop |"  exit 99
fi

echo "unrecoverable load data" > sqlloader_$3.par
echo "infile \"$4\"" >> sqlloader_$3.par
echo "append into table $3" >> sqlloader_$3.par
echo "fields terminated by \"$6\"" >> sqlloader_$3.par
echo "(" >> sqlloader_$3.par

sqlplus $1/$2i@$5 << EOF > sqlloader_$3.tmp select count(*) from user_tab_columns where table_name=upper('$3'); EOF
toplam=$(echo $(tail -5 sqlloader_$3.tmp | head -1) | tr -s ' ') sayac=1
while
[ $sayac -le $toplam ]
do
sqlplus $1/$2@$5 << EOF > sqlloader_$3.tmp select column_name
from user_tab_columns where table_name=upper('$3') and column_id=$sayac; EOF
katar=$(tail -5 sqlloader_$3.tmp | head -1) if [ $sayac -eq $toplam ]
then
  echo $katar >> sqlloader_$3.par
else
  echo "$katar," >> sqlloader_$3.par
fi
let sayac=$sayac+1
done
echo ')' >> sqlloader_$3.par
chmod 777 sqlloader_$3.par
rm sqlloader_$3.tmp
sqlldr $1/$2@$5 control=sqlloader_$3.par log=$3.log bad=$3.bad data=$4 discard=$3.discard direct=true errors=0 silent=header parallel=true readsize=1048576 bindsize=1048576
retcode=`echo $?`
case "$retcode" in
0) tail -19 $3.log;echo "SQL*Loader execution successful";; 1) echo "SQL*Loader execution exited with EX_FAIL, see logfile";more $3.log ;;
2) echo "SQL*Loader exectuion exited with EX_WARN, see logfile";more $3.log ;;
3) echo "SQL*Loader execution encountered a fatal error";more $3.log ;; *) echo "unknown return code";more $3.log;; esac rm sqlloader_$3.par
---

special thanks to hakan yuksel for load...:-)

--
=====================================
Hakan Yuksel, Oracle DBA
TekstilBank - Istanbul, TURKEY
email:hyuksel_at_tekstilbank.com.tr
http://members.xoom.com/hakan_yuksel/
=====================================




In article <8oobg5$3gp$1_at_mailint03.im.hou.compaq.com>,
  "Lisa Spielman" <lisa.spielman_at_compaq.com> wrote:
> I need to move data from 1 database to another and need
> to do this using files.  There are about 8 tables and the
> largest table has about 100 rows.
>
> Is there a simple way to do this in Oracle ?
>
> Informix makes this really easy:
>     unload to x.dat select * from tableX
>     load from x.dat ....
>
> Can I  select * from table x into a file, and then insert
> from this file into the same table in another database ?
>
>     thanks,  Lisa
>
>



Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Sep 13 2000 - 03:17:58 CDT