#!/usr/local/bin/oraperl 
'di';
'ig00';
###############################################################################
# @(#) unloadr : dump data from Oracle table to ASCII "merge" file
# @(#) SunOS deep sun4c (jstander)  
# @(#) loc: /home/jstander/bin
# @(#) $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|@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 '@' 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@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) = @_ ? @_ : 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) = @_;
        $width  = 80 if !$width;
        $offset =  0 if !$offset;
        local(@words) = split (/\s/,$dex);
        local($out,$leader,$result);
        $leader = sprintf("%${offset}s","");
        foreach $word (@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) = @_;
        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;
        @result = &ora_fetch($csr);
        &ora_close($csr);
        &ora_logoff($sqlx_lda) if !$nologon; 
        @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) = @_;
   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/^@//) ) { 
      $file=$table;
      &openf("r",FILEIN,$file) || exit;
   }

   if ( $redirect ) { 
      $out=""; 
      $outname=""; 
      $opt_o=0; 
   }
   else { 
      $outname=$opt_o || $table;
   }

   $lda = &ora_login("","","") || 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 @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;
         @_ = split(/,/,$_);

         for ( $i=0; $i<=$#_; $i++ ) { 
            $column[$ncols++] = @_[$i];
         }
      }

      if ( $opt_l ) {
         $in_expr="AND column_name IN (";
         for ( $i=0; $i<=$#column; $i++ ) { 
            $col = @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 && (@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;
      @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;
      @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

   $qh=<<EOS;
   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;
      @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;
      @tablecomment = &ora_fetch($csr);
      &ora_close($csr);

      $maxline=0;
      $cnt=0;
      $date=&today(1) . " @ " . &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@\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@\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@ml.csiro.au
.br