by Administrator
16. December 2009 21:27
You get a request to export a bunch of tables into a spreadsheet file. Sure there are ODBC connectors available for spreadsheets, but sometimes you don't want to have to figure all that out. This is where the power and simplicty of scripting languages can really shine, especially those that treat data as lists. Sure the following code block seems cryptic to a non-programmer, but it is really not that hard.
To solve this problem in less than 5 minutes I put together this piece of TCL code. It requires Tcl, and 2 of the support packages that are available for it: tcllib and Oratcl
This little script will take a list of tables and generate a [tablename].csv file for each one. The data in the csv file will be comma separated, and quoted. The first line of the output file will contain the column names.
#
# table_to_csv.tcl
#
#!/usr/local/tcl86/bin/tclsh8.6
package require Oratcl
package require csv
set lda [oralogon scott/tiger@database] set stm
[oraopen $lda] set tblList [list TABLE1 TABLE2 TABLE3]
proc csvjoin {values {sepChar ,} {delChar \"}} {
set out ""
set sep {}
foreach val $values {
append out $sep${delChar}[string map [list $delChar ${delChar}${delChar}] $val]${delChar}
set sep $sepChar
}
return $out
}
foreach tbl $tblList {
puts $tbl
set t [open ${tbl}.csv w+]
oraparse $stm "select * from ${tbl}"
puts $t [csvjoin [oracols $stm name]]
oraexec $stm
while {[orafetch $stm -datavariable row] == 0} {
puts $t [csvjoin $row]
}
close $t
}
Now sure, you could do this with perl or python or even 'C'. But then again, you would not be using my favorite scripting language.
Todd M. Helfter
Senior Oracle Database Administrator
