月別アーカイブ: 2015年5月

oerrのメッセージを日本語で表示させる

今週の名言

「宇宙でもっとも強い力は、幅広い興味である。」
アルベルト・アインシュタイン

oerrとは?

前回からのつながりで、今回もエラー・メッセージについて考えてみたいと思います。

oerrという非常に便利なツールがあります。「ORA-4031」について意味を調べたい時は、以下の要領でエラーメッセージの意味だけでなく、原因や対処についての説明をシェルから次のコマンドで確認することができます。

$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.

ORA-以外のエラーメッセージ、例えば「TNS-12541」も以下のように確認することができます。

$ oerr tns 12541
12541, 00000, "TNS:no listener"
// *Cause: The connection request could not be completed because the listener
// is not running.
// *Action: Ensure that the supplied destination address matches one of
// the addresses used by the listener - compare the TNSNAMES.ORA entry with
// the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
// go by way of an Interchange). Start the listener on the remote machine.

oerrコマンドの正体は?

oerrは実はシェル・スクリプトで、$ORACLE_HOME/bin 以下にあります。

$ which oerr
/u01/app/oracle/product/11.2.0.4/db_1/bin/oerr

スクリプトの内容は以下の110行から成るリストのとおりですが

  • 35行目に定義されたfacility.lisから接頭語と製品名の関連を取得
  • 93行目で製品名ディレクトリ配下にあるmsgファイルを指定
  • 104行目以下のawkコマンドにより該当メッセージの取得

という仕様になっています。

$ cat /u01/app/oracle/product/11.2.0.4/db_1/bin/oerr
#!/bin/sh
#
# $Id: oerr.sh /st_buildtools_11.2.0/1 2012/02/28 11:07:18 mrmehta Exp $
# Copyright (c) 1994, 2012, Oracle and/or its affiliates. All rights reserved.
#
# Usage: oerr facility error
#
# This shell script is used to get the description and the cause and action
# of an error from a message text file when a list of error numbers are passed
# to it.  It supports different language environments and errors from different
# facilities.
#

#
# Turn on script tracing if, requested
[ "$ORACLE_TRACE" = "T" ] && set -x

#
# If ORACLE_HOME is not set, we will not be able to locate
# the message text file.
if [ ! "$ORACLE_HOME" ]
then
echo "ORACLE_HOME not set.  Please set ORACLE_HOME and try again." 1>&2
exit 1
fi

#
# Ignore user locale
LC_ALL=C
export LC_ALL

#
# Definition script "constants"
Facilities_File=$ORACLE_HOME/lib/facility.lis

#
# Check script usage
if [ "$#" != "2" ]
then
exec 1>&2
echo 'Usage: oerr facility error'
echo
echo 'Facility is identified by the prefix string in the error message.'
echo 'For example, if you get ORA-7300, "ora" is the facility and "7300"'
echo 'is the error.  So you should type "oerr ora 7300".'
echo
echo 'If you get LCD-111, type "oerr lcd 111", and so on.'
exit 1
fi

#
# Pickup the command line arguments
Facility="$1"
Platform=`/bin/uname`

case $Platform in
SunOS)
SedPath='/usr/xpg4/bin/sed'
;;
*)
SedPath='/bin/sed'
;;
esac
Code=`echo $2|$SedPath 's/^[0]*//'`

#
# Get the facility information from the oerr data file
Fac_Info=`grep -i "^${Facility}:" $Facilities_File 2> /dev/null`
if [ $? -ne 0 ]
then
echo "oerr: Unknown facility '$Facility'" 1>&2
exit 1
fi

#
# Parse the components from the Fac_Info string into Shell variables
eval `echo "$Fac_Info" | awk -F: '{
if (index ($3, "*") == 0)
printf ("Facility=%s\n", $3);
else
printf ("Facility=%s\n", $1);
printf ("Component=%s\n", $2);
}'`
if [ -z "$Facility" -o -z "$Component" ]
then
echo "oerr: Invalid facilities entry '$Fac_Info'" 1>&2
exit 1
fi

#
# The message file searched is always the US English file
Msg_File=$ORACLE_HOME/$Component/mesg/${Facility}us.msg
if [ ! -r $Msg_File ]
then
echo "oerr: Cannot access the message file $Msg_File" 1>&2
exit 1
fi

#
# Search the message file for the error code, printing the message text
# and any following comments which should give the cause and action for
# the error.
awk "BEGIN { found = 0; }
/^[0]*$Code/	{ found = 1; print ; next;}
/^\/\//		{ if (found) { print; } next; }
{ if (found) { exit; } }" $Msg_File

exit 0

例えば、$OEACLE_HOME/rdbms/mesg/oraus.msg ファイルの内容は以下のようになっています。

1865 00000, 00000, "normal, successful completion"
1866 // *Cause:  Normal exit.
1867 // *Action: None.
1868 00001, 00000, "unique constraint (%s.%s) violated"
1869 // *Cause: An UPDATE or INSERT statement attempted to insert a duplicatee key.
1870 //         For Trusted Oracle configured in DBMS MAC mode, you may see
1871 //         this message if a duplicate entry exists at a different levell.
1872 // *Action: Either remove the unique restriction or do not insert the key.
1873 /0002        reserved for v2 compatibility (null column)
1874 /0003        reserved for v2 compatibility (column value truncated)
1875 /0004        reserved for v2 compatibility (end-of-fetch)
1876 /0009        reserved for v2 compatibility
1877 /
1878 / 10 - 49 user session and session switching errors
1879 /
1880 00017, 00000, "session requested to set trace event"
1881 // *Cause:  The current session was requested to set a trace event by another
1882 //          session.
1883 // *Action: This is used internally; no action is required.

oerrは英語圏の人にとっては非常に便利なツールですが、Oracle 8.1.6を最後に各国語サポートへは対応されなくなっています。

エラー・メッセージだけでも日本語化してみる

原因や対処までは無理としても、せめてエラー・メッセージの意味だけでも日本語で表示させるツールを作ってみたいと思いましたので、以下のようなストアド・ファンクションを作ってみました。
試作版なのですべての接頭語には対応していませんし、エラーハンドリングも考慮していません。(存在しないメッセージ番号を指定すると「該当メッセージはありません」という表示をさせています。)

