#!/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!
