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

From: Tapio Luukkanen <vtl_at_hemuli.tte.vtt.fi>
Date: 1996/11/22
Message-ID: <wa20dmozsd.fsf_at_morko.tte.vtt.fi>

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

Here is a Korn shell script which some of you might find useful. Caveat emptor, naturally.

--Tapio

#!/bin/ksh
#
# $Id: dump_oratable,v 1.6 1996/08/14 10:32:52 vtl Exp $
#
# Dump given Oracle table in flat format.
#
# Comments to: tapio.luukkanen_at_vtt.fi
#

config=/usr/local/kassu/runtime/bin/change_sid test -r $config && . $config

usage() {
test -n "$1" && echo "$program: $_at_" >&2 cat >&2 <<EOF_USAGE1
usage: dump_oratable [options] [USER] TABLE

  -compress         compress the result.
  -connect   STRING use this connect string to actually read the data.
  -dba              use sys.dba_columns instead of all_tab_columns.
  -help             print even longer help message.
  -output    FILE   output to this FILE.
  -sql              print the select script to standard output.
  -user             produce a script, which dumps given user's tables.
  -variable [CHR]   variable length output, columns separated
                    with ASCII character CHR (default 29).
  [USER] TABLE      select this USER's given TABLE,
                    if only TABLE is given (try to) use current user.

EOF_USAGE1 test -n "$long_usage" && cat >&2 <<EOF_USAGE2

  examples:

      dump_oratable -dba SCOTT EMP
      dump_oratable -dba SCOTT EMP -sql
      dump_oratable -dba SCOTT EMP -sql -variable 9

    output tab-separated fields and compress the data into emp.Z:
      dump_oratable 'OPS\$VTL' EMP -variable 9 -compress -output emp.Z

    output a shell-script, which dumps all my tables:
      dump_oratable -user 'OPS\$VTL'

    create sql-scripts into files named 'TABLE.sql' for all my tables:
      dump_oratable -user 'OPS\$VTL' -sql -variable 9 | sh

  caveats:

• when exporting other people's tables '-dba' may be needed
• long fields are not handled properly; use '-sql' and edit the script.
• tables with many wide fields may cause a buffer overflow; either use '-variable' or edit the script produced by '-sql'.
• if your account is an OPS-account, you need to give both USER & TABLE for your own tables.
• the fixed format actually outputs TABS, so it is fundamentally flawed (need to use 'expand' or something similar to fix the result); this is because 'set trimout' doesn't work with 'set tab off', and there is no reasonable general estimate for linesize available.
• the biggest thing: I have never (yet) used the output from this program for anything, just wrote it to pass my time one rainy July afternoon !!

EOF_USAGE2
exit 1
}

    # ------------------------------------------------------------

    # export all variables to subprocesses. set -a

program=`basename $0`
connectstring=/
sepchar=${sepchar:-29}
subpflags=""
filesuffix=.dat

arraysize=${arraysize:-100}
linesize=${linesize:-999}
maxdata=${maxdata:-32767}
longdata=${longdata:-30000}

test -z "$1" && usage "no parameters ?"

while true; do
  test -z "$1" && break
  case "$1" in

    -arraysize) arraysize=$2; shift ;;
    -linesize) linesize=$2; shift ;;
    -maxdata) maxdata=$2; shift ;;
    -longdata) longdata=$2; shift ;;

    -help) long_usage=yes usage ;;
    -debug) debug=true ;;
    -debug2) debug2=true ;;

    -connect) connect="$2"; subpflags="$subpflags -connect $2"; shift ;;     -compress) compress=true; subpflags="$subpflags -compress"

               filesuffix=.dat.Z
               ;;
    -dba) use_dba_tables=true; subpflags="$subpflags -dba" ;;
    -output | -o) redirect="> $2"; shift ;;
    -owner) owner=true ;; # use SELECT ... FROM OWNER.TABLE 
    -sql) filesuffix=.sql; subpflags="$subpflags -sql"; show_sqlfile=true ;;

    -user) do_user=true ;;
    -variable) variable=true

               case "$2" in [0-9]*) sepchar=$2; shift ;; esac
               subpflags="$subpflags -variable $sepchar"
               ;;

    -*) usage "unrecognized option '$1'" ;;     *) OWNER="$1"

       OMISTAJA="upper('$OWNER')"
       case "xyzzy$2" in xyzzy-*) test -n "$do_user" && OWNER="$OWNER"
                                  test -z "$do_user" && TABLE="$OWNER" &&
                                    OMISTAJA=user && OWNER=user
                                  ;;
                         xyzzy?*) TABLE="$2"; owner=true; shift ;;
                         *) #test -n "$do_user" && true
                            test -z "$do_user" && owner=true &&
                              TABLE="$OWNER" && OMISTAJA=user && OWNER=user
                            ;;
       esac
       ;;

  esac
  shift
