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>"
}
$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>