システム開発者ノウハウ集(Zin’sつぶやき): (Free Tool)Oracle Database TableデータをOracle用またはPostgreSQL用のInsert文に出力

業務システムの開発時にデータベースデータをファイルに出力したり、他のデータベースに移動するためにData Export/Importツールが必要とされますが、機能が不十分だったので、自前でExp2Dml.exeツールを作成してみました。この記事では Exp2Dml.exeのタウンロード、設置、使い方について紹介します。

Exp2Dml.exeについて…

既存のOracle Serverから新規Oracle Serverへ」、「既存のOracle Serverから新規PostgreSQL Serverへ」データを複製するためのExp2Dm.exeツールを作成しました。

次のように、既存のサーバーから別の新しいサーバーにデータをコピーするために、既存のOracle ServerからのデータをSQL Insertステートメントに出力するソフトウェアです。

既存OracleServer– > Oracle DML（ SQL Insert 文）-> 新規OracleServer
既存OracleServer– > PostgreSQL DML（ SQL Insert 文）-> 新規PostgreSQLServer

このツールは、既存のOracle Serverのすべての表データをSQL Insertステートメントに出力できます。出力ファイルは、環境ファイルの設定値に応じて、新しいOracle Serverに再ロードできるOracle DML（SQL Insertステートメント）と、新しいPostgreSQL ServerにロードできるPostgreSQL DML（SQL Insertステートメント）で作成されます。

Exp2Dml.exe ソフトウェアのダウンロード

下の[今すぐダウンロード]ボタンをクリックしてexp_ora2dml_until_YYYYMMDD.zi_をダウンロードします。ファイルはgithubに登録されています。YYYYMMDDは登録日ではなく使用期限を表します。使用期限が過ぎた場合、新たに最新をダウンロードしてください。

今すぐダウンロード

インストールする

exp_ora2dml_until_YYYYMMDD.zi_をexp_ora2dml_until_YYYYMDD.zipに拡張子を変更します。
exp_ora2dml_until_YYYYMMDD.zip圧縮ファイルに対して次のように展開を実行します。

[ファイルを選択 > 右マウスボタンをクリック > コンテキストメニュー(context menu) > すべて展開(T)… ]を実行します。

次のように解凍ダイアログポップアップウィンドウを表示します。次に、展開フォルダを C:\exp_ora2dmlまたはD:\exp_ora2dmlに変更し、[展開]を選択します。 Dドライブが存在する場合は、次のように D:\exp_ora2dmlフォルダに展開することをお勧めします。

D:\exp_ora2dmlフォルダにプログラムが展開されていることを確認します。

D:\exp_ora2dml
├─bin
│      exp2dml.exe
│      exp2dml.ini
├─instantclient_12_2
│  │  adrci.exe
│  │  adrci.sym
... 中間 省略 ...
├─oradml
│      01make.bat
│      02load.bat
│      exp2dml.ini
│      setenv.bat
├─output
│      yyyymmdd_hhmiss_dml_output.sql
└─pgdml
        01make.bat
        02load.bat
        exp2dml.ini
        setenv.bat

注意）exp_ora2dml_until_YYYYMMDD.zipツールの展開では instantclient_12_2ディレクトリは展開しません。以下のOracleダウンロードサイトにアクセスして、 instantclient-basic-windows.x64-12.2.0.1.0.zipファイルをダウンロードして展開します。その後、解凍したinstantclient_12_2ディレクトリを上記のD:\exp_ora2dmlディレクトリに移動します。

https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html www.oracle.com

この記事では、次のバージョンのinstantclientをダウンロードしました。

実行する（データを出力する）

Oracle DMLを作成する環境ファイルD:\exp_ora2dml\oradml\exp2dml.ini

[DEFAULT]
oracle_instantclient_dir = ..\instantclient_12_2
dbuser = scott
dbpass = tiger
dbconn = 192.168.220.145/orcl
#filter_where = and object_name like 'E%%'
filter_where =
# 1:Oracle, 2:PostgreSQL
output_dml_dbkind = 1

