Oracleで所有しているテーブルのDDL文を出力するPHPスクリプト

Oracleで自分のユーザーが所有しているテーブルのDDL文を一括で出力するPHPスクリプトを作ってみました。
DDLについてはSQLで取得が可能なので、SQL Plusから出力することも出来るのですが、今回はテーブル毎にファイルを分けたかったので、PHPで実装してみました。

SQL PlusでDDLを出力する場合

「忘れっぽいエンジニアのORACLE／オラクルSQLリファレンス」より
http://oracle.se-free.com/ddl/A1_get_ddl.html

set long 2000
set heading off
set line 120
set pages 2000
set long 2000
set heading off
set trimspool off
set feedback off
set longchunksize 600
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE)
spool create_table.sql
select dbms_metadata.get_ddl('TABLE',table_name) from user_tables;
spool off;

DDL文の取得 - dbms_metadata.get_ddl

OracleでDDL文の取得には「dbms_metadata.get_ddl」を使用します。
「dbms_metadata.get_ddl」の書式/引数は以下のような形になります。

dbms_metadata.get_ddlの書式

dbms_metadata.get_ddl( オブジェクトタイプ, オブジェクト名, スキーマ(省略可) )

引数名	説明	例
オブジェクトタイプ	オブジェクトの種類	'TABLE','VIEW','PACKAGE_BODY'
オブジェクト名	テーブル名、ビュー名など	'TABLE_NAME','VIEW_NAME'
スキーマ(※省略可)	オブジェクトの所有者	'USER_NAME'

※「スキーマ」を省略した場合、ログインしているユーザーがデフォルトになります。
※「オブジェクトタイプ」「オブジェクト名」「スキーマ」について、全て大文字での指定が必要になります。
※「dbms_metadata.get_ddl」の戻り値は「CLOB型」になります。

上にも書いていますが、「dbms_metadata.get_ddl」の戻り値は「CLOB型」になるので、PHPでデータを取得する場合、ひと工夫する必要がありました。

PHPでのDDL文の取得 - oci_fetch_array(OCI_ASSOC+OCI_RETURN_LOBS)

PHPでOracleに接続してデータを取得(フェッチ)する場合、「oci_fetch」を使えば良いのですが、CLOB型の取得が上手く出来なかったため、「」を使用しました。
「oci_fetch_array」は、クエリで取得したデータの次行を連想配列または数値添字配列で戻してくれる関数になります。
以下のような形で使用します。

<?php
# ■実行したSQL文(SELECT)
# select table_name, dbms_metadata.get_ddl('TABLE',table_name) as ddl from user_tables order by table_name")
#
# OCI_ASSOC→データを連想配列で戻してもらうための指定
# OCI_RETURN_LOBS→LOB(CLOB)のデータを戻してもらうための指定
# oci_fetch_arrayの戻りの連想配列を、$rowに格納
while( $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS) )
{
    # $rowは連想配列
    # 配列のキーはSELECT句で指定したデータ項目名
    $key = $row["TABLE_NAME"];
    $hash_ddl[$key] = $row["DDL"];
}

今回作成したサンプルコード

以下が今回作成したサンプルコード(全体)になります。
エラー/例外とかは最低限のことしかしていませんので、ご了承ください。

export_oracle_ddl.php

<?php
# DDL文ファイルの出力先ディレクトリ
$output_dir = "./ddl";

$ora_user = $_ENV['ORACLE_USER'];
$ora_password = $_ENV['ORACLE_PASSWORD'];
$ora_sid = $_ENV['ORACLE_SID'];

# Oracle接続
$con = oci_connect($ora_user,$ora_password,$ora_sid);
if( !$con ){
    die("[error]oci_connect");
}

# SQLをParse
# 「user_tables」テーブルから接続したユーザーのテーブル名を取得
$stmt = oci_parse($con, "select table_name, dbms_metadata.get_ddl('TABLE',table_name) as ddl from user_tables order by table_name");
if( !$stmt ){
    die("[error]oci_parse");
}

# SQLの実行
if( !oci_execute($stmt) ){
    die("[error]oci_execute");
}

# OCI_ASSOC→データを連想配列で戻してもらうための指定
# OCI_RETURN_LOBS→LOB(CLOB)のデータを戻してもらうための指定
# oci_fetch_arrayの戻りの連想配列を、$rowに格納
while( $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS) )
{
    # $rowは連想配列
    # 配列のキーはSELECT句で指定したデータ項目名
    $key = $row["TABLE_NAME"];
    $hash_ddl[$key] = $row["DDL"];
}

# DDLのSQLファイルを作成
# 出力ファイル名は「テーブル名.sql」
foreach( $hash_ddl as $key => $ddl )
{
    $out_file = sprintf("%s/%s.sql", $output_dir, $key);
    $f_handle = fopen($out_file, "w");
    fwrite($f_handle, $ddl);
    fclose($f_handle);
}
?>

今回はテーブルのDDLを対象にしていますが、SQLを変更することでVIEW等にも対応可能だと思います。
※例えばVIEWの場合、SQL文を以下のように変更すれば対応出来るかと思います。

select view_name as table_name, dbms_metadata.get_ddl('VIEW',view_name) as ddl from user_views order by view_name

今回参考にしたページ

忘れっぽいエンジニアのORACLE／オラクルSQLリファレンス(DBMS_METADATA.GET_DDL)
http://oracle.se-free.com/ddl/A1_get_ddl.html

PHP oci_fetch_array - Manual
http://php.net/manual/ja/function.oci-fetch-array.php