OCIの異機種DBLINK検証環境構築メモ ～その４

日曜日朝の秋晴れの中、リラックスして音楽を聴きながら、ひととき日常を忘れ、平和に執筆できることに幸せを感じる。さて、今回はいよいよOracle Gatewayの構成について記す。OCIのDBCSでOracle Gatewayを構成し、Oracle DatabaseのDBLINK経由でSQL ServerやDB2のEMP表をSELECTできるようになるまで構成した手順を記載する。

　１．VCNの設定
　２．DBCSの構築
　３．DB設定（初期化パラメータ設定、PDB構築）
　４．SQL Serverの構築
　５．LinkedServerの構成
　６．DB2の構築
　７．Federationの構成
　８．OracleGWの構築　★
　９．SQL ServerへのDBLINKの構成　★
　１０．DB2へのDBLINKの構成　★

なお、本記事執筆時点でOCIの30日フリートライアルは終了してしまったため、本記事記載の環境はまもなく削除されるだろう。30日でも目的を絞れば製品検証に結構使えるので、フィジビリティ確認には本当にありがたい。

８．OracleGWの構築

Oracle Gatewayの構成は、DBサーバに直接インストールする方法と、別サーバを立てる方法とあるが、今回は手っ取り早くDBCSに直接インストールすることにした。全体の導入方法は製品マニュアル（参考[1]）を確認しながら行った。このマニュアルにはOracle Gatewayが対応する全てのデータベースに対する構成方法が記載されているので、今回はこの中のSQL Serverの記載を参照しながら作業を進めた。また、バイナリは以下OTNから19.3のものがダウンロードした。

Oracle Database Gateways 19c (19.3) for Linux x86-64

また、SQL Server側は接続先データベース(k8)を新規に作成し、emp/dept表をあらかじめ作成しておく。SQL ServerのDDLを書くのは初めてであったので、備忘まで今回作成したテスト用初期データ作成のDDLを参考ファイル[1]に添付しておく。

(1)Oracle Gateway（SQL Server）のインストール

DBサーバの適当なディレクトリに展開すると、gatewaysディレクトリができるので、配下のrunInstallerを起動する。ウィザードにしたがってインストールを進める。

途中でインストールする対象製品を選択する画面が出る。今回はまずSQL Serverを選択する。

SQL Serverの接続情報の画面が表示されるので、SQL ServerのIPアドレス、ポート、インスタンス名、DB名を入力する。k8はSQL Server側に作成したデータベース名である。あらかじめemp表を作成しておく。

プレチェックが走り、インストールが開始される。最後にroot.shを実行する。

以上でインストールは完了である。インストールされた後の$ORACLE_HOMEを確認してみよう。dg4msqlというディレクトリが追加されていることがわかる。後述するが、この配下のファイルにSQL Serverへの接続設定を行うことになる。

[oracle@hostname dbhome_1]$ ls -ltr
total 332
-rw-r--r--  1 oracle oinstall   852 Aug 18  2015 env.ora
...
drwxr-xr-x  3 oracle asmadmin  4096 Sep  8 16:19 admin
drwxr-xr-t  4 oracle oinstall  4096 Sep 12 04:26 log
drwxr-xr-x  2 oracle oinstall  4096 Sep 13 20:38 dbs
drwxr-x--- 15 oracle oinstall  4096 Sep 15 16:58 inventory
drwxr-xr-x  7 oracle oinstall  4096 Sep 15 16:59 dg4msql　★これが追加されている
drwxr-xr-x  2 oracle oinstall 12288 Sep 15 16:59 bin
drwxr-xr-x  8 oracle oinstall  4096 Sep 15 16:59 oui
drwxrwx--- 10 oracle oinstall  4096 Sep 15 16:59 install
drwxr-xr-x  4 oracle oinstall  4096 Sep 15 17:00 cfgtoollogs

また、bin配下にはdg4msqlが増えていることがわかる。これがDBLINKで接続時に実際にSQL Serverと接続するプロセスの実体（Oracleでいうところのサーバプロセス相当）になる。