SQL> CREATE OR REPLACE FUNCTION show_errmsg (err_fac IN VARCHAR2,err_num IN PLS_INTEGER)
  2  RETURN VARCHAR2
  3  IS
  4   wrk_fac VARCHAR2(8);
  5   err_lan VARCHAR2(20) := 'japanese';
  6   err_msg VARCHAR2(512);
  7   i        PLS_INTEGER;
  8  BEGIN
  9   wrk_fac := UPPER(err_fac);
 10  CASE wrk_fac
 11     WHEN 'CRS'  THEN i := UTL_LMS.GET_MESSAGE(err_num,'crs',    err_fac,err_lan,err_msg);
 12     WHEN 'TNS'  THEN i := UTL_LMS.GET_MESSAGE(err_num,'network',err_fac,err_lan,err_msg);
 13     ELSE             i := UTL_LMS.GET_MESSAGE(err_num,'rdbms',  err_fac,err_lan,err_msg);
 14  END CASE;
 15  IF err_msg LIKE 'Message%not found%'
 16  THEN err_msg := wrk_fac || '-' || TO_CHAR(err_num,'FM00000') || ': 該当メッセージはありません';
 17  ELSE err_msg := wrk_fac || '-' || TO_CHAR(err_num,'FM00000') || ': ' || err_msg;
 18  END IF;
 19  RETURN err_msg;
 20  END;
 21  /

ファンクションが作成されました。

このファンクションの使用例は以下のようになります。

SQL> SELECT show_errmsg('ora',4031) ERROR_MESSAGE from dual;

ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
ORA-04031: 共有メモリーの%sバイトを割当てできません("%s"、"%s"、"%s"、"%s")

SQL> SELECT show_errmsg('crs',214) ERROR_MESSAGE from dual;

ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
CRS-00214: リソース'%s'を登録解除できません。

SQL> SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual;
facに値を入力してください: rman
numに値を入力してください: 567
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('rman',567) ERROR_MESSAGE from dual

ERROR_MESSAGE
--------------------------------------------------------------------------------
RMAN-00567: Recovery Managerで印刷できなかったエラー・メッセージがあります

SQL> /
facに値を入力してください: tns
numに値を入力してください: 12541
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('tns',12541) ERROR_MESSAGE from dual

ERROR_MESSAGE
--------------------------------------------------------------------------------
TNS-12541: TNS: リスナーがありません。

SQL> /
facに値を入力してください: ora
numに値を入力してください: 2
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('ora',2) ERROR_MESSAGE from dual

ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-00002: 該当メッセージはありません

SQL> /
facに値を入力してください: abc
numに値を入力してください: 1
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('abc',1) ERROR_MESSAGE from dual

ERROR_MESSAGE
--------------------------------------------------------------------------------
ABC-00001: 該当メッセージはありません

UTL_LMS.GET_MESSAGEはどこからメッセージを取得しているのか?

oerrが参照していたmsgファイルと同じディレクトリに「msb」という拡張子が付いたバイナリファイルがあるのですが、これらのファイルの中にメッセージが格納されているようです。

それでは、日本語と英語メッセージが格納されているであろうファイルをRenameして、メッセージが取得できなくなるかどうか確認してみましょう。

$ cd $ORACLE_HOME/rdbms/mesg
$ mv oraja.msb oraja.msb.bak
$ mv oraus.msb oraus.msb.bak
SQL> SELECT show_errmsg('ora',4031) ERROR_MESSAGE from dual;

ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-04031: 該当メッセージはありません

やはり、msbファイルが関係していました。

oerrは単純なテキストファイルの内容を表示させる仕様でしたが、Oracle9i以降は英語以外の言語でエラー・メッセージを取得するインターフェースはUTL_LMS.GET_MESSAGEだけになりました。

msbファイルの中にはmsgファイルのような原因や対処に関する説明は書いてあるのでしょうか?こればかりはOracle社の中の人でなければわかりませんが、将来そのような情報が使えるようになるといいですね。

今日はここまで

Apple Watchを1ヶ月使ってみて

発売初日にポチッ!

今までApple製品、特にiPhone/iPadは発売されてしばらく様子を見てから買うことが多かったのですが、Apple Watchに関しては発売初日4月12日の早朝16時過ぎに購入手続きをしました。

Apple Watch注文

今まで使っていたCASIOの腕時計GB-5600AAもiPhoneにBluetoothでペアリングして、電話やメールの着信を腕時計で知ることができるという「優れモノ」だったのですが、iOSがバージョンアップされるたびにペアリングが不調になり、最近はほとんど単なる腕時計としてしか使っていませんでした。

買ってから3年経って、水銀電池の残量が少なくなっているという警告がしばしば出ていたのと、今回は娘がプレゼントしてくれるというので、発売初日に思い切って注文しました。

10日もフライングで到着

購入完了メールがすぐに送られてきましたが、到着予定日には目がテンになってしまいました。最悪で1ヶ月半後!ありえん!!と思いました。(結局これは中国製部品が不良だったことによる納期の遅れだったらしいですが。)

ところが嬉しい誤算もあるものです。4月29日に明日到着予定というメールが来て、翌日無事に現物が到着しました。

Apple Watch開封の儀

腕時計としてのApple Watch

2015053001
Apple Watchは写真の「ユーティリティ」と呼ばれる自由度が高いアナログタイプの標準デザインを始め、全部で10種類もの文字盤のバリエーションを自分の好みで自由に切り替えることができます。

「ユーティリティ」の場合、文字盤の時間・分表示や秒針の色を変えることができますが、私の場合秒針はにするのが一番自然に感じられました。(ちなみに秒針はカチッカチッとメカニカルな動きではなく連続的に動きます。)

Apple Watchは基本的にiPhoneと常にペアリングした状態で使うので(つまり、カバンの中でもよいのでiPhoneは常に近くにあることが原則です。)

iPhoneはネットワークで時刻補正が行われているので、Apple Watchには常に正確な時刻が表示されます。iPhoneの時刻補正は以前正しく機能しなくてかなりイライラしたのですが、今では正確性に関しては非常に信頼を置いています。

また、Apple Watchを使っていて非常に心地よいのは、「手首検出機能」で手首を上げた時だけ表示がONになるところです。一日中身につけていてしかもバッテリが小さいので充電なしでどれくらい持つのか非常に気になっていましたが、普段は表示がOFFになっているので、思ったほどバッテリは消費しません。寝る前に30%を切ることはこの1ヶ月間皆無でした。

情報量が多いモジュラー

2015053002

アナログ時計もいいですが、私が一番使うことが多いのは「モジュラー」と呼ばれるデジタル表示です。

何と言っても情報量が多いのがよいです。表示させる情報は自由にカスタマイズすることが可能ですが、私の場合、左上に「曜日・日」を、真ん中に「スケジュール」を、下段左から「気温」「タイマー」「アクティビティ」を表示させています。

これらの表示域をタップすると、それぞれに関係したアプリケーションが起動されます。

