hi aidan,
the following script will create .ctl and .dat files for each table of
OWNER.
These files can then be used with sqlldr.
HTH alex
#!/bin/ksh
###############################################################################
#
# Desc: create CTL and DAT files for sqlldr usage
#
###############################################################################
#set -x
USR=user/password
echo "set feedback off
set pagesize 0
select table_name from all_tables where owner like 'OWNER';" | sqlplus -s
$USR | while read TABL
do
STR1="nothing"
STR="nothing"
COLUM="nothing"
TYPE="nothing"
echo "desc $TABL" | sqlplus -s $USR | awk '{print $1 " " $2}' |
tail +3 | grep "[A-Z]" | while read COLUM TYPE
do
if [ "$STR" = "nothing" ]
then
STR=$COLUM
STR1=$COLUM
else
if [ "$TYPE" = "DATE" ]
then
STR="$STR || '|' ||
to_char($COLUM,'DD-MON-YY:HH24:MM:SS')"
STR1="$STR1,$COLUM DATE
'DD-MON-YY:HH24:MM:SS'"
else
STR="$STR || '|' || $COLUM"
STR1="$STR1,$COLUM"
fi
echo $STR > STR
echo $STR1 > STR1
fi
done
echo "set feedback off
set pagesize 0
set linesize 3000
select `cat STR`|| '|' from $TABL;" | sqlplus -s $USR >> $TABL.dat
echo "LOAD DATA" > $TABL.ctl
echo "INFILE '$TABL'" >> $TABL.ctl
echo "INTO TABLE $TABL" >> $TABL.ctl
echo "FIELDS TERMINATED BY '|'" >> $TABL.ctl
echo "(`cat STR1`)" >> $TABL.ctl
done
# END OF SCRIPT
Daniel Wisser
<daniel.wisser@isis-p An: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
apyrus.com> Kopie:
Gesendet von: Thema: Re: Dump table to file,
edit and reimport
[EMAIL PROTECTED]
06.02.2003 16:28
Bitte antworten an
ORACLE-L
hi!
unload and load work like that
SQL> exp myuser/********@mydb file=C:\temp\mydump.dmp tables=(mytable)
SQL> imp myuser/********@mydb file=C:\temp\mydump.dmp tables=(mytable)
but i don't think you will have a good time editing the file.
if you want a | delimited file, you should use spool and gerenate
the file you need.
daniel
Aidan Whitehall wrote:
>
> In Sybase's ASA there was an "unload" command which wrote to a text file
> the SQL to recreate a table, along with all it's data which running the
> SQL then imported. This allowed you to very easily dump a table, edit
> it's structure and suck the data back in.
>
> The closest thing I've found in Oracle is right-clicking on a table |
> Data Management | Export. However, we're not running the Oracle
> Management Server (just because we don't know how to set that up yet),
> so this functionality isn't available.
>
> Barring installing OMS, is there a quick and dirty way to do this?
>
> And, if the answer is "no", is OMS easy to set up? Thanks!
>
> --
> Aidan Whitehall <[EMAIL PROTECTED]>
> Macromedia ColdFusion Developer
> Fairbanks Environmental Ltd +44 (0)1695 51775
>
> ________________________________________________________________________
> This e-mail has been scanned for all viruses by Star Internet. The
> service is powered by MessageLabs. For more information on a proactive
> anti-virus service working around the clock, around the globe, visit:
> http://www.star.net.uk
> ________________________________________________________________________
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Aidan Whitehall
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daniel Wisser
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