[oracle@hostname dbhome_1]$ cd bin
[oracle@hostname bin]$ ll -ltr
total 759504
-rwxr-xr-x 1 oracle oinstall       153 Nov  7  1997 echodo
...
-rwxr-x--- 1 oracle oinstall      5538 Sep  8 16:05 emca
-rwxr-xr-x 1 oracle oinstall   1036120 Sep  8 16:05 ldapbind
-rwsr-s--x 1 oracle asmadmin 448598824 Sep  8 16:05 oracle
-rwxr-x--x 1 oracle oinstall    786176 Sep 15 16:59 dg4msql ★これが増えている
[oracle@hostname bin]$

$ORACLE_HOME/dg4msql配下を確認してみよう。admin/initdg4msql.oraはOracle Gatewayの初期化パラメータであり、ここに接続先SQL Serverの情報を記載する。インストーラで入力した値がHS_FDS_CONNECT_INFOに設定されているが、変更が必要ならここを直接編集すればよい。その他、listener.ora.sampleやtnsnames.ora.sampleはサンプルの設定ファイルで、後にリスナやtnsnames.oraを設定する際に必要に応じて参照すればよい。

[oracle@hostname dbhome_1]$ cd dg4msql
[oracle@hostname dg4msql]$ ls
admin  demo  driver  lib  log
[oracle@hostname dg4msql]$ cd admin
[oracle@hostname admin]$ ls
dg4msql_cvw.sql  initdg4msql.ora　★      tnsnames.ora.sample
dg4msql_tx.sql   listener.ora.sample
[oracle@hostname admin]$ view initdg4msql.ora
[oracle@hostname admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/dg4msql/admin
[oracle@hostname admin]$ cat initdg4msql.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.0.101]:1433//k8　★
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

(2)Oracle Gatewayのリスナの構成

ここからOracle Gateway用のリスナを構成する。DBCSのリスナはgridで上がっているので、Gateway用はoracleでポートを1531として新たに起動する。dg4msql/admin配下のリスナのサンプルファイルを参考にlistener.oraを作成し、$ORACLE_HOME/network/admin配下に配置する。通常のリスナと異なる部分はLISTENER名はDBCSのものを重複しないようにLISTENER_DG4MSQLとすることと、SID_LISTでdg4msqlをSIDとしてリスナに登録する★部分である。

[oracle@hostname admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora/listener.ora
# Generated by Oracle configuration tools.

LISTENER_DG4MSQL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname.subnet.vcn.oraclevcn.com)(PORT = 1531))
  )

ADR_BASE_LISTENER_DG4MSQL = /u01/app/oracle


SID_LIST_LISTENER_DG4MSQL=　★
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
         (PROGRAM=dg4msql)
      )
  )

上記設定のあと、リスナを起動し、dg4msqlがリスナに登録されていることを確認する（★部分）。

[oracle@hostname admin]$ lsnrctl start LISTENER_DG4MSQL
...
/u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnrを起動しています。お待ちく ださい...
...
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.subnet.vcn.oraclevcn.com)(PORT=1531)))
...
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.subnet.vcn.oraclevcn.com)(PORT=1531)))
サービスのサマリー...
サービス"dg4msql"には、1件のインスタンスがあります。
  インスタンス"dg4msql"、状態UNKNOWNには、このサービスに対する1件のハンドラがあ ります...★
コマンドは正常に終了しました。

(3)Oracle Gatewayのtnsnamesの構成

リスナの構成が終わったら、tnsnames.oraにこのリスナへのエントリを追加する。$ORACLE_HOME/network/admin/tnsnames.oraに以下エントリを追加する。

dg4msql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1531))　★
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  )

tnspingで疎通できるか確認する。問題なく名前解決でき、ポート1531で接続要求できている。

[oracle@hostname admin]$ tnsping dg4msql
...
エイリアスを解決するためにTNSNAMESアダプタを使用しました。
(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1531)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK))に接続の試行中
OK (0ミリ秒)

以上でOracle Gatewayの基本的な構成は完了である。

９．SQL ServerへのDBLINKの構成

OracleDatabaseのPDBから、SQL ServerへのDBLINKを作成してemp表を検索する。以下のように結果が返ってくる。