PostgreSQL DMLを作成する環境ファイルD:\exp_ora2dml\pgdml\exp2dml.ini

[DEFAULT]
oracle_instantclient_dir = ..\instantclient_12_2
dbuser = scott
dbpass = tiger
dbconn = 192.168.220.145/orcl
#filter_where = and object_name like 'E%%'
filter_where =
# 1:Oracle, 2:PostgreSQL
output_dml_dbkind = 2

各環境変数の説明は次のとおりです。

環境	説明
oracle_instantclient_dir	makedml.exeにはOracle instantclientのライブラリファイルが必要です。Oracleダウンロードサイトからダウンロードしたinstantclient-basic-windows.x64-12.2.0.1.0.zipから解凍した instantclient_12_2ディレクトリのパスを指定します。
dbuser	Oracle接続スキーマ（ユーザー）情報を指定します。
dbpass	Oracle接続パスワードを指定します。
dbconn	Oracle接続サーバーのIPアドレスとSID情報を指定します。
filter_where	指定しないと、スキーマ（ユーザー）のすべてのテーブルがターゲットになります。ターゲットを減らしたい場合は、andで始まる条件文を指定します。
output_dml_dbkind	1を指定すると、Oracle Databaseに再入力できる形式で出力されます。 2を指定すると、PostgreSQL Databaseに入力できる形式で出力します。

コマンドプロンプトで、次の内容のOracle DMLまたはPostgreSQL DMLを作成するバッチファイルを実行します。実行時のlog結果は01make_yyyymmdd_hhmiss.logファイルに出力されます。

Oracle SQL文への出力	D:\exp_ora2dml\oradml\01make.bat
PostgreSQL文への出力	D:\exp_ora2dml\pgdml\01make.bat

バッチファイルの内容は両方とも次のとおりです。

@echo off
cd %~dp0
set CUR_PATH=%~dp0
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set FMTDATE=%date:~0,4%%date:~5,2%%date:~8,2%_%CUR_HH%%time:~3,2%%time:~6,2%
copy .\exp2dml.ini ..\bin\exp2dml.ini
cd ..\bin
echo "make dml processing..."
exp2dml.exe > %CUR_PATH%01make_%FMTDATE%.log 2>&1
notepad %CUR_PATH%01make_%FMTDATE%.log

次のメッセージが表示されたら、[詳細情報]を押します。

次のようにアプリ情報と[実行]ボタンが表示されたら、[実行]ボタンを押します。

exp2dml.exeが実行されると、D:\exp_ora2dml\output\ディレクトリに次のpgdml_output.sql ファイルまたは oradml_output.sql ファイルが出力されます。このファイルは、上記の環境ファイルexp2dml.iniで指定したoutput_dml_dbkind値に応じて、OracleバージョンのsqlステートメントまたはPostgreSQLバージョンのsqlステートメントに出力します。

-- ******************************************************
-- read [exp2dml.ini] file and display value
-- ******************************************************
-- oracle_instantclient_dir = ..\instantclient_12_2
-- dbuser = scott
-- dbpass = tiger
-- dbconn = 192.168.220.145/orcl
-- filter_where = 
-- output_dml_dbkind = 2

-- *****************************************************
-- Successfully connected to Oracle Database
-- *****************************************************
-- select object_name from user_objects where object_type = 'TABLE' 


-- select * from DEPT
--     DEPTNO                                   NOT NULL NUMBER(2,0) 
--     DNAME                                             VARCHAR(14) 
--     LOC                                               VARCHAR(13) 

begin;
truncate table DEPT;
end;

begin;
insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
commit;
... 以下 省略 ...

ちなみに、長い色のbegin; end; コマンドが存在すると、PostgreSQLバージョンのsql文で出力したことがわかります。

実行する（データを読み込む）

