Re: Export from Oracle in comma-separated format

From: Stuart Turton <sturton_at_maderich.demon.co.uk>
Date: 1997/02/03
Message-ID: <kcwvjqaa@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 of 

 QRC
>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

1. the SQl statement can take a WHERE clause;
2. 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 CST