Re: How to unload Oracle in ASCII flat file

I recently posted the anon ftp location of the 'unloadr' utility which is an oraperl script to extract Oracle tables as delimited flat files.

In case it was missed, it is available at:

	host:	ftp.ml.csiro.au
	IP:	192.67.12.100
	dir:	/pub/jstander/oracle_utilities

Another method is to convert spooled standard oracle SELECT output to a delimited file using the spaces between dashes in the second line of the output header to locate the columns. This is done with a perl (NOT oraperl) script I call 'insert_delims' which is one of several such scripts I need to massage raw data. It is also available at the above site and I am appending it to this message. Hope some of you find it useful. (Bug reports appreciated).

Jeff

---
___________________________________________________________________________

Jeff.Stander_at_ml.csiro.au        _--_|\        Database Analyst
CSIRO Division Of Fisheries    /      \       Pelagic Fisheries Resources
GPO Box 1538, Hobart           \_.--._/       Tasmania 7001, Australia
Aus Tel: 002-325-332                 v        Intl Tel: +61-02-325-332
Aus Fax: 002-325-000                          Intl Fax: +61-02-325-000
___________________________________________________________________________



#!/bin/sh

# This is a shell archive (produced by shar 3.50)

# To extract the files from this archive, save it to a file, remove

# everything above the "!/bin/sh" line above, and type "sh file_name".

#

# made 02/23/1994 20:39 UTC by jstander_at_solaris

# Source directory /a/aqueous/tuna/jstander/src/orautils

#

# existing files will NOT be overwritten unless -c is specified

#

# This shar contains:

# length  mode       name

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

#   2437 -rw-rw---- insert_delims.1

#   6387 -rwxrwx--x insert_delims

#

# ============= insert_delims.1 ==============


if test -f 'insert_delims.1' -a X"$1" != X"-c"; then
	echo 'x - skipping insert_delims.1 (File already exists)'
else
echo 'x - extracting insert_delims.1 (Text)'
sed 's/^X//' << 'SHAR_EOF' > 'insert_delims.1' &&
.PU
.TH INSERT_DELIMS 1 "24 February 1994" "CSIRO Fisheries" 
.SH NAME
insert_delims - insert delimters at set columns in fixed format data
.SH SYNOPSIS
.B insert_delims
[
.BI -T c
] [
.B -qws
] [
.I file.. 
] 
.I column 
[
.I column..
]
.br
.B insert_delims
[
.BI -T c
] [
.B -qwaAs
] [
.I file.. 
] 
.ll +8
.ad
.SH DESCRIPTION
.I Insert_delims
will add a delimiter (default is comma) at set columns in fixed format data.
Fields will automatically be trimmed of leading and trailing white space
unless the
.B -w 
option is set.
Optionally, fields will be enclosed in double quotation marks.
X
Empty lines or lines made up of whitespace only will be deleted.
X
If no filenames are given 
.I insert_delims
acts as a filter, otherwise files are fixed in place.
Be warned, if files are fixed in place original data will be lost if copies are not made first.
.SH OPTIONS
.TP
.BI -T c
set output delimiter (default=",")
.PD
.TP
.B -q
optionally delimit with quotes
.PD
.TP
.B -w
do not pack whitespace at beginning and end of fields.
.PD
.TP
.B -a
set columns from Oracle SELECT header (all SELECT headers are then deleted).
The first two lines of the input file are assumed to be a standard Oracle
SELECT header and the spaces in the dashed line are used to compute the
columns.  No column specification is required with the
.B -a
option.  
The first two lines are deleted from the output.
.PD
.TP
.B -A
.PD
Same as the
.B -a
option except that the delimited column names are retained
as line 1 of the output.
.TP
.I file..
file(s) to fix.
.PD
.TP
.I column..
column number(s) after which to insert delimiters
.SH EXAMPLES
If file "test" contains the following line ("b" for blanks):
.br
.in +10
7LOUARISObMARUbNOb58bbbb18044bb
.br
.sp 1
.in-10
Running "insert_delims 4 24 test" will convert it to:
.br
.in +10
7LOU,ARISObMARUbNOb58,18044
.br
.sp 1
.in-10
Running "insert_delims -q 4 24 test" will convert it to:
.br
.in +10
"7LOU","ARISObMARUbNOb58","18044"
.br
.sp 1
.in-10
Running "insert_delims -wq 4 24 test" will convert it to:
.br
.in +10
"7LOU","ARISObMARUbNOb58bbbb","18044bb"
.br
.sp 1
.in-10
.SH BUGS
Tabs in the input are not expanded and can cause erroneous output.
Be sure to include a "\fBset\ tab\ off\fR" line in the SQL statement 
used to generate the input or run the input through 
.IR expand (1)
before processing with
.IR insert_delims .
.SH SEE ALSO
add_delims(1), zerofill_fields(1)
.SH AUTHOR
Jeff.Stander_at_ml.csiro.au
SHAR_EOF
chmod 0660 insert_delims.1 ||
echo 'restore of insert_delims.1 failed'
Wc_c="`wc -c < 'insert_delims.1'`"
test 2437 -eq "$Wc_c" ||
	echo 'insert_delims.1: original size 2437, current size' "$Wc_c"
