Re: How to unload Oracle in ASCII flat files?
From: Stephane Hamel <shamel_at_tdsb-s.mais.hydro.qc.ca>
Date: Tue, 15 Feb 1994 13:16:28 GMT
Message-ID: <CL9ovH.5o8_at_ireq.hydro.qc.ca>
'di';
'ig00';
#
$Synopsis=<<X;
}
}
}
}
elsif ($type eq "r") {
}
elsif ($type eq "p") {
}
}
}
if ( $opt_u ) {
}
}
shift;
}
}
else {
}
else {
EOI $csr = &ora_open($lda,$qh) || die $ora_errstr;
}
}
EOI
}
}
elsif ( $outname ) {
}
}
elsif ( $printout ) {
}
elsif ( !$redirect ) {
EOI
EOI
-- \$Revision 1.0 $ ($USER) $date: new
LOAD DATA
INFILE "$table.mrg"
INSERT
INTO TABLE $TABLE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
EOI
EOI $csr = &ora_open($lda,$qh) || die $ora_errstr;
ORDER BY column_id
EOS
EOS $qh=<<EOS;
EOS
EOI $csr = &ora_open($lda,$qh) || die $ora_errstr;
}
EOI
EOI
Description: $tablecomment[1]
Oracle User ID: $orauser[0]
EOI $csr = &ora_open($lda,$qh) || die $ora_errstr;
}
'; __END__ ##### From here on it's a standard manual page #####
unloadr - extract data from Oracle table in "merge" format .SH SYNOPSIS
.B unloadr
[
.B -dmlsHhpbqSO]
] [
.B -C[cz]
] [
.BI -Fc
] [
.BI -o\fR[ outfile\fR]
] [
.BI -f fetchlimit
] [
.BI -D dfmt
] [
.BI -w where..
]
dumps an Oracle table as an ASCII flat file in "merge" format: each field is comma-delimited and enclosed by quotes. If none of the
.B -dmls
options are set, assume all of these options are set.
Create a file describing the structure of .IR table_name,
including any stored table and column comments. Output is to
Create ASCII flat file containing all columns in .IB table_name.
Output is to
Create an Oracle loader control file to reload the merge file.
Output is to
Create a file containing
.B SQL
statements to (re)create the table for the extracted data. Output is to
Suppress enclosure of each field by quotation marks. (Normally
.I unloadr
will surround each field in the merge file with double quotes).
First line of the output file holds field names, comma-separated.
Print a brief help message.
Send output to $PRINTCMD, your favorite spooler ("/home/jstander/bin/printw" if not defined).
Browse mode, send output to $PAGER (default="more").
Set column separator in merge file to
.IR c .
Default separator is a comma.
set date format (default=DD-MON-YYYY HH:24MI).
fetch only <fetchlimit> records from the database.
output is redirected to outfile if present.
Output is redirected to stdout
Default is to direct output to <tablename>.mrg.
Quiet mode. Don't report actions to STDERR.
Dump SQL statement used for merge extraction. No unloading is done.
Compress output files using
.I compress
utility (if present).
Compress output files using
.I zip
utility (if present).
A SQL expression to restrict SELECTed records from table. Note only the expression should be passed, not "WHERE =", see example below.
Name of the table to be unloaded. The column definitions are extracted and a generic SQL statement is automatically constructed to SELECT all columns for unloading, subject only to the restrictions of the
.B -w
option. The user may supply their own SQL statement to select columns for unloading by writing the SQL statement into a file and giving the file name, preceeded by an "\fB_at_\fR" character, in place of the .I tablename
argument.
.PD
.SH NOTE
Any fieldname ending in "COMMENT(S)" is right-trimmed before unloading. Any embedded newlines in the COMMENT(S) field are converted to the meta-char "\fB\\n\fR"
.SH EXAMPLE
Unload all data for cruise number RTMP92-02 from a table called RTMP_OBS_VES_ENTRY. Output will be to the file RTMP9202.ves.
unloader(1)
.SH AUTHOR
Jeff.Stander_at_ml.csiro.au
.br
Date: Tue, 15 Feb 1994 13:16:28 GMT
Message-ID: <CL9ovH.5o8_at_ireq.hydro.qc.ca>
Vladimir Rubashevsky (rubashev_at_hpcc01.corp.hp.com) wrote:
: Hi everybody.
: I am new to this group so excuse me if you already discussed this issue.
: Our project needs to Unload a good amount of data from Oracle database.
: The data should be in a classic flat file format with fixed length ASCII
: records.
: Do you know any good tool(s) to do it. I tried select * with various options,
: but it is very slow. And apparently Oracle does not provide tools for it.
: Their Export/Import facility creates a flat file in a very special format
: which cannot be readable by a non-Oracle application.
: Thanks.
: Vladimir.
One interesting solution is the unloadr script written by Jeff Stander (Jeff.Stander_at_ml.csiro.au). It's an oraperl script that will extract Oracle information into flat files (table structure description, flat file data dump, SQL*Loader format, sql format) and has many functional parameters.
Here it is...
----Cut here---------------------------------------#!/usr/local/bin/oraperl
'di';
'ig00';
############################################################################### # _at_(#) unloadr : dump data from Oracle table to ASCII "merge" file # _at_(#) SunOS deep sun4c (jstander) # _at_(#) loc: /home/jstander/bin # _at_(#) $Revision 1.0 $ (jstander 07.06.93): new # # Name # unloadr
#
$Synopsis=<<X;
unloadr [-dmlsHhnpbqSO] [-Fc] [-D{dfmt}] [-f{fetchlimit}]
[-w{where..}] [-o{outfile}] <table_name|_at_file>..
X
# Description
# dumps Oracle table as ASCII flat file in "merge" format, which has
# each field comma-delimited and enclosed by quotes.
#
$Options=<<X;
-d create .dex file - table structure description
-m create .mrg file - flat file data dump
-l create .ctl file - for Oracle SQLLOAD
-s create .sql file - re-create table in Oracle
-n suppress printing of quote marks around each field
-h help
-H First line of the output file holds field names,
comma-separated
-p send output to \$PRINTCMD, your favorite spooler
("/home/jstander/bin/printw" if not defined)
-b browse mode, send output to $PAGER (default="more")
-q Quiet mode. Don't report actions to STDERR.
-S Dump SQL statement used for merge extraction.
-O output is redirected to stdout
-Fc change field separator character to c.
-x pipe output to xtmsg
-o{outfile} output is redirected to outfile if present
-D{dfmt} set date format (default='DD-MON-YYYY HH:24MI')
-f{fetchlimit} fetch only <fetchlimit> records from the database
-Cz compress output with 'zip'
-Cc compress output with 'compress'
Default is to direct output to <tablename>.mrg
No unloading is done.
-w{whereclause} A SQL expression to restrict SELECTed records from
table. Note only the expression should be passed, not
"WHERE =", see example below.
tablename Name of the table to be unloaded. The column defini-
tions are extracted and a generic SQL statement is
automatically constructed to SELECT all columns for
unloading, subject only to the restrictions of the
-w option. The user may supply their own SQL statement
to select columns for unloading by writing the SQL
statement into a file and giving the file name,
preceeded by an '_at_' character, in place of the
"tablename" argument.
X
# Note # Any field name ending in "COMMENT(S)" is right-trimmed before unloading. # Any embedded newlines in the COMMENT(S) field are converted to the # meta-char "\n" # # Example # Unload all data for cruise number RTMP92-02 from a table called # RTMP_OBS_VES_ENTRY. Output will be to the file RTMP9202.ves. # # unloadr -w "cruise_num='RTMP92-02'" -o RTMP9202.ves rtmp_obs_ves_entry # # Bugs # Need to change login to handle OPS$db.table or db.table for table name # Clusters need to be handled correctly when creating table create .sql file # Clustering ignored for now. # Constraints not extracted when creating dex or sql files. # Author # Jeff.Stander_at_ml.csiro.au # CSIRO Division Of Fisheries # Hobart, Tasmania 7001, Australia ###############################################################################
require "getopts.pl";
require "pwd.pl";
require 'ctime.pl';
sub today {
#------------------------------------------------------------------------------
# today returns the date in one of six formats, depending on the argument
# 1 25 Jul 93 - dd mmm yy
# 2 25 Jul 1993 - dd mmm yyyy
# 3 25/06/93 - European/Australian format
# 4 06/25/93 - U.S.A. format
# 5 13:08 - hh:mm
# 6 13:08:24 - hh:mm:ss
#------------------------------------------------------------------------------
local($format) = _at__ ? @_ : 1;
local($sec, $min, $hour, $mday, $mon, $year, $wday, $YEAR, $tod, $month, $res);
($wday, $month, $mday, $tod, $YEAR) = split(' ',&ctime(time));
($sec, $min, $hour, $mday, $mon, $year, $wday) = localtime(time);
if ( $format == 1 ) { $res=sprintf("%02d %s %02d",$mday,$month,$year) }
if ( $format == 2 ) { $res=sprintf("%02d %s %02d",$mday,$month,$YEAR) }
if ( $format == 3 ) { $res=sprintf("%02d/%02d/%02d",$mday,$mon,$year) }
if ( $format == 4 ) { $res=sprintf("%02d/%02d/%02d",$mon,$mday,$year) }
if ( $format == 5 ) { $res=sprintf("%02d:%02d",$hour,$min) }
if ( $format == 6 ) { $res=$tod }
$res;
}
#-----------------------------------------------------------------------------
# Print line withoffset, split at word boundary
# Synopsis: printdex(offset,length,dex)
# Arguments:
# offset - offset from left margin in character spaces
# length - total length of line including offset in character spaces
# dex - line to print
#------------------------------------------------------------------------------
sub printdex {
local($offset,$width,$dex) = _at__;
$width = 80 if !$width;
$offset = 0 if !$offset;
local(_at_words) = split (/\s/,$dex);
local($out,$leader,$result);
$leader = sprintf("%${offset}s","");
foreach $word (_at_words) {
if (length($out) + length($word) > $width-$offset) {
$result .= $leader . $out . "\n";
$out = "";
}
$out .= "$word ";
}
$result .= $leader . $out . "\n" if $out;
$result;
}
#-----------------------------------------------------------------------------# Extract first line of output from oracle given SQL query
#------------------------------------------------------------------------------
sub sqlx {
local($sqlstmt,$passwd,$db) = _at__;
local($sqlx_lda,$nologon);
($user=$passwd)=~s/\/.*$//;
$passwd=~s/^.*\///;
$nologon = sprintf("%s",$user) =~ /^0x/ || 0;
if ( $nologon ) {
$sqlx_lda=$user;
}
else {
$sqlx_lda = &ora_login($db,$user,$passwd) || die $ora_errstr;
}
$csr = &ora_open($sqlx_lda,$sqlstmt) || die $ora_errstr;
_at_result = &ora_fetch($csr);
&ora_close($csr);
&ora_logoff($sqlx_lda) if !$nologon;
_at_result;
}
$TABLE=FOP; sub openf {
#------------------------------------------------------------------------------ # Subroutine to open a file in "r" or "w" mode and return error # message and status if unable to open file.# Synopsis: openf(<r|w|filter>,filehandle,name);
#------------------------------------------------------------------------------
local($type,$handle,$name,$filter) = _at__; if ($type eq "w") {
open($handle,"> $name");
stat($name);
print STDERR "$NAME: file not writeable: $name\n" if !-w_;
-w_;
}
elsif ($type eq "r") {
stat($name);
print STDERR "$NAME: file not found: $name\n" if !-e_;
-e_ && print STDERR "$NAME: file not readable: $name\n" if !-r_;
-r_ && open($handle,"< $name");
-r_;
}
elsif ($type eq "p") {
open($handle,"| $filter $name");
}
else {
die "$NAME: sub openf arg 1 must be r, w, or p.'\n";
1;
}
}
($NAME=$0) =~ s/^.*\///;
$Pager=$ENV{'PAGER'};
if ( length($Pager) == 0 ) { $Pager="more"; }
$Printcmd=$ENV{'PRINTCMD'};
if ( length($Printcmd) == 0 ) { $Printcmd="/home/jstander/bin/printlp"; }
#------------------------------------------------------------------------------# Process command line arguments
#------------------------------------------------------------------------------&Getopts('LUuzdmlshHnpbqxSOF:D:f:o:w:C:') || exit ;
if ( $opt_z || $#ARGV < 0 ) { # if no arguments, send usage
$Synopsis =~ s/^[ \t]*//;
print STDERR "Usage:\t$Synopsis";
print STDERR $Options if $opt_h;
exit;
}
open(UNAME,"uname -snm |");
$uname = <UNAME>;
chop($uname);
close(UNAME);
$HOME = $ENV{'HOME'} ? $ENV{'HOME'} : (getpwuid($<))[7];
$USERNAME = $ENV{'USERNAME'} ? $ENV{'USERNAME'} : (getpwuid($<))[6];
$USER = $ENV{'USER'} ? $ENV{'USER'} : (getpwuid($<))[1];
$USERNAME_p="($USERNAME)";
if ( $opt_b ) { $ENV{'COLUMNS'}="132"; $out="| $Pager"; }
$header = $opt_H; $xbox = $opt_x; $printout = $opt_p; $redirect = $opt_O; $dumpsql = $opt_S; $dfmt = $opt_D || "DD-MON-YYYY HH24:MI"; $fetchlimit = $opt_f; $where = $opt_w; $quiet = $opt_q; $opt_u = 1 if $opt_U; $opt_u = 1 if $opt_L; $sep = $opt_F ? $opt_F : ","; $quote = $opt_n ? "" : "\"";
if ( $opt_u ) {
$opt_d = 1; $redirect = 1; $quiet = 1;
}
if ( !$opt_d && !$opt_m && !$opt_l && !$opt_s ) {
$opt_d=1; $opt_m=1; $opt_l=1; $opt_s=1;
}
#------------------------------------------------------------------------------# Loop over command arguments: they are either tables or SQL statement files
#------------------------------------------------------------------------------while (!($#ARGV<0)) {
($table=$ARGV[0]) =~ tr/A-Z/a-z/;
$table =~ s/^.*\.//; # this strips the tablespace name off, should be changed
($TABLE=$table) =~ tr/a-z/A-Z/;
shift;
if ( ($readsql=$table =~ s/^_at_//) ) {
$file=$table;
&openf("r",FILEIN,$file) || exit;
}
if ( $redirect ) {
$out="";
$outname="";
$opt_o=0;
}
else {
$outname=$opt_o || $table;
}
$lda = &ora_login("","intg","gtni") || die $ora_errstr;
if ( !$dumpsql && !$quiet && !$readsql ) {
print STDERR "\n$NAME: extracting data from table: $table\n"; }
if ( $readsql ) {
#--------------------------------------------------------------------------- # This processes a user-provide SQL statement and extracts column names found # between the SELECT .. FROM keywords. # The SQL statement is preserved intact in the array _at_qh. # Note: This assumes the user provides a fairly vanilla SQL statement for # an unload operation, i.e. no column aliases and subquerys, but data# conversion (e.g. to_char(), decode(), etc., are ok.)
#---------------------------------------------------------------------------
$qh="";
while (<FILEIN>) {
# strip out comments
$_ =~ s/;$//;
$qh .= $_;
$_ =~ tr/a-z/A-Z/;
$_ =~ s/^[ \t]*//;
$_ =~ s/[ \t]*$//;
$_ =~ s/--.*$//;
if ( $ok && $_ =~ /^FROM[ \t]*/ ) { $ok=0; $from++; s/FROM[ \t]*// }
if ( $_ =~ s/^SELECT// ) { $ok=1; }
if ( $from == 1 ) {
$from++;
if ( $_ =~ /^[a-zA-Z]/ ) {
$_ =~ s/[ \t\n]//g;
$TABLE = $_;
$_ =~ tr/A-Z/a-z/;
$table = $_ ;
$outname = $table if !$outname;
$from=0;
next;
}
}
$ok || next;
# This mess extracts the column name, The "^" char. is a placeholder.
$_ =~ s/\^//g;
$_ =~ s/[ \t]*[^(,]*\(/^/g;
$_ =~ s/,[^^]*\)//g;
$_ =~ s/\),/,/g;
$_ =~ s/\^//g;
$_ =~ s/[ \t\n]//g;
_at__ = split(/,/,$_);
for ( $i=0; $i<=$#_; $i++ ) {
$column[$ncols++] = _at__[$i];
}
}
if ( $opt_l ) {
$in_expr="AND column_name IN (";
for ( $i=0; $i<=$#column; $i++ ) {
$col = _at_column[$i];
$in_expr .= "\n\t\t'$col',";
}
}
$in_expr =~ s/.$/\n\t)/;
print STDERR "\n$NAME: extracting data from table: $table\n"
if !$quiet && !$dumpsql;
}
else {
#------------------------------------------------------------------------------# Extract column names from the table
#------------------------------------------------------------------------------
$qh=<<EOI;
SELECT column_name,
data_type
FROM user_tab_columns
WHERE table_name='$TABLE'
ORDER BY column_id
EOI $csr = &ora_open($lda,$qh) || die $ora_errstr;
$ncols=0;
while (($column[$ncols],$datatype[$ncols]) = &ora_fetch($csr)) {
$ncols++;
}
if ( !$ncols ) {
!$quiet && print STDERR "$NAME: Table not found: $table\n" ;
exit;
}
&ora_close($csr);
#------------------------------------------------------------------------------
# Using column names for given table, assemble the SQL statement to extract
# the columns
#------------------------------------------------------------------------------
$qh="SELECT";
for ( $i=0; $i<$ncols; $i++ ) {
$fld = $column[$i];
$dt = $datatype[$i];
$qh .= "\t";
if ($fld =~ 'COMMENT[S]*') {
$qh .= "rtrim($fld)";
}
elsif (!($dt =~ 'DATE')) {
$qh .= $fld
}
else {
$qh .= "to_char( $fld,";
if (length($dfmt) > 0) {
$qh .= "$item '$dfmt'"
}
$qh .= $item . " )";
}
if ( $i<$ncols-1 ) {
$qh .= ",\n";
}
}
$qh .= "\nFROM\t$TABLE";
if ( $where ) { $qh .= "\nWHERE $where"; }
}
#------------------------------------------------------------------------------# if -S option, print SQL stmt and exit
#------------------------------------------------------------------------------
if ( $dumpsql ) {
print STDOUT "$qh\n";
exit;
}
if ( !$dumpsql ) {
if ( $opt_C ) {
if ( $opt_C eq "z" ) {
if ( $quiet ) {
$v = "-qq";
}
else {
print STDERR "$NAME: output will be compressed using zip." .
" Restore with -p pipe option.\n";
}
$filter = "zip $v $outname.zip -";
}
else {
if ( !$quiet ) {
$v = "-v";
print STDERR "$NAME: output will be " .
"compressed using compress\n";
}
$filter = "compress $v";
}
if ( !$quiet ) {
}
}
}
#------------------------------------------------------------------------------# get storage data for this table
#------------------------------------------------------------------------------
if ( $opt_d || $opt_s ) {
($tablespace_name,$cluster_name,$initial_extent,$next_extent,$pct_increase) =
&sqlx(<<EOI,"","");
SELECT tablespace_name,
cluster_name,
initial_extent,
next_extent,
pct_increase
FROM user_tables
WHERE table_name='$TABLE'
EOI
}
#------------------------------------------------------------------------------# Get file handle names
#------------------------------------------------------------------------------
if ( $redirect ) {
$out_mrg = "";
$out_dex = "";
$out_ctl = "";
$out_sql = "";
open(OUT_MRG,"| cat") if $opt_m ;
open(OUT_DEX,"| cat") if $opt_d ;
open(OUT_SQL,"| cat") if $opt_s ;
open(OUT_CTL,"| cat") if $opt_l ;
}
elsif ( $outname ) {
$out_mrg = "$outname.mrg";
$out_dex = "$outname.dex";
$out_ctl = "$outname.ctl";
$out_sql = "$outname.sql";
$outtype=".";
}
if ( $xbox ) {
$out_mrg="| xbox -h TABLENAME: $TABLE";
$out_dex="| xbox -h TABLENAME: $TABLE";
$out_ctl="| xbox -h TABLENAME: $TABLE";
$out_sql="| xbox -h TABLENAME: $TABLE";
open(OUT_MRG,$out_mrg) if $opt_m ;
open(OUT_DEX,$out_dex) if $opt_d ;
open(OUT_SQL,$out_sql) if $opt_s ;
open(OUT_CTL,$out_ctl) if $opt_l ;
$outtype="to xbox";
$quiet=1;
}
elsif ( $printout ) {
$out_mrg="| $Printcmd -q -s -a -h $table.mrg";
$out_dex="| $Printcmd -q -s -a -h $table.dex";
$out_ctl="| $Printcmd -q -s -a -h $table.ctl";
$out_sql="| $Printcmd -q -s -a -h $table.sql";
open(OUT_MRG,$out_mrg) if $opt_m ;
open(OUT_DEX,$out_dex) if $opt_d ;
open(OUT_SQL,$out_sql) if $opt_s ;
open(OUT_CTL,$out_ctl) if $opt_l ;
$outtype="to printer.";
}
elsif ( !$redirect ) {
&openf("w",OUT_MRG,$out_mrg) || exit if $opt_m ;
&openf("w",OUT_DEX,$out_dex) || exit if $opt_d ;
&openf("w",OUT_SQL,$out_sql) || exit if $opt_s ;
&openf("w",OUT_CTL,$out_ctl) || exit if $opt_l ;
}
#------------------------------------------------------------------------------ # Process query using SQL statement and retrieve each record # Record is processed field-by-field into single output string with each # field comma separated and delimited by double-quotes.# Fetchlimit (-f#) allows you to dump a few records to see how its going.
#------------------------------------------------------------------------------
if ( $opt_m ) {
$csr = &ora_open($lda,$qh) || die $ora_errstr;
$cnt=0;
$maxline=0;
if ( $header ) {
for ( $i=0; $i<$ncols; $i++ ) {
print OUT_MRG "$column[$i]";
if ( $i<$ncols-1 ) { print OUT_MRG "," }
else { print OUT_MRG "\n"; }
}
}
while ( !$done && (_at_SELECT = &ora_fetch($csr)) ) {
if ( !$fetchlimit || $cnt < $fetchlimit ) {
$out=$quote;
for ( $i=0; $i<=$#SELECT; $i++ ) {
$SELECT[$i] =~ s/[\n]/\\n/g;
$SELECT[$i] =~ s/["]/'/g;
$out .= "$SELECT[$i]$quote";
if ( $i < $#SELECT ) { $out .= $sep . $quote; }
}
print OUT_MRG "$out\n";
if ( $maxline < ($l=length($out)) ) { $maxline = $l }
$cnt++;
}
else { $done=1 }
}
if ( !$quiet ) {
print STDERR "$NAME: extracted $cnt records with $ncols fields each\n";
print STDERR "$NAME: maximum line length is $maxline\n";
}
&ora_close($csr);
close(OUT_MRG);
$outname && !$quiet && print STDERR "$NAME: $outname.mrg written$outtype\n";
}
#------------------------------------------------------------------------------# Create the *.ctl file to pass to oracle loader sqlload
#------------------------------------------------------------------------------if ( $opt_l ) {
#-----------------------------------------------------------------------
# Get user name from database
#-----------------------------------------------------------------------
$qh=<<EOI;
SELECT initcap(username)
FROM user_users
EOI
$csr = &ora_open($lda,$qh) || die $ora_errstr;
_at_username = &ora_fetch($csr);
&ora_close($csr);
#-----------------------------------------------------------------------
# Get table comments from database
#-----------------------------------------------------------------------
$qh=<<EOI;
SELECT initcap(table_name),
comments
FROM user_tab_comments
WHERE table_name='$TABLE'
AND table_type='TABLE'
EOI
$csr = &ora_open($lda,$qh) || die $ora_errstr;
_at_tablecomment = &ora_fetch($csr);
&ora_close($csr);
$maxline=0;
$cnt=0;
$date=&today(1);
#-----------------------------------------------------------------------
# Print ctl file header
#-----------------------------------------------------------------------
print OUT_CTL <<EOI;
-----------------------------------------------------------------------------
-- $table.ctl : use with $table.sql and $table.mrg
-- $uname ($ENV{'USER'}) $USERNAME
-- loc: $ENV{'PWD'}/$outname.ctl
-- \$Revision 1.0 $ ($USER) $date: new
LOAD DATA
INFILE "$table.mrg"
INSERT
INTO TABLE $TABLE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
EOI
#-----------------------------------------------------------------------
# Get column details from database
#-----------------------------------------------------------------------
$qh=<<EOI;
SELECT initcap(column_name),
initcap(data_type)
FROM user_tab_columns
WHERE table_name='$TABLE'
$in_expr
ORDER BY column_id
EOI $csr = &ora_open($lda,$qh) || die $ora_errstr;
$cnt=0;
while (( $column_name, $data_type) = &ora_fetch($csr) ) {
if ( $cnt++ > 0 ) { print OUT_CTL ",\n"; }
if ( $data_type eq "DATE" ) {
print OUT_CTL " $column_name DATE '$dfmt'";
}
else {
print OUT_CTL " $column_name";
}
}
&ora_close($csr);
print OUT_CTL "\n)\n";
close(OUT_CTL);
$outname && !$quiet && print STDERR "$NAME: $outname.ctl written$outtype\n";
}
#------------------------------------------------------------------------------# Create the .sql file to (re)create the table
#------------------------------------------------------------------------------if ( $opt_s ) {
$outname=$table if !$outname;
print OUT_SQL <<EOI;
/*****************************************************************************
* $outname.sql : use with $outname.ctl and $outname.mrg
* By: $ENV{'USER'} $USERNAME_p
* loc: $ENV{'PWD'}/$outname.sql
- \$Revision 1.0 $ ($ENV{'USER'} $date): new *****************************************************************************/ CREATE TABLE $table ( EOI
SELECT initcap(column_name),
initcap(data_type),
data_length ,
data_precision,
data_scale,
nullable
FROM user_tab_columns
WHERE table_name='$TABLE'
ORDER BY column_id
EOS
$csr = &ora_open($lda,$qh) || die $ora_errstr;
while ((
$column_name,
$data_type,
$data_length ,
$data_precision,
$data_scale,
$nullable) = &ora_fetch($csr) ) {
$null="";
$dec="";
$prec="";
$null=" NOT NULL" if $nullable eq "N";
if ( $data_type eq "NUMBER" ) {
$prec="($data_precision";
if ( $data_scale !~ /0/ ) {
$prec .= ",$data_scale";
}
$prec .= ")";
}
elsif ( $data_type eq "DATE" ) {
$prec="";
}
else {
$prec = "($data_length)";
}
$outsql .= sprintf(" %s %s%s%s,\n",
$column_name,$data_type,$prec,$null);
}
$Tablespace = " TABLESPACE $tablespace_name\n";
$Storage = " STORAGE INIT $initial_extent NEXT $next_extent PCTINCREASE $pct_increase\n";
$outsql =~ s/,$/\n$Tablespace$Storage);/;
print OUT_SQL $outsql;
&ora_close($csr);
$qh=<<EOS;
SELECT 'COMMENT ON TABLE '
|| initcap(TABLE_NAME)
|| ' IS '''
|| comments
|| ''';'
FROM user_tab_comments
WHERE table_name='$TABLE'
AND table_type='TABLE';
EOS $qh=<<EOS;
SELECT 'COLUMN '
|| table_name || '.'
|| initcap(column_name),
comments
FROM user_col_comments
WHERE table_name='$TABLE'
UNION
SELECT 'TABLE ' || initcap(table_name),
comments
FROM user_tab_comments
WHERE table_name='$TABLE'
EOS
$csr = &ora_open($lda,$qh) || die $ora_errstr;
while ( ($colname,$comment) = &ora_fetch($csr) ) {
print OUT_SQL
"COMMENT ON $colname IS\n '$comment';\n"
if $comment;
}
&ora_close($csr);
close(OUT_SQL);
$outname && !$quiet && print STDERR "$NAME: $outname.sql written$outtype\n";
}
#------------------------------------------------------------------------------# Create summary table description
#------------------------------------------------------------------------------if ( $opt_u ) {
!opt_L && print OUT_DEX <<EOI;
Table_name: $table
Seq# Name Null? Type ---- ----------------------------------- -------- --------EOI
#-----------------------------------------------------------------------
# Get column details
#-----------------------------------------------------------------------
$qh=<<EOI;
SELECT initcap(col.column_name),
initcap(data_type),
data_length,
data_precision,
data_scale,
nullable,
comments
FROM user_tab_columns col,
user_col_comments com
WHERE col.table_name='$TABLE'
AND com.table_name='$TABLE'
AND col.column_name=com.column_name
ORDER BY column_id
EOI $csr = &ora_open($lda,$qh) || die $ora_errstr;
while ((
$column_name,
$data_type,
$data_length ,
$data_precision,
$data_scale,
$nullable,
$comments) = &ora_fetch($csr) ) {
$nullable = ($nullable eq "Y") ? "" : "NOT NULL";
$dec="";
$dtype=$data_type;
if ( $data_type eq "Number" ) {
if ( $data_scale ne "0" ) {
$dec = $data_scale;
}
$len = $data_precision;
}
elsif ( $data_type eq "Date" ) {
$len="";
}
else {
$len = $data_length;
}
$dtype = $data_type . "(" . $len if $len;
$dtype .= "," . $dec if $dec;
$dtype .= ")" if $len;
if ( $opt_L ) {
printf OUT_DEX ("%s %s\n",$column_name,$dtype);
}
else {
$column_name =~ tr/a-z/A-Z/;
$dtype =~ tr/a-z/A-Z/;
printf OUT_DEX ("%5d %-35s %8s %s\n",
++$cnt,$column_name,$nullable,$dtype);
}
print OUT_DEX &printdex( 6,60,$comments ) if $opt_U && length($comments);
$len=0;
$dec=0;
}
&ora_close($csr);
close(OUT_DEX);
}
elsif ( $opt_d ) {
#------------------------------------------------------------------------------# Create the .dex file
#------------------------------------------------------------------------------
#-----------------------------------------------------------------------
# Get user name from database
#-----------------------------------------------------------------------
$qh=<<EOI;
SELECT initcap(username)
FROM user_users
EOI
$csr = &ora_open($lda,$qh) || die $ora_errstr;
_at_orauser = &ora_fetch($csr);
&ora_close($csr);
#-----------------------------------------------------------------------
# Get table comments from database
#-----------------------------------------------------------------------
$qh=<<EOI;
SELECT initcap(table_name),
comments
FROM user_tab_comments
WHERE table_name='$TABLE'
AND table_type='TABLE'
EOI
$csr = &ora_open($lda,$qh) || die $ora_errstr;
_at_tablecomment = &ora_fetch($csr);
&ora_close($csr);
$maxline=0;
$cnt=0;
$date=&today(1) . " _at_ " . &today(5);
#-----------------------------------------------------------------------
# Print dex file header
#-----------------------------------------------------------------------
$Tablespace = "Tablespace: $tablespace_name\n" if $tablespace_name;
$Cluster = "Cluster: $cluster_name\n" if $cluster_name;
$Storage = "Storage: INIT $initial_extent" if $initial_extent;
$Storage .= " NEXT $next_extent" if $next_extent;
$Storage .= " PCTINCREASE $pct_increase" if $pct_increase;
$Storage .= "\n" if $Storage;
print OUT_DEX <<EOI;
-----------------------------------------------------------------------------
Table name: $tablecomment[0]
Description: $tablecomment[1]
Oracle User ID: $orauser[0]
Database:
Author: $USERNAME
Revision:
Unloaded: $date
By user: $ENV{'USER'}
Location: $ENV{'PWD'}/$outname.dex
$Tablespace$Cluster${Storage}Note: Use 'mkloadf' to generate CREATE TABLE SQL script from this file
(c) Copyright 1993, CSIRO Div. of Fisheries, Hobart Tasmania, Australia
Fld Field Type Len Dec Nul Comment No Name Ok ---- --------------------- -------- -------- --- --- ----------------------EOI
#-----------------------------------------------------------------------
# Get column details and comments from database
#-----------------------------------------------------------------------
$qh=<<EOI;
SELECT initcap(col.column_name),
initcap(data_type),
data_length,
data_precision,
data_scale,
nullable,
comments
FROM user_tab_columns col,
user_col_comments com
WHERE col.table_name='$TABLE'
AND com.table_name='$TABLE'
AND col.column_name=com.column_name
ORDER BY column_id
EOI $csr = &ora_open($lda,$qh) || die $ora_errstr;
while ((
$column_name,
$data_type,
$data_length ,
$data_precision,
$data_scale,
$nullable,
$comment) = &ora_fetch($csr) ) {
$dec="";
if ( $data_type eq "Number" ) {
if ( $data_scale ne "0" ) {
$dec = $data_scale;
}
$len = $data_precision;
}
elsif ( $data_type eq "Date" ) {
$len="";
$comment .= " FMT=\"" . $dfmt . "\"";
}
else {
$len = $data_length;
}
printf OUT_DEX ("% 4d %-20s %-6s%11s%4s %s # %s\n",
++$cnt,$column_name,$data_type,$len,$dec,$nullable,$comment);
}
&ora_close($csr);
close(OUT_DEX);
$outname && !$quiet && print STDERR "$NAME: $outname.dex written$outtype\n";
}
}
close(STDOUT);
&ora_logoff($lda);
################################################################ END OF PERL SCRIPT
###############################################################
# These next few lines are legal in both Perl and nroff.
.00; # finish .ig 'di \" finish diversion--previous line must be blank .nr nl 0-1 \" fake up transition to first page again .nr % 0 \" start at page 1
'; __END__ ##### From here on it's a standard manual page #####
.PU .TH UNLOADR 1 "04 August 1993" "CSIRO Fisheries" .SH NAME
unloadr - extract data from Oracle table in "merge" format .SH SYNOPSIS
.B unloadr
[
.B -dmlsHhpbqSO]
] [
.B -C[cz]
] [
.BI -Fc
] [
.BI -o\fR[ outfile\fR]
] [
.BI -f fetchlimit
] [
.BI -D dfmt
] [
.BI -w where..
]
.RI < table_name | \fB_at_\fRfile >.. .ll +8 .ad .SH DESCRIPTION .I Unloadr
dumps an Oracle table as an ASCII flat file in "merge" format: each field is comma-delimited and enclosed by quotes. If none of the
.B -dmls
options are set, assume all of these options are set.
.SH OPTIONS .TP .B -d
Create a file describing the structure of .IR table_name,
including any stored table and column comments. Output is to
.IB table_name .dex .PD .TP .B -m
Create ASCII flat file containing all columns in .IB table_name.
Output is to
.IB table_name .mrg . .PD .TP .B -l
Create an Oracle loader control file to reload the merge file.
Output is to
.IB table_name .ctl . .PD .TP .BI -s
Create a file containing
.B SQL
statements to (re)create the table for the extracted data. Output is to
.IB table_name .sql . .PD .TP .B -n
Suppress enclosure of each field by quotation marks. (Normally
.I unloadr
will surround each field in the merge file with double quotes).
.PD .TP .B -H
First line of the output file holds field names, comma-separated.
.PD .TP .B -h
Print a brief help message.
.PD .TP .B -p
Send output to $PRINTCMD, your favorite spooler ("/home/jstander/bin/printw" if not defined).
.PD .TP .B -b
Browse mode, send output to $PAGER (default="more").
.PD .TP .BI -F c
Set column separator in merge file to
.IR c .
Default separator is a comma.
.PD .TP .BI -D dfmt
set date format (default=DD-MON-YYYY HH:24MI).
.PD .TP .BI -f fetchlimit
fetch only <fetchlimit> records from the database.
.PD .TP .BI -o outfile
output is redirected to outfile if present.
.PD .TP .B -O
Output is redirected to stdout
Default is to direct output to <tablename>.mrg.
.PD .TP .B -q
Quiet mode. Don't report actions to STDERR.
.PD .TP .B -S
Dump SQL statement used for merge extraction. No unloading is done.
.PD .TP .B -Cc
Compress output files using
.I compress
utility (if present).
.PD .TP .B -Cz
Compress output files using
.I zip
utility (if present).
.PD .TP .BI -w whereclause
A SQL expression to restrict SELECTed records from table. Note only the expression should be passed, not "WHERE =", see example below.
.PD .TP .I tablename
Name of the table to be unloaded. The column definitions are extracted and a generic SQL statement is automatically constructed to SELECT all columns for unloading, subject only to the restrictions of the
.B -w
option. The user may supply their own SQL statement to select columns for unloading by writing the SQL statement into a file and giving the file name, preceeded by an "\fB_at_\fR" character, in place of the .I tablename
argument.
.PD
.SH NOTE
Any fieldname ending in "COMMENT(S)" is right-trimmed before unloading. Any embedded newlines in the COMMENT(S) field are converted to the meta-char "\fB\\n\fR"
.SH EXAMPLE
Unload all data for cruise number RTMP92-02 from a table called RTMP_OBS_VES_ENTRY. Output will be to the file RTMP9202.ves.
.sp 1 .in+2 .na .B unloadr -w "cruise_num='RTMP92-02'" -o RTMP9202.ves rtmp_obs_ves_entry .in-2 .SH SEE ALSO
unloader(1)
.SH AUTHOR
Jeff.Stander_at_ml.csiro.au
.br
----Cut here---------------------------------------
Stephane Hamel, Sysadmin/DBA *.mais.hydro.qc.ca
Hydro-Quebec TDSB, Montreal, Quebec
E-mail: shamel_at_mais.hydro.qc.ca Voice: (514) 858-7704
Received on Tue Feb 15 1994 - 14:16:28 CET