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

http://d.hatena.ne.jp/tigerii/20120730/1343660136
で作ったプログラムを見直す。

前回は ./show_user_tables.pl HR　でHRが所有するテーブルを一行ずつ出力し、./table_info2.xls.pl ではテーブル毎にテーブル定義をselectして、出力結果をexcelファイルに書き込んだ。

$ ./show_user_tables.pl HR | ./table_info2xls.pl

今回は、DB接続情報とownerを渡して、xml形式で出力する。
(ora_table_info.pl)

#!/usr/bin/env perl

use DBI;

$ENV{ORACLE_HOME}='/u01/app/oracle/product/11.2.0/xe';
$driver = 'Oracle';

if( $ARGV[0] =~ /(\S+)\/(\S+)\@(\S+):(\d+)\/(\S+)/ ){
  $user     = $1;
  $password = $2;
  $hostname = $3;
  $port     = $4;
  $sid      = $5;
}else{
  die "usage: ora_table_info.pl <user>/<password>@<hostname>:<port>/<sid> <owner>"
}

#dbi:$driver:database=$db;host=$host;port=$port
$dbh = DBI->connect("dbi:$driver:host=$hostname;sid=$sid;port=$port",$user,$password);

$sql_table_name = <<EOS;
select table_name
from all_tables where owner='$ARGV[1]'
order by table_name
EOS
$sth_table_name = $dbh->prepare($sql_table_name);
$sth_table_name->execute();

print "<owner name=\"$ARGV[1]\">\n";
while($table_name=$sth_table_name->fetchrow_array()){

$sql_table_info = <<EOS;
select utcol.column_name,
       utcol.data_type,
       utcol.data_length,
       uccom.comments
from user_tab_cols utcol, user_col_comments uccom
where utcol.table_name = '$table_name' and
      utcol.table_name = uccom.table_name and
      utcol.column_name = uccom.column_name
EOS

  $sth_table_info = $dbh->prepare($sql_table_info);
  $sth_table_info->execute();

  print "  <table name=\"$table_name\">\n";

  print "    <row><!--header-->\n";
  for($i=0; $i<$sth_table_info->{NUM_OF_FIELDS}; $i++){
    print "      <column name=\"$sth_table_info->{NAME}->[$i]\">$sth_table_info->{NAME}->[$i]<\/column>\n";
  }
  print "    <\/row><!--header-->\n";

  while(@row=$sth_table_info->fetchrow_array()){
    print "    <row>\n";
    for($i=0; $i<$sth_table_info->{NUM_OF_FIELDS}; $i++){
      print "      <column name=\"$sth_table_info->{NAME}->[$i]\">$row[$i]<\/column>\n";
    }
    print "    <\/row>\n";
  }

  print "  </table>\n";

  $sth_table_info->finish();

}
print "<\/owner>\n";

$sth_table_name->finish();
$dbh->disconnect();

2015.4.5
出力がXML形式ではなかったため、
<table>..</table>
<table>..</table>
から
<owner>
<table>..</table>
<table>..</table>
</owner>
の形式に修正

実行してみる

$ ./ora_table_info.pl 
usage: ora_table_info.pl <user>/<password>@<hostname>:<port>/<sid> <owner> at ./ora_table_info.pl line 15.
$ ./ora_table_info.pl hr/hr@192.168.0.19:1521/xe HR
<owner name="HR">
  <table name="COUNTRIES">
    <row><!--header-->
      <column name="COLUMN_NAME">COLUMN_NAME</column>
      <column name="DATA_TYPE">DATA_TYPE</column>
      <column name="DATA_LENGTH">DATA_LENGTH</column>
      <column name="COMMENTS">COMMENTS</column>
    </row><!--header-->
    <row>
      <column name="COLUMN_NAME">COUNTRY_ID</column>
      <column name="DATA_TYPE">CHAR</column>
      <column name="DATA_LENGTH">2</column>
      <column name="COMMENTS">Primary key of countries table.</column>
    </row>
    <row>
      <column name="COLUMN_NAME">COUNTRY_NAME</column>
      <column name="DATA_TYPE">VARCHAR2</column>
      <column name="DATA_LENGTH">40</column>
      <column name="COMMENTS">Country name</column>
    </row>
    <row>
      <column name="COLUMN_NAME">REGION_ID</column>
      <column name="DATA_TYPE">NUMBER</column>
      <column name="DATA_LENGTH">22</column>
      <column name="COMMENTS">Region ID for the country. Foreign key to region_i
d column in the departments table.</column>
    </row>
  </table>
...
    <row>
      <column name="COLUMN_NAME">REGION_NAME</column>
      <column name="DATA_TYPE">VARCHAR2</column>
      <column name="DATA_LENGTH">25</column>
      <column name="COMMENTS"></column>
    </row>
  </table>
</owner>