次のsetenv.bat環境ファイルを開き、データロード先のデータベースサーバー情報を変更します。

データのロード先がOracle Server

D:\exp_ora2dml\oradml\setenv.bat

@echo off
set NLS_LANG=American_America.AL32UTF8
REM set NLS_LANG=KOREAN_KOREA.KO16MSWIN949
set PATH=C:\app\oracle\product\12.2.0\dbhome_1\bin;%PATH%
set ORACLE_HOME=C:\app\oracle\product\12.2.0\dbhome_1
set ORAHOST=localhost
set ORAPORT=1521
set ORACLE_SID=ORCL
set ORAUSER=scott
set ORAPASS=tiger

データロード先の PostgreSQL Server

D:\exp_ora2dml\pgdml\setenv.bat

@echo off
set PGCLIENTENCODING=SJIS
REM set PGCLIENTENCODING=UTF-8
set PATH=C:\PostgreSQL\12\bin;%PATH%
set PGDATA=C:\PostgreSQL\12\data
set PGHOST=localhost
set PGPORT=5432
set PGDATABASE=postgres
set PGUSER=postgres
set PGPASS=password

02load.batで指定されたyyyymmdd_hhmiss_dml_output.sqlファイル名をの 6. で出力したデータのファイル名に変更し、コマンドプロンプトで次の内容の02load.batを実行します。実行時のlog結果は02load_yyyymmdd_hhmiss.logファイルに出力されます。これにより、setenv.batで指定したサーバーにロードできます。

データのロード先がOracle Server

D:\exp_ora2dml\oradml\02load.bat

@echo off
cd %~dp0
set CUR_PATH=%~dp0
call setenv.bat
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set FMTDATE=%date:~0,4%%date:~5,2%%date:~8,2%_%CUR_HH%%time:~3,2%%time:~6,2%
echo "load dml processing..."
sqlplus %ORAUSER%/%ORAPASS%@%ORAHOST%/%ORACLE_SID% @..\output\oradml_output.sql >  %CUR_PATH%02load_%FMTDATE%.log 2>&1
notepad  %CUR_PATH%02load_%FMTDATE%.log

データロード先の PostgreSQL Server

D:\exp_ora2dml\pgdml\02load.bat

@echo off
cd %~dp0
set CUR_PATH=%~dp0
call setenv.bat
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set FMTDATE=%date:~0,4%%date:~5,2%%date:~8,2%_%CUR_HH%%time:~3,2%%time:~6,2%
echo "load dml processing..."
psql.exe -f ..\output\pgdml_output.sql >  %CUR_PATH%02load_%FMTDATE%.log 2>&1
notepad  %CUR_PATH%02load_%FMTDATE%.log

注意）データロード先データベースには、oradml_output.sqlの出力に使用したのと同じ構造のテーブルが存在する必要があります。ロードは Truncate /Insert 方式で実行されるため、既存のデータは削除されます。

エラー対応

01make.bat実行ログに次のエラーが表示された場合は、D:\exp_ora2dml\instantclient_12_2にdllファイルが存在することを確認するか、exp2dml.iniのoracle_instantclient_dirキーの値..\instantclient_12_2と実際のディレクトリ名が同じであることを確認してください。

Traceback (most recent call last):
  File "exp2dml.py", line 100, in <module>
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
[12912] Failed to execute script 'exp2dml' due to unhandled exception!

02load.bat実行ログに次のエラーが表示された場合は、setenv.batの特殊環境変数PATHにpsql.exe実行ファイルパスが存在することを確認していない場合は追加します。

'psql.exe' は、内部コマンドまたは外部コマンド、
操作可能なプログラムまたはバッチ ファイルとして認識されていません。

システム開発者ノウハウ集(Zin’sつぶやき)

このブログを検索

ラベル

2022年2月15日火曜日

(Free Tool)Oracle Database TableデータをOracle用またはPostgreSQL用のInsert文に出力