SQL Server から Oracle に接続する【ODBC編】

「Oracle から SQL Server へ接続する」とは全く逆で、SQL Server からリンクサーバー経由で Oracle に接続してデータ操作を行ってみます。
環境は関連記事と同じく、Windows10 、Oracle 19c 、 SQL Server 2019 です。
接続用のドライバですが、Oracle から SQL Server が「ODBC」でしたので、今回も合わせて「ODBC」で接続してみたいと思います。

Oracle ODBC ドライバ

私の環境では Oracle 19c をインストール済なので Oracle に接続するための ODBC ドライバもインストールされています。接続元に Oracle がインストールされているとは限らないので、ODBC ドライバは別途インストールする必要があります。普通に Oracle Client をダウンロードしてからインストールしても良いですが、必要最小限にしたいのであれば「Oracle Instant Client ODBCインストール・ノート」を参考にしてみてください。
尚、Oracle Instant Client については「Oracle SQL Developer を導入する【Oracle Instant Client 編】」でも触れていますので参考にしてください。

Oracle 側にユーザを作成する

今回も SQL Server からの接続のための専用ユーザを作成します。Oracle の場合、ユーザ＝スキーマという考え方なので、作成したユーザのスキーマにテスト用のテーブルとデータを作成します。
（もちろん、別スキーマのテーブルでも可能ですが、話が脱線しないようにシンプルに考えます）
接続用のユーザをGUI（SQL Developer）で作成していきます。

ユーザの作成（１）

ユーザ名は"MSQLUSER"です。SQL Developer がデフォルトの設定だとケースセンシティブのようなのでユーザ名は大文字にしておきます。

ユーザの作成（２）

接続（CONNECT）と自身のスキーマにテーブル作成等ができる（RESOURCE）のロールを与えておきます。

ユーザの作成（３）

表領域 USERS への容量割当ては無制限としておきます。

ユーザの作成（４）

これで「適用」を選択すればユーザが作成されます。

ユーザの作成（５）

作成したユーザでログインして、関連記事と同じようなテスト用のテーブルとサンプルデータを作成します。ここの手順は省略します。以下が参照結果です。

Oracle テストデータ

ODBC データソースの作成

今回も ODBC 経由とするため、ODBC データソースを作成したいと思います。

【2021年9月17日 追記】
　データソース作成をしないで接続する方法がありましたので別記事にしています。
　「SQL Server から Oracle に接続する【ODBC DSNレス接続編】」を参照ください。

スタートメニューより「ODBC データソース (64 ビット)」を起動し、「システム DSN」タブで「追加」を行います。ドライバは「Oracle in OraDB19Home1」を選択します。

データソースの作成（１）

データソースの名前は「ORACLE19DSN」とします。TNSサービス名は、tnsname.ora に定義されているのであればその名前でも良いですが、定義しなくても簡易接続ネーミング・メソッドを使用することができます。
先頭の"//"は省略できるようなので以下のように記述しました。

データソースの作成（２）

「接続テスト」を選択するとIDとパスワードを聞かれるので、最初に登録したユーザで接続確認してみてください。問題なさそうなら、OK ボタンで完了です。

データソースの作成（３）

今回作成したDSN（ORACLE19DSN）が、前回登録したDSN（MSSQL2019DSN）の下に追加されました。

リンクサーバーの作成

Oracle からは Database Gateway の設定が必要だったため少々面倒でしたが、SQL Server ではそのような設定なしにリンクサーバ（＝Oracle でいうところのデータベースリンク）を作成できます。
プロバイダーの指定があるので、GUIの方がわかりやすいので、今回はGUIで作成します。

リンクサーバーの作成（１）

リンクサーバー名は「ORACLE19LINK」で、プロバイダーは「Microsoft OLE DB Provider for ODBC Drivers」を選択、カタログに作成したデータソース「ORACLE19DSN」を設定します。

リンクサーバーの作成（２）

セキュリティページで最初に作成した専用ユーザ（MSQLUSER）とパスワードを指定します。

リンクサーバーの作成（３）

OKを選択するとリンクサーバーが作成されるのですが、このときに同時にテスト接続が行われ、もし入力不備や環境不備があれば例えば以下のようなメッセージが表示されます。

接続エラーメッセージ

接続テストで問題がなければリンクサーバーが追加されます。

リンクサーバーの作成（４）

SQL Server からの接続テスト

では、リンクサーバー経由で、テストデータを参照してみます。

SELECT * FROM ORACLE19LINK..MSQLUSER.SAMPLE;
Oracle テストデータ参照（１）
メッセージ 7318、レベル 16、状態 1、行 1
リンク サーバー "ORACLE19LINK" の OLE DB プロバイダー "MSDASQL" から、テーブル ""MSQLUSER"."SAMPLE"" の無効な列定義が返されました。

げげっ！なんということでしょう！「無効な列定義！？」なんだか意味わかりませんが VARCHAR2 とかが変換できないのでしょうか、、、では、パススルークエリで実行してみます。OPENQUERYという関係演算子を使います。

SELECT * FROM OPENQUERY( ORACLE19LINK, 'SELECT * FROM MSQLUSER.SAMPLE')
Oralce テストデータ参照（２）

うまく検索できました。では、登録もやってみます。

INSERT OPENQUERY (ORACLE19LINK, 'SELECT * FROM MSQLUSER.SAMPLE')  
  VALUES (5,'SQL Serverからの追加','X');
Oracle テストデータの追加

これを Oracle 側で参照してみます。

Oracle テストデータの参照（３）

登録できました。んー、でも少し消化不良ですね。Oracle から SQL Server のときのように普通の SQL にしたいですね。次回は接続プロバイダを変えて試してみたいと思います。

データベースODBC,Oracle,Setup,SQLServer

Posted by takmaru