PL/SQL で Python Challenge 7 再チャレンジ！ Tweet

前回、「PL/SQL　で　Python Challenge 7 残念！」　でPL/SQL+Oracle10gで解くことを断念するつもりだった。しかし、Python Challengeの問題自体の面白さに、再開することにしたのだ！。
再開するにあたり、PL/SQLでは面倒な部分（例えば、画像処理とか）だけを、Java (Java stored procedure）で解くことにした。（場合によっては、Javaで解く部分がクイズの核心だったりするが・・・）




java　stored procedureを利用したことで、Level7は簡単に解けた。それなりにひねりの効いた問題だったが、java stored procedureを利用した箇所以外は、Level7以前の問題を解く中で利用してきた機能や関数で十分解ける問題だった。

Level7でもいままで通り答えをそのまま書くつもりはなく、PL/SQL と Java そして Oracle10g をどのように利用して解いたのかという所だけをヒントとして書いておく。
Level7は画像自体に答えが隠されている。（Level7のページに行けば、なんとも怪しい画像にお目にかかるので感のよい方はすぐに気づくと思う）。そこで問題になるのは、PL/SQL や Oracle10g で提供されているパッケージや機能では画像データをピクセル単位で扱うことは今のところできないということなのである。その問題を解決する手段として Java を利用することにした。

注）2005/07/17時点では、MacOSX Tiger 10.4.1において、無理矢理動作するようにしたOracle10gではエラーが発生する。以下はすべて、MacOSX Panther 10.3.9のOracle10gで行った結果である。

１．問題の画像をOracle10g の directoryオブジェクト以下に配置し、BFILE　として扱う。

（BFILEは、外部LOBと呼ばれ、Oracleデータベース内には格納されないLOBである。）
BFILEへの参照（ロケータ）を取得するのは、PL/SQLだけで可能である。
Level7では、directoryオブジェクトへの読み込み権限さえあれば問題ないが今後のために書き込み権限を付与している。（実行ログの書き出しに利用する場合を考えている）

SQL> conn scott/tiger
SQL> select table_name as "directory",privilege from user_tab_privs;

directory                      PRIVILEGE
------------------------------ ----------------------------------------
PYTHON_CHALLENGE_DIR           WRITE
PYTHON_CHALLENGE_DIR           READ

２．１で取得した BFILE から画像データとして読み込み怪しい部分の画像情報から答えの元になる情報を取り出す。

（なぜ元になる情報なのか？。”ひねりの効いたクイズ”なので一筋縄では答えは導きだせないだけ！）
ここで、Java （実際には　Java stored procedure にするのだが）を利用する。


Java stored procedure といっても通常の java プログラムとほとんど同じなのだが、Java stored procedureとして利用する場合にはいくつかの作法が必要でなのだ。methodは、staticにする必要がある点と、利用するJDBCドライバは通常は、Server-side thin ドライバを利用する点である。
このドライバは、 thinドライバと同じ機能を持っているが接続先データベースがjava stored procedureが格納されているデータベースになる点が異なる。
Java stored procedure が格納されているデータベース以外のデータベースに接続する必要がある場合には、通常のthinドライバを利用することになる。
今回は、Java stored procedureの格納されているデータベースだけにしかアクセスしないのでServer-side thin ドライバを利用する。（単体テストの時は、thinドライバを利用し、テスト完了後に Server-side thinドライバへ切り替えた）
尚、Java stored procedureの詳細は、OTNのオンラインマニュアルなどを参照してもらいたい。

３．２で取得した”答えの元になる情報”から答えを取り出す。これは PL/SQL だけで可能である。

以下に、上記1〜３の処理をそれぞれ関数として作成した PL/SQL パッケージ（level7パッケージ）のapiを示す。

getBFile() が、BFILEへのロケータを返す関数である。

getAnswer()　が、java stored procedureである。画像データを読み込み”答えの元ネタ”を返す関数である。

getNextLevelUrl()　が、Level8へのURLを返す関数である。

注）本来であれば、getNextLevelUrl()だけを公開すればよいのであるがデバッグのために全関数を公開してある。

SQL> 
SQL> desc level7
FUNCTION GETANSWER RETURNS VARCHAR2
 引数名                    　　 タイプ                  In/Out Default?
------------------------------ ----------------------- ------ --------
 DIRECTORYNAME                  VARCHAR2                IN
 FILENAME                       VARCHAR2                IN
FUNCTION GETBFILE RETURNS BINARY FILE LOB
 引数名                    　　 タイプ                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DIRECTORYNAME                  VARCHAR2                IN
 FILENAME                       VARCHAR2                IN
FUNCTION GETNEXTLEVELURL RETURNS VARCHAR2
 引数名                    　　 タイプ                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DIRECTORYNAME                  VARCHAR2                IN
 FILENAME                       VARCHAR2                IN

後は作成した stored procedure を実行するだけである。今回は関数（スカラ関数）にしてあるので SQL から呼び出して実行している。また、java stored procedure に不慣れな方の為に手順も載せておく。

１）OS上で、適当な位置にディレクトリを作成する。

この時、oracle所有者が、ディレクリに対する書き込みおよび、読み込みパーミッションを持っているか確認すること。また、画像ファイルも配置しておく。尚、外部LOBや、directory オブジェクトにマッピングされるOS上のディレクトリが実在するかどうかは
Oracle側では検証されない。利用する側で適切な関数などを呼び出してチェックする必要がある。

２）SQL*Plus を起動し、１）で作成したOS上のパスを対象とする directory オブジェクトを作成する。

３）２）で作成したdirectory オブジェクトへの read権限、 write権限を付与する。

例）SQL> grant read, write on directory python_challenge_dir to scott;

４）level7.getBfile() だけを作成しておく。

５）level7.getAnswer() に対応づける Java program を作成する。

java program の作成には、JDeveloper10g for MacOSX Preview Releaseを利用した。

６）５）で作成した　Java programを　Oracle10g の scottスキーマへ配布する。

loadjava コマンドを利用するか、JDeveloperを利用する。
配布にも、JDeveloper10g for MacOSX Preview Releaseを利用した。

７）level7パッケージを作成する。

getAnswer() 　の実装。６）で配布したclassを利用する　java stored functionにする。

getNextLevelUrl() 　の実装。ここでは、これまでの問題でも利用したOracle10g新機能（Pythonなら re で判るはず！)を利用するのである。

８）実行。　スカラ関数として利用できるようにしておいたのでSQLから実行した！

ネタバレになるので、いつものように画像はぼかしてあります。 m(_ _)m

ということで、再開した　PL/SQL で Python Challenge。　　Level8 へつづく。

海の日。梅雨も開けた湘南にて　（どこでしょう？）