fi


# ============= insert_delims ==============


if test -f 'insert_delims' -a X"$1" != X"-c"; then
	echo 'x - skipping insert_delims (File already exists)'
else
echo 'x - extracting insert_delims (Text)'
sed 's/^X//' << 'SHAR_EOF' > 'insert_delims' &&


#! /usr/local/bin/perl

###############################################################################

# Name:		insert_delims - insert delimters at set columns in fixed format data


#	

X  $Synopsis = <<X;
X	insert_delims [ -qw ] [ -Tc ] [ file.. ] column [ column.. ]
X	insert_delims [ -qwaA ] [ -Tc ] [ file.. ] 
XX


# Description:	

#	Add delimiter (comma) at set columns in a fixed format text file.

#	Fields automatically will be trimmed of leading and trailing white space

#         unless -w option is set.

#	Optionally, fields will be enclosed in double quotation marks

#	If no files are specified, act as a filter

#	Empty lines or lines made up of whitespace only will be deleted

# Warning

#	Files will be modified by this script


#	

X  $Options = <<X;
X	file..		file(s) to fix
X	column#..	column number(s) after which to insert commas 
X	-q		optionally delimit with quotes
X	-w		do not pack whitespace at beginning and end of fields
X	-a		set columns from Oracle SELECT header (headers are deleted)
X	-A		same as -a but retain column names list as line 1
X	-s		silent mode, don't report on results to user
X	-Tc		change output delimiter to character "c" instead of comma
XX


# Example:

#	IF file "test" contains the following line

#		7LOUARISO MARU NO 58    18044  

#      	Running "insert_delims 4 24 test" will convert it to:

#		7LOU,ARISO MARU NO 58    ,18044

#      	Running "insert_delims -q 4 24 test" will convert it to:

#		"7LOU","ARISO MARU NO 58    ","18044"

#      	Running "insert_delims -wq 4 24 test" will convert it to:

#		"7LOU","ARISO MARU NO 58","18044"

# Returns: 	

# On Error:	

# Host:         SunOS deep sun4m

# Author:       Jeff Stander (jstander_at_ml.csiro.au)

# Revision:     1.0 (jstander 18.01.94): new

# (c) 1994	CSIRO Div. of Fisheries, Hobart Tasmania, Australia


###############################################################################


X 
require "getopts.pl";
X


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

# Get environmental details.  


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


($Name="$0")  =~ s+.*/++ if !$Name;
X


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

# Process command line arguments


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


&Getopts('qszaAwhT:') || ($opt_z=1);
X
$delim=$opt_T || ",";
X
if ( $opt_z || $opt_h ) {                
X        print STDERR "\nusage: $Synopsis$Options"; 
X        exit;
}
X
$opt_a = 1 if $opt_A;
X


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

# Push values onto index arrays


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


sub pushindex {
X	local($index) = pop(_at__);
X	$len[$last] = $index - $last;
X	$last = $index;
X	push(_at_columns,$last) ;
}
X


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