done

test -z "$OMISTAJA" && OMISTAJA="upper('$OWNER')"

    # ------------------------------------------------------------

datefield_alias=datefield

COLUMN_SEPARATOR="|| DECODE(dc.column_id,d2.column_id,'',',')"

test -n "$variable" && {
  datefield_alias=""
  SPACESETTING="prompt set space 0"
  COLUMN_SEPARATOR="|| DECODE(dc.column_id,

                              d2.column_id,'',' ||chr($sepchar)||')"

}

COLUMN="DECODE(dc.data_type,

               'DATE','TO_CHAR('||lower(dc.column_name)
                                ||',''YYYYMMDD:HH24MISS'') $datefield_alias',
               lower(dc.column_name))"

TABLEDEF=$TABLE
test -n "$owner" && TABLEDEF="$OWNER.$TABLE"

test -n "$connect" && connectstring="$connect"

alltables='all_tables atab'
allcolumns='all_tab_columns dc'
maxcolumn='all_tab_columns d2'

test -n "$use_dba_tables" && {
  alltables='sys.dba_tables atab'
  allcolumns='sys.dba_tab_columns dc'
  maxcolumn='sys.dba_tab_columns d2'
}

cmd="sqlplus -s $connectstring"
test -n "$debug" && cmd=cat

process="sqlplus -s $connectstring $redirect" test -n "$debug2" && process=cat

test -n "$compress" &&
  process="sqlplus -s $connectstring | compress $redirect"

    # ------------------------------------------------------------

test -n "$do_user" && {
  $cmd <<EOF_USER
set heading off
set pagesize 0
set linesize 999
set tab on
set trimout on
set showmode off
set feedback off
set verify off

prompt #!/bin/sh
SELECT DISTINCT '$program ' || replace('$OWNER','$','''$''') ||' '

             || replace(table_name,'$','''$''')
             || ' -o ' || table_name || '$filesuffix'
             || ' -owner$subpflags'

  FROM $alltables
 WHERE owner = $OMISTAJA

       $condition;

EOF_USER exit 0
}

    # ------------------------------------------------------------

test -n "$show_sqlfile" && cmd=cat

(
$cmd <<END_OF_SCRIPT
set heading off
set pagesize 0
set linesize 999
set showmode off
set feedback off
set verify off
whenever sqlerror exit 1 rollback

prompt set heading off
prompt set pagesize 0
prompt set showmode off
prompt set feedback off
prompt set verify off
prompt set arraysize $arraysize
prompt set maxdata $maxdata
prompt set long $longdata
  rem linesize should be calculated from column lengths prompt set linesize $linesize
rem prompt set tab off
rem prompt set trimout on

prompt column datefield format a15
prompt column comma format a1
$SPACESETTING
prompt whenever sqlerror exit 1 rollback

prompt SELECT
SELECT
    ' ' || $COLUMN
           $COLUMN_SEPARATOR
  FROM $allcolumns, $maxcolumn

 WHERE dc.owner = $OMISTAJA
   AND dc.table_name = upper('$TABLE')
   AND d2.owner = $OMISTAJA
   AND d2.table_name = upper('$TABLE')
   AND d2.column_id = (SELECT max(column_id) FROM $allcolumns
                        WHERE owner = d2.owner
                          AND table_name = d2.table_name)

 ORDER BY dc.column_id;

prompt FROM $TABLEDEF
prompt /

QUIT;
END_OF_SCRIPT
) | eval $process Received on Fri Nov 22 1996 - 00:00:00 CET