Re: Export from Oracle in comma-separated format
Date: 1997/02/03
Message-ID: <kcwvjqaa_at_maderich.demon.co.uk>#1/1
In Article <32EBF15C.3E9B_at_qrcsun.qrc.org> Oracle writes:
>BoB Pierce wrote: >> >--- >Name : Lun Wing San >Title : Oracle Application Developer of Hong Kong Productivity Council > Oracle Database Administrator and System Administrator ofQRC
>Phone : (852)27885841
>
The attachment for this posting is a UNIX shell script which builds a select statement for the table that you want to dump. It then dumps the data in the table in format suitable for reloading by SQL*Loader.
The data itself is in CSV format.
I use it for dumping data for testing. The benefit over 'export' is that
- the SQl statement can take a WHERE clause;
- you can edit the data before reloading.
Sorry, that it's UNIX specific, but you can replace the pipes with
temporary files.
# set -x
USAGE="Usage:\t$(basename $0)\n\n
\t\t[\n
\t\t -t <table name> |\n
\t\t -d <database> |\n
\t\t -u <username>/<password> |\n
\t\t -w <where clause>\n
\t\t]\n
\n\n
\t\ttable name defaults to environment variable TABLE_NAME\n
\t\tdatabase defaults to environment variable ORACLE_SID\n
\t\tuser name/ password defaults to environment variable DBNAME\n
\t\twhere clause defaults to environment variable WHERE_CLAUSE"
# Set defaults and do command line processing ORACLE_UID=${DBNAME:-misapp/password}
# Process any command-line arguments
if [ $# -gt 0 ]
then
while getopts 't:d:u:w:h?' c do case $c in t) TABLE_NAME=$OPTARG ; export TABLE_NAME ;; d) ORACLE_SID=$OPTARG ; export ORACLE_SID ;; u) ORACLE_UID=$OPTARG ; export ORACLE_UID ;; w) WHERE_CLAUSE=$OPTARG ; export WHERE_CLAUSE ;; h | \?) echo $USAGE >&2 exit 0;; esac done shift $(expr $OPTIND - 1)
fi
if [ "$TABLE_NAME" = "" -o "$ORACLE_SID" = "" -o "$ORACLE_UID" = "" ] then
echo $USAGE >&2 exit 1
fi
echo "Creating Unload Script for ${TABLE_NAME} " >&2 echo "... for UID ${ORACLE_UID} on SID ${ORACLE_SID}" >&2 echo "... ${WHERE_CLAUSE:-for all rows} " >&2 # PROMPT PROMPT OPTIONS(DIRECT=TRUE) # PROMPT PROMPT
# PROMPT PROMPT UNRECOVERABLE
# PROMPT PROMPT
sqlplus -s ${ORACLE_UID} <<EndOfHereFile | sqlplus -s ${ORACLE_UID} | expand SET PAGES 0 HEADING OFF UNDERLINE OFF FEEDBACK OFF SQLPROMPT '' VERIFY OFF line 1000
COLUMN UnloadFormat FORMAT A100 WORD_WRAPPED
PROMPT SET PAGES 0 HEADING OFF UNDERLINE OFF FEEDBACK OFF SQLPROMPT '' VERIFY OFF line 1000
PROMPT PROMPT LOAD DATA
PROMPT PROMPT
PROMPT PROMPT INFILE *
PROMPT PROMPT
PROMPT PROMPT APPEND
PROMPT PROMPT
PROMPT PROMPT INTO TABLE ${TABLE_NAME}
PROMPT PROMPT
PROMPT PROMPT FIELDS TERMINATED BY ',' ENCLOSED BY '"'
PROMPT PROMPT
PROMPT PROMPT (
SELECT
'PROMPT ' ||
DECODE(column_id,1,'', ',' ) ||
COLUMN_NAME || ' '||
DECODE(DATA_TYPE,
'DATE', 'DATE "YYYYMMDDHH24MISS" ' , 'CHAR' ) ||
' ' UnloadFormat
FROM user_tab_columns
WHERE table_name = UPPER('${TABLE_NAME}') ORDER BY column_id
/
PROMPT PROMPT )
PROMPT PROMPT BEGINDATA
PROMPT SELECT
SELECT
DECODE(column_id,1,'','||'',''||') ||
'''"''||'||
DECODE(DATA_TYPE,
'CHAR', ' REPLACE(' || COLUMN_NAME || ', ''"'', ''\"'') ' , 'DATE', ' TO_CHAR(' || COLUMN_NAME || ', ''YYYYMMDDHH24MISS'') ' , COLUMN_NAME ) ||
'||''"''' UnloadFormat
FROM user_tab_columns
WHERE table_name = UPPER('${TABLE_NAME}') ORDER BY column_id
/
PROMPT FROM ${TABLE_NAME} ${WHERE_CLAUSE} PROMPT / SET SQLPROMPT SQL>
EndOfHereFile Received on Mon Feb 03 1997 - 00:00:00 CET