# Evaluate arguments 


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


$last=0;
X
&pushindex(0) if !$opt_a;
while ( $#ARGV>-1 ) {
X	$arg = $ARGV[0];
X	if ( $arg =~ /^\d+$/ ) {
X		die "$Name: column out of order: $arg\n" if $last>$arg;
X		&pushindex ($arg);
X	}
X	else {
X		die "$Name: $!: $arg\n" if !-e $arg;
X		push(_at_files,$arg);
X		$|=1;
X	}
X	shift;
}
X


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

# Exit if no arguments


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


print STDERR "$Name: no columns specified" if ( $last==0 && !$opt_a );
if ( $last==0 && !$opt_a ) {                
X        print STDERR "\nusage: $Synopsis$Options"; 
X        exit 2;
}
X
$len[$last] = 32767;
X
$q='"' if $opt_q;
X
_at_columns = reverse @columns;
X
_at_files=(STDIN) if !_at_files;
X


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

# Open each file and fix it, or act as filter if no files specified


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


$tmp = "/tmp/insert_delims$$";
foreach $file (_at_files) {
X	if ( $| ) {
X		open($file,$file) || die "$Name: $!\n" ;;
X		open(STDOUT,"> $tmp") ;
X		print STDERR "$Name: delimiters added: $file " if !$opt_s;
X	}
X	$cnt = $opt_a ? 0 : 3;
X	$recs=0;
X	while (<$file>) {
X		next if /^$/ ;
X		chop;
X		$cnt++;
X
X		#-----------------------------------------------------------------------
X		# Read first two lines of SELECT header to determine column position
X		#-----------------------------------------------------------------------
X		if ( $cnt == 1 ) {
X			$head = $_;
X			$_ =~ s/\s*$//;
X			next
X		}
X		if ( $cnt == 2 ) {
X			$_ =~ s/\s*$//;
X			die "$Name: Oracle SELECT header not found.  Required with -a option.\n" if !/^[-\s]*$/; 
X			next if $cnt==1;
X			undef _at_len; 
X			undef $last; 
X			undef _at_columns; 
X			$index = $[;
X			&pushindex(0);
X			while (( $index = index($_, ' ', $index)) >= $[ ) {
X				$index++;
X				&pushindex($index);
X				#print STDERR "INDEX: $index\n";
X			}
X			$len[$last] = 32767;
X			_at_columns = reverse @columns;
X			if ( $opt_A ) {
X				$_ = $head;
X			}
X			else {
X				next;
X			}
X		}
X
X		#-----------------------------------------------------------------------
X		# Skip SELECT headers if -a option
X		#-----------------------------------------------------------------------
X		if ( $opt_a && $cnt > 2 ) {
X			if ( $head eq $_ ) { <$file>; next; }
X		}
X
X		#-----------------------------------------------------------------------
X		# Process each line in reverse order of columns
X		#-----------------------------------------------------------------------
X		$recs++;
X		foreach $col (_at_columns) {
X			$column = substr($_,$col,$len[$col]);
X			#print STDERR "COL: $col $len[$col] '$column'  \n";
X			if ( ! $opt_w ) {
X				$column =~ s/\s*$//;
X				$column =~ s/^\s*//;
X			}
X			$column .= (length($delim) ? $q . $delim . $q : $delim);
X			substr($_,$col,$len[$col]) = $column;
X		}
X		$_ = $q . $_ . $q;
X		s/$q$q//g if $q;
X		s/[$delim]$//;
X		print $_, "\n";
X	}
X	if ( $| ) {
X		close($file) ;
X		close(STDOUT) ;
X		system("cat $tmp > $file; rm $tmp") ;
X		$recs-- if $opt_A;
X		print STDERR "($recs records)\n" if !$opt_s;
X	}
}
X
__END__
SHAR_EOF
chmod 0771 insert_delims ||
echo 'restore of insert_delims failed'
Wc_c="`wc -c < 'insert_delims'`"
test 6387 -eq "$Wc_c" ||
	echo 'insert_delims: original size 6387, current size' "$Wc_c"
fi
exit 0