[oracle@hostname admin]$ sqlplus f/****@k5
SQL> CREATE PUBLIC DATABASE LINK dg4msql CONNECT TO "sa" IDENTIFIED BY "****" USING 'dg4msql';
データベース・リンクが作成されました。
SQL> select * from emp@dg4msql;

     empno
----------
ename
--------------------------------------------------------------------------------
job
--------------------------------------------------------------------------------
       mgr hiredate        sal       comm     deptno
---------- -------- ---------- ---------- ----------
      7369
SMITH
CLERK
      7902 80-12-17        800                    20
...
14行が選択されました。

実際は上記のようにすんなり接続できた訳ではなかった。はじめはクエリの結果が返ってこず、ハングしたような状態となった。原因はSQL Server側のWindows Firewallでポート1433が開いていないこと、TCP/IPの通信がdisableにされていたことであった。この原因解析で結局一番有効だったのは、telnetでポート疎通確認であった。DBCSにはtelnetが入っていない、かつ、yumリポジトリも構成されていない。このため、原因解析のために、yumリポジトリを構成し、telnetを導入し、問題を切り分けた。先の投稿でSQL Serverの構成手順を記載したが、Windows Firewallを開ける等の手順を追加しているのはこのためである。クラウドの場合は通信は明示的に開けなければ通信できない、という前提で低レベルのレイヤから丁寧に疎通確認をしていく必要があるとつくづく感じた。

１０．DB2へのDBLINKの構成

DB2に対してOracle Gatewayを構成する。手順はSQL Serverの場合とほぼ同じであるため、異なる部分を中心に記載する。前提としてDB2側のtestdbにemp/deptを作成しておく。DDLおよび初期データは参考ファイル[2]を参照されたい。

(1)Oracle Gateways（DRDA）のインストール

Oracle Gateway for DRDAの基本的な手順はSQL Serverと同様、runInstallerからDRDAを選択してインストールする。

DB2への接続情報を入れる。ここでDB2 UDB Target TypeはLUWを設定する（画面上はIOSとなっているが、Linux, UNIX, and Windowsを表すLUWが正しい）。

ここで個別パッチ(interim patches)に影響する旨のワーニングが発生した。これは比較的最新のパッチが当たった状態のDBCSに19.3の素のGatewayをインストールしようとしたためと思われる。今回は無視して前に進み、インストールは問題なく完了したが、本来は＄ORACLE_HOMEは分けてインストールすることが望ましいということだろう。

インストール結果を確認すると、$ORACLE_HOMEにdg4db2ディレクトリが追加されていることがわかる。

[oracle@hostname dbhome_1]$ ls -ltr
...
drwxr-xr-x  6 oracle oinstall  4096 Sep 19 03:05 dg4db2　★

dg4db2/network/admin配下に初期化パラメータinitdg4db2.oraがある。HS_FDS_CONNECT_INFOに画面で投入した接続情報が記録されていることがわかる。

[oracle@hostname admin]$ cat initdg4db2.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for DB2

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.0.10]:25010/testdb,LUW　★
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRANSACTION_ISOLATION=READ_COMMITTED
HS_FDS_SUPPORT_STATISTICS=TRUE
HS_FDS_RESULTSET_SUPPORT=FALSE
HS_FDS_PACKAGE_COLLID=ORACLEGTW
HS_IDLE_TIMEOUT=0

(2)Oracle Gatewayのリスナの構成

リスナの構成は以下の通りSID_LISTにエントリを追加する。

SID_LIST_LISTENER_DG4MSQL=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
         (PROGRAM=dg4msql)
      )
      (SID_DESC=　★
         (SID_NAME=dg4db2)
         (ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
         (PROGRAM=dg4db2)
      )
  )

リスナを再起動し、dg4db2が登録されていることを確認する。

[oracle@hostname admin]$ lsnrctl stop LISTENER_DG4MSQL
...
コマンドは正常に終了しました。
[oracle@hostname admin]$ lsnrctl start LISTENER_DG4MSQL
...
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.subnet.vcn.oraclevcn.com)(PORT=1531)))
...
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.subnet.vcn.oraclevcn.com)(PORT=1531)))
サービスのサマリー...
サービス"dg4db2"には、1件のインスタンスがあります。
  インスタンス"dg4db2"、状態UNKNOWNには、このサービスに対する1件のハンドラがあります...　★
サービス"dg4msql"には、1件のインスタンスがあります。
  インスタンス"dg4msql"、状態UNKNOWNには、このサービスに対する1件のハンドラがあ ります...
コマンドは正常に終了しました。

(3)Oracle Gatewayのtnsnamesの構成

tnsnames.oraに以下のエントリを追加する。tnspingで名前解決できることを確認し、Gatewayの構成は基本的には完了である。

dg4db2  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1531))
    (CONNECT_DATA=(SID=dg4db2))
    (HS=OK)
  )

(4)DBLINK作成

DBLINKを作成し、DB2のtestdbのtbl1をselectする。以下の通り正常にtbl1のselect結果が返ってくることが確認できた。ここで、db2inst1はDB2のOSユーザである。当然であるが、このパスワードでOSにログインできない場合は正常に動作しない（DB2側のインストーラでユーザを作成した際、SE Linuxのパスワード規則を満たしていなくてもDB2のインストーラはエラーを出さないので注意が必要。OSユーザできちんとログインできることを確認しておくとよい）。

SQL> CREATE PUBLIC DATABASE LINK dg4db2 CONNECT TO "db2inst1" IDENTIFIED BY "****" USING 'dg4db2';

データベース・リンクが作成されました。

SQL> select * from emp@dg4db2;

     EMPNO ENAME              JOB                    MGR
---------- ------------------------------ --------------------------- ----------
HIREDATE    SAL   COMM     DEPTNO
-------- ---------- ---------- ----------
      7839 KING               PRESIDENT
81-11-17       5000            10
...
14行が選択されました。

なお、上記は正常に動作したときの結果であるが、初めに疎通させてときには以下のようにORA-28500が発生した。

SQL> select * from tbl1@dg4db2;
select * from tbl1@dg4db2
                   *
行1でエラーが発生しました。:
ORA-28500:
OracleからOracle以外のシステムへの接続で次のメッセージが戻されました:  [Oracle][ODBC DB2 Wire
Protocol driver]Connection refused. Verify Host Name and Port Number. {08001}
ORA-02063: 先行のエラー・メッセージを参照してください2 lines(DG4DB2)。

解析の結果、上記エラーは、DB2側のDBサーバのLinux firewallにDB2通信用のポートが開いていないことが原因であった。OCIのOracle Linuxはデフォルトで以下のようにLinux Firewallが有効になっている。これではDB2への接続を確立するためのポート25010への接続要求は拒否されてしまう。

[db2inst1@db2 cfg]$ systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2021-09-18 09:07:31 JST; 18h ago
     Docs: man:firewalld(1)
 Main PID: 1298 (firewalld)
   CGroup: /system.slice/firewalld.service
           mq1298 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid

Linux Firewallにポートを開けるには以下のようにfirewall-cmd --add-portで通信許可設定を行う。設定後、firewall-cmd --reloadで設定を反映する。Oracle Database側からDB2のサーバへtelnetで当該ポートが通ることを確認するとよい。

[root@db2 tmp]# firewall-cmd --add-port=25010/tcp --zone=public
success
[root@db2 tmp]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens3
  sources:
  services: dhcpv6-client ssh
  ports: 25010/tcp ★
...

まとめ

今回はOracle Gatewayを用いてSQL ServerおよびDB2へDBCSのPDBからDBLINK経由で接続する構成方法について述べた。いずれも、emp表へのselectが返ってくることを確認した。
構成する上でつまずいた点は主にDBサーバ間の通信である。Windows FirewallやLinux Firewallで必要なポートが開いているか、telnetで確認してから構成を行うことが望ましい。特にDB2用に新規作成したOracle LinuxのインスタンスはSE Linuxが有効となっており、OSユーザのパスワード変更には厳しい規則が適用されていたりと注意が必要だろう。
また、今回の検証ではDBCS上にOracle Gatewayを構成したが、DBCSはパッチが適用されることを考えると、$ORACLE_HOMEを分ける、または別の独立したサーバ上に構成するするなどの配慮は必要だろう。
Oracle gatewayには初期化パラメータにより様々な設定が可能であるが、その点については考慮が及んでいない。例えば２フェーズコミットを行う場合などはそれに応じた追加作業が必要である。また、ディクショナリを使うためにはDB2側でスクリプトを流す必要があるようだが、今回そこまでは検証できていない。あくまで穴あけができた程度の検証、ととらえている。それでも、OCI上でこの検証を行うことによって、DB2やSQL Serverの表をOracleから参照できること、構成上注意するポイントはいくつか確認できた。

◇参考

[1] Oracle Database Gateway Installation and Configuration Guide

◇参考ファイル

[1] SQL Serverのemp/dept表作成スクリプト

USE [k8]
GO

/****** Object:  Table [dbo].[dept]    Script Date: 2021/09/15 23:50:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dept]') AND type in (N'U'))
DROP TABLE [dbo].[dept]
GO

/****** Object:  Table [dbo].[dept]    Script Date: 2021/09/15 23:50:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dept](
	[deptno] [numeric](2, 0) NOT NULL,
	[dname] [varchar](14) NULL,
	[loc] [varchar](13) NULL,
 CONSTRAINT [PK_dept] PRIMARY KEY CLUSTERED 
(
	[deptno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[emp]    Script Date: 2021/09/15 23:51:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[emp]') AND type in (N'U'))
DROP TABLE [dbo].[emp]
GO

/****** Object:  Table [dbo].[emp]    Script Date: 2021/09/15 23:51:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[emp](
	[empno] [numeric](4, 0) NOT NULL,
	[ename] [varchar](10) NULL,
	[job] [varchar](9) NULL,
	[mgr] [numeric](4, 0) NULL,
	[hiredate] [date] NULL,
	[sal] [numeric](7, 2) NULL,
	[comm] [numeric](7, 2) NULL,
	[deptno] [numeric](2, 0) NULL,
 CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED 
(
	[empno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept
values(20, 'RESEARCH', 'DALLAS');

insert into dept
values(30, 'SALES', 'CHICAGO');

insert into dept
values(40, 'OPERATIONS', 'BOSTON');

insert into emp
values(
 7839, 'KING', 'PRESIDENT', null,
 convert(datetime,'1981-11-17'),
 5000, null, 10
);

insert into emp
values(
 7698, 'BLAKE', 'MANAGER', 7839,
 convert(datetime,'1981-5-1'),
 2850, null, 30
);


insert into emp
values(
 7782, 'CLARK', 'MANAGER', 7839,
 convert(datetime,'1981-6-9'),
 2450, null, 10
);

insert into emp
values(
 7566, 'JONES', 'MANAGER', 7839,
 convert(datetime,'1981-4-2'),
 2975, null, 20
);

insert into emp
values(
 7788, 'SCOTT', 'ANALYST', 7566,
 convert(datetime,'1987-7-13')-85,
 3000, null, 20
);

insert into emp
values(
 7902, 'FORD', 'ANALYST', 7566,
 convert(datetime,'1981-12-3'),
 3000, null, 20
);

insert into emp
values(
 7369, 'SMITH', 'CLERK', 7902,
 convert(datetime,'1980-12-17'),
 800, null, 20
);

insert into emp
values(
 7499, 'ALLEN', 'SALESMAN', 7698,
 convert(datetime,'1981-2-20'),
 1600, 300, 30
);

insert into emp
values(
 7521, 'WARD', 'SALESMAN', 7698,
 convert(datetime,'1981-2-22'),
 1250, 500, 30
);

insert into emp
values(
 7654, 'MARTIN', 'SALESMAN', 7698,
 convert(datetime,'1981-9-28'),
 1250, 1400, 30
);

insert into emp
values(
 7844, 'TURNER', 'SALESMAN', 7698,
 convert(datetime,'1981-9-8'),
 1500, 0, 30
);

insert into emp
values(
 7876, 'ADAMS', 'CLERK', 7788,
 convert(datetime,'1987-7-13')-51,
 1100, null, 20
);

insert into emp
values(
 7900, 'JAMES', 'CLERK', 7698,
 convert(datetime,'1981-12-3'),
 950, null, 30
);

insert into emp
values(
 7934, 'MILLER', 'CLERK', 7782,
 convert(datetime,'1982-1-23'),
 1300, null, 10
);

[2] DB2のemp/dept表作成スクリプト

--   Script: EMP and DEPT
--   Example EMP and DEPT tables.  Classic Oracle tables with 4 departments and 14 employees.  Includes a join query example.

-- Create DEPT table which will be the parent table of the EMP table. 
drop table emp;
drop table dept;

create table dept
(  
  deptno     int not null,  
  dname      varchar(14),  
  loc        varchar(13)
);

alter table dept add constraint pk_dept primary key (deptno);

-- Create the EMP table which has a foreign key reference to the DEPT table.  The foreign key will require that the DEPTNO in the EMP table exist in the DEPTNO column in the DEPT table.
create table emp(  
  empno    int not null,  
  ename    varchar(10),  
  job      varchar(9),  
  mgr      int,  
  hiredate date,  
  sal      decimal(7,2),  
  comm     decimal(7,2),  
  deptno   int
);

alter table emp add constraint pk_emp primary key (empno);
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);


-- copy emp/dept from oracle database
-- insert into dept select * from k5.f.dept;
-- commit;
-- insert into emp select * from k5.f.emp;
-- commit;
--

-- Insert row into DEPT table using named columns.
insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK');

-- Insert a row into DEPT table by column position.
insert into dept  
values(20, 'RESEARCH', 'DALLAS');

insert into dept  
values(30, 'SALES', 'CHICAGO');

insert into dept  
values(40, 'OPERATIONS', 'BOSTON');

-- Insert EMP row, using TO_DATE function to cast string literal into an oracle DATE format.
insert into emp  
values(  
 7839, 'KING', 'PRESIDENT', null,  
 to_date('17-11-1981','dd-mm-yyyy'),  
 5000, null, 10  
);

insert into emp  
values(  
 7698, 'BLAKE', 'MANAGER', 7839,  
 to_date('1-5-1981','dd-mm-yyyy'),  
 2850, null, 30  
);

insert into emp  
values(  
 7782, 'CLARK', 'MANAGER', 7839,  
 to_date('9-6-1981','dd-mm-yyyy'),  
 2450, null, 10  
);

insert into emp  
values(  
 7566, 'JONES', 'MANAGER', 7839,  
 to_date('2-4-1981','dd-mm-yyyy'),  
 2975, null, 20  
);

insert into emp  
values(  
 7788, 'SCOTT', 'ANALYST', 7566,  
 to_date('13-7-1987','dd-mm-yyyy') - 85,  
 3000, null, 20  
);

insert into emp  
values(  
 7902, 'FORD', 'ANALYST', 7566,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 3000, null, 20  
);

insert into emp  
values(  
 7369, 'SMITH', 'CLERK', 7902,  
 to_date('17-12-1980','dd-mm-yyyy'),  
 800, null, 20  
);

insert into emp  
values(  
 7499, 'ALLEN', 'SALESMAN', 7698,  
 to_date('20-2-1981','dd-mm-yyyy'),  
 1600, 300, 30  
);

insert into emp  
values(  
 7521, 'WARD', 'SALESMAN', 7698,  
 to_date('22-2-1981','dd-mm-yyyy'),  
 1250, 500, 30  
);

insert into emp  
values(  
 7654, 'MARTIN', 'SALESMAN', 7698,  
 to_date('28-9-1981','dd-mm-yyyy'),  
 1250, 1400, 30  
);

insert into emp  
values(  
 7844, 'TURNER', 'SALESMAN', 7698,  
 to_date('8-9-1981','dd-mm-yyyy'),  
 1500, 0, 30  
);

insert into emp  
values(  
 7876, 'ADAMS', 'CLERK', 7788,  
 to_date('13-7-1987', 'dd-mm-yyyy') - 51,  
 1100, null, 20  
);

insert into emp  
values(  
 7900, 'JAMES', 'CLERK', 7698,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 950, null, 30  
);

insert into emp  
values(  
 7934, 'MILLER', 'CLERK', 7782,  
 to_date('23-1-1982','dd-mm-yyyy'),  
 1300, null, 10  
);