この1ヶ月間カスタマイズをいろいろ試行錯誤してきましたが、最近はこの表示に落ち着いています。

グランスにはあまり詰め込みすぎない

2015053003

文字盤を下から上にスワイプすると表示される、文字通り「ちらっと見る」という意味のグランス機能は頻繁に使うアプリケーションを登録するのに便利な機能です。

グランスで表示させたアプリケーション画面を、さらに左右にスワイプさせると別のアプリケーションに切り替えることができますが、あまり多くのアプリケーションをグランスに追加する(iPhoneアプリで緑+をタップする)と、欲しい情報にたどり着くのが大変になりますので、必要最小限のアプリに絞り込むことがコツだと思います。

それから、時計表示画面からとグランスからアプリを起動した場合は、デジタルクラウンを押すとアプリケーションが終了し、再び時計表示画面に戻ります。

ホーム画面の整理法

腕時計の竜頭に似た「デジタルクラウン」を1回押すと、ホーム画面と呼ばれる、アイコンが沢山並んだ画面が表示されます。

2015053004

時計表示からホーム画面を表示させると、時計アイコンを中心にしたホーム画面になります。

起動させたいアプリケーションのアイコンが中心になるように動かして、アイコンをタップするかデジタルクラウンを回すとアプリケーションが起動されます。

周辺のアイコンは小さくしか表示されませんし、画面をはみ出たアイコンは表示されませんので、起動する頻度の高いアプリケーションほど中央の時計アイコンに近い場所に配置した方が使いやすいです。

特に時計アイコンの周りの6つのポジションはiPhoneのドックに相当する大事な位置です。

ホーム画面が時計以外のアイコンが中央にある状態で、デジタルクラウンを1回押すと時計アイコンを中央にするように画面全体を動かします。

そしてもう一度、デジタルクラウンを押すと時計表示になります。

従って、どんな状態でも2回デジタルクラウンを押すと、時計表示に戻ることができます。

レイアウト変更はiPhoneアプリで

iPhone上でアイコンの位置を変更する要領でApple Watch上でもアイコンを長押しして変更モードにした後レイアウト変更を行うことは可能です。ただし、元々小さなApple Watch画面上で行うのはかなり大変です。(つい最近までそういう変更方法があることすら知りませんでした。)

従って、レイアウト変更はiPhoneのApple Watchアプリ上で行います。

2015053005-1

上で説明したように、中央の時計アイコンを中心とした位置が重要ですので、上の写真の赤枠で囲った範囲に頻繁に使うアイコンを配置します。特に時計アイコンの周りの6つのポジションはよく考えて配置します。

時計表示画面から直接起動できる、カレンダーやタイマー、天気アプリなどは赤枠の外に配置するようにします。

また、グランスに登録したアプリもなるべく外側に配置するようにします。(例外もありますが)

サイドボタンはほとんど使わない

2015053006

デジタルクラウンの隣(写真だと向かって左)にある「サイドボタン」はあらかじめ登録しておいた連絡先に素早くコミュニケーションを取るためのボタンです。

私だけかもしれませんが、このボタンを使ったことはほとんどありません。

なぜなら、電話をかけたりメッセージを送ったりするのはiPhoneから行う方が楽なので、このボタンを使う必要性を感じたことがないからです。(実はもっと賢い使い方があるのかもしれませんが)

サイドボタンを押しながらデジタルクラウンを押すと、画面のスナップショットが撮れるのでそれくらいしか使ったことはありません。

iPhone/iPadは、ソフトウェア・アップデートでボタンに割り当てられる機能が変更されたりしていろいろ進化しているので、Apple Watchもそのようになることを期待しています。

保護フィルムはあると安心

届いて2週間くらいは、素のままでApple Watchを使っていたのですが、このままだと絶対に表面を傷つけてしまいそうだったので、保護フィルムを貼ることにしました。

いろいろ探した結果 Wrapsol(ラプソル)擦れ傷・割れ防止 衝撃吸収フィルム Apple Watch対応【2枚入り】(42mm) A005-IWC42 というのをAmazonで買いました。

急いで貼ったので最初わずかに気泡が入ったりしましたが、タオルでこすったりいろいろしていたら次に日には気泡が抜けてぴったり貼れていました。

素のままに比べると若干柔らかい感触になりますが、動作が遅くなったりすることは全くないので、細かい擦り傷等を心配する必要がなくなる安心感は大きいです。

1ヶ月使った感想まとめ

単純に小さくて軽いデバイスであればiPod nanoとか今までもにもありましたが、これだけの多機能を腕時計に詰め込んでそれなりに使えるものに仕上がっているのはさすがAppleと思いました。でも、細かい部分ではまだまだ改善の余地があると思います。

例えばアプリケーションによっては、起動に時間がかかりすぎてイライラするものもあります。

iPhoneから同じアプリを起動してもあまりそのようなことを感じないので、やはり遅いのは気になります。

腕を上げてチラッと見る(グランス)間に期待したレスポンスがないと、腕を不自然に上げたままになっていたりして「俺何やってるんだろう」という気持ちになります。

近い将来Apple Watchアプリはネイティブ(Apple Watch向けに最適化される仕様)になるという話もありますので、今後の進歩に期待です。

iPhoneがなければほとんど何もできない高い時計、という点は特に家族の理解をまだ得られていない気がしますが、iPhoneの出先デバイスとしてiPhone自体をポケットやカバンに入れたままにしている時間が長くなったというのは、ちょっと驚きでした。こんなことだったらiPhone6じゃなくてiPhone6 Plusにしておけばよかったです。

混雑した駅とかでのいわゆる「歩きスマホ」が問題になっていますが、Apple Watchのようなウェアラブル・デバイスは持っていることを忘れてしまうくらいの自然な感覚が特にいいです。

アプリケーションをいっぱい詰め込んで何にでも使えるデバイスという考えは捨てるべきで、本当に使えるものだけに極力絞り込む使い方が正しいと思います。

今後も気づいたTipsがあれば積極的に紹介していきます。

今回はここまで

 

ORAエラーはいくつある?訂正しました。

今週の名言

「勇気とは不安を感じないことではない。勇気とは不安をコントロールすることだ。」
マーク・トウェイン

