Datapipe has provided award winning service for over a decade.  The experts behind that effort are some of the most knowledgeable in the world.  This blog is intended as an open forum for our experts to express their thoughts and ideas regarding the managed IT services industry.  We welcome comments and questions and hope you find the entries helpful, informative, and thought provoking. 

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