承認これくしょん

my black histories

Oracleで特定のテーブル・ビューに依存するビューの一覧を取得する

Oracle Java JavaScript

ビューを参照するビュー、つまり多重ビューがあるとテーブルのスキーマ変更も一苦労です。そもそもアンチパターンでは…？
とはいえ、既に作られてしまったなら立ち向かうしかない。とにかく依存関係を明らかにしなければ。
そこでテーブル・ビュー名を指定すると、それに依存するビューの一覧を出力するスクリプトを作りました。
Oracle専用です。動作確認には11g XEを使いました。

ソース (viewFinder.js)

var Properties = java.util.Properties;
var OracleDriver = Packages.oracle.jdbc.OracleDriver;
var PreparedStatement = java.sql.PreparedStatement;

var queries = {
  dependencies: "SELECT NAME FROM USER_DEPENDENCIES WHERE TYPE = 'VIEW' AND REFERENCED_NAME = ? ORDER BY NAME",
  views: 'SELECT VIEW_NAME, TEXT FROM USER_VIEWS WHERE VIEW_NAME IN '
};
var viewNames = [];

// JDBC接続設定
var url = 'jdbc:oracle:thin:@localhost:1521:xe';  // 接続文字列
var user = 'scott'; // ユーザーID
var password = 'tiger'; // パスワード

if (arguments[0]) {
  var arg = arguments[0];
  var prop = new Properties();
  prop.setProperty('user', user);
  prop.setProperty('password', password);

  try {
    var conn = new OracleDriver().connect(url, prop);
    print(arg);
    printTree(arg, '');

    if (viewNames.length > 0) {
      queries.views += '(' + repeat('?', viewNames.length, ',') + ') ORDER BY VIEW_NAME';
      query(queries.views, viewNames, function(rs) {
        var viewName = rs.getString('VIEW_NAME');
        var text = rs.getString('TEXT');
        print('\r\n' + viewName + '\r\n' + repeat('-', viewName.length) + '\r\n' + text);
      });
    }

  } finally {
    if (conn) {
      try {
        conn.close();
      } catch(e) {}
    }
  }
}

function printTree(name, prefix) {
  var items = query(queries.dependencies, [name], function(rs) {
    return rs.getString('NAME');
  });

  items.forEach(function(item) {
    if (viewNames.indexOf(item) == -1) {
      viewNames.push(item);
    }
  });

  if (items.length > 0) {
    for (var i = 0, length = items.length; i < length; i++) {
      var item = items[i];
      if (i == length - 1) {
        print(prefix + '└─' + item);
        printTree(item, prefix + '    ');
      } else {
        print(prefix + '├─' + item);
        printTree(item, prefix + '│   ');
      }
    }
  }
}

function query(query, params, callback) {
  try {
    var ps = conn.prepareStatement(query);
    if (params) {
      for (var i = 0, length = params.length; i < length; i++) {
        var param = params[i];
        ps.setObject(i + 1, param);
      }
    }
    var arr = [];
    var rs = ps.executeQuery();
    while (rs.next()) {
      arr.push(callback(rs));
    }
    return arr;
  } finally {
    if (ps) {
      try {
        ps.close();
      } catch(e) {}
    }
  }
}

function repeat(str, times, separator) {
  var newStr = '';
  for (var i = 0; i < times; i++) {
    if (separator && i > 0) newStr += separator;
    newStr += str;
  }
  return newStr;
}

実行方法

まずはojdbc6.jarことOracle JDBC Thin Driverを用意。
そしてJDK付属のjrunscript、JREにも入ってるjjsコマンドのいずれかで実行します。

jrunscript -cp ojdbc6.jar viewFinder.js (テーブル・ビュー名)

jjs -cp ojdbc6.jar viewFinder.js -- (テーブル・ビュー名)

出力イメージ

treeコマンド風にビューの依存関係を、その下に各ビューのソースを出力します。

EMP
└─EMP1
    ├─EMP2
    │   └─EMP4
    └─EMP3

EMP1
----
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM EMP

EMP2
----
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM EMP1

EMP3
----
SELECT EMPNO FROM EMP1

EMP4
----
SELECT JOB FROM EMP2

参考