bashからsqlplusを起動し、Oracleデータベースの出力を変数に取り込む方法(基本篇)

はじめに

bashからsqlplusを起動し、Oracleデータベースの内容を取り出した上で変数に取り込む方法についてまとめます。

本記事については以下の方針としています:

• 可能な限りbashスクリプト単体で動作すること。tmpファイルとして中間結果を保存したり、外部sqlファイルを読み込んだりしません。
• bashで使用できる機能は特に遠慮なく使用します。shへの後方互換性は留意しません。
• コマンド置換の手法として`command`あるいは$(command)のいずれかの方法が考えられますが、専ら$(command)の方を使用します。
• sqlplusへの接続はローカル接続を前提とします。リスナー接続は使用しません。
  • したがって、パスワードを入力したり接続文字列を使用したりはしていません。
  • マルチテナント化しているOracle Databaseの接続には一部制限が想定されます(マルチテナント化したデータベースへの接続については次回の記事で説明予定です)。

この辺りについては本記事の説明のためにコードを簡略化したい等が理由に含まれるため、上記に従わないと何か都合の悪いことが起こるということは特にありません。

共通の前提

これからいくつかの取得例を挙げたいと思いますが、いずれも以下の点の共通した前提がありますので、先に挙げておきます。

• sqlplusは-silent(あるいは-s)オプションを付与して起動します。これにより、SQLの実行結果を変数に取り込む際に邪魔となるSQL*Plusのバナーやプロンプトなどを出力させないようにすることができます。
• sqlplusの環境変数として以下の設定を行います。結果を変数を取り込む際に邪魔となるカラムのヘッダやフィードバックメッセージの出力を抑止します。

  SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
  

• sqlplusへのPATHの設定やORACLE_SID、ORACLE_HOMEの設定は省略していますので、環境によってはそれらを別途設定する必要があります。

取得したい結果別の取得例

単一の結果を取得して一つのbash変数に収める単純な場合もあれば、複数行×複数列の比較的大規模な結果をbash変数に収めて活用したい場合もあります。それぞれ分けて具体例を示します。

1. 単一の結果の取得

1行かつ1カラムの出力を取得し変数に取り込む簡単なケースです。
ヒアドキュメントを使用してsqlplusを実行し、コマンド置換を使用して出力を変数に取り込みます。
情報を取得するSQLについては、sysdateのように単一の結果が得られる(2行以上のデータを返さない)ものを使用します。

SYSDATE=$(sqlplus -silent "/ as sysdba" << EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
select sysdate from dual;
EXIT;
EOF
)
echo ${SYSDATE}

2番目のEOFは行頭にそれだけを記述し、最後の)についてはそれとは分けて記述します(そうしないとEOFを正しく認識せずエラーとなります)。以下は実行例です。

$ SYSDATE=$(sqlplus -silent "/ as sysdba" << EOF
> SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
> select sysdate from dual;
> EXIT;
> EOF
> )
$ echo ${SYSDATE}
10-JAN-24

2. 単一カラム×複数行の結果の取得

readarray -tを使用することで配列に取り込むことができます。
readarrayコマンドは、出力結果をそのままbashの配列に取り込むことが可能な大変強力なコマンドです。
ただし、bash 4以降でしか使用できない点は注意してください。
1.とは違い、使用するSQLは複数行が返される前提のものを使用します。

readarray -t USERS < <(sqlplus -silent "/ as sysdba" << EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
select username from dba_users;
EXIT;
EOF
)

実際の実行例です。取得した結果は配列として変数に入ります。
${USERS[0]}、${USERS[1]}といった表記で変数の内容を参照することができます。

$ readarray -t USERS < <(sqlplus -silent "/ as sysdba" << EOF
> SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
> select username from dba_users;
> EXIT;
> EOF
> )
$ echo ${USERS[0]}
SYS
$ echo ${USERS[1]}
SYSTEM
$

3. 複数カラム×複数行の結果の取得

複数カラム×複数行の結果の取得については、配列に取り込んで\${USERS[0][1]}や${USERS[3][2]}みたいな記述でスクリプト内で活用したいところですが、bashにはそのような2次元配列の概念がありません。

そのため、CSV形式で従来の配列に取り込み、cutコマンドを使用してn番目の取得カラムの情報を取る方法を考えます。

最初に述べた前提に加えて、ここでは以下の点を考慮します:

• readarrayを使用して取得結果を変数に取り込むに当たり、途中で意図しない改行が入ると想定通りに動作しません。従って、sqlplusの環境変数LINESIZEは余裕を持って大きめに設定します。
• LINESIZEでの設定を制御しても1行の長さが必要以上に大きくなりすぎる場合は、COLUMNコマンドを使用して各カラムの長さを個別に設定します。
  例: column username format a30
• デリミタ(区切り文字)としてCOLSEP ','を追加します(cutコマンドで利用するため)。

以上より、取得例は以下のようになります。

$ readarray -t USERS < <(sqlplus -silent "/ as sysdba" << EOF
> SET LINESIZE 500 PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF COLSEP ','
> select username, account_status from dba_users;
> EXIT;
> EOF
> )

実際の実行例です:

$ readarray -t USERS < <(sqlplus -silent "/ as sysdba" << EOF
> SET LINESIZE 500 PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF COLSEP ','
> select username, account_status from dba_users;
> EXIT;
> EOF
> )
$ echo ${USERS[0]} | cut -f1 -d',' # 1件目の1カラム目を表示
SYS
$ echo ${USERS[0]} | cut -f2 -d',' # 1件目の2カラム目を表示
OPEN
$ echo ${USERS[1]} | cut -f1 -d',' # 2件目の1カラム目を表示
SYSTEM
$ echo ${USERS[1]} | cut -f2 -d',' # 2件目の2カラム目を表示
OPEN
$ echo ${USERS[2]} | cut -f1 -d',' # 3件目の1カラム目を表示
XS$NULL
$ echo ${USERS[2]} | cut -f2 -d',' # 3件目の2カラム目を表示
EXPIRED & LOCKED
$

COLSEPには','以外の任意の文字を指定できますので、取得するデータに含まれない文字を選択してください。

COLSEPではなく連結演算子(||)を使用する場合

COLSEP ','を使用する方法の他、すべてのカラムを|| ',' ||でつなげて取得する方法も考えられます。結果をCSVで取得したい、となった時こちらの方法を先に検討する人も多いと思います。例えば以下のような方法ですね。

readarray -t USERS < <(sqlplus -silent "/ as sysdba" << EOF
SET LINESIZE 500 PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
select username ||','|| account_status from dba_users;
EXIT;
EOF
)

ただし、(SELECTのカラムの数-1)の数だけ|| ',' ||を使ってカラムの間を繋げないといけないのでやや面倒です。||を使用するのは、COLSEPを最初に検討してみて何らかのダメそうな事情が確認された場合に検討するのが良いでしょう。

まとめ

以上、bashとsqlplusの組み合わせでOracleデータベースからの情報を変数に取得する基本的な方法を簡単にまとめてみました。

なお、現行のサポートバージョンのOracle Databaseはマルチテナント化しており、従来よりも構造が複雑化しています。また、基本的にここに挙げた方法は1つの結果を得るために1回の新規接続が必要となりますので、効率的な問題もあります。

それらの問題点をうまく解決するための手法にはもう少し手の込んだ改善が必要となりますので、それについては次回の【応用篇】で紹介したいと思います。