すみません、大嘘ついてました。(^^;

4月12日の投稿ORAエラーはいくつある?でORAエラーの数を数えてみたのですが、結果が大間違いであることがわかりました。

間違いの内容は既に記事を訂正していますのでそちらを見ていただければよいと思いますが、1ヶ月以上経っているのにこの件に対して誰からも何のツッコミもなかったということは、このブログもまだまだだなという悲しい現実を再認識させられました。

でも、他の人に指摘される前に自分で見つけられて正直ホッとしているというのも事実ですので、今後も自分できちんと検証し自信を持って記事を書いていくという方針は貫いていきたいと思います。

UTL_LMSパッケージを使う

最初に結論を書きますが、Oracleのエラー・メッセージを取得するにはUTL_LMS.GET_MESSAGEファンクションを使います。

このファンクションはかなり使えるので後で他の応用も紹介しますが、以前紹介したORAエラーの数を数えるのは以下のPL/SQLスクリプトで可能です。(環境はOracle 11.2.0.4)

SQL> set serveroutput on
SQL> DECLARE
  2   err_msg LONG;
  3   err_cod PLS_INTEGER;
  4   i       PLS_INTEGER;
  5   cnt     NUMBER :=0;
  6  BEGIN
  7   FOR err_cod IN 1..99999 LOOP
  8    i := UTL_LMS.GET_MESSAGE (err_cod, 'rdbms', 'ora', 'japanese', err_msg);
  9    IF err_msg not like 'Message%not found%' THEN
 10  --   DBMS_OUTPUT.PUT_LINE ('ORA-' || TO_CHAR (err_cod, 'FM00000') || ': ' || err_msg);
 11     cnt := cnt +1;
 12    END IF;
 13   END LOOP;
 14   DBMS_OUTPUT.PUT_LINE('Nuber of Error Code : ' || cnt);
 15  END;
 16  /

Nuber of Error Code : 18414

PL/SQL procedure successfully completed.

このスクリプト例ではあえてコメントアウトしていますが、11行目のコメントを外すとメッセージが得られます。

「ORAエラーはいくつある?」という問いに対しては18,414個という結果を答えとして挙げておきます。
(お時間のある方、是非数えてみてください。)

応用編:CRSエラーの数を数えてみる

SQLERRMファンクションは、ORAエラーのみを表示します(しかも余計な番号も含む)が、UTL_LMS.GET_MESSAGEファンクションは第2引数と第3引数を変えると、Oracle RDBMS以外の製品が出力するエラー・メッセージを取得することができます。(第4引数を変えると表示する言語を選択することができます。)

例えば、Oracle Grid Infrastructureのエラー・メッセージの場合は「crs」と「crs」を指定して以下のように実行します。(ハイライト行に注目)

SQL> DECLARE
  2   err_msg LONG;
  3   err_cod PLS_INTEGER;
  4   i       PLS_INTEGER;
  5   cnt     NUMBER :=0;
  6  BEGIN
  7   FOR err_cod IN 1..99999 LOOP
  8    i := UTL_LMS.GET_MESSAGE (err_cod, 'crs', 'crs', 'japanese', err_msg);
  9    IF err_msg not like 'Message%not found%' THEN
 10  --   DBMS_OUTPUT.PUT_LINE ('CRS-' || TO_CHAR (err_cod, 'FM00000') || ': ' || err_msg);
 11     cnt := cnt +1;
 12    END IF;
 13   END LOOP;
 14   DBMS_OUTPUT.PUT_LINE('Nuber of Error Code : ' || cnt);
 15  END;
 16  /

Nuber of Error Code : 1778

PL/SQL procedure successfully completed.

CRSエラーは1,778個あることがわかりました。

今回はここまで

カレンダーで遊んでみる②

今週の名言

「幸せを与えてくれるのは、富でも豪華さでもなく、穏やかさと仕事である。」
トーマス・ジェファーソン

各カレンダーを比べてみる

Oracle Database 11gR2 で和暦を含む7つのカレンダーをサポートしていることは前回紹介しました。(12cではさらに1つ追加)
私たち日本人は西暦(グレゴリオ暦)と和暦しか馴染みがないのですが、他のカレンダーを使う機会はまずないので、それらでちょっと遊んでみたいと思います。

2015年1月のカレンダーで比較する

例として、2015年1月を各カレンダーで表示させてみたいと思います。
1月1日のデータは作成しているので2日から31日までのデータを追加します。

SQL> INSERT INTO jcal VALUES (11,TO_DATE('20150102','YYYYMMDD'));

1 row created.

SQL> INSERT INTO jcal VALUES (12,TO_DATE('20150103','YYYYMMDD'));

1 row created.
....................................................................
SQL> INSERT INTO jcal VALUES (39,TO_DATE('20150130','YYYYMMDD'));

1 row created.

SQL> INSERT INTO jcal VALUES (40,TO_DATE('20150131','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

それでは、6つのカレンダーを比較してみましょう。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''')         "グレゴリオ暦"
  4  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Arabic Hijrah''')     "イスラム暦"
  5  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Persian''')           "ペルシャ暦"
  6  ,TO_CHAR(sdate,'eeyy.mm.dd', 'NLS_CALENDAR = ''Japanese Imperial''') "和暦"
  7  ,TO_CHAR(sdate,'eeyyy.mm.dd','NLS_CALENDAR = ''ROC Official''')      "台湾暦"
  8  ,TO_CHAR(sdate,'eeYYYY.MM.DD','NLS_CALENDAR = ''Thai Buddha''')      "タイ仏教暦"
  9  FROM jcal
 10  WHERE id = 1 OR id BETWEEN 11 AND 40
 11  ORDER BY sdate
 12 ;

 ID グレゴリオ暦  イスラム暦  ペルシャ暦  和暦          台湾暦             タイ仏教暦
--- ------------ ---------- ---------- ------------- ----------------- --------------------
  1 2015.01.01   1436.03.10 1393.10.11 平成27.01.01  中華民國104.01.01  พุทธศักราช2558.01.01
 11 2015.01.02   1436.03.11 1393.10.12 平成27.01.02  中華民國104.01.02  พุทธศักราช2558.01.02
 12 2015.01.03   1436.03.12 1393.10.13 平成27.01.03  中華民國104.01.03  พุทธศักราช2558.01.03
 13 2015.01.04   1436.03.13 1393.10.14 平成27.01.04  中華民國104.01.04  พุทธศักราช2558.01.04
 14 2015.01.05   1436.03.14 1393.10.15 平成27.01.05  中華民國104.01.05  พุทธศักราช2558.01.05
 15 2015.01.06   1436.03.15 1393.10.16 平成27.01.06  中華民國104.01.06  พุทธศักราช2558.01.06
 16 2015.01.07   1436.03.16 1393.10.17 平成27.01.07  中華民國104.01.07  พุทธศักราช2558.01.07
 17 2015.01.08   1436.03.17 1393.10.18 平成27.01.08  中華民國104.01.08  พุทธศักราช2558.01.08
 18 2015.01.09   1436.03.18 1393.10.19 平成27.01.09  中華民國104.01.09  พุทธศักราช2558.01.09
 19 2015.01.10   1436.03.19 1393.10.20 平成27.01.10  中華民國104.01.10  พุทธศักราช2558.01.10

 ID グレゴリオ暦  イスラム暦  ペルシャ暦  和暦          台湾暦             タイ仏教暦
--- ------------ ---------- ---------- ------------- ----------------- --------------------
 20 2015.01.11   1436.03.20 1393.10.21 平成27.01.11  中華民國104.01.11  พุทธศักราช2558.01.11
 21 2015.01.12   1436.03.21 1393.10.22 平成27.01.12  中華民國104.01.12  พุทธศักราช2558.01.12
 22 2015.01.13   1436.03.22 1393.10.23 平成27.01.13  中華民國104.01.13  พุทธศักราช2558.01.13
 23 2015.01.14   1436.03.23 1393.10.24 平成27.01.14  中華民國104.01.14  พุทธศักราช2558.01.14
 24 2015.01.15   1436.03.24 1393.10.25 平成27.01.15  中華民國104.01.15  พุทธศักราช2558.01.15
 25 2015.01.16   1436.03.25 1393.10.26 平成27.01.16  中華民國104.01.16  พุทธศักราช2558.01.16
 26 2015.01.17   1436.03.26 1393.10.27 平成27.01.17  中華民國104.01.17  พุทธศักราช2558.01.17
 27 2015.01.18   1436.03.27 1393.10.28 平成27.01.18  中華民國104.01.18  พุทธศักราช2558.01.18
 28 2015.01.19   1436.03.28 1393.10.29 平成27.01.19  中華民國104.01.19  พุทธศักราช2558.01.19
 29 2015.01.20   1436.03.29 1393.10.30 平成27.01.20  中華民國104.01.20  พุทธศักราช2558.01.20

 ID グレゴリオ暦  イスラム暦  ペルシャ暦  和暦          台湾暦             タイ仏教暦
--- ------------ ---------- ---------- ------------- ----------------- --------------------
 30 2015.01.21   1436.03.30 1393.11.01 平成27.01.21  中華民國104.01.21  พุทธศักราช2558.01.21
 31 2015.01.22   1436.04.01 1393.11.02 平成27.01.22  中華民國104.01.22  พุทธศักราช2558.01.22
 32 2015.01.23   1436.04.02 1393.11.03 平成27.01.23  中華民國104.01.23  พุทธศักราช2558.01.23
 33 2015.01.24   1436.04.03 1393.11.04 平成27.01.24  中華民國104.01.24  พุทธศักราช2558.01.24
 34 2015.01.25   1436.04.04 1393.11.05 平成27.01.25  中華民國104.01.25  พุทธศักราช2558.01.25
 35 2015.01.26   1436.04.05 1393.11.06 平成27.01.26  中華民國104.01.26  พุทธศักราช2558.01.26
 36 2015.01.27   1436.04.06 1393.11.07 平成27.01.27  中華民國104.01.27  พุทธศักราช2558.01.27
 37 2015.01.28   1436.04.07 1393.11.08 平成27.01.28  中華民國104.01.28  พุทธศักราช2558.01.28
 38 2015.01.29   1436.04.08 1393.11.09 平成27.01.29  中華民國104.01.29  พุทธศักราช2558.01.29
 39 2015.01.30   1436.04.09 1393.11.10 平成27.01.30  中華民國104.01.30  พุทธศักราช2558.01.30

 ID グレゴリオ暦  イスラム暦  ペルシャ暦  和暦          台湾暦             タイ仏教暦
--- ------------ ---------- ---------- ------------- ----------------- --------------------
 40 2015.01.31   1436.04.10 1393.11.11 平成27.01.31  中華民國104.01.31  พุทธศักราช2558.01.31

31 rows selected.

西暦と年だけが異なるもの、月日も異なるもの、いろいろな違いがあることがわかります。
タイ仏教暦などはなんて書いてあるかもわかりませんね。

各カレンダーの紀元を調べてみる

それでは、各カレンダーで「1年1月1日」というデータを作成して、西暦(グレゴリオ暦)で確認してみましょう。

イスラム暦

イスラム暦に関してはOracle Databaseでは「Arabic Hijrah(イスラム暦)」と「English Hijrah(英語版イスラム暦)」をサポートしていますが、両者の正確な違いは確認していません。
今回は「Arabic Hijrah(イスラム暦)」を使います。
ちなみにWikipediaでは次のように紹介されています。
イスラム暦(ヒジュラ暦)

SQL> ALTER SESSION SET NLS_CALENDAR='Arabic Hijrah';

Session altered.

SQL> INSERT INTO jcal VALUES (41,TO_DATE('00010101','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') "A.D."
  4  FROM jcal
  5  WHERE id = 41;

 ID A.D.
--- ----------
 41 0622.07.16

上記Wikipediaのリンクには「預言者ムハンマドがマッカからマディーナへ聖遷(ヒジュラ)したユリウス暦622年を「ヒジュラの年」と定めヒジュラ暦元年とする新たな暦を制定した。なお、ヒジュラがあったとされる正確な日付は同622年7月16日(ユリウス通日1948439日)である。」との記述があります。

ペルシャ暦

これまたWikipediaからの引用になりますが次のリンクに解説があります。
ペルシャ暦(イラン暦)

SQL> ALTER SESSION SET NLS_CALENDAR='Persian';

Session altered.

SQL> INSERT INTO jcal VALUES (42,TO_DATE('00010101','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') "A.D."
  4  FROM jcal
  5  WHERE id = 42;

 ID A.D.
--- ----------
 42 0622.03.21

紀元はヒジュラ暦と同じ年ですが、年初がグレゴリオ暦の3月21日となっているところが特徴です。

台湾暦

台湾暦とは「民国紀元」と呼ばれ、中華民国が成立された1912年を紀元とする暦です。
民国紀元(Wikipedia)
中華民国は、1911年の辛亥革命の結果、アジアで史上初の共和制国家として成立しました。
「台湾暦」というくらいなので中国(中華人民共和国)でこの暦法は使用されておらず、西暦が用いられているそうです。

SQL> ALTER SESSION SET NLS_CALENDAR='ROC Official';

Session altered.

SQL> INSERT INTO jcal VALUES (43,TO_DATE('00010101','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') "A.D."
  4  FROM jcal
  5  WHERE id = 43;

 ID A.D.
--- ----------
 43 1912.01.01

ちなみに、民国紀元の年は日本の大正(元年から15年のみ)あるいは北朝鮮の主体暦(チュチェ暦-金日成の生誕年が紀元)と偶然同じになっています。
北朝鮮のOracle使いは裏技で台湾暦を使っているかもしれません。(冗談)
また、台湾では「中華民國99年」から「中華民國100年」(西暦2011年)にかけて、年データを一部のシステムにおいて2桁で管理していたことによる、いわゆる「民国100年問題」というどこかで聞いたことがある問題が起きたそうです。

タイ仏教暦

これもWikipediaからの受け売りですが、タイ仏教暦は「仏滅紀元(Wikipedia)」とも呼ばれ、紀元はお釈迦様が入滅(死去)された年だそうです。
この年は宗教上の伝来によるものとされているようで、学術的に言われている年とは一致しないようですが、Oracleの仕様上はこの年が採用されています。

他の暦法と違って、(グレゴリオ暦)の紀元前から始まっているので、日付書式も若干異なっていることに注意してください。

SQL> ALTER SESSION SET NLS_CALENDAR='Thai Buddha';

Session altered.

SQL> INSERT INTO jcal VALUES (44,TO_DATE('00010101','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'BC YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') sdate
  4  FROM jcal
  5  WHERE id = 44;

 ID SDATE
--- --------------------
 44 紀元前 0542.01.01

日付書式BC/ADについて

上の問い合わせで日付書式「BC」は「紀元前」と日本語で表示されましたが、これは以下のように「NLS_DATE_LANGUAGE」パラメータが「JAPANESE」であることによります。
このパラメータは「NLS_LANGUAGE」パラメータから導出され、さらに「NLS_LANGUAGE」パラメータは環境変数「NLS_LANG」の先頭に指定された言語によって決まります。

SQL> SELECT PARAMETER,VALUE FROM NLS_SESSION_PARAMETERS
  2  WHERE PARAMETER LIKE 'NLS_%LANGUAGE';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   JAPANESE
NLS_DATE_LANGUAGE              JAPANESE

SQL> !env|grep NLS_LANG
NLS_LANG=Japanese_Japan.AL32UTF8

また、NLS_DATE_LANGUAGE=JAPANESEの環境においては、以下のように日付書式「BC」は日付書式「AD」と同じ結果を返す、つまり日付データが西暦0年よりも前であればどちらも「紀元前」と表示されるようです。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'AD YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') sdate
  4  FROM jcal
  5  WHERE id = 44;

 ID SDATE
--- --------------------
 44 紀元前 0542.01.01

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'AD YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') sdate
  4  FROM jcal
  5  WHERE id = 43;

 ID SDATE
--- --------------------
 43 西暦 1912.01.01

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'BC YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') sdate
  4  FROM jcal
  5  WHERE id = 43;

 ID SDATE
--- --------------------
 43 西暦 1912.01.01

前回から2回に渡ってカレンダーでいろいろ遊んでみましたがいかがでしたでしょうか?

このように日付データはDATE型で格納しておけば、様々な日付書式が使えるだけでなく、「2月30日」のような明らかに不正なデータが混入する危険を防ぐことができるため大変有益です。

ところが、未だに文字型8桁のようなフォーマットで日付データを保持しているシステムを見ることがあります。
どうしてそのような設計になるのか理由はわかりませんが、わずかなパフォーマンス上のメリットを追求するあまりデータの正確性を犠牲にするような設計は根本的に間違っていると思います。

終わり

カレンダーで遊んでみる①

今週の名言

「知識に対する投資は常に一番の利益を生み出す」
- ベンジャミン・フランクリン -

Oracleと暦法

以下はOracleデータベースがサポートする暦法(カレンダー)です。

Oracle® Databaseグローバリゼーション・サポート・ガイド
11gリリース2 (11.2)
B56307-04
NLS_CALENDAR 参照

  • Arabic Hijrah(イスラム歴)
  • English Hijrah(英語版イスラム歴)
  • Gregorian(グレゴリオ暦)
  • Japanese Imperial(日本の元号暦)
  • Persian(ペルシャ暦)
  • ROC Official(台湾暦)
  • Thai Buddha(タイ仏教暦)
  • Ethiopian(エチオピア歴、12c〜)

こんなにも多くのカレンダーを使うことができるというのも驚きですが、我々日本人にとって元号歴がサポートされているというのは非常にありがたいことです。(でも、どの年号からサポートされているのでしょうか?まさか「大化」というとこはないでしょうが。)

昭和天皇が崩御された時、私は某官公庁で大型汎用計算機の仕事をしていました。(Oracleアーキテクチャをどのように理解するか 参照)

官公庁における報告書等は基本的に和暦が使用されますので、元号が「昭和」から「平成」に変わるということは非常に大変でした。

西暦から和暦に変換するファンクションを作成していたのですが、「平成01年」ではなくて「平成年」と表示させてくれという仕様変更があり大慌てで修正したことを今でもよく覚えています。

その当時の苦労から比べると、今回紹介する和暦変換は非常に簡単ですので、是非業務に活用していただければと思います。

検証環境

今回はOracle11g R2(Oracle Linux6)の環境を使用します。
NLS_LANGは日本語表示の設定であることを確認します。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> !uname -rs
Linux 3.8.13-44.el6uek.x86_64

SQL> !env|grep NLS_LANG
NLS_LANG=Japanese_Japan.AL32UTF8

テーブル作成とデータ作成

日付データを格納する単純なテーブルを作成し、特定の日付データ(西暦)を挿入します。

SQL> CREATE TABLE jcal (id NUMBER,sdate DATE);

Table created.

SQL> INSERT INTO jcal VALUES (1,to_date('20150101','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (2,to_date('20141231','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (3,to_date('19890108','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (4,to_date('19890107','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (5,to_date('19261225','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (6,to_date('19261224','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (7,to_date('19120730','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (8,to_date('19120729','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (9,to_date('18680908','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (10,to_date('18680907','yyyymmdd'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  FROM jcal
  5  ORDER BY sdate
  6 ;

  ID A.D.
---- ----------
  10 1868.09.07
   9 1868.09.08
   8 1912.07.29
   7 1912.07.30
   6 1926.12.24
   5 1926.12.25
   4 1989.01.07
   3 1989.01.08
   2 2014.12.31
   1 2015.01.01

10 rows selected.

10件のデータが作成されました。

和暦変換した日付を表示させる

とりあえず和暦変換した日付を表示させましょう。
ポイントは「NLS_CALENDAR=’Japanese Imperial’」の指定です。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  ORDER BY sdate
  7 ;
,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
 *
ERROR at line 4:
ORA-01863: この年は現行カレンダではサポートされていません

おっと!いきなりエラーとなってしまいました。

サポートされるのは、明治元年9月8日以降

明治天皇が即位されたのは、1898年9月8日(慶応4年9月8日)で、この日を「明治元年9月8日」としたということです。
また、遡って「慶応4年1月1日」を「明治元年1月1日」と正式に定めたそうです。(Wikipedia「明治」参照。)

「NLS_CALENDAR=’Japanese Imperial’」では、1898年1月1日から9月7日までを、和暦(明治元年)で表示させようとするとエラーになるようです。(当然「慶応」以前の元号はサポートしていません。)

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  ORDER BY sdate
  7 ;
,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
 *
ERROR at line 4:
ORA-01863: この年は現行カレンダではサポートされていません

日付範囲を指定し直して再挑戦

今度は、「1898年9月7日」のデータを除外して再挑戦してみましょう。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  WHERE id < 10
  7  ORDER BY sdate
  8 ;

  ID A.D.       WAREKI-S
---- ---------- ----------
   9 1868.09.08 M01.09.08
   8 1912.07.29 M45.07.29
   7 1912.07.30 T01.07.30
   6 1926.12.24 T15.12.24
   5 1926.12.25 S01.12.25
   4 1989.01.07 S64.01.07
   3 1989.01.08 H01.01.08
   2 2014.12.31 H26.12.31
   1 2015.01.01 H27.01.01

9 rows selected.

エラーとなる和暦変換データがないので、クエリーは正常な結果を返します。
「eyy」の日付書式は「年号(短縮表記)+年(2桁)」を示します。

年号(漢字表記)を表示させる

「eeyy」の日付書式は「年号(漢字表記)+年(2桁)」を示します。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''') "WAREKI-L"
  6  FROM jcal
  7  WHERE id < 10
  8  ORDER BY sdate
  9 ;

  ID A.D.       WAREKI-S   WAREKI-L
---- ---------- ---------- ------------------
   9 1868.09.08 M01.09.08  明治01年09月08日
   8 1912.07.29 M45.07.29  明治45年07月29日
   7 1912.07.30 T01.07.30  大正01年07月30日
   6 1926.12.24 T15.12.24  大正15年12月24日
   5 1926.12.25 S01.12.25  昭和01年12月25日
   4 1989.01.07 S64.01.07  昭和64年01月07日
   3 1989.01.08 H01.01.08  平成01年01月08日
   2 2014.12.31 H26.12.31  平成26年12月31日
   1 2015.01.01 H27.01.01  平成27年01月01日

9 rows selected.

応用編:「01年」を「元年」と表示させる

DECODE関数を使って、「01年」のみ「元年」と表示させてみます。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,DECODE(TO_CHAR(sdate,'yy"','nls_calendar = ''Japanese Imperial''')
  6        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')
  7        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')) "WAREKI-L"
  8  FROM jcal
  9  WHERE id < 10
 10  ORDER BY sdate
 11 ;

  ID A.D.       WAREKI-S   WAREKI-L
---- ---------- ---------- ------------------
   9 1868.09.08 M01.09.08  明治元年09月08日
   8 1912.07.29 M45.07.29  明治45年07月29日
   7 1912.07.30 T01.07.30  大正元年07月30日
   6 1926.12.24 T15.12.24  大正15年12月24日
   5 1926.12.25 S01.12.25  昭和元年12月25日
   4 1989.01.07 S64.01.07  昭和64年01月07日
   3 1989.01.08 H01.01.08  平成元年01月08日
   2 2014.12.31 H26.12.31  平成26年12月31日
   1 2015.01.01 H27.01.01  平成27年01月01日

9 rows selected.

曜日も表示させる

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,DECODE(TO_CHAR(sdate,'yy"','nls_calendar = ''Japanese Imperial''')
  6        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')
  7        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')) "WAREKI-L"
  8  ,TO_CHAR(sdate,'day') day
  9  FROM jcal
 10  WHERE id < 10
 11  ORDER BY sdate
 12 ;

  ID A.D.       WAREKI-S   WAREKI-L           DAY
---- ---------- ---------- ------------------ --------
   9 1868.09.08 M01.09.08  明治元年09月08日    火曜日
   8 1912.07.29 M45.07.29  明治45年07月29日    月曜日
   7 1912.07.30 T01.07.30  大正元年07月30日    火曜日
   6 1926.12.24 T15.12.24  大正15年12月24日    金曜日
   5 1926.12.25 S01.12.25  昭和元年12月25日    土曜日
   4 1989.01.07 S64.01.07  昭和64年01月07日    土曜日
   3 1989.01.08 H01.01.08  平成元年01月08日    日曜日
   2 2014.12.31 H26.12.31  平成26年12月31日    水曜日
   1 2015.01.01 H27.01.01  平成27年01月01日    木曜日

9 rows selected.

セッション単位で暦法を変更する

NLS_CALENDARは、「TO_CHAR」SQL関数だけでなく、ALTER SESSION、初期化パラメータおよび環境変数で指定することが可能です。
ここでは、ALTER SESSIONで変更する方法を示します。

SQL> ALTER SESSION SET NLS_CALENDAR='Japanese Imperial';

Session altered.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'eyy.mm.dd') "WAREKI-S"
  4  ,DECODE(TO_CHAR(sdate,'yy"')
  5        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"')
  6        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"')) "WAREKI-L"
  7  ,TO_CHAR(sdate,'day') day
  8  FROM jcal
  9  WHERE id < 10
 10  ORDER BY sdate
 11 ;

  ID WAREKI-S   WAREKI-L           DAY
---- ---------- ------------------ --------
   9 M01.09.08  明治元年09月08日    火曜日
   8 M45.07.29  明治45年07月29日    月曜日
   7 T01.07.30  大正元年07月30日    火曜日
   6 T15.12.24  大正15年12月24日    金曜日
   5 S01.12.25  昭和元年12月25日    土曜日
   4 S64.01.07  昭和64年01月07日    土曜日
   3 H01.01.08  平成元年01月08日    日曜日
   2 H26.12.31  平成26年12月31日    水曜日
   1 H27.01.01  平成27年01月01日    木曜日

9 rows selected.

このテーマは1回限りの小ネタのつもりでしたが、ちょっと面白くなってきたので次回も遊んでみたいと思います。

続く

12c新機能「Identity Column」の検証⑤

今週の名言

「無知を恐れてはいけない。偽りの知識を恐れよ。」
ブレーズ・パスカル

「独立・依存エンティティ」をIdentity Columnで実装する

独立・依存エンティティそれでは、前回紹介した「独立・依存エンティティ(テーブル)」をIdentity Columnで実装してみましょう。
まずは、親表であるT_PAR表から作成します。Identity Columnのオプションは「ALWAYS」です。
忘れずに主キーも作成します。

SQL> CREATE TABLE t_par
  2  (pid  NUMBER GENERATED ALWAYS AS IDENTITY
  3  ,pval VARCHAR2(10)
  4  );

表が作成されました。

SQL> ALTER TABLE t_par ADD CONSTRAINT pk_t_par PRIMARY KEY (pid);

表が変更されました。

次に子表となるT_CHD表を作成します。親表とほぼ同じですが、PID列に参照整合性制約(外部キー)を作成するのがポイントです。

SQL> CREATE TABLE t_chd
  2  (cid  NUMBER GENERATED ALWAYS AS IDENTITY
  3  ,pid  NUMBER NOT NULL
  4  ,cval VARCHAR2(10)
  5  );

表が作成されました。

SQL> ALTER TABLE t_chd ADD CONSTRAINT pk_t_chd PRIMARY KEY (cid);

表が変更されました。

SQL> ALTER TABLE t_chd ADD CONSTRAINT fk_t_pid FOREIGN KEY (pid)
  2                        REFERENCES t_par(pid);

表が変更されました。

このタイミングで、表とともに作成された順序の名前も確認しておきましょう。

SQL> SELECT TABLE_NAME,COLUMN_NAME,SEQUENCE_NAME
  2  FROM USER_TAB_IDENTITY_COLS
  3  ORDER BY TABLE_NAME;

TABLE_NAME      COLUMN_NAME     SEQUENCE_NAME
--------------- --------------- ---------------
T_CHD           CID             ISEQ$$_91944
T_PAR           PID             ISEQ$$_91941

レコード作成スクリプト

連続したレコードを作成する以下のPL/SQLスクリプトを実行します。
ハイライト行に示すように、上で確認した順序名を直接指定しています。

SQL> get ins.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pval) VALUES ('Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(pid,cval) VALUES (ISEQ$$_91941.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;
SQL> @ins.sql

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        23          5 Child
        24          5 Child
        25          5 Child

25行が選択されました。

親レコード1行毎に子レコードが5行作成され、全部で親レコード5行、子レコード25行が作成されていることがわかります。

共有プールをフラッシュさせてみる

順序が生成したシーケンス番号は、共有プール上に「CACHE_SIZE」パラメータで指定された数だけキャッシュされています。
デフォルト値は20となっており、次に生成されるシーケンス番号は以下のSQL文の「LAST_NUMBER」列で確認することができます。

SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  FROM USER_SEQUENCES;

SEQUENCE_NAME   INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91941               1         20          21
ISEQ$$_91944               1         20          41

ここで、共有プールをフラッシュさせるとどのようになるかを確認します。

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

システムが変更されました。

再度、レコード作成スクリプトを実行し新たな25行をInsertします。

SQL> @ins.sql

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent
        21 Parent
        22 Parent
        23 Parent
        24 Parent
        25 Parent

10行が選択されました。

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        23          5 Child
        24          5 Child
        25          5 Child
        41         21 Child
        42         21 Child
        43         21 Child
................................
        63         25 Child
        64         25 Child
        65         25 Child

50行が選択されました。

上で確認した「LAST_NUMBER」列の値にシーケンス番号が飛んで採番されていることがわかります。(PID=21,CID=41)
飛び番は発生しますが重複が発生しているわけではないので、問題なくInsertできます。

レコード作成スクリプト改

「ISEQ$$_91941」のようにシステムが生成した順序の名前は、プログラムの中で使用するにはあまり適切ではありません。
できれば以下のハイライト行のように、開発者が認識できる順序の名前を使用できることが望ましいと考えます。

SQL> get ins1.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pval) VALUES ('Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(pid,cval) VALUES (SEQ_PAR.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;

順序は改名できない

テーブル名は「ALTER TABLE <テーブル名> RENAME <新テーブル名>」で改名することができます。
しかし、残念ながら順序は「ALTER SEQUENCE」文で名称を変更することはできません

改名はできませんが、シノニムを作成することで「別名」を使用することができます。
順序名を再確認し、早速シノニムを作成してみましょう。

SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  FROM USER_SEQUENCES;

SEQUENCE_NAME   INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91941               1         20          41
ISEQ$$_91944               1         20          81

SQL> CREATE SYNONYM seq_par FOR ISEQ$$_91941;

シノニムが作成されました。

レコード作成スクリプト改を実行する

それでは、順序名をシノニムに置き換えたスクリプトを実行してみます。

SQL> @ins1.sql

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent
        21 Parent
        22 Parent
        23 Parent
        24 Parent
        25 Parent
        26 Parent
        27 Parent
        28 Parent
        29 Parent
        30 Parent

15行が選択されました。

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        63         25 Child
        64         25 Child
        65         25 Child
        66         26 Child
        67         26 Child
        68         26 Child
................................
        88         30 Child
        89         30 Child
        90         30 Child

75行が選択されました。

問題なく、新規レコードが作成されました。

もし、Identity Columnを使用しなければ

Oracle12c以前の環境で、同様のレコード作成スクリプトを書くと以下のようになります。
ハイライト行を冗長と見るか否かがIdentity Columnを使う判断基準となるのではないでしょうか?

SQL> get ins2.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pid,pval) VALUES (SEQ_PAR.nextval,'Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(cid,pid,cval) VALUES (SEQ_CHD.nextval,SEQ_PAR.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;

Identity Columnは使える機能か?

私がIdentity Columnという新機能を知った時、今回検証した親子関係のテーブルにおいて問題なく使用できるのであれば、プログラムを簡単にすることで開発効率品質を向上させることができる注目すべき機能である予感がしました。

上で確認したように、一旦テーブルを作成して順序名を確認してからシノニム(別名)を作成するという、ちょっと面倒な手順を踏めば何とか使えそうな気がしますが、このままでは今一な感じもします。

せめて、以下のようにテーブル作成時に任意の順序名を指定できる仕様であればよいのですが。。。

SQL> CREATE TABLE t_par
  2  (pid  NUMBER GENERATED ALWAYS AS IDENTITY "SEQ_PAR"
  3  ,pval VARCHAR2(10)
  4  );

Identity Columnは、現時点では他RDBMSからの移行を容易にするために実装された機能だと認識しています。

しかし、将来的により使いやすいように改良されることを期待しつつ、このシリーズを終えたいと思います。

終わり