Re: best way to export Oracle table into a text file?

From: Stephen A Tahmosh <tahmossx_at_itd.ssb.com>
Date: 1996/11/19
Message-ID: <56sq7f$ohe_at_svna0001.clipper.ssb.com>#1/1

Igor <igor_at_dogleg.cv.com> wrote:

>I'm looking to easiest ( and free ) way to export Oracle table as a TEXT
>file ( in a format 1 row/line, several K rows, each row <100 chars).
>Any suggestions/scripts/programs etc will be appreciated
>
>		Igor

A script which I found on this newsgroup some time ago: Uses sqlplus / plsql to generate data file and sqlloader control file.

Hope this helps, It's helped me a lot.
Steve

#!/bin/sh
# unload_prep Routine
#
# Creates a SQL*Plus script to unload an Oracle table to a flat file,
# and creates a SQLLOAD controlfile that can be used to load the file.
#
# Input arguments:
# $1: user/password
# $2: table owner
# $3: table name
# $4: file to receive SQL*Plus unload script
# $5: file to receive SQLLOAD controlfile
#
# Typical usage: execute unload_prep once, use the resulting SQL*Plus
# script and SQLLOAD controlfile multiple times in varying situations.
# It is most useful with very large tables that can be loaded with the
# direct path option of SQLLOAD - likely to be far quicker than IMPORT.
#
# The unload dataset will have 1-byte null indicator columns added
# as a positive indication of null values (this may be easily
# disabled). SQL*Plus will substitute blanks in null situations,
# and SQLLOAD will force blanks to null during the load (not always
# the desired result). The null indicator columns are included for
# greater portability of the data into other applications and uses.
#
# If a primary key index is associated with the table, an ORDER BY
# clause is built that specifies the PK columns. This will coerce
# usage of the PK index during unloading, so the unloaded data will
# be in PK order without need for sorting. If the PK index is to be
# recreated, the nosort option may be specified since data will be
# loaded in physical order that conforms to the PK columns.
#
# This utility does not build spool directives in the SQL*Plus script
# because large unload datasets are likely to require positioning on
# a per-run basis. In addition, the unloaded data may be piped directly
# to the compress utility in archiving situations, avoiding the need to
# acquire space for the full dataset (sample below).
#
# Most of the logic is in PL/SQL and requires a work table and a
# sequence generator to build the commands. Three types of
# commands are assembled in this table: SQL*Plus column commands
# (numeric and date fields), the SELECT statement, and the
# SQLLOAD controlfile directives. The table and sequence may be
# generated with these DDL statements:
#
# create table unload_commands

 #     ( comm_type     char     ( 1 )


#     , line_num      number   ( 5 )

#     , comm_line     varchar2 ( 80 ) ;

# create sequence unld_seq ;
#
# Known restrictions:
# - LONG datatypes are not expected or handled.
# - RAW datatypes are not handled, lacking a "hex to raw" conversion
# option in SQLLOAD.
#
# Numeric fields with unspecified precision (default 22 digits) are
# held to 12 digits for space saving purposes. The site where the
# code was developed does not deal in huge integers, and normally
# specifies precision during column definitions. This limit may be
# raised up to 22 by editing the variable 'MAX_NUM_LENGTH' in the
# PL/SQL block.
#
# Sample unload scripts using the unload SQL*Plus script (developed
# on an HP-UX unix platform). Script name is unload_script.sql
#
# sqlplus -s <<-EOF
# userid/password
# spool <unload dataset>
# _at_unload_script
# spool off
# EOF
#
# Piping output to compress:
#
# sqlplus -s <<-EOF | compress -c > unload_dataset.Z
# userid/password
# _at_unload_script
# EOF
#
# The piping of stdout requires removal of the "termout off"
# SQL*Plus directive in the unload script.
#
# Dave Irick Feb. 24, 1996
# The Arbitron Co. email: dave.irick_at_arbitron.com

if [ $# -lt 4 ]
then
  echo "Usage: unload_prep user/pass table_owner table_name unload.sql unload.c tl"
  exit 1
fi

user_pass=$1
table_owner=$2

table_name=$3
unload_sql=$4
unload_ctl=$5

sqlplus -s $user_pass > /dev/null <<-EOF set pagesize 0 echo off feedback off termout off column username noprint
column comm_type noprint
column line_num noprint
truncate table unload_commands ;
_at_unload_prep $table_owner $table_name spool __unload_prep_sql_temp
-- spool SQL*Plus unload script:
select * from unload_commands

   where comm_type in ('S','C')
   order by comm_type, line_num ;
spool off
spool __unload_prep_ctl_temp
-- spool SQLLOAD controlfile:
select * from unload_commands

   where comm_type = 'L'
   order by line_num ;
spool off
exit
EOF
# remove trailing blanks:

sed 's/ *$//g' __unload_prep_sql_temp.lst > $unload_sql sed 's/ *$//g' __unload_prep_ctl_temp.lst > $unload_ctl

#rm -f __unload_prep_???_temp.lst

echo "\nFiles created:"
ls -l $unload_sql $unload_ctl

exit 0

Steve Tahmosh
State Street Bank Received on Tue Nov 19 1996 - 00:00:00 CET