Oracleのテーブル定義をExcelシートに出力する

(table_info2xls.pl)

#!/usr/bin/env perl

use DBI;
use Spreadsheet::WriteExcel;

$ENV{ORACLE_HOME}='/u01/app/oracle/product/11.2.0/xe';
$ENV{ORACLE_SID}='XE';
$ENV{NLS_LANG}='JAPANESE_JAPAN.AL32UTF8';
$ENV{NLS_DATE_FORMAT}='yyyy/mm/dd hh24:mi:ss';
$user = 'hr';
$password = '******';

$workbook = Spreadsheet::WriteExcel->new('table_info.xls');

$dbh = DBI->connect("dbi:Oracle:$ENV{ORACLE_SID}",$user,$password);

while(<>){
  chomp();
  
$sql = <<EOS;
select user_tab.column_name,
       user_tab.data_type,
       user_tab.data_length,
       user_col.comments
from user_tab_cols user_tab, user_col_comments user_col
where user_tab.table_name = '$_' and
      user_tab.table_name = user_col.table_name and
      user_tab.column_name = user_col.column_name
EOS

  $sth = $dbh->prepare($sql);
  $sth->execute();

  $worksheet = $workbook->add_worksheet($_);
  $format = $workbook->add_format();

  $i = 0;
  while(@row=$sth->fetchrow_array()){
    $j = 0;
    foreach(@row){
      $worksheet->write_string($i, $j, $_, $format);
      #print $worksheet->get_name,",$i,$j,$_\n";
      $j++;
    }
    $i++;
  }

  $sth->finish();
  
}

$dbh->disconnect();

$workbook->close();

使ってみる

$ ./show_user_tables.pl HR
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
$ ./show_user_tables.pl HR | ./table_info2xls.pl

どうだろう？
table_info.xls

<2012.10.13 追記>
以下の部分を書き換えると、先頭行にカラム名を追加できます。

37c37,42
<   $i = 0;
---
>   #write column name
>   for($j=0; $j<$sth->{NUM_OF_FIELDS}; $j++){
>     $worksheet->write_string(0, $j, $sth->{NAME}->[$j], $format);
>   }
> 
>   $i = 1;

参照したURL:
http://www.ksknet.net/dbi/dbi_1.html

oracle テーブル定義取得
http://d.hatena.ne.jp/fezg00/20090413/1239594632

oracleでテーブル定義の取得の仕方に関して MySQLではshow full columns from *テ...
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q128597432