SQL Server から Oracle に接続する【OLE DB編】
「SQL Server から Oracle に接続する【ODBC編】」及び「SQL Server から Oracle に接続する【ODBC DSNレス接続編】」では、ODBC 経由で SQL Server より Oracle に接続しましたが、今回は、OLE DB を使って接続したいと思います。OPENQUERY を使わずに Oracle にアクセスすることを目指します。
Oracle Provider for OLE DB
「Provider for OLE DB開発者ガイド」に詳しく記載があります。ODBC と OLE DB の違いですが、ODBCは
Open Database Connectivity (ODBC) は、関係データベース管理システム (RDBMS) にアクセスするための共通インタフェース (API)である。
Wikipedia – Open Database Connectivity
であり、OLE DB は
OLE DB (Object Linking and Embedding, Database)はマイクロソフトの設計したAPIであり、一様な形で格納されている様々な種類のデータへのアクセスを行うものである。
Wikipedia – OLE DB
だそうです。うーん、違いがよくわからない、、、
まぁ誤解を恐れずにいうなら、ODBC は Windows 以外でもOKな汎用性の高い API だが、OLE DB は Windows コテコテの API である、という感じでしょうか。ただ、OLE DB は、ODBC をより抽象化しているとのことなので、よりデータベース固有の問題に対応できるってことですかね。なんだか OPENQUERY 使わなくても良いのではないでしょうか。
また、実際のところ関連記事で ODBC 接続に利用したプロバイダも「OLE DB Provider for ODBC」といって 結局 OLE DB 経由なんですよね。それに Oracle としては、Oracle Provider for OLE DB を出しているわけですから、わざわざ ODBC 経由にする必要ないよ、ということなのでしょうかね。
データベース接続用のドライバは色々と複雑なので、別途調査したいとは思います。
さて肝心の Oracle Provider for OLE DB ですが、普通に Oracle Client をインストールするか、Oracle Data Access Components (ODAC) に含まれているようですので、こちらを使っても良いのかもしれません。
詳しくは「Oracle Data Access Components (ODAC) for Windows ダウンロード」で確認してください。
リンクサーバーの作成
私の環境は Oracle 19c が入ってるので既に「Oracle Provider for OLE DB」はあります。
早速、リンクサーバーから作ってみます。プロバイダーは「Oracle Provider for OLE DB」で、データソースに tnsnames.ora で定義した TNS 名または簡易接続ネーミング・メソッドで接続先を記載します。
前回記事で ODBC データソースを作成したときと同じですね。
次に接続用のユーザー、パスワードですが、この辺は 前回記事 と同じです。
これで新しいリンクサーバ（ORACLE19LINK3）ができました。
SQL Server からの接続テスト
では、新しいリンクサーバー経由で、テストデータを参照してみます。OPENQUERY を利用せずに見てみます。
SELECT * FROM ORACLE19LINK3..MSQLUSER.SAMPLE;
メッセージ 7399、レベル 16、状態 1、行 1
リンク サーバー "ORACLE19LINK3" の OLE DB プロバイダー "OraOLEDB.Oracle" により、エラーがレポートされました。アクセスが拒否されました。
メッセージ 7301、レベル 16、状態 2、行 1
リンク サーバー "ORACLE19LINK3" の OLE DB プロバイダー "OraOLEDB.Oracle" から必要なインターフェイス ("IID_IDBCreateCommand") を取得できません。
なんと、、、「アクセスが拒否されました！？」これまた前回と違うエラーですね。Google 先生に聞いても、そのものズバリでは出てきませんでしたが、どうもアクセス権周りの問題のようです。例えばサービスの実行ユーザとかを変えたりしてみたらいけるのかもしれませんが、プロバイダーオプションの InProcess 許可 を有効にすることで解決することがわかりました。
[InProcess 許可]
SQL Server で、インプロセス サーバーとしてプロバイダーのインスタンスを作成できます。 このオプションを設定しない場合、既定の動作として、 SQL Server プロセス外でプロバイダーのインスタンスが作成されます。
リンク サーバーの作成 (SQL Server データベース エンジン)
具体的には、リンクサーバーで利用するプロバイダーである Oracle Provider for OLE DB (OraOLEDB.Oracle) のプロパティから「InProcess 許可」にチェックを入れます。
これで再度、SQLを実行してみます。
OPENQUERY を利用せずとも参照できました！では、レコード追加もしてみます。
INSERT INTO ORACLE19LINK3..MSQLUSER.SAMPLE
VALUES (6,'SQL Serverからの追加2','Y'); 
最後に、これをOracle 側で参照。
ちゃんと追加されていました。問題なさそうです。