ora2txt

MC, 5 August 2004: ora2txt is a simple script to query an Oracle database, using Oratcl and retrieve the results of a select statement as tab-delimited text. (This is one of the Oratcl examples.)

 #!/bin/sh
 # -*- tcl -*- \
 exec tclsh $0 ${1+"$@"}

 #
 # ora2txt: Output the results of a query in a textual format
 #

 package require Oratcl 4.0

 # If you want to hardcode a username/password combo, do so here:
 set username ""
 set password ""

 proc usage {} {
     global argv0
     puts stderr "Usage: $argv0 ?-v | -vv | -vvv? ?user/pass? QUERY"
     exit 1
 }

 proc output {text} {
     if {[catch {puts $text}]} then exit
 }


 proc main {} {
     global argv
     # Defaults
     global username password
     set verbosity 0

     set SQL  [lindex $argv end]
     set argv [lrange $argv 0 end-1]

     if {[llength $argv] > 0} {
         if {[regexp -- {^--?(v{1,3})(?:erbose)?$} [lindex $argv 0] => v]} {
             set verbosity [string length $v]
             set argv [lrange $argv 1 end]
         }

         if {[llength $argv] > 0} {
             foreach {username password} [split [lindex $argv 0] /] break
             set argv [lrange $argv 1 end]
         }
         if {[llength $argv]} then usage
     }

     if {[string length $SQL] == 0 ||
         [string equal $SQL "-"]   ||
         [string equal $SQL "--"]} {
         set SQL [read stdin]
     }

     set SQL [string trim $SQL]
     set SQL [string trimright $SQL ";"]

     set RE {^(?:\s*--[^\n]*\n)*\s*(select.+)?$}
     if {[regexp -nocase -- $RE $SQL => SQL] == 0} then usage

     set lh [oralogon $username/$password]
     set sh [oraopen $lh]
     oraconfig $sh nullvalue ""
     orasql $sh $SQL

     set cols [oracols $sh]
     if {$verbosity >= 2} {
         set len 0
         foreach col $cols {
             if {[string length $col] > $len} {
                 set len [string length $col]
             }
         }

         set counter 0
         set div [string repeat - 80]

         while {[orafetch $sh -datavariable data] == 0} {
             if {$counter} {
                 if {$verbosity == 3} {
                     set tag [format "\[^^%d / %dvv\]" \
                         [expr {$counter + 1}] \
                         [expr {$counter + 2}]]

                     set half [expr {(80 - [string length $tag]) / 2}]
                     set div  [string repeat - $half]
                     append div $tag
                     append div [string repeat - $half]

                     if {[string length $div] == 79} {
                         append div -
                     }
                 }

                 output \n$div\n
             }

             foreach col $cols datum $data {
                 output [format "%-${len}s %s" ${col}: $datum]
             }

             incr counter
         }
     } else {
         if {$verbosity == 1} {
             output [join $cols \t]
         }

         while {[orafetch $sh -datavariable data] == 0} {
             set row [list]
             foreach datum $data {
                 lappend row [string map [list \t " "] $datum]
             }
             output [join $row \t]
         }
     }
 }

 if {[catch main problem]} {
     puts stderr $problem
     exit 1
 }

LV Okay, so today I tried this out, in desperation because sql/plus was being such a pain to use. Here's how I ended up using it. I copied the entire page to a file. I removed all the text stuff at the top and bottom. I made certain that I fixed the #! line (so there was no leading space). I made certain that the tclsh in my PATH knew about oratcl.

I then tried running it. I didn't have much luck figuring out, initially, what I needed to do. Finally, this is what I did:

$ ./ora2txt -vvv - > ./outputora.txt
select * from mytable;
^D
$

and when I looked in outputora.txt, what I found was lines like this:

--------------------------------[^^217 / 218vv]---------------------------------

ID:         241
SYSID:      A123B
VERSION:    8.4.04.0
VERTYPE:    N
WIN9X:      0
WINNT:      1
WINXP:      1
MAC:        0
SOLARIS:    0
LINUX:      0
MAINFRAME:  0
POCKETPC:   0
VERSIONURL:
PREFIXHTML:
SUFFIXHTML:
VERSIONNOTE:
STOCKED:    N
SCRIPTED:   A
MACOS9:     0
WIN2KSERVER:
W2KSERVER:  0
WTS:        0

--------------------------------[^^218 / 219vv]---------------------------------

which was much better than sqlplus's output... Thank you!