2016年11月 5日 (土)

簡単! 「Model」タブでインスタント・リバース・エンジニアリング

クックパッド風のタイトルにしてみましたw

前回の続きを書こうと思ってたのですが、Oracle SQL Developer 4.1.3で追加された、「Model」タブ。

これ意外にいいです。いままではData Modelerを起動しなくても参照整合性制約が定義されていれば、簡単にリバースエンジニアリングできるの知ってました? (参照整合性制約のない表も含め、スキーマ全体をリバースエンジニアリングしたい場合は今まで通り、SQL Developer Data Modelerを利用する必要があります)
(私は最近気づいたんですけどねw


以下、最新版の4.1.5で試した動画をYoutubeにアップしておきました:)

※サウンドトラックなしです。


え!?
参照整合性制約が無い! ですって!!!!!!wwww

そのような方は、参照整合性制約について、深く考えてみるのもよいのではないでしょうか。
db tech showcase tokyo 2013 - A35 - JPOUG特濃:潮溜まりでジャブジャブ、SQLチューニング


本日のネタは以上です。

| | コメント (0) | トラックバック (0)

2016年4月18日 (月)

Oracle SQL Developer 4.1.x - OUTLINE HINT でるのな


関連エントリ OTHER_XMLの中身

別ネタを作ろうとして気づいた。

Oracle SQL Developer 4.1.1で実行計画を表示すると、普通にOUTLINE HINTというかOther XML列が丸っとリストされてて素敵だったw

20160417_233308

ついでに、Oracle Tuning Packの機能だけど、SQLチューニングアドバイザも軽く使ってみた。

20160417_233319


これらの機能、sqlclだとどうなんだろう、使えるのかも調べてないので別途しらべるか TODO

20160417_233340


| | コメント (0) | トラックバック (0)

2014年6月13日 (金)

SQL Developer 4の素敵なコマンドライン de SQL整形 :)

Version 4.0.1ではWindowsでも同じ問題がでていたようですね。以下OTNフォーラムにも投稿されていたようです。
OS X版 Version 4.0.1でも同じだったので不具合だったようです。
https://community.oracle.com/thread/3562219


Version 4.0.2がリリースされたので改善されたか確かめてみました。 OS X版のSQLDeveloper 4.0.2 で!

OTN-JPではまだ、4.0.1のようですが、USでは 4.0.2のようです。OTN-JPでもそのうち4.0.2になるのでしょうね :)
以下はUSへリンクです。
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

20140613_45308


OS X版のsdcliを実行するには実行権を付与する必要があります。sdcliは以下のパスにあります。かなり深いところにあります!
(OS X版は以下深いところにあるので、SQLDeveloperのアイコン上で右クリック>コンテクストメニュー>パッケージの内容を表示から辿ってパスを探してもいいですし、以下のパス直接コピペでも行けます。)

20140613_45551

20140613_45613


discus-mother:˜ oracle$ cd /Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin
discus-mother:bin oracle$ chmod +x sdcli
discus-mother:bin oracle$ ll
total 672
-rw-r--r--@ 1 oracle staff 49279 5 3 02:48 SQLDeveloperIcons.icns
-rw-r--r--@ 1 oracle staff 0 5 3 02:48 jdk.conf
-rw-r--r--@ 1 oracle staff 440 5 3 02:48 logging-debug.conf
-rw-r--r--@ 1 oracle staff 363 5 3 02:48 logging.conf
-rwxrwxr-x@ 1 oracle staff 586 5 3 02:50 sdcli
-rw-r--r--@ 1 oracle staff 99 5 3 02:50 sdcli-Darwin.conf
-rw-r--r--@ 1 oracle staff 446 5 3 02:50 sdcli.boot
-rw-r--r--@ 1 oracle staff 854 5 3 02:50 sdcli.conf
-rw-r--r--@ 1 oracle staff 71205 5 3 02:48 splash.gif
-rw-r--r--@ 1 oracle staff 71205 5 3 02:48 splash.png
-r-xr-xr-x@ 1 oracle staff 3220 5 3 02:48 sqldeveloper
-rw-r--r--@ 1 oracle staff 415 5 3 02:48 sqldeveloper-Darwin.conf
-rw-r--r--@ 1 oracle staff 661 5 3 02:48 sqldeveloper-debug.conf
-rw-r--r--@ 1 oracle staff 204 5 3 02:48 sqldeveloper-nondebug.conf
-rw-r--r--@ 1 oracle staff 445 5 3 02:48 sqldeveloper.bat
-rw-r--r--@ 1 oracle staff 340 5 3 02:48 sqldeveloper.boot
-rw-r--r--@ 1 oracle staff 1175 5 3 02:48 sqldeveloper.conf
-rw-r--r--@ 1 oracle staff 83456 5 3 02:48 sqldeveloper64W.exe
-rw-r--r--@ 1 oracle staff 131 5 3 02:54 version.properties
discus-mother:bin oracle$


パスは通っていないので設定する必要があります。

discus-mother:˜ oracle$ cat .bashrc
alias ll='ls -lv'

#for SQLDeveloper sdcli
export PATH=$PATH:/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin

discus-mother:˜ oracle$


Version 4.0.1では以下の状況でExceptionが発生していましたが、Version 4.0.2では解決してます!!! 

discus-mother:˜ oracle$ sdcli

Oracle SQL Developer
Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved.

使用可能な機能:
cart: データベース・カート・バッチ・タスク
dba: 基本バッチDBAタスク
format: SQL Format Task
migration: Database Migration Tasks
reports: 基本バッチ・レポート・タスク
unittest: ユニット・テスト・バッチ・タスク
discus-mother:˜ oracle$

ということで、
前から試したかった SQL文の整形機能。 GUIだとSQLワークシートでSQL文整形機能が提供されているためデータベースへ接続必要だったんですよね。
機能としては素敵だったのですが、データベース接続が行えない環境では使えない機能だったんですよ。
そんな環境で読むに耐えない巨大で未整形なSQL文を渡され涙目だった日々を思い出しますw

コマンドラインならデータベース接続不要なのがいいっす!

discus-mother:˜ oracle$ sdcli format

Oracle SQL Developer
Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved.

format input=<入力ファイルまたはディレクトリ> output=<出力ファイルまたはディレクトリ>
成功しました。
discus-mother:˜ oracle$


以下のようなSQL文を整形してみます。

discus-mother:˜ oracle$ cat sample_unformated.sql
select a.id,a.name,b.location,b.phone_no from foo a innter join bar b on a.id = b.id order by a.name;


意外と地味な感じの処理終了メッセージですが、整形できたようです。整形結果を確認!

discus-mother:˜ oracle$ sdcli format input=sample_unformated.sql output=sample_formated.sql

Oracle SQL Developer
Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved.

成功しました。
discus-mother:˜ oracle$


いいですね〜 :) 最後の改行がないのですは、ご愛嬌ということで.

discus-mother:˜ oracle$ cat sample_formated.sql
SELECT a.id,
a.name,
b.location,
b.phone_no
FROM foo a innter
JOIN bar b
ON a.id = b.id
ORDER BY a.name;discus-mother:˜ oracle$
discus-mother:˜ oracle$

では、超便利な一括整形のテスト。 以下のような未整形なSQLファイルを含むディレクトリと整形後SQLファイルを格納するディレクトリを用意して....

discus-mother:˜ oracle$ mkdir unformated_sqls
discus-mother:˜ oracle$ mkdir formated_sqls
discus-mother:˜ oracle$ dir=unformated_sqls; for fname in `ls $dir`; do echo -e \\n\\nfile : $fname; cat $dir/$fname; done;


file : sample1.sql
select a.id,a.name,b.location,b.phone_no from foo a innter join bar b on a.id = b.id order by a.name;


file : sample2.sql
select a.id,a.name,b.location,b.phone_no from foo a innter join bar b on a.id = b.id order by a.name;


一括整形の場合はディレクトリ名のみ指定すればOK!
おおおおおおおおお〜〜〜〜、できた〜〜〜〜SQL文の一括整形!!!  素敵!!

discus-mother:˜ oracle$ sdcli format input=unformated_sqls output=formated_sqls

Oracle SQL Developer
Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved.

成功しました。


かくに〜〜〜〜〜ん!!

discus-mother:˜ oracle$ dir=formated_sqls; for fname in `ls $dir`; do echo -e \\n\\nfile : $fname; cat $dir/$fname; done;


file : sample1.sql
SELECT a.id,
a.name,
b.location,
b.phone_no
FROM foo a innter
JOIN bar b
ON a.id = b.id
ORDER BY a.name;

file : sample2.sql
SELECT a.id,
a.name,
b.location,
b.phone_no
FROM foo a innter
JOIN bar b
ON a.id = b.id
ORDER BY a.name;


Good job!!!!! SQL Developer Team!


| | コメント (0) | トラックバック (0)

2013年12月 9日 (月)

no ocijdbc11 in java.library.path on OS X Mavericks

JPOUG Advent Calendar 2013、9日目のエントリー、かつ、チューニングネタではなく Java on OS X の話という変化球 :) です。

OS X版Oracle SQL DeveloperやOracle JDeveloperで、OCI/Thick JDBCを利用しようとして以下のようなメッセージに遭遇したら...みなさんどうしてるのだろう? 

no ocijdbc11 in java.library.path

と考えだしたら眠れなくなったので、役に立つのか、たたないのかわからないけど書いておきます

私は、Oracle SQL DeveloperやOracle JDeveloperの起動シェルでDYLD_LIBRARY_PATHなどの環境変数をセットすることで対処しています。
(今となってはこれが楽だと思います。 environment.plistでなんとかできた時代もありましたけど...いまは使えないしね)

no ocijdbc11 in java.library.pathとなっている状態から解決するまでの操作をYouTubeで....


映像では、ユーザーのホームディレクトリーにOracle向け環境変数設定ファイル (この例では、oracleenvというファイルを作成してあります) を作成しておき、その設定ファイルをOracle SQL Developer等の起動シェルで読み込ませて問題を解決するまでの操作を行っています。

主演
MacBook Air (mid2013)

ホストOS、その他

  • OS X 10.9 Maveriks
  • Oracle Instant Client 11g 11.2.0.3.0 for OS X (64bit)
  • Terminal 2.4
  • VirtualBox 4.3.4 for OS X
  • Oracle SQL Developer 4.0.0 for OS X
  • Oracle JDeveloper12c 12.1.2.0.0 Studio Edition Generic

ゲストOS、その他

  • Oracle Linux Server 6.4 x86_64
  • Oracle Database 12c EE R1 for Linux x86_64

映像では見づらい方向けの解説。

事前にOracle向け設定ファイルを該当ユーザーのホームディレクトリーに作成しておきます。
この例では、oracleenvとして作成しました。(不過視ファイルとしてもよいかもしれません。)
Oracle_environment_variables


Oracle SQL Developer 4.0 for OS X

「Oracle SQL Developerメニュー」→「Preference...」を選択
002_sdev_004

「データベース」→「拡張」→「OCI/Thickドライバの使用」チェックボックス」をチェック→「テスト」ボタンをクリック
002_sdev_005

no ocijdbc11 in java.library.pathエラーとなる(ライブラリーへのパスが通ってないので当然ですよね)
002_sdev_006

「Finder」→「アプリケーション」→「SQL Developer」→「右クリック」→ポップアップメニューの「パッケージの内容表示」
002_sdev_009

「Contents」→「MacOS」→「sqldeveloper.sh」を選択
002_sdev_010

ポップアップメニュー「このアプリケーションで開く」でお好きなテキストエティタを選択
002_sdev_012

oracle向け環境設定ファイルを読み込ませるように編集。この例ではユーザーホームディレクトリにある oracleenvというファイルを読み込むように変更。
002_sdev_013

Oracle SQL Developer 4.0を再起動しOCI/Thick JDBCドライバーで接続可能か再確認
002_sdev_014

002_sdev_015


Oracle JDeveloper12c 12.1.2.0.0 Studio Edition Generic

「データベース接続編集」ダイアログの「接続のテスト」ボタンをクリックするとno ocijdbc11 in java.library.pathエラー(これもパスが通ってないのが原因なので...)
003_jdev_002

003_jdev_003

「Finder」でOracle JDeveloper12cのインストールディレクトリーからアプリケーション「JDeveloper」を右クリック
→ポップアップメニューの「パッケージの内容を表示」を選択

003_jdev_004

「Contents」→「MacOS」→「JDeveloper」を選択して右クリック→「このアプリケーションを開く」でお好きなテキストエディタを選択
003_jdev_005

oracle向け環境設定ファイルを読み込ませるように編集。この例ではユーザーホームディレクトリにある oracleenvというファイルを読み込むように変更。
003_jdev_006

Oracle JDeveloper12cを再起動し、OCI/Thick JDBCドライバーで接続可能か再確認
003_jdev_007

003_jdev_008


明日は、@dekasasaki さんの担当です。引き続き、JPOUG Advent Calendar 2013をお楽しみください。:)


| | コメント (0) | トラックバック (0)

2013年3月23日 (土)

R(relation)の線が無いERDを見てて..思いついたこと....でもメンテする気がなけりゃ同じだけどな

ExcelでメンテされてないERDとか、リリース当初からERDのR(relation)の線がない(ヒアリングするrelationは確かにあるんだよ)ERDとか見てて...寂しくなった。
ERD見る意味ないじゃん。というか、なんでそんなERDメンテするのに工数取ってんの?
とか、そんなERDベースにテストデータどうやって作るのさ...急に呼ばれて飛び出てきたのに...
ということをもやもや考えてた日々がしばらくありまして...

Oracle SQL Developer ModelerでリバースエンジニアリングしてERD作ろうと思っても...

そういう所って外部参照制約なんてないんですよ。

そもそも外部参照制約作れないだろって所もあるわけですけど...それはない前提で...

20130323_145704

20130323_145856

ERDをリバースエンジニアリングで作成しようと思うと、エンティティ図になってしまうという残念な結果になるのは目に見えてるわけです。はい。(><)

外部参照制約作成していれば.....

20130323_154430_2

20130323_154436


20130323_154444


20130323_154451_2


20130323_154503

こんな感じですぐにできるので、面倒くさくなくて素敵なんですね。

だた、参照整合性嫌いだし。とか、毎日夜間バッチで整合性チェックしてるからいいの!
ってところは多いわけです。


....で....ある日、電球がピカりんこと光った訳ですよ....


Oracle SQL Developer Data Modelerのリバースエンジニアリングって 、外部参照制約がdisableかつnovalidateで制約が効かない(一部制限あり*1)状態でもERDではrelation作成してくれるかも〜〜〜〜。と。

忘れないうちにすぐ試す!
外部参照制約はdropしないで、disable かつ novalidate済み!


20130323_144207


20130323_144244


20130323_144256


20130323_144311


20130323_144318

できた〜〜〜〜っ!


Enjoy! Oracle SQL Data Modeler and Developer !



*1:
外部参照整合性をdisable かつ novalidateにしていても外部参照整合性が作成されているだけで親表は子表より先にdropできなくなるのでその点はお忘れなく!

試験環境は以下のとおり
Oracle11g R2 for linux x86
Oracle SQL Developer 3.3.20 for OS X
Oracle DataModeler 3.1.4 for OS X

| | コメント (0) | トラックバック (0)

2009年9月18日 (金)

Oracle SQL Developer Data Modeler #1

最近、Oracle SQL Developerのエントリが検索上位に来ていたので久々に新ネタです。Oracle SQL Developerに組込まれる?(ちゃんと読んだわけではないので違うかも)というOracle SQL Developer Data Modelerのお話。

このエントリでは書きませんが、メニューなど日本語化されていないのですがエンティティなどでは多少問題はありますが日本語は扱えるようです。(注)私が日本語を使って試した範囲に於いて。

今回利用したのはMacOS X Snow Leopard でOracle SQL Developer Data Modeler for MacOS Xを単体で起動できるようにした後にOracle SQL Developer for MacOS Xの外部アプリケーションとして起動するところまで。(ちなみに、PowerPC版のMacOS X TigerでもJDK6 Developer Previewがインストールされていれば利用できます。)


Datamodeler_2

では、さっそくやってみましょう!

OTNのサイトからダウンロードしたOracle SQL Developer Data Modeler for MacOS Xを適当な場所に解凍します。(今回は/Applications以下に展開)
Oracle SQL Developer Data ModelerをMacOS X上で起動するには、Data Modelerを解凍したディレクトリ直下にあるdatamodeler.shをTerminalから実行します。
尚、ダウンロード・解凍直後はdatamodeler.shの実行権限が無いので実行権限を付与しておきます。

guppy:˜ discus$ cd /Applications/datamodeler 
guppy:datamodeler discus$ ll
total 256
drwxr-xr-x@ 8 discus staff 272 9 18 04:10 bin
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 conf
-rw-r--r--@ 1 discus staff 118784 6 26 03:42 datamodeler.exe
-rw-r--r--@ 1 discus staff 60 6 26 03:42 datamodeler.sh
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 doc
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 domains
-rw-r--r--@ 1 discus staff 4279 6 26 03:42 icon.png
drwxr-xr-x@ 16 discus staff 544 9 18 04:05 lib
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 logtypes
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 rdbms
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 reports
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 templates
guppy:datamodeler discus$
guppy:datamodeler discus$ chmod ug+x datamodeler.sh
guppy:datamodeler discus$ ll
total 256
drwxr-xr-x@ 8 discus staff 272 9 18 04:10 bin
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 conf
-rw-r--r--@ 1 discus staff 118784 6 26 03:42 datamodeler.exe
-rwxr-xr--@ 1 discus staff 60 6 26 03:42 datamodeler.sh
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 doc
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 domains
-rw-r--r--@ 1 discus staff 4279 6 26 03:42 icon.png
drwxr-xr-x@ 16 discus staff 544 9 18 04:05 lib
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 logtypes
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 rdbms
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 reports
drwxr-xr-x@ 3 discus staff 102 9 18 04:05 templates
guppy:datamodeler discus$


JavaSEは1.6がデフォルトになっていますが、まだ、このままではData Modelerを実行できません。

guppy:datamodeler discus$ java -version
java version "1.6.0_15"
Java(TM) SE Runtime Environment (build 1.6.0_15-b03-219)
Java HotSpot(TM) 64-Bit Server VM (build 14.1-b02-90, mixed mode)
guppy:datamodeler discus$


なにもしないで実行すると、~/jdk.confに設定されるJ2SEのホームディレクトリのフルパスの入力を求められます。指示に従いそのまま入力してもよいのですが、datamodelerとは直接関係の無いディレクトリにjdk.confが作成されるのが嫌なので、別の方法で設定します。

guppy:datamodeler discus$ ./datamodeler.sh &
[1] 2198
guppy:datamodeler discus$
Oracle SQL Developer Data Modeler
Copyright (c) 1997, 2009, Oracle and/or its affiliates.All rights reserved.

Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ˜/jdk.conf

[1]+ Stopped ./datamodeler.sh
guppy:datamodeler discus$


Data Modelerを解凍したディレクトリ以下の /bin/datamodeler.confのSetJavaHomeパラメータにJavaSE6.0へのフルパスを設定します。

guppy:datamodeler discus$ vi ./bin/datamodeler.conf
guppy:datamodeler discus$ grep SetJavaHome ./bin/datamodeler.conf
SetJavaHome /System/Library/Frameworks/JavaVM.framework/Versions/1.6/home


尚、MacOS XのJavaへのパスは以下のように管理されています。今回は、1.6/homeを設定しています。

guppy:datamodeler discus$ 
guppy:datamodeler discus$ ll /System/Library/Frameworks/JavaVM.framework/Versions
total 48
lrwxr-xr-x 1 root wheel 5 9 2 17:21 1.3 -> 1.3.1
drwxr-xr-x 3 root wheel 102 7 21 08:35 1.3.1
lrwxr-xr-x 1 root wheel 5 9 2 17:21 1.5 -> 1.5.0
drwxr-xr-x 7 root wheel 238 9 2 17:21 1.5.0
lrwxr-xr-x 1 root wheel 5 9 2 17:21 1.6 -> 1.6.0
drwxr-xr-x 7 root wheel 238 9 2 17:21 1.6.0
drwxr-xr-x 8 root wheel 272 9 2 17:21 A
lrwxr-xr-x 1 root wheel 1 9 2 17:21 Current -> A
lrwxr-xr-x 1 root wheel 3 9 2 17:21 CurrentJDK -> 1.6
guppy:datamodeler discus$


これでやっと起動できるようになります。

guppy:datamodeler discus$ ./datamodeler.sh &
Oracle SQL Developer Data Modeler
Copyright (c) 1997, 2009, Oracle and/or its affiliates.All rights reserved.


[2]- Done ./datamodeler.sh
guppy:datamodeler discus$
Osdm_window_2

Data Modelerを起動するのに毎回Terminalを開いて、shellを実行するというのは面倒という方は、.sh の部分を .command と書き換えてください。ダブルクリックで起動できるようになります。
.command に変更後エイリアスをDockに登録しておけばクリック一発で起動できます。


他の方法として、Oracle SQL Developerの外部アプリケーションとして登録しておきOracle SQL Developer経由で起動する方法があります。
登録するのは、 .sh の方でも .command の方でも、どちらでも構いません。以下の例では datamodeler.sh を外部アプリケーションとして登録・起動しています。


おまけ、JavaSE5.0を設定してしまうと・・・・・・こんなことになっちゃいますからご注意を。

guppy:datamodeler discus$ ./datamodeler.sh &
[1] 891
guppy:datamodeler discus$
Oracle SQL Developer Data Modeler
Copyright (c) 1997, 2009, Oracle and/or its affiliates.All rights reserved.

java.lang.UnsupportedClassVersionError: Bad version number in .class file
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:620)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:260)
at java.net.URLClassLoader.access$100(URLClassLoader.java:56)
at java.net.URLClassLoader$1.run(URLClassLoader.java:195)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
Exception in thread "main" ll


次回につづく。

| | コメント (0) | トラックバック (0)

2009年4月20日 (月)

Oracle SQL Developer 1.5.4がJavaSE6.0 Developer Preview 1(PowerPC版ね)を嫌だと言うので・・

さて、今日はSQL Developer 1.5.4 for MacOSX(PowerPC)の話題をちょっとだけ。

PowerMac G5 with MacOSX Tiger 10.4.11(PowerPC)へJDeveloper 11g for MacOSXをインストールした際、JavaSE6.0 Developer Preview 1をデフォルトのJavaVMに変更してしまったので、それ以前にイストールしていたOracle SQL Developer 1.5.4がJDK6.0 Developer Preview 1なんてダメダメ、ちゃんとしてくれなくちゃだわ!。とアラートを表示するので、Oracle SQL Developer1.5.4はJavaSE5.0を利用するよう変更した。。(警告がでるだけで今のところ特に問題はなかったのですが、起動するだびアラートが表示されうざいので。)

ついでにどうにもこうにもSQL Developerのアイコンが気に入らないのでアイコンはカスタマイズしちゃってます。:)



Icon

1

Java6dp1

デフォルトのJDKをJavaSE6.0 Developer Preview 1(PowerPC版では正式なJDK6.0は無いので)にしてしまったのだが、旧リリースもちゃんと残してあるのでOracle SQL Developer for MacOSXではsqldeveloper-Darwin.confを編集し、SetJavaHomeにJavaSE5.0のパスをセットして再起動すれば作業終了。

ちなみに、sqldeveloper-Darwin.confの場所は、SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin 以下。

G5Server:˜ discus$
G5Server:˜ discus$ ls -l /System/Library/Frameworks/JavaVM.framework/Versions/
total 56
lrwxr-xr-x 1 root wheel 5 Sep 25 2008 1.3 -> 1.3.1
drwxr-xr-x 8 root wheel 272 Jul 21 2005 1.3.1
lrwxr-xr-x 1 root wheel 5 Sep 25 2008 1.4 -> 1.4.2
drwxr-xr-x 8 root wheel 272 Jul 21 2005 1.4.2
lrwxr-xr-x 1 root wheel 5 Sep 25 2008 1.5 -> 1.5.0
drwxr-xr-x 8 root wheel 272 Apr 9 10:02 1.5.0
lrwxr-xr-x 1 root wheel 5 Jul 21 2007 1.6 -> 1.6.0
drwxr-xr-x 10 root wheel 340 Mar 24 10:30 1.6.0
drwxr-xr-x 6 root wheel 204 Mar 17 19:53 A
lrwxr-xr-x 1 root wheel 1 Sep 25 2008 Current -> A
lrwxr-xr-x 1 root wheel 3 Mar 25 13:52 CurrentJDK -> 1.6
G5Server:˜ discus$
G5Server:˜ discus$
G5Server:˜ discus$ cd /Volumes/ExtraDisk/Application/
G5Server:/Volumes/ExtraDisk/Application discus$ ls SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin
G5Server:/Volumes/ExtraDisk/Application discus$ ll SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin
total 136
-rw-r--r-- 1 discus discus 0 Mar 2 21:47 jdk.conf
-rw-r--r-- 1 discus discus 315 Mar 2 21:47 logging-debug.conf
-rw-r--r-- 1 discus discus 316 Mar 2 21:47 logging.conf
-rw-r--r-- 1 discus discus 12702 Mar 2 21:47 splash.gif
-rw-r--r-- 1 discus discus 10286 Mar 2 21:47 splash.png
-r-xr-xr-x 1 discus discus 3168 Mar 2 21:47 sqldeveloper
-rw-r--r-- 1 discus discus 392 Apr 20 04:32 sqldeveloper-Darwin.conf
-rw-r--r-- 1 discus discus 350 Apr 20 04:32 sqldeveloper-Darwin.conf.org
-rw-r--r-- 1 discus discus 63 Mar 2 21:47 sqldeveloper-debug.conf
-rw-r--r-- 1 discus discus 170 Mar 2 21:47 sqldeveloper-nondebug.conf
-rw-r--r-- 1 discus discus 173 Mar 2 21:47 sqldeveloper.boot
-rw-r--r-- 1 discus discus 516 Mar 2 21:47 sqldeveloper.conf
-rw-r--r-- 1 discus discus 132 Mar 2 21:56 version.properties
G5Server:/Volumes/ExtraDisk/Application discus$
G5Server:/Volumes/ExtraDisk/Application discus$ cd
G5Server:˜ discus$ SQL_DEV_HOME=/Volumes/ExtraDisk/Application/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin
G5Server:˜ discus$ diff $SQL_DEV_HOME/sqldeveloper-Darwin.conf.org $SQL_DEV_HOME/sqldeveloper-Darwin.conf
6c6
< SetJavaHome /Library/Java/Home/
---
> SetJavaHome /System/Library/Frameworks/JavaVM.framework/Versions/1.5/home
G5Server:˜ discus$
G5Server:˜ discus$


以下、JDKをJavaSE6.0 DP1からJavaSE5.0へダウングレードした結果。
Java50

| | コメント (0) | トラックバック (0)

2009年4月 9日 (木)

Oracle SQL Developer 1.5.4 for MacOSXでキーボードプリセットがロードされていない件

「Oracle SQL Developer 1.5.4 がリリースされたので早速入れ替えてみた」という記事を書いたが、使い始めてさらに気付いたことがあった。それはコードエディタで既存プロシージャを編集中にタイプミスした文字を削除しようとしたことから発覚。以下、発見当時の生中継! w

backspaceをタイプ。

ん?
backspace

backspace、ん?

backspace backspace backspace

あれ〜〜、backspaceが効かない。

enter keyをタイプしても改行しない!!


Oracle SQL Developer 1.5.4ってMacOSXのkeyboardにキ−イベントをちゃんとハンドリングしてくれてない?!。command+sで保存もできないし〜〜。なんで〜〜〜。

というところから捜査は始まった!

しかし、US OTNで同様の話題があることを発見しすぐに解決。MacOSXのkeyboard presetが正しくロードされていないのが原因らしい。。
次期リリースでは改善されることを期待します。オラクル様


6

上記のhogehoge部分を削除したいだけなのだがbackspaceが効かない。US OTNの該当スレッドによれば単純にkeyboard presetがロードされていないということなのでやってみた。
7

1

9_2

10

12

お〜〜〜めでたくbackspaceキーで削除できた。改行もenterキーでできた!。 command+sで保存もできた。ふ〜〜っ。やれやれ。

| | コメント (0) | トラックバック (0)

2009年3月 4日 (水)

Oracle SQL Developer 1.5.4 がリリースされたので早速入れ替えてみた


S/N Ratio:Oracle SQL Developer 1.5.4 リリース

のアナウンスがあったので早速Linux(CentOS5)/WindowsXP Professional(Vista入れてないの〜)/MacOSX Tiger/Leopardの各プラットフォームで旧リリースをアップグレードしてみた。
取りあえず入れ替えて起動〜Oracle11gへ接続(TNS接続)できるところまで確認。
小さいな問題なのだが英語版では気付かなかったが日本語にローカライズされ文字列が長くなってしまった為にデータベース接続の作成/選択ダイアログで「ネットワーク別名」とドロップダウンメニューの表示がオーバーラップしている。私が使っている範囲ではそれ以外に問題はさそう。。もうちょっと本気で使うと何か出てくるかもしれないけどね。:)


12

まず、MacOSX Tiger/Leopardのデータベース接続の作成/選択ダイアログ。赤丸部分で表示がオーバーラップしてしまっている。
尚、MacOSXのOracle SQL DeveloperでTNSを利用する方法は「Oracle SQL Developer for MacOSX で Oracle Instant Clientを使ってみる」を参照のこと。


7


次はLinux(CentOS5)で起動したSQL Developerの同ダイアログ。(同じくオーバーラップしている)
ちなみに以下はMacOSX側でX11 forwardingを利用してCentOS側のOracle SQL Developerを起動している。


5


WindowsXP Professionalで起動したSQL Developer1.5.4の同ダイアログでも現象は同じ。(ダイアログのスナップショットではわからないですが、Remote Desktop Connection for MacOSX経由でWindowsのSQL Developerを起動している。うちではまだリアルな環境しかないので・・w)

6

Linux環境で以下の様なメッセージで起動できない場合

Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ˜/.sqldeveloper/jdk
JDKのインストール先へのフルパスを.bashrc等でJAVA_HOME環境変数に設定してやればOK。以下の例ではJava SE6 update 12へのフルパスを設定している。
[oracle@corydoras ˜]$ echo $JAVA_HOME
/usr/java/jdk1.6.0_12

| | コメント (0) | トラックバック (0)

2008年5月 7日 (水)

PL/SQL で Python Challenge Level 18 う〜〜〜ん。

やることは分かったのだがPL/SQLでは重要な部分(pythonなら一発だけど)を手作りしないとならない。。。とりあえず適当に作ってはみたもののまだ思うような結果にはなっていない。。だが、そこさえクリアできればlevel18はPL/SQLだけで解けるはず。。。。。ということで暇を見て実装中。といってもその暇がなかなか取れなくなりそうな予感も。。。まあ、今回のネタはおもしろそうなので、少しづつでも進めて行きますよ。。。w

| | コメント (0) | トラックバック (0)

2008年3月23日 (日)

PL/SQL で Python Challenge Level 16 - 2年5ヶ月ぶり

そう、あれは、このブログをはじめたころ、Oracle10g R1 for MacOSX ServerとPL/SQL(画像処理関係はjava stored procedure)を利用し、Python Challengeに PL/SQLでチャレンジしていたっけ〜〜。遠い目。

アクセスログを見ていたら最近、またPython Challengeのキーワード検索が多くヒットしているのにびっくり!。
前述のエントリを書いていたのが約2年半前になるわけですから! ということで、久々にちょっとだけ再開してみようかと。。Level 16を見てみる。


Level 16の問題の画像を見ると、いかにもわざとらしい柄というか模様が目につく!。

で、Level 16のページタイトルもいつものようにヒントになっている。

画像にある模様を眺めつつ、ページタイトルの英文(簡単な英文です)を読み試しにJavaだけで作ってみると、考え方はいいと思うのだが全然答えにはなっていない。。。

こうか? ん、、、、それとも、こうか? でも違う。 あ、横にしてたよ。。。あはは。。(^^;;
と独り言を言いつつ何とか答えを見つけました。。は〜〜っ。暫くぶりだと疲れるわ。

次回は答えは書きませんが、Level 16の結果をぼかしたスナップショットを載せる予定。

ちなみに、今回の環境は以下。
Database Server : Oracle11g R1 EE for Linux x86

開発用クライアント:
JDeveloper10g 10.1.3 for MacOSX
Oracle SQL Developer 1.1.3 for MacOSX
(Java Stored Procedure、PL/SQL package、Java関連で利用した)

準備が整ったらMacOSXからsshでLinuxのOracle11gへ接続して実行!
結果確認は、MacOSXのVNCからLinuxへ接続して確認するという面倒なことまでしてMacを絡めている。
(Mac de Oracle ですから! 笑)

尚、以前のように、なるべくPL/SQLやSQLレベルで問題を解いて行くがPL/SQLやSQLでは不可能な処理はJava stored procedureで行う。Level 16もLevel14と同じような処理になっている。

| | コメント (0) | トラックバック (0)

2008年2月 4日 (月)

Oracle SQL Developer for MacOSX で Oracle Instant Clientを使ってみる

前回、Oracle Instant Client 10g R1 for MacOSX(PPC)がMacOSX LeopardのRosettaによって動作するところまでは確認できたということを書いたが、今日は、Oracle SQL Developer 1.2.1 for MacOSX(現時点での最新版)で Oracle Instant Client 10g R1のTNS接続する方法を書いておくことにする。

SQL*PlusをTerminalやxtermから起動するのであれば .bashrc 等で環境変数を設定すればよいですが、Oracle SQL DeveloperのようなGUIツールはどうすればいいのか?
MacOSXのGUI環境では、.bashrc等は利用できない。その変わりにenvironment.plistが利用される。environment.plistの説明は、Apple Technical Q&A QA1067 : Setting environment variables for user processesを見てもらうとして早速試してみます。

Oracle_sql_developer_sprash Oracle_sql_developer_1_2_1

まず、最初は、environment.plistが存在しない状態でOracle SQL Developerを起動し、前回設定したOracle Instant Client 10g R1 for MacOSX(PPC)のtnsnames.oraを利用したTNS接続が行えるか確認してみる。
Oracle_sql_developer_create_conne_2


tnsnames.oraの定義してある接続文字列がドロップダウンメニューに現れないですね。TNS_ADMIN環境変数などが効いていません。
Oracle_sql_developer_not_appear_con


次にApple Technical Q&A QA1067 : Setting environment variables for user processesの説明通りにenvironment.plistを作成後、一旦ログオフする。
(尚、environment.plistに設定する環境変数に関しては、前回のエントリを参照してください。

Last login: Mon Feb  4 18:58:45 on console
Macintosh:˜ discus$ mkdir .MacOSX
Macintosh:˜ discus$ cat environment.plust
cat: environment.plust: No such file or directory
Macintosh:˜ discus$ cat environment.plist
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>DYLD_LIBRARY_PATH</key>
<string>/Users/Shared/oracleInstantClient</string>
<key>NLS_LANG</key>
<string>Japanese_Japan.AL32UTF8</string>
<key>ORACLE_HOME</key>
<string>/Users/Shared/oracleInstantClient</string>
<key>TNS_ADMIN</key>
<string>/Users/Shared/oracleInstantClient</string>
</dict>
</plist>
Macintosh:˜ discus$ mv environment.plist .MacOSX
Macintosh:˜ discus$

再度、ログインして、Oracle SQL Developerを起動し同じ手順でTNS接続できるか確認する。
お〜〜、こんどは、tnsnames.oraに定義してある接続文字列がドロップダウンメニューにリストされています。
Oracle_sql_developer_appear_connect


接続テストも上手く行きました。
Oracle_sql_developer_test_session_s


最後に、SQL WorkSheetなどでEMP表にデータを登録したり問い合わせたりしてみました。これまた問題なく使えます。
Oracle_sql_developer_insert_data_to

Oracle_sql_developer_query_data_fro


この例では、MacOSX Leopard(Intel Mac)のRosetta配下で動作しているOracle Instant Clientを利用しましたが、MacOSX Tiger(PPC)版でも同様の手順で動作します。
興味のある方はお試しあれ。。..

| | コメント (0) | トラックバック (0)

2007年8月19日 (日)

PL/SQL de Conditional Compile #5

さて、PL/SQL条件付きコンパイルもネタが無くなってきたので、最後に、DBMS_DB_VERSIONパッケージを利用して、各リリース毎にコードを切り替えてみよう!
ということで、Oracle database 10g以前のリリースとOracle databse 11g以降のリリースでコードを切り替える例。

● 条件付きコンパイルが無い場合、Oracle11gより前のリリースで以下ようなコードを書いてしまったら....

当然コンパイルエラー!
FUNCTION CONDITIONAL_COMP_SAMPLE3 RETURN NUMBER
IS
vSeq# NUMBER;
BEGIN
vSeq# := mySeq.NEXTVAL;
RETURN vSeq#;
END CONDITIONAL_COMP_SAMPLE3;
/

警告: ファンクションが変更されましたが、コンパイル・エラーがあります。

SCOTT> show errors
FUNCTION CONDITIONAL_COMP_SAMPLE3のエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/5 PL/SQL: Statement ignored
9/20 PLS-00357:
表またはビュー、シーケンス参照MYSEQ.NEXTVALは、このコンテキストで
は使用できません。

SCOTT>

● しかし、DBMS_DB_VERSIONパッケージでリリース毎にコードを選択し切り替えたら・・・・便利ですよね。

CREATE OR REPLACE FUNCTION CONDITIONAL_COMP_SAMPLE3 RETURN NUMBER 
IS
vSeq# NUMBER;
BEGIN

$IF DBMS_DB_VERSION.Ver_LE_10 $THEN
SELECT mySeq.NEXTVAL INTO vSeq# FROM DUAL;
$ELSE
vSeq# := mySeq.NEXTVAL;
$END

RETURN vSeq#;

END CONDITIONAL_COMP_SAMPLE3;

DBMS_DB_VERSIONパッケージをリリース番号でどのようにソースコードが変化するか確認。
● Oracle database 10g以前のバージョンでコンパイルした場合

SCOTT> 
SCOTT> set serveroutput on
SCOTT> exec dbms_preprocessor.print_post_processed_source('FUNCTION','SCOTT','CONDITIONAL_COMP_SAMPLE3');
FUNCTION CONDITIONAL_COMP_SAMPLE3 RETURN NUMBER
IS
vSeq# NUMBER;
BEGIN
SELECT mySeq.NEXTVAL INTO vSeq# FROM DUAL;
RETURN vSeq#;
END CONDITIONAL_COMP_SAMPLE3;

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


● Oracle database 11g以降でコンパイルした場合

SCOTT> exec dbms_preprocessor.print_post_processed_source('FUNCTION','SCOTT','CONDITIONAL_COMP_SAMPLE3');
FUNCTION CONDITIONAL_COMP_SAMPLE3 RETURN NUMBER
IS
vSeq# NUMBER;
BEGIN
vSeq# := mySeq.NEXTVAL;
RETURN vSeq#;
END CONDITIONAL_COMP_SAMPLE3;

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

SCOTT>


● 各リリース毎のDBMS_DB_VERSIONパッケージの違い
参考 http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_dbver.htm

● 10.2:
package DBMS_DB_Version is 
Version constant pls_integer := 10;
Release constant pls_integer := 2;
Ver_LE_9_1 constant boolean := false;
Ver_LE_9_2 constant boolean := false;
Ver_LE_9 constant boolean := false;
Ver_LE_10_1 constant boolean := false;
Ver_LE_10_2 constant boolean := true;
Ver_LE_10 constant boolean := true;
end DBMS_DB_Version;
● 10.1:
package DBMS_DB_Version is 
Version constant pls_integer := 10;
Release constant pls_integer := 1;
Ver_LE_9_1 constant boolean := false;
Ver_LE_9_2 constant boolean := false;
Ver_LE_9 constant boolean := false;
Ver_LE_10_1 constant boolean := true;
Ver_LE_10 constant boolean := true;
end DBMS_DB_Version;
● 9.2:
package DBMS_DB_Version is 
version constant pls_integer := 9;
release constant pls_integer := 2;
Ver_LE_9_1 constant boolean := false;
Ver_LE_9_2 constant boolean := true;
Ver_LE_9 constant boolean := true;
end DBMS_DB_Version;




PL/SQL de Conditional Compile #1
PL/SQL de Conditional Compile #2
PL/SQL de Conditional Compile #3
PL/SQL de Conditional Compile #4

| | コメント (0) | トラックバック (0)

2007年8月18日 (土)

PL/SQL de Conditional Compile #4

PL/SQLの条件付きコンパイルのつづきです。

今回は、エラーディレクティブ($ERROR)を利用して、ちょっとした物忘れ対策を!

どのような物忘れ対策かというと、コード中に書かなければならないロジックがあるが、今は書かずに後で追加しようとコメントでメモを残しておいた。
だが〜〜、うっかりミスで、コーディングしないまま、結合テストに〜〜〜〜〜!。あ〜〜〜、大変だ〜!!!! 

なんてことを、防止できるかも・・・・・・・・。しれないという使い方!。

たとえば、以下のように、後でコードを追加しようとストアドファンクションをロジックなして仮に作成しておいたとする。
後でロジックを追加するというこを、すっかり忘れてしまっていると、テスト段階になって、あれ? 結果が帰ってこない!!! ソースを見ると。
あ〜〜〜〜、忘れてた〜〜〜 (^^;;;;;;;

なんてことに・・・・ コンパイルが通ってしまうので、実行してみないと気付かない。。なんてことになっちゃいます。

SCOTT> l
1 create or replace
2 FUNCTION CONDITIONAL_COMP_SAMPLE2
3 RETURN TIMESTAMP
4 IS
5 vNow TIMESTAMP;
6 BEGIN
7 -- TODO - 結合テストまでに、ここにロジック書かなきゃ!
8 RETURN vNow;
9* END CONDITIONAL_COMP_SAMPLE2;
10 /

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

SCOTT> set null 'NULL!'
SCOTT> select conditional_comp_sample2 from dual;

CONDITIONAL_COMP_SAMPLE2
---------------------------------------------------------------------------
NULL!

SCOTT>

しかし、$ERRORディレクティブを利用して、コンパイルエラーになるようにしておけば。。。。
さらに、エラーメッセージには、やることを思い出させてくれるようなコメントを設定しておけば、これまた、物忘れの激しい方でもなんとかなるかもしれません。

SCOTT> l
1 CREATE OR REPLACE
2 FUNCTION CONDITIONAL_COMP_SAMPLE2
3 RETURN TIMESTAMP
4 IS
5 vNow TIMESTAMP;
6 BEGIN
7
8 -- TODO - 結合テストまでに、ここにロジック書かなきゃ!
9 $ERROR
10 '結合テストまでに、ここにロジック書かなきゃ!'
11 $END
12
13
14 RETURN vNow;
15
16* END CONDITIONAL_COMP_SAMPLE2;
SCOTT> /

警告: ファンクションが変更されましたが、コンパイル・エラーがあります。

SCOTT> show errors
FUNCTION CONDITIONAL_COMP_SAMPLE2のエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3 PLS-00179: $ERROR: 結合テストまでに、ここにロジック書かなきゃ!
SCOTT>

応用編としては、上記のように、単純に、コンパイルエラーにはせず、前々回のエントリで利用したPLSQL_DEBUG初期化パラメータ等と組み合わせ、デバッグ時はコンパイル正常に行わせるという使い方も考えられます。


参考
http://www.oracle.com/technology/oramag/oracle/06-jul/o46plsql.html
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#BEIJFDHG

| | コメント (0) | トラックバック (0)

2007年8月17日 (金)

PL/SQL de Conditional Compile #3

PL/SQL条件付きコンパイルの続きです。

前回までのおさらい。

初期化パラメータをセッション単位で変更してコンパイルしていたが、Oracle10g R1 10.1.0.4.0以降及び、Oracle9i R2 9.2.0.6.0以降へバックポートされた条件付きコンパイル機能では一部利用できない初期化パラメータがあり、全リリースで共通のソースコードを利用するには、各リリースで利用できる初期化パラメータ PLSQL_DEBUG を利用するようにした。というところまでだった。

今回はいままでとは異なる方法で条件付きコンパイルを行ってみることにする。

その方法とは、初期化パラメータを利用せずパッケージの仕様部で宣言した定数を利用する方法だ。
この方法であれば、リリース毎にいくつかの制限のある初期化パラメータを利用せずに条件コンパイルが可能だ。

但し、その都度、パッケージ定数を変更するという手間はかかる。
実際に利用する際には、定数を変更する為のスクリプトを事前に用意しておき、そのスクリプトを実行するだけで行えるようにすると良いだろう。
また、パッケージをどのスキーマに定義するか、という点なども、よ〜〜〜く検討した上で利用した方が良いだろう。
ルール無しで利用した場合、後々混乱の原因になる可能性があるということは容易に想像できると思う。

● 条件コンパイルのフラグだけを定義したパッケージを作成する

以下の例では、C_DEBUG_ON定数だけを定義し、値はfalseとした。
CREATE OR REPLACE PACKAGE MyPROPERTIES AS

C_DEBUG_ON CONSTANT BOOLEAN := false;

END MyPROPERTIES;

● 以下、条件コンパイルのサンプルコード

以前作成したコードとの違いは、初期化パラメータではなく、前述したパッケージのC_DEBUG_ON定数を参照している点である。
CREATE OR REPLACE FUNCTION CONDITIONAL_COMPILATION_SAMPLE
RETURN TIMESTAMP
IS
vNow TIMESTAMP;
BEGIN
$IF myproperties.c_debug_on $THEN
DBMS_OUTPUT.ENABLE(20000);
DBMS_OUTPUT.PUT_LINE('======= start =====');
$END

SELECT SYSTIMESTAMP INTO vNow FROM dual;

$IF myproperties.c_debug_on $THEN
DBMS_OUTPUT.PUT_LINE('======= end =======');
$END

RETURN vNow;

END CONDITIONAL_COMPILATION_SAMPLE;

● MYPROPERTIES.C_DEBUG_ON=falseで上記サンプルコードをコンパイルし実行した例

Conditional_compilation137 Conditional_compilation138 Conditional_compilation139Conditional_compilation1310


● MYPROPERTIES.C_DEBG_ON=trueで、上記サンプルコードをコンパイルし実行した例

Conditional_compilation131 Conditional_compilation132Conditional_compilation133
Conditional_compilation134 Conditional_compilation135 Conditional_compilation136

| | コメント (0) | トラックバック (0)

2007年8月16日 (木)

PL/SQL de Conditional Compile #2


とにかく、熱過ぎる! 沖縄の方が気温が低いってどうよ!? 避暑のために、石垣島なんていいかも。 午前10時の気温。東京:摂氏34.6度、沖縄:摂氏29.9度 沖縄のほうが気温が低い!(爆笑) http://www.weather-eye.com/amedas_graph/index.html

yanokami (矢野顕子 × レイ・ハラカミ) - yanokami - EP - You Showed Me yanokami (矢野顕子 × レイ・ハラカミ) - yanokami - EP - You Showed Me



さて、PL/SQL条件付きコンパイルの続きです。

前回は、本機能が正式にサポートされているOracle10g R2 10.2.0.2.0で行ったが、今回は、バックポートされた Oracle10g R1 10.1.0.4.0で行う。
PL/SQL条件付きコンパイル機能は、前回も書いたように、Oracle10g R1 10.1.0.4.0以降、Oracle9i R2 9.2.0.6.0以降へバックポートされている。

但し、Oracle10g R1 10.1.0.4.0以降及び、Oracle9i R2 9.2.0.6.0以降へバックポートされたPL/SQL条件付きコンパイルでは幾つかの制限があり、Oracle10g R2 からサポートされている同機能と比べて利用できない機能などが存在するので注意が必要だ。
ちなみに、前回のエントリで紹介したPLSQL_CCFLAG初期化パラメータは、パックポートされたOracle10g R1 10.1.0.4.0及び、Oracle9i R2 9.2.0.6.0以降では利用できないと記載されている。

参考:plsql_conditional_compilation.pdfのFunctionality restrictions in 10.1 and 9.2 (P.61)


では、前述の制限を踏まえつつ、以下、Oracle10g 10.1.0.4.0でのテストを!

環境:Oracle10g R1 10.1.0.4.0 for Linux x86
 (TurboLinux Enterprise Server 8 SP3- powered by United Linux 1.0)

前回の例では、ユーザ定義フラグとして、独自に DEBUGONブラグをPLSQL_CCFLAGS初期化パラメータで設定していたが、前述の制限の通り、ハックポートされた機能では利用できない。

そのため、バックポートされた条件付きコンパイルでも利用できるよう、PLSQL_DEBUG初期化パラメータを利用することにした。
このパラメータを利用すれば、Oracle10g R2/R1(10.1.0.4.0以降)/Oracle9i R2(9.2.0.6.0以降)それにOracle11gでも利用できるコードにすることができる。

注)初期化パラメータをしなくても各リリースでソースコードを共有できる方法がある。
但し、初期化パラメータを利用する方法に比べ、いちいちコードに手を加えなければならないなど、イマイチな感じはある。(別途掲載予定)

Conditional_compilation_sql_develop

● まずは、PLSQL関連の初期化パラメータの確認から。

PL/SQL関連の隠しパラメータが幾つかあるが、ここで大切なのは、_plsql_conditional_compilationという初期化パラメータでこれがTRUEであれば、PL/SQLの条件コンパイルが機能する。
また、Oracle10g R2には存在していた、PLSQL_CCFLAGS初期化パラメータが存在していないことも確認できる。前述した通り、今回は、PLSQL_DEBUG初期化パラメータを利用して条件コンパイルを行うことにする。

SYS> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SYS> l
1 select
2 a.ksppinm,
3 b.ksppstvl,
4 b.ksppstdf
5 from
6 x$ksppi a join x$ksppcv b
7 on a.indx = b.indx
8 and a.ksppinm like '%plsql%'
9 order by
10* a.ksppinm
SYS> /

KSPPINM KSPPSTVL KSPPSTDF
---------------------------------- ------------------------------ ------------------------------
_plsql_anon_block_code_type INTERPRETED TRUE
_plsql_conditional_compilation TRUE FALSE
_plsql_dump_buffer_events TRUE
plsql_code_type INTERPRETED TRUE
plsql_compiler_flags INTERPRETED, NON_DEBUG TRUE
plsql_debug FALSE TRUE
plsql_native_library_dir TRUE
plsql_native_library_subdir_count 0 TRUE
plsql_optimize_level 2 TRUE
plsql_v2_compatibility FALSE TRUE
plsql_warnings DISABLE:ALL TRUE

11行が選択されました。

SYS>

● 前回のコードから変更したコードは赤字部分

SCOTT> l
1 create or replace
2 FUNCTION CONDITIONAL_COMPILATION_SAMPLE
3 RETURN TIMESTAMP
4 IS
5 vNow TIMESTAMP;
6 BEGIN
7 $IF $$PLSQL_DEBUG $THEN
8 DBMS_OUTPUT.ENABLE(20000);
9 DBMS_OUTPUT.PUT_LINE('======= start =====');
10 $END
11 SELECT SYSTIMESTAMP INTO vNow FROM dual;
12 $IF $$PLSQL_DEBUG $THEN
13 DBMS_OUTPUT.PUT_LINE('======= end =======');
14 $END
15 RETURN vNow;
16* END CONDITIONAL_COMPILATION_SAMPLE;
17 /

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

● では、PLSQL_DEBUG=falseとして確認!

SCOTT> set serveroutput on
SCOTT> alter session set plsql_debug=false;

セッションが変更されました。

SCOTT> alter function conditional_compilation_sample compile;

ファンクションが変更されました。

SCOTT> exec dbms_preprocessor.print_post_processed_source('FUNCTION','SCOTT','CONDITIONAL_COMPILATION_SAMPLE');
FUNCTION CONDITIONAL_COMPILATION_SAMPLE
RETURN TIMESTAMP
IS
vNow TIMESTAMP;
BEGIN
SELECT SYSTIMESTAMP INTO vNow FROM dual;
RETURN vNow;
END CONDITIONAL_COMPILATION_SAMPLE;

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


SCOTT> select conditional_compilation_sample from dual;

CONDITIONAL_COMPILATION_SAMPLE
---------------------------------------------------------------------------
07-08-13 22:53:14.161995000


● 次に、PLSQL_DEBUG=trueとして確認!

SCOTT> alter session set plsql_debug=true;

セッションが変更されました。

SCOTT> alter function conditional_compilation_sample compile;

ファンクションが変更されました。

SCOTT> exec dbms_preprocessor.print_post_processed_source('FUNCTION','SCOTT','CONDITIONAL_COMPILATION_SAMPLE');
FUNCTION CONDITIONAL_COMPILATION_SAMPLE
RETURN TIMESTAMP
IS
vNow TIMESTAMP;
BEGIN
DBMS_OUTPUT.ENABLE(20000);
DBMS_OUTPUT.PUT_LINE('======= start =====');
SELECT SYSTIMESTAMP INTO vNow FROM dual;
DBMS_OUTPUT.PUT_LINE('======= end =======');
RETURN vNow;
END CONDITIONAL_COMPILATION_SAMPLE;

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

SCOTT> select conditional_compilation_sample from dual;

CONDITIONAL_COMPILATION_SAMPLE
---------------------------------------------------------------------------
07-08-13 22:54:01.071903000

======= start =====
======= end =======
SCOTT>
SCOTT>


次回は、初期化パラメータを利用しない場合コードの例を載せる予定。

| | コメント (0) | トラックバック (0)

2007年8月15日 (水)

PL/SQL de Conditional Compile #1

PL/SQLで条件付きコンパイル? 耳慣れないのも方も多いかもしれない。 C/C++では、おなじみの#ifdefなどのディレクティブ!

そう、それです! プリプロセッサといえば分かり易いでしょうか。 
Oracle10g R2以降で、正式に、PL/SQLのプリプロセッサが実装されています。(正式にと書いたのには理由があります、実は、この条件付きコンパイルは、Oracle10g R1 10.1.0.4以降、Oracle9i R2 9.2.0.6.0以降にバックポートされています。) 

マニュアルでは、 conditional compilation (条件付きコンパイル)として解説されています。

PL/SQLの条件付きコンパイル、マニュアルには記載はされているものの、この機能を解説している日本語のサイトはほとんど見た事がありませんし、実際の所、私もまだ本格的に利用したことはありません。しかし、うまく使えばそれなりの効果は期待できそうですね。

では、早速、条件付きコンパイルの例を。

● 正式に機能追加されたリリースである、Oracle10g R2で試す。

SYS> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

初期化パラメータにいくつかのPL/SQLコンパイル用パラメータが追加されている。 (Oracle10g R1や、Oracle9i R2と比較するわかりやすいかも。。) 以下は、初期化パラメータの幾つかをリストしたもの。詳細はマニュアル参照のこと。
SYS> show parameter plsql

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_debug boolean FALSE
plsql_native_library_dir string
plsql_native_library_subdir_count integer 0
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL


● 条件付きコンパイル、PLSQL_CCFLAG初期化パラメータで独自フラグを利用する例

以下は、PLSQL_CCFLAG初期化パラメータで、DEBUGONという独自のフラグを設定し、条件付きコンパイルを行う例で、選択ディレクティブである$IF - $THENを利用してデバッグ用ログの表示コードを制御しています。

PLSQL_CCFLAGで、debugonフラグをtrueに設定すれば、DBMS_OUTPUT.PUT_LINE()でデバッグ用ログを表示するコードがコンパイルされ、false又はフラグが設定されていなければ、表示しないコードでコンパイルされます。

SCOTT> l
1 create or replace
2 FUNCTION CONDITIONAL_COMPILATION_SAMPLE
3 RETURN TIMESTAMP
4 IS
5 vNow TIMESTAMP;
6 BEGIN
7 $IF $$DEBUGON $THEN
8 DBMS_OUTPUT.ENABLE(20000);
9 DBMS_OUTPUT.PUT_LINE('======= start =====');
10 $END
11 SELECT SYSTIMESTAMP INTO vNow FROM dual;
12 $IF $$DEBUGON $THEN
13 DBMS_OUTPUT.PUT_LINE('======= end =======');
14 $END
15 RETURN vNow;
16* END CONDITIONAL_COMPILATION_SAMPLE;
17 /

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


● dbms_preprocessorパッケージによる条件付きコンパイル後のソースの確認

dbms_preprocessorパッケージのprint_post_processed_source()プロシージャを利用すると、条件付きコンパイル後のソースを確認することができる。(all_sourceビューには、条件付きコンパイル前のソースが格納されているため、条件付きコンパイル後のソースとは異なってしまう。)
DBMS_PREPROCESSORパッケージに関する詳細は、マニュアルを参照のこと。

SCOTT> set serveroutput on
SCOTT> exec dbms_preprocessor.print_post_processed_source('FUNCTION','SCOTT','CONDITIONAL_COMPILATION_SAMPLE');
FUNCTION CONDITIONAL_COMPILATION_SAMPLE
RETURN TIMESTAMP
IS
vNow TIMESTAMP;
BEGIN
SELECT SYSTIMESTAMP INTO vNow FROM dual;
RETURN vNow;
END CONDITIONAL_COMPILATION_SAMPLE;

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

上記のコードは、PLSQL_CCFLAG初期化パラメータにdebugonフラグが未定義又は、falseで定義された場合のコードである。


● 実行してみると、デバッグ用ログは表示されていない。(当然!)

SCOTT> set serveroutput on
SCOTT> select conditional_compilation_sample from dual;

CONDITIONAL_COMPILATION_SAMPLE
---------------------------------------------------------------------------
07-08-13 18:20:39.234000000

SCOTT> set serveroutput off

● PLSQL_CCFLAG初期化パラメータに debugonフラグをtrueで設定してみると・・・。

SCOTT> 
SCOTT> alter session set plsql_ccflags='debugon:true';

セッションが変更されました。

SCOTT> l
1 create or replace
2 FUNCTION CONDITIONAL_COMPILATION_SAMPLE
3 RETURN TIMESTAMP
4 IS
5 vNow TIMESTAMP;
6 BEGIN
7 $IF $$DEBUGON $THEN
8 DBMS_OUTPUT.ENABLE(20000);
9 DBMS_OUTPUT.PUT_LINE('======= start =====');
10 $END
11 SELECT SYSTIMESTAMP INTO vNow FROM dual;
12 $IF $$DEBUGON $THEN
13 DBMS_OUTPUT.PUT_LINE('======= end =======');
14 $END
15 RETURN vNow;
16* END CONDITIONAL_COMPILATION_SAMPLE;
17 /

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


SCOTT> exec dbms_preprocessor.print_post_processed_source('FUNCTION','SCOTT','CONDITIONAL_COMPILATION_SAMPLE');
FUNCTION CONDITIONAL_COMPILATION_SAMPLE
RETURN TIMESTAMP
IS
vNow TIMESTAMP;
BEGIN
DBMS_OUTPUT.ENABLE(20000);
DBMS_OUTPUT.PUT_LINE('======= start =====');
SELECT SYSTIMESTAMP INTO vNow FROM dual;
DBMS_OUTPUT.PUT_LINE('======= end =======');
RETURN vNow;
END CONDITIONAL_COMPILATION_SAMPLE;

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

SCOTT>
SCOTT> set serveroutput on
SCOTT> select conditional_compilation_sample from dual;

CONDITIONAL_COMPILATION_SAMPLE
---------------------------------------------------------------------------
07-08-13 18:26:13.906000000

======= start =====
======= end =======
SCOTT>
ということで、デバッグ用ログが表示されるコードでコンパイルされています!。

次回は、Oracle10g R1 10.1.0.4.0で試す予定。
Conditional_compilation_sql_develop

| | コメント (0) | トラックバック (0)

2007年8月 5日 (日)

Estimate of rollback completion time

さて、前回、おおよそのロールバック完了時刻を見積もる関数でも作ってみるか! なんて言っていたが実際に作ってみた。

尚、今回は、Oracle SQL Developer 1.2 for MacOSXからWindows XP professional上のOracle10g R2 EEへ接続。

Estimate_rollback_complete1 Estimate_rollback_complete2 Estimate_rollback_complete3

● 準備

まずは、テーブルの作成から。
SCOTT> 
SCOTT> desc test
名前 NULL? 型
----------------------------------------- -------- ----------------------------
DATA VARCHAR2(4000)

SCOTT>
SCOTT>
SCOTT>

● データの登録

ロールバックに長時間を要するデータを登録する。(更新でも削除でも構わないが、この例ではINSERT文で。)
SCOTT> begin 
2 for i in 1..400000 loop
3 insert into test values(lpad('x',4000,'x'));
4 end loop;
5 end;
6 /

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

経過: 00:05:30.06

SCOTT>
SCOTT>

● ロールバック!

準備が整ったので、早速、ロールバック!!
SCOTT>
SCOTT> rollback;

以下、ロールバック処理中に別途起動した、SQL*plusより実行

========以下、ロールバック中、別途起動したSQL*plusから実行した内容==============

● おおよそのロールバック完了時刻を確認する。

自作関数estimateRollbackCompleteTime()を以下のようにスカラ関数として実行すれば、おおよそのロールバック完了日時を確認できる。尚、関数の引数は、ロールバック完了時刻を見積もりたいセッションのSIDとSERIAL#で事前にv$session、v$transactionを問い合わせて確認しておく。

尚、今回は、dbms_lockパッケージの実行権限と、select any dictionaryシステム権限をSCOTTユーザへ付与し、estimateRollbackCompleteTime()関数を作成してある。

SCOTT> select estimateRollbackCompleteTime(146,91) as "Estimate Time",sysdate as now from dual;
Estimate Time NOW
------------------- -------------------
2007/08/02 16:29:46 2007/08/02 16:21:16

SCOTT>


● 以下、UNDOブロックのモニタリング。

ロールバックが進み、used_ublkの値が減少していくことが確認できる。ロールバックの完了時刻は、前述の自作関数で求めたおおよそのロールバック完了時間に近い。
環境などにも影響sれるのだが、予想時間を超えることもあるし、短くなる場合もあるので何度か実行してみるといいですね。または、関数を改造して、見積もりのベースとしている30秒間の処理件数を1分間の処理件数に変更するなどして、精度を高めることは可能かもしれない。
ただ、いつ終わるか分からないロールバック処理時間のおおよその時間を知るにはこのままでも十分なのではないかと思う。

SYSTEM> r
1 select
2 s.sid,
3 s.serial#,
4 s.username,
5 t.used_ublk
6 ,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') as time
7 from
8 v$session s join v$transaction t
9 on t.addr = s.taddr
10 where
11* s.username = 'SCOTT'

SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 1813 2007/08/02 16:19:13

経過: 00:00:00.01

SYSTEM> /

SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 1594 2007/08/02 16:24:21

経過: 00:00:00.00
SYSTEM> /

SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 882 2007/08/02 16:26:45

経過: 00:00:00.01
SYSTEM> /

SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 747 2007/08/02 16:27:10

経過: 00:00:00.01
SYSTEM> /

SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 668 2007/08/02 16:27:24

経過: 00:00:00.01
SYSTEM> /

SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 28 2007/08/02 16:29:23

経過: 00:00:00.01
SYSTEM> /

レコードが選択されませんでした。

SYSTEM> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') as now from dual;

NOW
-------------------
2007/08/02 16:29:30

経過: 00:00:00.01
SYSTEM>

ということで、今回は、たまたま、見積もり時刻より早めに終わったようですね。ラッキー!

===========ロールバック中に別途起動したSQL*Plusでのログ、ここまで==============

ロールバックが完了しました。

経過: 00:14:34.83
SCOTT>


以下、ロールバック完了時間の見積もり関数の例。
内容は単純で、2点間のused_ublk数の残量から単位時間あたりのロールバック処理時間をもとめ、その時点のused_ublk数を元にロールバックが完了するおおよその日時を算出しているだけ。FORループを利用しているのは、単に、明示カーソルのオープン/フェッチ/クローズと例外処理を書かずにすむから。特に必要性がなければ、このプログラミングスタイルが個人的には好き。(この辺りは、好みの問題だけど。)

CREATE OR REPLACE FUNCTION estimateRollbackCompleteTime
(
iSid IN NUMBER,
iSerial# IN NUMBER
)
RETURN DATE IS
C_INTERVAL CONSTANT PLS_INTEGER := 30;

vStartTimestamp TIMESTAMP;
vEndTimestamp TIMESTAMP;
vStartUsedUndoBlocks NUMBER;
vEndUsedUndoBlocks NUMBER;
vEstimatedSecs NUMBER;
vIsFound BOOLEAN;

CURSOR csr_undoblocks
(
pSid IN NUMBER,
pSerial# IN NUMBER
) IS
SELECT
s.username,
t.used_ublk,
SYSTIMESTAMP AS now
FROM
v$session s JOIN v$transaction t
ON t.addr = s.taddr
WHERE
s.sid = pSid
AND s.serial# = pSerial#;
BEGIN
vIsFound := FALSE;
FOR rUndoBlocks IN csr_undoBlocks(iSid, iSerial#) LOOP
vStartTimeStamp := rUndoBlocks.now;
vStartUsedUndoBlocks := rUndoBlocks.used_ublk;
vIsFound := TRUE;
END LOOP;

IF NOT vIsFound THEN
RETURN NULL;
ELSE
vIsFound := FALSE;
END IF;

DBMS_LOCK.SLEEP(C_INTERVAL);

FOR rUndoBlocks IN csr_undoBlocks(iSid, iSerial#) LOOP
vEndTimestamp := rUndoBlocks.now;
vEndUsedUndoBlocks := rUndoBlocks.used_ublk;
vIsFound := TRUE;
END LOOP;

IF NOT vIsFound
OR (vStartUsedUndoBlocks - vEndUsedUndoBlocks) <= 0
THEN
RETURN NULL;
ELSE
vEstimatedSecs :=
CEIL(vEndUsedUndoBlocks / (vStartUsedUndoBlocks - vEndUsedUndoBlocks)) * C_INTERVAL;
RETURN SYSDATE + NUMTODSINTERVAL(vEstimatedSecs, 'SECOND');
END IF;
END;

| | コメント (0) | トラックバック (0)

2007年6月 8日 (金)

Mac De Oracle : PL/SQL de Collection #5


Marcus Miller - Silver Rain - It'll Come Back to YouMarcus Miller - Silver Rain - It'll Come Back to You

ちょいと間が空いたが、PL/SQLのコレクションでのお遊び。その5回目。

検索ワードで結構目にするのが、"N次元 結合配列" というキーワード。

ということで、今回は、多次元のコレクションの例を。

N次元のコレクションってどうやって作るの? ってことで情報を検索しているのだと思いますが、しっかりマニュアルにも記載されているので、そちらもしっかり読んでおくことをおすすめしますよ!。

Associative Array(以下、結合配列)、VARRAYやNested Table(以下、ネスト表)どれでも、考え方は同じ。
コレクション自体を別コレクションの要素にすればできるんですよ。 コレクションを入れ子にする と言えばイメージしやすい? ですかね。


以下のコードで赤太字で示しているところが多次元コレクション定義とアクセスのポイント。
まず、empType型を要素とするNestedTableType型(ネスト表)を定義し、次にNestedTalbType型を要素とするStrAssociativeArrayType型(結合配列)を定義している。
以下の例は多少、ひねくれた例? として結合配列とネスト表という異なるコレクションを利用して多次元コレクションを定義している。その影響でコレクションの操作は多少面倒になるのだが..

尚、empType型は、事前に作成しておいたオブジェクト型。

create or replace
PROCEDURE multiLevelCollections
AS
TYPE NestedTableType IS TABLE OF empType;
TYPE StrAssociativeArrayType IS TABLE OF NestedTableType INDEX BY VARCHAR2(50);

myNestedTable NestedTableType := NestedTableType();
myStrAssociativeArray StrAssociativeArrayType;


claerMyStrAssociateArray StrAssociativeArrayType;

myEmp empType;
j dept.dname%TYPE;
vDeptName dept.dname%TYPE := NULL;

CURSOR csrEmp IS
SELECT
emp.job,
emp.deptno,
dept.dname,
emp.empno,
emp.ename,
emp.sal,
emp.hiredate
FROM
emp JOIN dept
ON emp.deptno = dept.deptno
ORDER BY
emp.deptno,
emp.ename;

PROCEDURE printArray
IS
BEGIN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('**** コレクションに要素が存在している状態 ****');
DBMS_OUTPUT.PUT_LINE(
'myStrAssociativeArray('
|| myStrAssociativeArray.COUNT
|| ')'
);

j := myStrAssociativeArray.FIRST;
WHILE j IS NOT NULL LOOP
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE(
'myStrAssociativeArray('
|| j || ')(' || myStrAssociativeArray(j).COUNT || ')'
);

FOR k IN
myStrAssociativeArray(j).FIRST..myStrAssociativeArray(j).LAST
LOOP
DBMS_OUTPUT.PUT_LINE(
'myStrAssociativeArray(' || j || ').'
|| 'myNestedTable(' || TO_CHAR(k) || ') = '
|| myStrAssociativeArray(j)(k).TO_STRING()
);
END LOOP;
j := myStrAssociativeArray.NEXT(j);
END LOOP;
END;

BEGIN
FOR emp_rec IN csrEmp LOOP
IF csrEmp%ROWCOUNT = 1 THEN
vDeptName := emp_rec.dname;
END IF;

myEmp := empType (
emp_rec.empno,
emp_rec.ename,
emp_rec.job,
emp_rec.hiredate,
emp_rec.sal,
emp_rec.deptno
);

IF vDeptName <> emp_rec.dname THEN
myStrAssociativeArray(vDeptName) := myNestedTable;
vDeptName := emp_rec.dname;
myNestedTable := NestedTableType();
END IF;

myNestedTable.EXTEND(1);
myNestedTable(myNestedTable.COUNT) := myEmp;
END LOOP;

IF myNestedTable.COUNT > 0 THEN
myStrAssociativeArray(vDeptName) := myNestedTable;
END IF;

printArray();

-- 結合配列を空にする!
myStrAssociativeArray := claerMyStrAssociateArray;

DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('**** コレクションが空の状態 ****');
DBMS_OUTPUT.PUT_LINE(
'myStrAssociativeArray('
|| myStrAssociativeArray.COUNT
|| ')'
);


END;
/

SCOTT> l
1 select
2 emp.deptno,
3 dept.dname,
4 emp.empno,
5 emp.ename,
6 emp.sal,
7 emp.hiredate
8 from
9 emp join dept
10 on emp.deptno = dept.deptno
11 order by
12 emp.deptno,
13* emp.ename
SCOTT> /

DEPTNO DNAME EMPNO ENAME SAL HIREDATE
---------- -------------- ---------- ---------- ---------- --------
10 ACCOUNTING 7782 CLARK 2450 81-06-09
10 ACCOUNTING 7839 KING 5000 81-11-17
20 RESEARCH 7876 ADAMS 1100 87-05-23
20 RESEARCH 7566 JONES 2975 81-04-02
20 RESEARCH 7788 SCOTT 3000 87-04-19
20 RESEARCH 7369 SMITH 800 80-12-17
30 SALES 7499 ALLEN 1600 81-02-20
30 SALES 7698 BLAKE 2850 81-05-01
30 SALES 7900 JAMES 950 81-12-03
30 SALES 7654 MARTIN 1250 81-09-28
30 SALES 7844 TURNER 1500 81-09-08
30 SALES 7521 WARD 1250 81-02-22

12行が選択されました。

SCOTT>

実行結果は次のようになる。(Oracle SQL Developer 1.1 for MacOSX) 
1.2がリリースされたようなので後でダウンロードしとくか・・> TODO

Oracleplsqldev11

Mlevelcollresults


ということで、今日はここまで。

Have a good Week end!

| | コメント (0) | トラックバック (0)

2007年6月 6日 (水)

PL/SQL de File Upload / Download #5


Kate Bush - The Whole Story - Experiment IV Kate Bush - The Whole Story - Experiment IV


PL/SQL de File Upload / Download の最終回。

さて、前回は、日本語ファイル名のファイルをダウンロードするとFirefoxやMozilla以外のブラウザでは文字化け等が発生するというところまでだった。

また、IE6では、Content-Disposition: attachment; filenameを指定する方法であれば文字化けを回避できるということも確認した。

ただ、Mac De Oracleでは一番大切な Safari。そのSafariでは常に文字化けしていた。残念!〜〜で終わらせないのがMac De Oracle.

調べた結果、文字エンコードをUTF-8で統一すれば回避できるのでは? という考えに至り、即実行!


OTN-J の Code Tipsでも公開されました。(2007/6/16更新) http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2235 http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2236

下記手順で mod_plsqlのファイルアップロード/ダウンロード環境を再構築した。

  1. データベースキャラクタセットをAL32UTF8で新たなデータベースを作成する。JA16SJISTILDEからAL32UTF8へデータベースキャラクタセットを変更することはできないため、新たにデータベースを作成することになる。
  2. dads.confのPlsqlNLSLanguageをJAPANESE_JAPAN.JA16SJISTILDEからJAPANESE_JAPAN.AL32UTF8へ変更する。
  3. ストアドプロシージャで生成するHTMLのcharsetをShift_JISからUTF-8へ変更する。
  4. utl_url.escapeプロシージャでURLエンコードする際の文字コードをShift_JISからAL32UTF8へ変更する。

1.データベースの作成

DBCA (Database Configuration Assistant)を利用し、データベースキャラクタセット:AL32UTF8のデータベースをサクッと作成しちゃいます。時間のかかるのはこの部分だけですね。
既に、データベースキャラクタセットがAL32UTF8であればこのステップはスキップしてください。

2.dads.confの変更

PlsqlNLSLanguageをJAPANESE_JAPAN.JA16SJISTILDEからJAPANESE_JAPAN.AL32UTF8へ変更します。
# ============================================================================ 
# mod_plsql DAD Configuration File
# ============================================================================
# 1. Please refer to dads.README for a description of this file
# ============================================================================

# Note: This file should typically be included in your plsql.conf file with
# the "include" directive.

# Hint: You can look at some sample DADs in the dads.README file

# ============================================================================
<Location /plsql_de_fileupload>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername scott
PlsqlDatabasePassword tiger
PlsqlDatabaseConnectString 192.168.1.19:1521:amazon
PlsqlAuthenticationMode Basic
PlsqlDefaultPage scott.fileUploadForm
PlsqlDocumentTablename scott.discusDocTable
PlsqlDocumentPath docs
PlsqlDocumentProcedure scott.fileDownloader
PlsqlNLSLanguage JAPANESE_JAPAN.AL32UTF8
</Location>

3.プロシージャの変更
(1)ファイルを最大5件同時にアップロードするフォームを表示するプロシージャの変更。

charsetをShift_JISからUTF-8へ変更するだけ。
CREATE OR REPLACE PROCEDURE fileUpLoadForm
IS
numOfFiles CONSTANT PLS_INTEGER := 5;
BEGIN
htp.htmlopen;
htp.headopen;
htp.meta('CONTENT-Type',null,'text/html; charset=UTF-8');
htp.title('PL/SQL de File Upload');
htp.headclose;

htp.bodyopen;
htp.centeropen;
htp.print('<div align="center" style="width:500; height:600">');
htp.STRONG(
cattributes=> 'style="font-size:24px; color:#ff0000; '
|| 'text-shadow:1px 2px 3px #7f7f7f"',
ctext=> 'PL/SQL de ファイルアップロード'
);
htp.prn('<br><br>');
htp.formopen(
cenctype=>'multipart/form-data',
curl=>'action',cmethod=>'POST'
);

FOR i IN 1..numOfFiles LOOP
htp.prn(
'<p><span style="text-shadow:1px 2px 3px #7f7f7f">ファイルの選択:</span>'
);
htp.formfile(cname=>'file');
htp.br;
END LOOP;
htp.prn('<br><br><p>');
htp.formsubmit(cattributes=>'align=right',cvalue=>'アップロード');

htp.formclose;
htp.print('</div>');
htp.centerclose;
htp.bodyclose;
htp.htmlclose;
END;
/


(2)<FORM>要素のaction属性に指定するプロシージャの変更

charsetをShift_JISからUTF-8へ変更するだけ。
CREATE OR REPLACE PROCEDURE action
(
file IN owa_util.vc_arr
) IS
vOpe# number(10);
BEGIN
htp.htmlopen;
htp.meta('CONTENT-TYPE',null,'text/html; charset=UTF-8');
htp.headopen;
htp.title('PL/SQL de File Upload (Action)');
htp.headclose;
htp.bodyopen;
htp.header(1,'アップロード・ステータス');
htp.print('以下のアップロードが終了しました。<br><br>');
htp.prn('<span style="color:#ff0000; text-shadow:1px 2px 3px #7f7f7f">');
FOR i IN file.FIRST..file.LAST LOOP
IF FILE(i) IS NOT NULL THEN
htp.print('-&gt; ' || file(i) || '<br>');
END IF;
END LOOP;
htp.print('</span>');

-- 同一操作でアップロードしたファイルへ、同一操作番号を設定する。
SELECT upload_operation#.nextval INTO vOpe# FROM dual;
FOR i IN file.FIRST..file.LAST LOOP
UPDATE DISCUSDOCTABLE SET operation# = vOpe# WHERE name=file(i);
END LOOP;

htp.bodyclose;
htp.htmlclose;
END;
/


(3)ダウンロード可能なファイルリストを表示するプロシージャの変更。

charsetをShift_JISからUTF-8へ変更、utl_url.escapeでURLエンコードする際の文字コードをShift_JISからAL32UTF8へ変更。
CREATE OR REPLACE PROCEDURE fileList
IS
BEGIN
htp.htmlopen;
htp.headopen;
htp.meta('CONTENT-Type',null,'text/html; charset=UTF-8');
htp.title('PL/SQL de File upload - ダウンロード可能なファイル一覧');
htp.headclose;
htp.bodyopen;
htp.print(
'<span style="text-shadow:1px 2px 3px #7f7f7f; font-size: 22px">'
|| 'ダウンロードファイル一覧</span><br><br>'
);
FOR doc IN (SELECT name FROM discusDocTable) LOOP
htp.anchor('docs/' || utl_url.escape(doc.name, false, 'AL32UTF8'), 'docs/' || doc.name);
htp.br;
END LOOP;
htp.bodyclose;
htp.htmlclose;
END;
/

(4)ファイルのダウンロード時にmod_plsqlから呼び出されるプロシージャの変更。

charsetをShift_JISからUTF-8へ変更。
CREATE OR REPLACE PROCEDURE fileDownloader
IS
vFilePath VARCHAR2(256);
BEGIN
vFilePath := SUBSTR(owa_util.get_cgi_env('PATH_INFO'), 2);
wpg_docload.download_file(vFilePath);
EXCEPTION
WHEN OTHERS THEN
htp.htmlopen;
htp.headopen;
htp.meta('Ccntent-Type',null,'text/html; charset=UTF-8');
htp.headclose;
htp.bodyopen;
htp.print('ダウンロードファイル:' || vFilePath);
htp.print('<strong style="color:#ff0000">' || sqlerrm() || '</strong>');
htp.bodyclose;
htp.htmlclose;
END fileDownloader;
/


最後に、各プラットフォーム、各ブラウザでダウンロードしているスクリーンショットを。
MacOSX
Safari:
Macosx_safari

Firefox:
Macosx_firefox

Opera9.2:
Macosx_opera92

Linux(TurboLinux8 , CentOS4.4)
Firefox:
Centos44_firefox

SeaMonkey:
Centos44_seamonkey103

Mozilla1.4.2(TurboLinux8):
Turbolinux_mozilla142

Windows XP Professional
IE6:
Windows_ie6

FIrefox:
Windows_firefox


結果として、データベースからHTMLまでの文字エンコードを UTF-8 に統一することで、Firefox以外のブラウザで発生していた文字化けを回避することができるということになる。   (^▽^)v

追記、Oracle10gでは、(おそらく Oracle11g以降も)、新規に作成するデータベースのキャラクタセットは、Unicodeが推奨されているので、データベース作成時のキャラクタセットは、AL32UTF8にしておいたほうがいいだろうね。

以下、マニュアルより引用


データベース・キャラクタ・セットとしてのUnicodeの選択

すべての新規システム配置にUnicodeを使用することをお薦めします。レガシー・システムも最終的にはUnicodeに移行することをお薦めします。現在システムをUnicodeで配置すると、利便性、互換性および拡張性の面で多くの利点があります。Oracle Databaseの包括的なサポートにより、Unicodeの利点を活かしながら高パフォーマンス・システムを高速かつ容易に配置できます。現時点で多言語データをサポートする必要がない場合、またはUnicodeが必要ない場合でも、長期的には新規システムに最適な選択となる可能性が高く、結局は時間を短縮してコストを節減し、競争上の優位性を得ることになります。Unicodeの詳細は、第6章「Unicodeを使用した多言語データベースのサポート」を参照してください。


ついでに、グローバリゼージョンガイドの6 Unicodeを使用した多言語データベースのサポートあたりも熟読して最終的にどの文字コードやエンコーディングにするか判断するべし。

| | コメント (0) | トラックバック (0)

2007年6月 5日 (火)

PL/SQL de File Upload / Download #4


Howard Jones - The Peaceful Tour - Things Can Only Get BetterHoward Jones - The Peaceful Tour - Things Can Only Get Better

PL/SQL de File Upload / Download のつづきです。


前回は、MacOSXのSafariで発生する問題。日本語ファイル名のファイルをダウンロードした際、ファイル名が、 hostname.拡張子 のような形式で保存されてしまうというところまでだった
Firefoxでは全く問題が発生していないので、ブラウザ依存の問題ということなのだろうか。


ということで、今回は、MacOSX以外のプラットフォームで試した結果+αも。


OTN-J の Code Tipsでも公開されました。(2007/6/16更新) http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2235 http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2236

ファイル名が日本語であるファイルをダウンロードした場合のファイル名の文字化けについて

・Firefox 2 (Mozilla含む) : MacOSX/Linux/Windows XP すべてのプラットフォームで
 文字化け等の問題は発生しない。

Firefox2macosxok Firefox2windowsok Mozillalinuxok


・IE6 : 文字化け。

Ieng_1


・Safari : 前述の通り。 hostname.拡張子 という形式で保存されてしまう。

・Opera 9.2 (MacOSX版): URLエンコードされた文字列.拡張子 という形式で保存されてしまう


Operamacosxng



文字エンコードが統べてUTF-8だったらどうなんだろう。。。解決できたりして。。。という思いつきは置いといて、今日は、Shift_JISのままでIE6での文字化けを(無理矢理?)解決する方法があったので載せておくことにする。

参考:
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19260-01/concept.htm#i1011220


※2007/6/8一部修正
以下のようにContent-Disposition: attachment; filename=UTF-8でURLエンコードしたファイル名を指定する方式であれば、IE6でも正しいファイル名で保存できるようになる。
MacOSXのSafariでは依然として改善されず、下記方法では文字化けしてしまう。 

以下の方法もマニュアルに記載されている方法だが、あくまで英語圏で作成されたマニュアルの翻訳なので、ファイル名が日本語である場合の文字化け回避方法までは記載されていない。
文字化けの回避はブラウザにも依存する部分なのでなかなか面倒ですね。文字コードやエンコーディングがいろいろとありすぎてうれしくないな〜。

create or replace PROCEDURE fileDownloader
IS
vFilePath VARCHAR2(256);
vUserAgent VARCHAR2(512);
vContentType discusdoctable.BLOB_CONTENT%TYPE;
vMimeType discusdoctable.MIME_TYPE%TYPE;
vDocSize discusdoctable.DOC_SIZE%TYPE;
vFileName discusdoctable.NAME%TYPE;

BEGIN
vFilePath := SUBSTR(owa_util.get_cgi_env('PATH_INFO'), 2);
vUserAgent := owa_util.get_cgi_env('HTTP_USER_AGENT');
IF INSTR(vUserAgent,'MSIE') != 0 THEN
-- IE (かなり無理矢理な対応)
-- IE6 - Version 6.0.290000.2180.xpsp_sp2_dgr.070227-2254 で動作確認!
-- 他のバージョンのIEではどうなるか未確認。。。
SELECT
SUBSTR(name, INSTR(name, '/')+1),
blob_content,
mime_type,
doc_size
INTO
vFileName,
vContentType,
vMimeType,
vDocSize
FROM
discusDocTable
WHERE
name = vFilePath;
OWA_UTIL.mime_header(vMimeType, FALSE);
HTP.p('Content-Length: ' || TO_CHAR(vDocSize));
HTP.p(
'Content-Disposition: attachment; filename=' || utl_url.escape(vFileName,false, 'AL32UTF8')
);
OWA_UTIL.http_header_close;
wpg_docload.download_file(vContentType);
ELSE
-- その他のブラウザ(MacOSX Safariでは日本語ファイル名のダウンロードは文字化けする)
wpg_docload.download_file(vFilePath);
END IF;
EXCEPTION
WHEN OTHERS THEN
htp.htmlopen;
htp.headopen;
htp.meta('Ccntent-Type',null,'text/html; charset=Shift_JIS');
htp.headclose;
htp.bodyopen;
htp.print('ダウンロードファイル:' || vFilePath);
htp.print('<strong style="color:#ff0000">' || sqlerrm() || '</strong>');
htp.bodyclose;
htp.htmlclose;
END fileDownloader;
/

IE6で日本語ファイル名のファイルをダウンロードしたスクリーンショット。
Ieok

| | コメント (0) | トラックバック (0)

2007年6月 4日 (月)

PL/SQL de File Upload / Download #3


Foundation - The Best of House, Vol 2 - Somebody's Watching Me Foundation - The Best of House, Vol 2 - Somebody's Watching Me


PL/SQL de File Upload / Download のつづきです。

前回は、プロシージャの実装まで行ったので実際にアップロードとダウンロードを行った結果を載せておく。


以下、MacOSXのSafariとFirefox2でファイルアップロードを行ったスクリーンショット

Modplsql3_1
Modplsql3_2
Modplsql3_3





OTN-J の Code Tipsでも公開されました。(2007/6/16更新)
http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2235
http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2236


5.ファイルアップロード後のドキュメント表の内容の例

(1)〜(4)のようなプロシージャを実装するだけで簡単にファイルアップロード/ダウンロードを行うWebアプリケーションを作成できる。
ただし、多少の癖はあるようですが。。。

尚、OPERATION#が等しいファイルは同一操作でアップロードされたことを意味する。今回、拡張した部分です!。想定通りうまくいきました。 (^^。

SCOTT> col name for a46
SCOTT> col content_type for a10
SCOTT> set linesize 132
SCOTT> set pagesize 1000
SCOTT> select name,content_type,doc_size,operation# from discusdoctable;

NAME CONTENT_TY DOC_SIZE OPERATION#
---------------------------------------------- ---------- ---------- ----------
F1416265333/DSC00002.JPG BLOB 32970 1
F462708383/CocoaAdvanced.zip BLOB 6189625 1
F701507294/Cocoaセミナー上級編.pdf BLOB 2673700 2
F622467197/MyQuickTimeMovieSoundTrack#3.m4a BLOB 663868 2
F1413335442/PLSQL_COLLECTION_sample.txt BLOB 15865 2
F612154227/ユーザの等価化.txt BLOB 1706 2
F1814846582/secondLife.jpg BLOB 231581 2
F621341128/ピクチャ 1.png BLOB 904551 3

8行が選択されました。

SCOTT>


まず、MacOSXの SafariとFirefoxで同じファイルをダウンロードしてみたのだが、ファイル名が日本語のファイルについては、Safariでダウンロードしたファイルのファイル名称は元のファイル名称とは異なりダウンロード元サーバのIPアドレスになっていた。(ホスト名を指定していればホスト名になっていただろう)。
また、Firefox(Mozillaでも)であればWindows/Linux上であっても文字化け等は発生しなかった。という結果であった。

Modplsql3_4

SafariとFirefoxで ピクチャ 1.pngというファイルを開いてたところなのだが、どちらもファイルは開けるものの、Safariではファイル名が192.168.1.19.pngとなっている。。
Modplsql3_5


Safariでも Cocoaセミナー上級編スライド.pdfをダウンロードしたのだが、ダウンロードされたファイル名は、192.168.1.19.pdfとなってしまった。FirefoxやMozillaでは問題ないんですが。。。

Modplsql3_6 Modplsql3_7

Modplsql3_8

初回にも書いたのだが、今回の環境は全てのレイヤーで文字コードはシフトJISにしてある。それがSafariの挙動に影響しているような・・・・・。
ということで、今日はここまで。 次回へつづく。

| | コメント (0) | トラックバック (0)

2007年6月 3日 (日)

PL/SQL de File Upload / Download #2


Ultravox - Vienna - ViennaUltravox - Vienna - Vienna

さて、環境が整ったのでプロシージャを作成する。
(今回はマニュアルに記載されているサンプルのようにそれぞれ独立したプロシージャとしたが、これらを纏めてパッケージ化しても問題ない。 私が仕事で依頼されたものならパッケージとして実装するでしょうね。)


OTN-J の Code Tipsでも公開されました。(2007/6/16更新) http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2235 http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2236

4.ファイルアップロード/ダウンロードプロシージャの例

注)style属性のtext-shadowを利用していますが、MacOSXのSafari以外のブラウザではドロップシャドウ効果はありません。


URLの例)- hostnameが discus 、 portが 7780 である場合、以下のようなURLでアクセスする。
http://discus:7780/plsql_de_fileupload/fileuploadform


(1)ファイルを最大5件同時にアップロードするフォームを表示するプロシージャ。

参考:
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19260-01/concept.htm#i1005985
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19260-01/concept.htm#i1011204

CREATE OR REPLACE PROCEDURE fileUpLoadForm
IS
numOfFiles CONSTANT PLS_INTEGER := 5;
BEGIN
htp.htmlopen;
htp.headopen;
htp.meta('CONTENT-TYpe',null,'text/html; charset=Shift_JIS');
htp.title('PL/SQL de File Upload');
htp.headclose;

htp.bodyopen;
htp.centeropen;
htp.print('<div align="center" style="width:500; height:600">');
htp.STRONG(
cattributes=> 'style="font-size:24px; color:#ff0000; '
|| 'text-shadow:1px 2px 3px #7f7f7f"',
ctext=> 'PL/SQL de ファイルアップロード'
);
htp.prn('<br><br>');
htp.formopen(
cenctype=>'multipart/form-data',
curl=>'action',cmethod=>'POST'
);

FOR i IN 1..numOfFiles LOOP
htp.prn(
'<p><span style="text-shadow:1px 2px 3px #7f7f7f">ファイルの選択:</span>'
);
htp.formfile(cname=>'file');
htp.br;
END LOOP;
htp.prn('<br><br><p>');
htp.formsubmit(cattributes=>'align=right',cvalue=>'アップロード');

htp.formclose;
htp.print('</div>');
htp.centerclose;
htp.bodyclose;
htp.htmlclose;
END;
/


(2)<FORM>要素のaction属性に指定するプロシージャの例

下記サンプルコードでは、マニュアルに記載されているサンプルの他に、同一操作でアップロードされたファイルに対してシーケンスから採番した操作番号を設定する処理を追加している。

CREATE OR REPLACE PROCEDURE action
(
file IN owa_util.vc_arr
) IS
vOpe# number(10);
BEGIN
htp.htmlopen;
htp.meta('CONTENT-TYPE',null,'text/html; charset=Shift_JIS');
htp.headopen;
htp.title('PL/SQL de File Upload (Action)');
htp.headclose;
htp.bodyopen;
htp.header(1,'アップロード・ステータス');
htp.print('以下のアップロードが終了しました。<br><br>');
htp.prn('<span style="color:#ff0000; text-shadow:1px 2px 3px #7f7f7f">');
FOR i IN file.FIRST..file.LAST LOOP
IF FILE(i) IS NOT NULL THEN
htp.print('-&gt; ' || file(i) || '<br>');
END IF;
END LOOP;
htp.print('</span>');

-- 同一操作でアップロードしたファイルへ、同一操作番号を設定する。
SELECT upload_operation#.nextval INTO vOpe# FROM dual;
FOR i IN file.FIRST..file.LAST LOOP
UPDATE DISCUSDOCTABLE SET operation# = vOpe# WHERE name=file(i);
END LOOP;

htp.bodyclose;
htp.htmlclose;
END;
/


(3)ダウンロード可能なファイルリストを表示するプロシージャの例。

URLの例) - hostname = discus, port = 7780である場合。
http://discus:7780/plsql_de_fileupload/filelist

マニュアルに記載されているファイルダウンロードのサンプルは、同マニュアルに記載されている他のサンプルコードに比べ、少々不親切(端折り過ぎ)なサンプルコードになっている。(CGIなどのWebアプリケーションに慣れている方なら想像できなくもないのだが・・・・。)
下記サンプルコードを見れば容易に理解できると思うのだが、アップロードしたファイルをダウンロードするには、dads.confのPlsqlDocumentPathパラメータで指定したドキュメントルートのパスと、ドキュメント表のname列の値を連結したパスでダウンロードするファイル指すURLを生成できる。

CREATE OR REPLACE PROCEDURE fileList
IS
BEGIN
htp.htmlopen;
htp.headopen;
htp.meta('CONTENT-Type',null,'text/html; charset=Shift_JIS');
htp.title('PL/SQL de File upload - ダウンロード可能なファイル一覧');
htp.headclose;
htp.bodyopen;
htp.print(
'<span style="text-shadow:1px 2px 3px #7f7f7f; font-size: 22px">'
|| 'ダウンロードファイル一覧</span><br><br>'
);
FOR doc IN (SELECT name FROM discusDocTable) LOOP
htp.anchor('docs/' || utl_url.escape(doc.name, false, 'Shift_JIS'), 'docs/' || doc.name);
htp.br;
END LOOP;
htp.bodyclose;
htp.htmlclose;
END;
/

(4)ファイルのダウンロード時にmod_plsqlから呼び出されるプロシージャの例。

このプロシージャは、(3)の方法でダウンロードする場合には必須であり、dads.confのPlsqlDocumentProcedureパラメータで指定する。
このプロシージャのサンプルコードはマニュアルにも記載されているのだが少々難解ですね。
実際には、以下のように wpg_docload.download_file()プロシージャを実行するだけの処理なのだがマニュアルのサンプルコードを見ると少々混乱してしまう! 私も少々考えましたもん!!!

以下コードを見てもらうと実は非常にシンプルな操作であることに気付くと思う。 解説するまでも無いと思うが念のために解説しておくと、wpg_docload.download_file(環境変数PATH_INFOの値)を実行しているだけなのである。

CREATE OR REPLACE PROCEDURE fileDownloader
IS
vFilePath VARCHAR2(256);
BEGIN
vFilePath := SUBSTR(owa_util.get_cgi_env('PATH_INFO'), 2);
wpg_docload.download_file(vFilePath);
EXCEPTION
WHEN OTHERS THEN
htp.htmlopen;
htp.headopen;
htp.meta('Ccntent-Type',null,'text/html; charset=Shift_JIS');
htp.headclose;
htp.bodyopen;
htp.print('ダウンロードファイル:' || vFilePath);
htp.print('<strong style="color:#ff0000">' || sqlerrm() || '</strong>');
htp.bodyclose;
htp.htmlclose;
END fileDownloader;
/


以上でプロシージャの作成は終わり。 簡単ですよね!。 

次回へつづく。

| | コメント (0) | トラックバック (0)

2007年6月 1日 (金)

PL/SQL de File Upload / Download #1

先日、OTN-Jでmod_plsqlが提供するファイルアップロード/ダウンロードフレームワークに関連するスレッドがあり、その中でのやり取りと、作成したサンプルコードに多少手を加えて公開しておく。
ファイルダウンロード及び、ドキュメント表の拡張方法など明確な解説が無いこともあり、一部マニュアルの行間を読みつつ試した結果を含む。
(OTN-JのCodeTipsへも投稿する予定)
http://otn.oracle.co.jp/forum/message.jspa?messageID=35011699&tstart=0


mod_plsqlのファイルアップロード/ダウンロードに関する詳細は、下記ドキュメント参照のこと。
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19260-01/concept.htm#i1009257
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19260-01/concept.htm#i1005866


※ファイルアップロード/ダウンロードサンプルの概要

・複数のファイル(この例では最大5件)を1操作でアップロードする。
・同一操作でアップロードされた複数のファイルは、同一の操作番号(OPERATION#)を
 設定する。

※環境

HTTP Servetとデータベースは同ーサーバ上に構築。(尚、$ORACLE_HOMEは別にしてインストールする必要がある。)
尚、今回はWindows XP Professional上に構築し、Oracle SQL Developer for MacOSXを利用してプロシージャを作成した。

・サーバ

・Oracle10g HTTP Server (Apache Standalone 10.1.2.0.0)
  - Oracle Database 10g Compaion CD Release 1 (10.1.0.3.0)
・Oracle10g Database R1 EE
  - Oracle Database 10g Release 1 (10.1.0.3.0)
  - データベースキャラクタセット: JA16SJISTILDE
・OWA - 10.1.2.0.6

・クライアント(ブラウザ)

・Safari 2 - MacOSX 10.4.9
・Opera 9.2 - MacOSX 10.4.9
・FireFox 2 - MacOSX 10.4.9
・Firefox 2 - Linux (TurboLinux 8 - powered by United Linux1.0)
・FireFox 2 - Linux (CentOS 4.4)
・IE6 - Windows XP Professional
・FireFox 2 - Windows XP Professional


OTN-J の Code Tipsでも公開されました。(2007/6/16更新) http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2235 http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=2236

1.ドキュメント表の定義

ドキュメント表で定義する必要のある列に加て、OPERATION#という列を追加した。
尚、アップロードするコンテンツはすべて BLOB列へ格納する。
(同一操作でアップロードされたファイルに同一操作番号を設定する為に利用する。)
SCOTT> desc discusdoctable
名前 NULL? 型
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(128)
MIME_TYPE VARCHAR2(128)
DOC_SIZE NUMBER
DAD_CHARSET VARCHAR2(128)
LAST_UPDATED DATE
CONTENT_TYPE VARCHAR2(128)
BLOB_CONTENT BLOB
OPERATION# NUMBER(10)

SCOTT>

2.操作番号を取得するシーケンスの定義

CREATE SEQUENCE UPLOAD_OPERATION#  
MINVALUE 1 MAXVALUE 9999999999
INCREMENT BY 1 START WITH 1
NOCACHE NOORDER NOCYCLE;

3.dads.confの定義

dads.confに関連するマニュアルは以下の通り。
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19260-01/concept.htm#i1012038
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19260-01/concept.htm#i1011190
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19260-01/concept.htm#i1010512

# ============================================================================ 
# mod_plsql DAD Configuration File
# ============================================================================
# 1. Please refer to dads.README for a description of this file
# ============================================================================

# Note: This file should typically be included in your plsql.conf file with
# the "include" directive.

# Hint: You can look at some sample DADs in the dads.README file

# ============================================================================
<Location /plsql_de_fileupload>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername scott
PlsqlDatabasePassword tiger
PlsqlDatabaseConnectString 192.168.1.19:1521:amazon
PlsqlAuthenticationMode Basic
PlsqlDefaultPage scott.fileUploadForm
PlsqlDocumentTablename scott.discusDocTable
PlsqlDocumentPath docs
PlsqlDocumentProcedure scott.fileDownloader
PlsqlNLSLanguage JAPANESE_JAPAN.JA16SJISTILDE
</Location>


環境、構成ファイル、ドキュメント表及び、その拡張部分の定義はこれで終了。次回へつづく。
Making1

| | コメント (0) | トラックバック (0)

2007年4月10日 (火)

Oracle SQL Developer #12

Oracle SQL Developer 1.1 Patch 1 (1.1.1.25.14) for MacOSX でと思ったが、Oracle SQL Developer 1.1 Patch 2 (1.1.2.25.79) for MacOSX が既にリリースされていたので最新版で、Oracle SQL Developerのデバッグ機能を試してみた。

http://www.oracle.com/technology/software/products/sql/index.html
尚、ダウンロードにはユーザ登録(無料)が必要。

リリースノートは以下。
http://www.oracle.com/technology/products/database/sql_developer/files/relnotes_v1.1.html

SQL Developer 1.1 Path 1(1.1.1.25.14) for MacOSXの時点で確認できていたので、ネタとしては少々鮮度が落ちるが、PL/SQLのデバッガ機能でハングが発生しなくなった。 
1.0か、1.1のころデバッグ機能を試した時は、デバッグモードで実行するとSQL Developerがハングしてしまい強制終了するしか無かったが、前回リリースされた Oracle SQL Developer 1.1 Patch 1 (1.1.1.25.14) for MacOSXで解決されていた。
これができるようになれば MacOSX上でもPL/SQLの開発が楽になりますね。

以下、Oracle10g R2 EE 10.2.0.2.0に接続してdebugしている画面のスナップショット。

Oraclesqldeveloper_debug

ちなみに、Oracle SQL Developerでデバッグ機能を利用するには、DEBUG CONNECT SESSION及び、DEBUG ANY PROCEDUREシステム権限が必要。もし実行するユーザが該当システム権限を付与されていない場合、以下のようなメッセージが返される。

Connecting to the database pleco.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.1.2', '53865' )
ORA-01031: 権限が不足しています。
ORA-06512: "SYS.DBMS_DEBUG_JDWP", 行68
ORA-06512: 行1
This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.
Process exited.
Disconnecting from the database pleco.

| | コメント (0) | トラックバック (0)

2007年3月29日 (木)

Mac De PL/SQL RSS Reader #30 (XSLT編 #2)

さて、SQL Developerでの確認も終わり、さっそく、Apex3.0で試してみた。

いつものように、「Hide and Show Region」を選択し、RSS 0.91/0.92/1.0/2.0 ATOM 0.3/1.0で配信されているFEEDを読み込むよう設定した。

Ap1_1 Ap2 


アプリケーションを実行した結果は以下。思い通りに変換されている。(XSLTスタイルシートを利用しているので、見栄えを変更する場合はXSLTスタイルシートを修正するだけ。。)

Ap3 Ap4

まあ、なんとか、以前のものとおなじような見栄えにはなりますね。(XSLT2.0ならもっといろいろとできるでしょうけども)
以下マニュアル引用


PL/SQL XSLT Processor for XMLType: Features

PL/SQL XSLT Processor for XMLType (DBMS_XSLPROCESSOR) is the Oracle XML DB implementation of the XSL processor. This follows the W3C XSLT final recommendation (REC-xslt-19991116).

W3C XSL Transformations(XSLT) バージョン 1.0の和訳はこちら。
http://www.infoteria.com/jp/contents/xml-data/REC-xslt-19991116-jpn.htm

次回へつづく。




植木等さんの名作といえば、無責任シリーズかもしれません。(今見ても、笑えますもん)
ただ、私などはリアルタイムで無責任シリーズを見た世代ではないので、「オヨビでない奴!」というドラマの印象が強烈かもしれない。
所ジョージと高橋良明が植木等にからみ、植木等の3乗くらいの面白さのあったドラマに仕上がっていたと思います。
ところで、所ジョージに植木等のまねをやらせたら似てるよ、きっと。。。

植木等さんのご冥福を心よりお祈りいたします。

| | コメント (0) | トラックバック (0)

Mac De PL/SQL RSS Reader #29 (XSLT編#1)

ノドが痛いと思っていたら、熱。インフルエンザではないが、風邪には違いない。(油断禁物ですね。。)1日休んだら、回復しましたけど、本調子に戻っていないので今日はおとなしくして明日は完全復活する予定。(週末は花見もしないとならんし。。な)


さて、PL/SQLでRSS Readerを作ってきたが、XSLTスタイルシートを利用した場合はどうなるか?
ということで、以前のプロシージャをベースに、XSLT版に書き換えてみることにする。

利環境は前回からの続きなので変更はないのだが、念のために、データベース、Web Toolkit、そして、Apexのバージョンを載せておく。

SYS> select version from v$instance;

VERSION
-----------------
10.1.0.3.0


SYS> select owa_util.get_version from dual;

GET_VERSION
--------------------------------------------------------
10.1.2.0.6

SYS> select flows_030000.wwv_flows_release from dual;

WWV_FLOWS_RELEASE
--------------------------------------------------------
3.0.0.00.20

SYS>

尚、開発には、 Oracle SQL Developer 1.1.2.25 Build Main 25.79 を使用した。

SQL Developerでは、OWAの出力もできるようなので試したかったということもあったのだが。。。

試してみるとOWAが書き出す量が多いとエラーになるようだ、具体的にどの程度ということまでは確認していないのだが。。HTP.PRNで4000バイト以上は書き出している。
但し、エラー時にもHTP.PRINTでエラー内容を出力しているのだが、こちらは、画面のスナップショットからも判るように問題なく表示されている
(前述の問題は、dbms_outputパッケージで代用できるので、特に困るということもないので深入りしないでスルー。(^^;;;)


X1 X2

前回までとは異なり、PL/SQL内部でDOMをトラバースしたりすることはせず、XSLTスタイルシートを利用してRSS/RDF/ATOMをHTMLに変換する。
変換後のHTMLには、FEEDのタイトル及びリンク、各記事の要約とリンク、それに記事の作成日があれば表示することにした。
尚、記事の作成日については、該当要素値から日付(RFC3339形式)部分を表示することにした。(RFC882形式の場合は、曜日も含める)
また、XSLスタイルシートの読み込み方法は幾つかあるが、今回はディレクトリオブジェクト以下にあるスタイルシートを読み込むことにした。。

XSLTスタイルシートで、xsl:choose要素や、xsl:if要素。xsl:variable要素なんて多用してしまうと、もの凄く読みにくいJSPのようになってしまいそうな気がする。。。
ところで、しっかりと内容を確認していないのだが、XML Master professional Database を取るべきか、Applicationを取るべきか悩みなすね。。


DBMS_OUTPU.PUT_LINEを利用してデバック変換後のHTMLを表示しているところ。

X3 X4
X5 X6


適当に作ったXSLTスタイルシートもなんとか思い通りのもにに仕上がったので、Apex3.0でお試し。。

次回へつづく。

| | コメント (0) | トラックバック (0)

2007年2月25日 (日)

Mac De Oracle - 10万円 de RAC #42

10万円 de RACのつづき。PowerBook G4のJDeveloper10g/SQL Developer/SQL*Plusなどからの接続確認。Oracle SQL Developer 1.1編。

  1. ハードウェアの価格構成
  2. Linux (CentOS 4.4)のインストール
  3. ネットワークの構成
  4. Openfilerのインストール
  5. OpenfilerによるiSCSIボリュームの構成
  6. Oracle RACノードでのiSCSIボリュームの構成 その1。 その2
  7. Oracle所有者と関連ディレクトリの作成及び環境変数の設定
  8. Oracle向けLinuxサーバーの構成(カーネルパラメータの設定)
  9. hangcheck-timerカーネル・モジュールの構成
  10. Oracle RACノード間リモートアクセスの構成
  11. Oracle RACノード構成ファイルの確認
  12. Oracle Cluster File System (OCFS2)のインストール及び構成 その1/その2/その3
  13. Oracle Automatic Storage Management(ASMLib 2.0)のインストール及び構成
  14. Oracle 10gソフトウェアのダウンロード
  15. Oracle10g R2インストール事前作業 その1/その2/その3/その4
  16. Oracle10g Clusterwareのインストール
  17. Oracle10g R2 Database softwareのインストール その1/その2
  18. Oracle10g R2 Companion CD softwareのインストール
  19. TNS Listenerの構成
  20. Oracle Cluster databaseの作成 その1/その2
  21. Oracle Net Serviceの確認
  22. 表領域の作成と変更
  23. Oralce RAC ClusterとDatabase構成の確認
  24. Clusterの開始と停止の確認
  25. 簡単なTransparent Application Failover (TAF)の確認
  26. PowerBook G4のJDeveloper10g/SQL Developer/SQL*Plusなどからの接続確認(Mac De Oracleではお約束!なので) 
    SQL*Plus編/SQL Developer編
注)
MacOSX 10.4.8(PowerPC)へのOracle10g clientインストールは特に新しいネタでもないので記事として書く予定はないが、MacOSX 10.4.8(PowerPC)のJDeveloper10g、SQL DeveloperやSQL*Plusからの接続確認等の記録は載せる予定である。

今日は、PowerBook G4 (MacOSX10.4.8)の Oracle SQL Developer 1.1 Patch 1 (1.1.1.25.14) for MacOSXからの接続確認を行う。
現時点(2007/2/25現在)では最新版のOracle SQL Developerである。

● 起動
スプラッシュ画面

Rac_de_oracle_sqldev1

● connecttionの設定
jdbc thin driverにて接続。

Rac_de_oracle_sqldev_connection

● emp表を問い合わせてみる。。

Rac_de_oracle_sqldev2


ということで、PowerBook G4の Oracle SQL Developer 1.1 for MacOSXからの接続は確認できた。次回へつづく。

| | コメント (0) | トラックバック (0)

2006年9月 6日 (水)

Mac De Oracle (PL/SQL De COMPRESS/UNCOMPRESS)

OTN JapanCodeTipsに、Mac De Oracle (PL/SQL De UNCOMPRESS)及び、Mac De Oracle (PL/SQL De COMPRESS)に掲載したコードとほぼ同じコードを投稿しました。

| | コメント (0) | トラックバック (0)

2006年8月29日 (火)

Mac De Oracle (PL/SQL De COMPRESS)

解凍につづき、圧縮を試す。


まずは gzip するテキストファイルは前回準備しておいたものを利用する。このテキストファイルをcompressed_file.gzとして圧縮してみる。尚、UTL_COMPRESS.LZ_COMPRESS()では、圧縮レベルが1〜9あり、デフォルトは、6。最も圧縮率が高いのは、9。この例では、圧縮率=6(デフォルト)で圧縮する。

G5Server:˜ oracle$ ls -l
total 48096
中略
-rw-r--r-- 1 oracle oinstall 559 Aug 20 06:35 org.txt
中略
G5Server:˜ oracle$
G5Server:˜ oracle$ cat org.txt
操作上の注意
■ LZ* ファンクションによって戻された一時 LOB は、コール元が
DBMS_ LOB.FREETEMPORARY コールを使用して解放する必要があります。
■ LZ_COMPRESS* または LZ_UNCOMPRESS* に渡された BFILE は、
DBMS_LOB.FILEOPENでオープンする必要があります。
■ 特別な状況(入力がすでに圧縮されている場合)では、UTL_COMPRESS サブプログラムの
1 つで生成された出力が、入力と同じ大きさかまたは入力よりもわずかに大きくなる
ことがあります。
■ UTL_COMPRESS で圧縮したデータの出力は、1 つのファイル上の gzip(-n オプション を指定)/gunzip と
互換性があります。
G5Server:˜ oracle$

早速取りかかる。今回も Oracle SQL Developer 1.0 for MacOSXを利用する。

だ〜〜〜〜っと、前回のコードを少々修正して実行(無名PL/SQLブロックにしてあるが、ストアドプロシージャでもファンクションでも、パッケージで実装しても全く問題はない。尚、事前にディレクトリオブジェクトは作成してある。)
Macdecompress1_1

圧縮ファイル(compressed_file.gz)が正しく作成できたか確認する。

G5Server:˜ oracle$ ls -l
total 48112
中略
-rw-r--r-- 1 oracle oinstall 408 Aug 20 13:11 compressed_file.gz
-rw-r--r-- 1 oracle oinstall 559 Aug 20 06:35 org.txt
中略
G5Server:˜ oracle$
G5Server:˜ oracle$ gunzip -lt compressed_file.gz
compressed uncompressed ratio uncompressed_name
408 559 30.2% compressed_file
G5Server:˜ oracle$
G5Server:˜ oracle$ gunzip -c compressed_file.gz
操作上の注意
■ LZ* ファンクションによって戻された一時 LOB は、コール元が
DBMS_ LOB.FREETEMPORARY コールを使用して解放する必要があります。
■ LZ_COMPRESS* または LZ_UNCOMPRESS* に渡された BFILE は、
DBMS_LOB.FILEOPENでオープンする必要があります。
■ 特別な状況(入力がすでに圧縮されている場合)では、UTL_COMPRESS サブプログラムの
1 つで生成された出力が、入力と同じ大きさかまたは入力よりもわずかに大きくなる
ことがあります。
■ UTL_COMPRESS で圧縮したデータの出力は、1 つのファイル上の gzip(-n オプション を指定)/gunzip と
互換性があります。
G5Server:˜ oracle$

前述のように、gunzipで解凍してしまうのもよいのだが、ダブルクリックして解凍しちゃいましょう。すばらしいGUIがあるんですから。
Macdecompress2_1 Macdecompress3


以下に今回作成したコードを示す。

DECLARE
v_src_text_file BFILE;
v_compressed_blob BLOB;
v_directory_name VARCHAR2(30);
v_src_text_file_name VARCHAR2(510);
v_dest_file_name VARCHAR2(510);

PROCEDURE close_bfile
(
i_bfile IN OUT NOCOPY BFILE
)
IS
BEGIN
IF DBMS_LOB.ISOPEN(i_bfile) = 1 THEN
DBMS_LOB.CLOSE(i_bfile);
END IF;
END close_bfile;

PROCEDURE write_blob_to_file
(
i_directory_name IN VARCHAR2,
i_file_name IN VARCHAR2,
i_src_blob IN OUT NOCOPY BLOB
)
IS
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(
UPPER(i_directory_name),
i_file_name,
'wb',
32767
);

-- ファイルへ書き出すBLOBはオープンされていることを前提とする
DECLARE
c_chunk_size CONSTANT PLS_INTEGER := 32767;

v_buffer RAW(32767);
v_amount PLS_INTEGER := c_chunk_size;
v_num_of_chunk PLS_INTEGER;
BEGIN
v_num_of_chunk := CEIL(DBMS_LOB.GETLENGTH(i_src_blob)/c_chunk_size);
FOR chunk# IN 1..v_num_of_chunk LOOP
DBMS_LOB.READ(
i_src_blob,
v_amount,
(c_chunk_size * (chunk# - 1)) + 1, /*offset*/
v_buffer
);
UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
END LOOP;
END;

UTL_FILE.FCLOSE(v_file);
END write_blob_to_file;
--
BEGIN
-- ディレクトリ名の設定
v_directory_name := 'MYDIR';
-- テキストファイル名
v_src_text_file_name := 'org.txt';
-- 出力ファイル名
v_dest_file_name := 'compressed_file.gz';

DBMS_OUTPUT.PUT_LINE('========== compress start ==========');

-- テキストファイルをBFILEとして開く。
v_src_text_file
:= BFILENAME(UPPER(v_directory_name), v_src_text_file_name);
DBMS_LOB.OPEN(v_src_text_file);

-- BFILEを圧縮し、一時BLOBへ (圧縮品質はデフォルトで圧縮する)
v_compressed_blob := UTL_COMPRESS.LZ_COMPRESS(v_src_text_file);

-- BLOB(圧縮済み)を書き出す
write_blob_to_file(
UPPER(v_directory_name),
v_dest_file_name,
v_compressed_blob
);

-- 一時LOBの解放及び、BFILEのクローズ
DBMS_LOB.FREETEMPORARY(v_compressed_blob);
close_bfile(v_src_text_file);

DBMS_OUTPUT.PUT_LINE('========== compress end ==========');

EXCEPTION
WHEN OTHERS THEN
-- 一時LOBの解放及び、BFILEのクローズ
DBMS_LOB.FREETEMPORARY(v_compressed_blob);
close_bfile(v_src_text_file);
RAISE;
END;
/

OTN JapanCodeTipsに公開してあります。
聞いている曲:
KraftWerk - Minimum - Maximum (live) Kraftwerk - Minimum - Maximum (Live)
Kraftwerk - Minimum - Maximum (Live)

| | コメント (2) | トラックバック (0)

2006年8月28日 (月)

Mac De Oracle (PL/SQL De UNCOMPRESS)

Oracle10g R1から UTL_COMPRESSパッケージという、PL/SQLでデータの圧縮/解凍を行う為のパッケージが提供されているのをご存知だろうか?

恐らく、大きなデータを圧縮してBLOBとしてデータベースに格納し、取り出す時に解凍するというのが一般的な利用方法なのではないかと思うのだが、Mac De Oracle としては、そのまま試すのでは面白くない!
ということで、UTL_FILEパッケージを利用し、ディレクトリオブジェクトにある圧縮ファイルを解凍したり、テキストファイルを圧縮してみることにする。さて、うまくいきますかどうか。。。
尚、BLOBをディレクトリオブジェクト以下に書き出す方法は、PL/SQL De Phython Challengeでも利用した方法である。以前はコードを掲載していなかったが、今回は、それに近いコードも載せることにする。
(UTL_FILEパッケージでディレクトリオブジェクト以下にあるファイルを読み書きするには、Oracle9i R2以降が必要である。)

マニュアルにも書いてあるが、UTL_COMPRESSの圧縮、解凍は、gzip/gunzipと互換があるということなので素直に信じて試してみる!

まずは gzip するテキストファイルの準備から。
以下のようなテキストファイルを用意する。テキストファイルの内容は、「Oracle10g R1 PL/SQL タイプ及びパッケージリファレンス」のUTL_COMPRESSパッケージの解説を一部を引用することにした。(テキストファイルの文字コードは、SJIS、データベースキャラクタセットは、JA16SJISTILDEである。)

操作上の注意 
■ LZ* ファンクションによって戻された一時 LOB は、コール元が
DBMS_ LOB.FREETEMPORARY コールを使用して解放する必要があります。
■ LZ_COMPRESS* または LZ_UNCOMPRESS* に渡された BFILE は、
DBMS_LOB.FILEOPENでオープンする必要があります。
■ 特別な状況(入力がすでに圧縮されている場合)では、UTL_COMPRESS サブプログラムの
1 つで生成された出力が、入力と同じ大きさかまたは入力よりもわずかに大きくなる
ことがあります。
■ UTL_COMPRESS で圧縮したデータの出力は、1 つのファイル上の gzip(-n オプション を指定)/gunzip と
互換性があります。

Last login: Mon Aug 19 16:42:12 on ttyp1
Welcome to Darwin!
G5Server:˜ oracle$ gzip -cvn org.txt > org.gz
org.txt: 30.2%
G5Server:˜ oracle$ gzip -l org.gz
compressed uncompressed ratio uncompressed_name
408 559 30.2% org
G5Server:˜ oracle$ ls -l
total 48096
中略
-rw-r--r-- 1 oracle oinstall 408 Aug 20 06:35 org.gz
-rw-r--r-- 1 oracle oinstall 559 Aug 20 06:35 org.txt
中略
G5Server:˜ oracle$

準備は整ったので早速取りかかる。今回も Oracle SQL Developer 1.0 for MacOSXを利用する。


だ〜〜〜〜っと、コード書をきなぐり、即、実行!(無名PL/SQLブロックにしてあるが、ストアドプロシージャでもファンクションでも、パッケージで実装しても全く問題はない。尚、事前にディレクトリオブジェクトは作成してあり、無名PL/SQLブロックを実行するユーザへ読み込み権限と書き込み権限を付与してある。)
Macdecompress1


圧縮前のテキストファイル(org.txt)と、org.gzをUTL_COMPRESS.LZ_UNCOMPRESS()で解凍したファイル(uncompressed_file.txt)が同じ内容であることを確認する。(diffを取って見せるのがいいのだが、それだけだとMacOSXの奇麗なGUIが見れないので、ファイルを開いて確認する。)
Macdecompress2


以下に今回作成したコードを示す。

DECLARE
v_src_gzipped_file BFILE;
v_uncompressed_blob BLOB;
v_directory_name VARCHAR2(30);
v_src_gzipped_file_name VARCHAR2(510);
v_dest_file_name VARCHAR2(510);

PROCEDURE close_bfile
(
i_bfile IN OUT NOCOPY BFILE
)
IS
BEGIN
IF DBMS_LOB.ISOPEN(i_bfile) = 1 THEN
DBMS_LOB.CLOSE(i_bfile);
END IF;
END close_bfile;

PROCEDURE write_blob_to_file
(
i_directory_name IN VARCHAR2,
i_file_name IN VARCHAR2,
i_src_blob IN OUT NOCOPY BLOB
)
IS
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(
UPPER(i_directory_name),
i_file_name,
'wb',
32767
);

-- ファイルへ書き出すBLOBはオープンされていることを前提とする
DECLARE
c_chunk_size CONSTANT PLS_INTEGER := 32767;

v_buffer RAW(32767);
v_amount PLS_INTEGER := c_chunk_size;
v_num_of_chunk PLS_INTEGER;
BEGIN
v_num_of_chunk := CEIL(DBMS_LOB.GETLENGTH(i_src_blob)/c_chunk_size);
FOR chunk# IN 1..v_num_of_chunk LOOP
DBMS_LOB.READ(
i_src_blob,
v_amount,
(c_chunk_size * (chunk# - 1)) + 1, /*offet*/
v_buffer
);
UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
END LOOP;
END;

UTL_FILE.FCLOSE(v_file);
END write_blob_to_file;
--
BEGIN
-- ディレクトリ名の設定
v_directory_name := 'MYDIR';
-- 圧縮ファイル名
v_src_gzipped_file_name := 'org.gz';
-- 解凍ファイル名
v_dest_file_name := 'uncompressed_file.txt';

DBMS_OUTPUT.PUT_LINE('========== uncompress start ==========');

-- 圧縮ファイルをBFILEとして取り込む
v_src_gzipped_file
:= BFILENAME(UPPER(v_directory_name), v_src_gzipped_file_name);
DBMS_LOB.OPEN(v_src_gzipped_file);

-- BFILEを解凍し一時BLOBへ
v_uncompressed_blob := UTL_COMPRESS.LZ_UNCOMPRESS(v_src_gzipped_file);

-- BLOBを書き出す
write_blob_to_file(
UPPER(v_directory_name),
v_dest_file_name,
v_uncompressed_blob
);

-- 一時LOBの解放及び、BFILEのクローズ
DBMS_LOB.FREETEMPORARY(v_uncompressed_blob);
close_bfile(v_src_gzipped_file);

DBMS_OUTPUT.PUT_LINE('========== uncompress end ==========');

EXCEPTION
WHEN OTHERS THEN
-- 一時LOBの解放及び、BFILEのクローズ
DBMS_LOB.FREETEMPORARY(v_uncompressed_blob);
close_bfile(v_src_gzipped_file);
RAISE;
END;
/

さて、次回は、テキストファイルを読み込み圧縮してみることにする。

OTN JapanCodeTipsに公開してあります。

聞いている曲:
Marcus Miller - Silver Rain
Marcus Miller - Silver Rain - Silver Rain (Featuring Eric Clapton)

| | コメント (0) | トラックバック (0)

2006年8月 9日 (水)

Mac De PL/SQL RSS Reader #4

前回のつづきです。
前回までで文字化けを回避できることが確認できたので、本題RSSリーダーの元ネタで試してみることにする。
尚、オリジナルになるべく近い形にしてある。(対象は、megawattさんRSS 0.91フォーマット)

まずは、結果から。(いつものように、Oracle SQL Developer 1.0 for MacOSXを使ってます)
Sqldevok



データベースOracle10g R1 grasscatfishに接続中です。
begin...
====================
メガ放談 - most recent 15 articles:
====================
TITLE: ギザ耳は地域猫の証し
URL : http://megawatt.blogdns.net/blog/254
—————————————-

TITLE: 亀田家親子夏休み日記
URL : http://megawatt.blogdns.net/blog/253
—————————————-

TITLE: 社会保険事務所に行ってきた
URL : http://megawatt.blogdns.net/blog/252
—————————————-

TITLE: 重なるときは重なる Part2
URL : http://megawatt.blogdns.net/blog/251
—————————————-

TITLE: 重なるときは重なる
URL : http://megawatt.blogdns.net/blog/250
—————————————-

TITLE: うしろすがた
URL : http://megawatt.blogdns.net/blog/249
—————————————-

TITLE: 猫のための写真講座/2006年度前期・4
URL : http://megawatt.blogdns.net/blog/248
—————————————-

TITLE: ワイン100mlが80キロカロリー!!
URL : http://megawatt.blogdns.net/blog/247
—————————————-

TITLE: はっちゃんではないはっちゃん
URL : http://megawatt.blogdns.net/blog/246
—————————————-

TITLE: ちょっと、走ってる
URL : http://megawatt.blogdns.net/blog/245
—————————————-

TITLE: 命名、じみへん
URL : http://megawatt.blogdns.net/blog/244
—————————————-

TITLE: 猫まんが at門前仲町
URL : http://megawatt.blogdns.net/blog/243
—————————————-

TITLE: 猫を拾った日
URL : http://megawatt.blogdns.net/blog/242
—————————————-

TITLE: お飲物なににしますか?
URL : http://megawatt.blogdns.net/blog/241
—————————————-

TITLE: 門前仲町・深川不動堂
URL : http://megawatt.blogdns.net/blog/240
—————————————-

...End
プロセス終了。
データベースOracle10g R1 glasscatfishから切断中です。

以下、プロシージャのソース。
比較し易いように、オリジナルのソースになるべく近い形式にしてある。

CREATE OR REPLACE PROCEDURE RSS_FEED_READER
(
i_url IN VARCHAR2
)
AS
--============ TYPEs/VARIABLEs ===============================================
TYPE rss_type IS RECORD
(
title VARCHAR2(2000),
link VARCHAR2(200),
pubDate VARCHAR2(200),
category VARCHAR2(500),
description VARCHAR2(4000)
);
v_rss_item rss_type;
v_rss_empty_item rss_type; -- v_rss_item初期化用

v_url VARCHAR2(32767);

-- XML PARSER
v_myParser DBMS_XMLPARSER.Parser;

-- RSSまたは、ATOMフォーマットのDOM
v_rssDoc DBMS_XMLDOM.DomDocument;

v_itemNode DBMS_XMLDOM.DomNode;
v_childnode DBMS_XMLDOM.DomNode;
v_rssEntryList DBMS_XMLDOM.DomNodeList;
v_rssItemFields DBMS_XMLDOM.DomNodeList;
v_childNodeText VARCHAR2(32767);

-- for UTL_HTTP
v_req UTL_HTTP.REQ;
v_resp UTL_HTTP.RESP;
-- HTTP経由でRSS/ATOMソースを取り込むためのワーク
v_source VARCHAR2(1024);
v_tempSourceClob CLOB;

-- RSS/ATOMをXMLTYPEで扱うためのワーク
v_tempXMLTYPE XMLTYPE;

--========== Internal PROCEDUREs/FUNCTIONs ===================================
--
--****************************************************************************
--* VARCHAR2の文字列を最大で255文字または、改行コード毎にプリントする内部プロシージャ
--*
--* NOTE:
--* Oracle10g R2では、DBMS_OUTPUT.PUT_LINEプロシージャの255文字制限がないため、
--* 255文字毎に分割出力する必要はなくなる
--****************************************************************************
PROCEDURE println
(
i_text IN VARCHAR2
)
IS
v_src VARCHAR2(32767);
v_tempStr VARCHAR2(32767);
BEGIN
v_src := i_text;
IF INSTR(v_src, UTL_TCP.CRLF) <> 0 THEN
v_tempStr := SUBSTR(v_src, 1, INSTR(v_src, UTL_TCP.CRLF)-1);
v_src := SUBSTR(v_src, INSTR(v_src, UTL_TCP.CRLF)+LENGTH(UTL_TCP.CRLF));
ELSE
v_tempStr := v_src;
END IF;

WHILE v_tempStr IS NOT NULL LOOP
IF LENGTH(v_tempStr)> 255 THEN
FOR i IN 1..(TRUNC(LENGTH(v_tempStr)/255)+1) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_tempStr,1+ 255*(i-1),255));
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(v_tempStr);
END IF;

IF INSTR(v_src, UTL_TCP.CRLF) <> 0 THEN
v_tempStr := SUBSTR(v_src, 1, INSTR(v_src, UTL_TCP.CRLF)-1);
v_src := SUBSTR(v_src, INSTR(v_src, UTL_TCP.CRLF)+LENGTH(UTL_TCP.CRLF));
ELSE
EXIT;
END IF;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Occured : println(VARCHAR2) internal procedure.');
DBMS_OUTPUT.PUT_LINE(sqlerrm());
RAISE;
END println;


PROCEDURE report_rss_item(p_rss_item IN rss_type)
IS
BEGIN
println('TITLE: '||p_rss_item.title);
println('URL : '||p_rss_item.link);

IF p_rss_item.pubDate IS NOT NULL THEN
println('Published on: '||p_rss_item.pubDate);
END IF;

IF p_rss_item.category IS NOT NULL THEN
println('Category: '||p_rss_item.category);
END IF;

IF p_rss_item.description IS NOT NULL THEN
println('==');
println('Summary: '||p_rss_item.description);
END IF;

println('—————————————-');
println(' ');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occured : report_rss_item internal procedure');
DBMS_OUTPUT.PUT_LINE(sqlerrm());
RAISE;
END report_rss_item;

--****************************************************************************
--* Main procedure
--****************************************************************************
BEGIN
DBMS_OUTPUT.ENABLE(200000);
println('begin...');

v_url := i_url;
IF v_url IS NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'URLを指定してください。');
END IF;

v_req := UTL_HTTP.BEGIN_REQUEST(v_url);
UTL_HTTP.SET_HEADER(v_req, 'User-Agent', 'Oracle UTL_HTTP/Oracle10g R1');
UTL_HTTP.SET_HEADER(v_req, 'Content-Type', 'text/xml;charset=UTF-8');
v_resp := UTL_HTTP.GET_RESPONSE(v_req);

-- RSSソースを一時CLOBへ書き出す
DBMS_LOB.CREATETEMPORARY(v_tempSourceClob, FALSE);
BEGIN
LOOP
UTL_HTTP.READ_LINE(v_resp, v_source, true);

-- 改行コード強制的に付加。
-- xml_println()でDBMS_OUTPUT.PUT_LINE()が、最大255文字までしか
-- 表示できないことへの一時的な対策。(試しているサイトについてはこれで
-- 回避できているので今のところはこのままで。)
v_source := v_source || UTL_TCP.CRLF;
DBMS_LOB.WRITEAPPEND(v_tempSourceClob, LENGTH(v_source), v_source);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(v_resp);
END;

println('====================');
v_myParser := DBMS_XMLPARSER.NEWPARSER();
DBMS_XMLPARSER.PARSECLOB(v_myParser, v_tempSourceClob);
v_rssDoc := DBMS_XMLPARSER.GETDOCUMENT(v_myParser);
v_tempXMLType := DBMS_XMLDOM.GETXMLTYPE(v_rssDoc);
v_rssEntryList := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(v_rssDoc, 'item');
v_rss_item := v_rss_empty_item;

println
(
v_tempXMLType.EXTRACT('/rss/channel/title/text()').GETSTRINGVAL
|| ' - most recent '
|| DBMS_XMLDOM.GETLENGTH(v_rssEntryList)
|| ' articles:'
);
println('====================');

FOR i IN 1..DBMS_XMLDOM.GETLENGTH(v_rssEntryList) LOOP
v_itemNode := DBMS_XMLDOM.ITEM(v_rssEntryList, i-1);
v_rssItemFields := DBMS_XMLDOM.GETCHILDNODES(v_itemNode);

FOR j IN 1..DBMS_XMLDOM.GETLENGTH(v_rssItemFields) LOOP
v_childnode := DBMS_XMLDOM.ITEM(v_rssItemFields, j-1);
v_childNodeText := DBMS_XMLDOM.GETNODEVALUE(
DBMS_XMLDOM.GETFIRSTCHILD(v_childnode)
);

CASE DBMS_XMLDOM.GETNODENAME(v_childnode)
WHEN 'title' THEN
v_rss_item.title := v_childNodeText;

WHEN 'link' THEN
v_rss_item.link := v_childNodeText;

WHEN 'published' THEN
v_rss_item.pubDate := v_childNodeText;

WHEN 'category' THEN
v_rss_item.category := v_childNodeText;

WHEN 'description' THEN
v_rss_item.description := v_childNodeText;

ELSE
NULL;
END CASE;
END LOOP;

report_rss_item(v_rss_item);
END LOOP;

DBMS_LOB.FREETEMPORARY(v_tempSourceClob);
DBMS_XMLPARSER.FREEPARSER(v_myParser);

println('...End');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'Main Procedure:'
|| sqlerrm()
);
RAISE;

END RSS_FEED_READER;


今聞いている曲:
これらの曲がリリースされた時期から随分経っているが、種々の問題は残されたままのような気がする。と考えつつ。。。

ALCATRAZZ -- Hiroshima mon amour

Human Leage - The LebanonThe Human League - Hysteria - The Lebanon
The Human League - Hysteria - The Lebanon

| | コメント (0) | トラックバック (0)

2006年8月 8日 (火)

Mac De PL/SQL RSS Reader #3

前回のつづきです。
さて、文字化けを回避する方法はないかと考えてみた。
URL指定で、Oracle10g R1のDBMS_XMLPARSERパッケージのパーサーでパースする場合、裏で行われて言うHTTPで正しくエンコーディングされていないのが原因でだろうと考え、試しにUTL_HTTPパッケージを使いRSSソースを取得してみることにした。

尚、DBMS_XMLPARDERパッケージ、DBMS_XMLDOMパッケージ、UTL_HTTPパッケージ、UTL_TCPパッケージの詳細については、「PL/SQL パッケージ・プロシージャおよびタイプ・リファレンス」を参照のこと。

まずは、結果から。
以下のように、HTTPで取得したRSSを表示すると文字化けは発生しなかった。(思った通り!)

データベースOracle10g R1 glasscatfishに接続中です。<br>
begin...
====================
DBMS_XMLPARSERバージョン:10.1.0.2.0
DBMS_XMLDOMバージョン   :

<rss version="0.91">
  <channel>
    <title>メガ放談</title>
    <link>http://megawatt.blogdns.net/blog</link>
    <description>A Weblog Product for Zope</description>
    <item>
      <title>亀田家親子夏休み日記</title>
      <link>http://megawatt.blogdns.net/blog/253</link>
    </item>
    <item>
      <title>社会保険事務所に行ってきた</title>
      <link>http://megawatt.blogdns.net/blog/252</link>
    </item>
    <item>
      <title>重なるときは重なる Part2</title>
      <link>http://megawatt.blogdns.net/blog/251</link>
    </item>
    <item>
      <title>重なるときは重なる</title>
      <link>http://megawatt.blogdns.net/blog/250</link>
    </item>
    <item>
      <title>うしろすがた</title>
      <link>http://megawatt.blogdns.net/blog/249</link>
    </item>
    <item>
      <title>猫のための写真講座/2006年度前期・4</title>
      <link>http://megawatt.blogdns.net/blog/248</link>
    </item>
    <item>
      <title>ワイン100mlが80キロカロリー!!</title>
      <link>http://megawatt.blogdns.net/blog/247</link>
    </item>
    <item>
      <title>はっちゃんではないはっちゃん</title>
      <link>http://megawatt.blogdns.net/blog/246</link>
    </item>
    <item>
      <title>ちょっと、走ってる</title>
      <link>http://megawatt.blogdns.net/blog/245</link>
    </item>
    <item>
      <title>命名、じみへん</title>
      <link>http://megawatt.blogdns.net/blog/244</link>
    </item>
    <item>
      <title>猫まんが at門前仲町</title>
      <link>http://megawatt.blogdns.net/blog/243</link>
    </item>
    <item>
      <title>猫を拾った日</title>
      <link>http://megawatt.blogdns.net/blog/242</link>
    </item>
    <item>
      <title>お飲物なににしますか?</title>
      <link>http://megawatt.blogdns.net/blog/241</link>
    </item>
    <item>
      <title>門前仲町・深川不動堂</title>
      <link>http://megawatt.blogdns.net/blog/240</link>
    </item>
    <item>
      <title>う〜ん、まずい。</title>
      <link>http://megawatt.blogdns.net/blog/239</link>
    </item>
    <item>
      <title>短歌熱</title>
      <link>http://megawatt.blogdns.net/blog/238</link>
    </item>
  </channel>
</rss>
====================

...End
プロセス終了。<br>
データベースOracle10g R1 glasscatfishから切断中です。<br>

以下、変更後のプロシージャのソースを示す。
変更点は、
DBMS_XMLPARSERパッケージのパーサーで、URL直指定でパースするのではなく、HTTP経由でCLOBにRSSソースを取り込み、CLOBをパースするようにした。
XMLをプリントする内部プロシージャも変更し、println(CLOB)からxml_println(XMLTYPE)へ変更した。(XMLTYPEにしておいたほうがなにかと便利なので。尚、元ネタは、ここ) 

CREATE OR REPLACE PROCEDURE RSS_FEED_READER
(
  i_url IN VARCHAR2
)
AS
  --============ TYPEs/VARIABLEs ===============================================
  TYPE rss_type IS RECORD
  (
    title       VARCHAR2(2000),
    link        VARCHAR2(200),
    pubDate     VARCHAR2(200),
    category    VARCHAR2(500),
    description VARCHAR2(4000)
  );
  v_rss_item        rss_type;
  v_rss_empty_item  rss_type; -- v_rss_item初期化用

  v_url             VARCHAR2(32767);

 
    -- XML PARSER
  v_myParser        DBMS_XMLPARSER.Parser;

  -- RSSまたは、ATOMフォーマットのDOM
  v_rssDoc          DBMS_XMLDOM.DomDocument; 

    -- for UTL_HTTP
  v_req             UTL_HTTP.REQ;
  v_resp            UTL_HTTP.RESP;

  -- HTTP経由でRSS/ATOMソースを取り込むためのワーク
  v_source          VARCHAR2(1024);
  v_tempSourceClob  CLOB;
         
   --========== Internal PROCEDUREs/FUNCTIONs ===================================
  --
  --****************************************************************************
  --* VARCHAR2の文字列を最大で255文字または、改行コード毎にプリントする内部プロシージャ
  --*
  --* NOTE:
  --* Oracle10g R2では、DBMS_OUTPUT.PUT_LINEプロシージャの255文字制限が緩和され32767文字まで出力できるようになるためがないため
  --* 255文字毎に分割出力する必要はなくなるなぁ。
  --****************************************************************************
  PROCEDURE println
  (
    i_text IN VARCHAR2
  )
  IS
    v_src     VARCHAR2(32767);
    v_tempStr VARCHAR2(32767);
  BEGIN
    v_src := i_text;
    IF INSTR(v_src, UTL_TCP.CRLF) <> 0 THEN
      v_tempStr := SUBSTR(v_src, 1, INSTR(v_src, UTL_TCP.CRLF)-1);
      v_src := SUBSTR(v_src, INSTR(v_src, UTL_TCP.CRLF)+LENGTH(UTL_TCP.CRLF));
    ELSE
      v_tempStr := v_src;
    END IF;
   
    WHILE v_tempStr IS NOT NULL LOOP
      IF LENGTH(v_tempStr)> 255 THEN
        FOR i IN 1..(TRUNC(LENGTH(v_tempStr)/255)+1) LOOP
          DBMS_OUTPUT.PUT_LINE(SUBSTR(v_tempStr,1+ 255*(i-1),255));
        END LOOP;
      ELSE
        DBMS_OUTPUT.PUT_LINE(v_tempStr);
      END IF;

      IF INSTR(v_src, UTL_TCP.CRLF) <> 0 THEN
        v_tempStr := SUBSTR(v_src, 1, INSTR(v_src, UTL_TCP.CRLF)-1);
        v_src := SUBSTR(v_src, INSTR(v_src, UTL_TCP.CRLF)+LENGTH(UTL_TCP.CRLF));
      ELSE
        EXIT;
      END IF;
    END LOOP;
   
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Occured : println(VARCHAR2) internal procedure.');
      DBMS_OUTPUT.PUT_LINE(sqlerrm());
      RAISE;
  END println;

  --****************************************************************************
  --* XMLTYPEに格納されたXMLソースをプリントする。
  --* (Oracle10g R1だと、255文字毎の分割プリントは必要かも。。。未実装)
  --*
  --* NOTE:
  --* Oracle10g R2では、DBMS_OUTPUT.PUT_LINEプロシージャの255文字制限がないため、
  --* 255文字毎に分割出力する必要はなくなる32767文字毎の分割出力でもOK.
  --****************************************************************************
  PROCEDURE xml_println
  (
    i_text IN XMLTYPE
  )
  IS
    v_str LONG;
  BEGIN
    v_str := i_text.EXTRACT('/*').GETSTRINGVAL();
    WHILE(v_str IS NOT NULL) LOOP
      DBMS_OUTPUT.PUB_LINE(SUBSTR(v_str, 1, INSTR(v_str, CHR(10)) - 1));
      v_str := SUBSTR(v_str, INSTR(v_str, CHR(10)) + 1);
    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Occured : xml_println(XMLTYPE) internal procedure.');
      DBMS_OUTPUT.PUT_LINE(sqlerrm());
      RAISE;
  END xml_println;

--****************************************************************************
--* Main Procedure
--****************************************************************************
BEGIN
  DBMS_OUTPUT.ENABLE(200000);
  println('begin...');

  v_url := i_url;
  IF v_url IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'URLを指定してください。');
  END IF;

  v_req := UTL_HTTP.BEGIN_REQUEST(v_url);
  UTL_HTTP.SET_HEADER(v_req, 'User-Agent', 'Oracle UTL_HTTP/Oracle10g R1');
  UTL_HTTP.SET_HEADER(v_req, 'Content-Type', 'text/xml;charset=UTF-8');
  v_resp := UTL_HTTP.GET_RESPONSE(v_req);
 
  -- RSSソースを一時CLOBへ書き出す
  DBMS_LOB.CREATETEMPORARY(v_tempSourceClob, FALSE);
  BEGIN
    LOOP
      UTL_HTTP.READ_LINE(v_resp, v_source, true);
      
      -- 改行コード強制的に付加。
      -- xml_println()でDBMS_OUTPUT.PUT_LINE()が、最大255文字までしか
      -- 表示できないことへの一時的な対策。(試しているサイトについてはこれで
      -- 回避できているので今のところはこのままで。)
      v_source := v_source || UTL_TCP.CRLF;
      DBMS_LOB.WRITEAPPEND(v_tempSourceClob, LENGTH(v_source), v_source);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
      UTL_HTTP.END_RESPONSE(v_resp);
  END;
 
  println('====================');
  v_myParser := DBMS_XMLPARSER.NEWPARSER();
  DBMS_XMLPARSER.PARSECLOB(v_myParser, v_tempSourceClob);
  v_rssDoc := DBMS_XMLPARSER.GETDOCUMENT(v_myParser);
 
  println('DBMS_XMLPARSERバージョン:'||DBMS_XMLPARSER.GETRELEASEVERSION());
  println('DBMS_XMLDOMバージョン   :'||DBMS_XMLDOM.GETVERSION(v_rssDoc));
  println(' ');
  xml_println(DBMS_XMLDOM.GETXMLTYPE(v_rssDoc));
  println('====================');
  println(' ');
  DBMS_LOB.FREETEMPORARY(v_tempSourceClob);
  DBMS_XMLPARSER.FREEPARSER(v_myParser);

  println('...End');

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error Occured : main procedure.');
    DBMS_OUTPUT.PUT_LINE(sqlerrm());
    RAISE;

    END RSS_FEED_READER;

また、前述のようにした事で、Mac De Oracleなどで発生してたココログのATOM配信フォーマットのパースエラーも消えた!?(やはり文字化けが影響していたようだ。)
以下に、Mac De OracleのATOMを読み込んだOracle SQL Developer 1.0 for MacOSXのスナップショットを示す。


Jdevok

Oracle10g R1で、なんとか日本語対応のRSS Readerを作ることもできそうな感触。。。。Oracle10g R2は、MacOSX版がリリースされていないの(本当にリリースされるのか??)で、それまではLinux版のOracel10g R2で試してみるか。。。

今聞いている曲:
Malene Mortensen - Date with a DreamMalene Mortensen - デイト・ウィズ・ア・ドリーム GENAI - HAEVEN OF EARTHジェナイ - ヘブン・オン・アース

| | コメント (0) | トラックバック (0)

2006年8月 7日 (月)

Mac De PL/SQL RSS Reader #2

前回のつづきです。

前回、


RSS Readerの実験に利用するBlogは、私の友人であるmegawattさんのサイトを利用させて頂いた。(自分のブログである、Mac De OracleのRSSを利用していないのだが、理由は後日!)

と書いたが、当初、私のブログ(Mac De Oracle)で試そうと思ったのだが、何故、利用しなかったのか書いておく。
ココログは、RSSフォーマットではなく、Atom配信フォーマット。これはXMLのフォーマットが異なるだけなので、利用しなかった理由ではない。問題は、ココログのATOM配信フォーマットをDBMS_XMLPARSERパッケージのParserでパースすると、パースエラーが発生するためだ。

以下、前回のプロシージャを利用してMac De OracleのATOMを解析した実行結果を示す。(実行には、Oracle PL/SQL Developer 1.0を利用した。)

データベースOracle10g Scottに接続中です。
ORA-31011: XML解析に失敗しました
ORA-19202: XML処理
LPX-00244: 小なり不等号 ('<')文字の使用が無効です。 (&lt;を使用します)
Error at line 63
中にエラーが発生しました
ORA-06512: "SCOTT.RSS_FEED_READER", 行155
ORA-06512: 行6
begin...
Main Procedure:ORA-31011: XML解析に失敗しました
ORA-19202: XML処理
LPX-00244: 小なり不等号 ('<')文字の使用が無効です。 (&lt;を使用します)
Error at line 63
中にエラーが発生しました
プロセス終了。
データベースOracle10g Scottから切断中です。

私のブログだけが問題なのか? 少々、心配になったので、私のお気に入りブログの一つである「鎌倉withLove」(現在は、ご自身で立ち上げたサイトに移行されているが、まだ、ココログにも残っているのでココログのATOMでも試させていただいた。)でも試してみた。やはり、同じエラーでXMLのパースエラーとなる。

Macdeoracleatom_error

新しい「鎌倉withLove」のサイトのATOMでも試してみたのだが、そちらではパースエラーは発生していない。文字化けは発生するが。。。(ココログもベースはMovableTypeだと思うのだが。。。随分カスタマイズされているようで。。)

Kamakurawithlove_ok

#尚、パースエラーの原因は、DBMS_XMLPARDERパッケージで行うURL直指定パースで発生する文字化けが絡んでいる可能性も捨てきれない。(これは後日確認する予定。)


今聞いている曲:
Najee - Tokyo Blue Kim Waters - All for Love

| | コメント (0) | トラックバック (0)

2006年8月 6日 (日)

Mac De PL/SQL RSS Reader #1

Mac De Oracle、久々の投稿です。前回までの騒ぎはなんとか収束。(あとはベンダーさん次第。。)

ということで、以前から読んでいるこのブログの記事にあるPL/SQLでRSS Reader!
そのネタの一部?!を試してみようと思う。まず、手始めに一番気になっているエンコーディングの部分から始めることにする。

環境は、 いつもの Oracle10g R1 EE for MacOSX Serverを利用する。(インストール方法などは、こちらを参考にしてください。
尚、MacOS Xは、以下のように現在の最新版を利用している。なお、データベースキャラクタセットは、JA16SJISTILDEである。

Os

RSS Readerの実験に利用するBlogは、私の友人であるmegawattさんのサイトを利用させて頂いた。(自分のブログである、Mac De OracleのRSSを利用していないのだが、理由は後日!)
megawattさんRSSのバージョンは、RSS 0.91RSS 1.0を提供しているが、当面はRSS 0.91で遊ぶことにする。

megawattさん、いろいろと試していたので、アクセスログがタップリ吐かれていると思いますが、DOS攻撃ではないのでご安心を! (^^;;;;;

さて、本題。
結果を先に書いておくと、Oracle10g R1のDBMS_XMLPARSERパッケージのXMLパーサーでRSS FeedのURLを直指定しパースした場合、マルチバイト文字は文字化した。(Oracle10g R2ではどうなのかは後日確認する予定)。 なんとかエンコーディングを指定できないものかと調べてみたが、Oracle10g R1のDBMS_XMLPARSERでは、XMLパーサーに対してエンコーディングを指定することはできないようだ。(マニュアルをざっと読んでみたが、該当するプロシージャもファンクションも存在しない。)


以下は、Oracle10g R1 EE for MacOSX ServerのDBMS_XMLPARSERパッケージを利用してパース後、XMLDOMからRSSソースを取り出しリストした結果である。文字の化け方から想像すると、UTF-8をISO Latin 1としてエンコードしているのかもしれない。
尚、すべての操作はOracle SQL Developer 1.0を利用して行った。(便利なのだがMacOSXだと、debuggerの動作がかなり遅いかも。。実際にはdebuggerを起動しなくても試せるので問題は無かったのだが。。)


_1 _2

_3 _4



データベースOracle10g R1 glasscatfishに接続中です。
begin...
DBMS_XMLPARSERバージョン:10.1.0.2.0
DBMS_XMLDOMバージョン :1.0
=== パース済みDOMから取得したRSSソース ===
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE rss PUBLIC "-//Netscape Communications//DTD RSS 0.91//EN"
"http://my.netscape.com/publish/formats/rss-0.91.dtd">
<rss version="0.91">
<channel>
<title>a?!a?¬a??e≪?</title>
<link>http://megawatt.blogdns.net/blog</link>
<description>A Weblog Product for Zope</description>

<item>
<title>c??a??a??e?oao?a??a??a?≪e!?a?£a?|a??a??</title>
<link>http://megawatt.blogdns.net/blog/252</link>
</item>

<item>
<title>e??a?aa??a?¨a??a? ̄e??a?aa?? Part2</title>
<link>http://megawatt.blogdns.net/blog/251</link>
</item>

<item>
<title>e??a?aa??a?¨a??a? ̄e??a?aa??</title>
<link>http://megawatt.blogdns.net/blog/250</link>
</item>

<item>
<title>a??a??a??a??a??a??</title>
<link>http://megawatt.blogdns.net/blog/249</link>
</item>

<item>
<title>c?≪a?Ra??a??a?Ra??c??e¬?ao§i??2006a1´ao|a??a??a?≫i??</title>
<link>http://megawatt.blogdns.net/blog/248</link>
</item>

<item>
<title>a? ̄a??a?3100mla??80a?-a?-a?≪a?-a?aa??!!</title>
<link>http://megawatt.blogdns.net/blog/247</link>
</item>

<item>
<title>a? ̄a?£a?!a??a??a?§a? ̄a?aa??a? ̄a?£a?!a??a??</title>
<link>http://megawatt.blogdns.net/blog/246</link>
</item>


<item>
<title>a?!a??a?£a?¨a??eμ°a?£a?|a??</title>
<link>http://megawatt.blogdns.net/blog/245</link>
</item>

<item>
<title>a??a??a??a??a??a?,a??</title>
<link>http://megawatt.blogdns.net/blog/244</link>
</item>

<item>
<title>c?≪a??a??a?? ate??a??a≫2c?o</title>
<link>http://megawatt.blogdns.net/blog/243</link>
</item>

<item>
<title>c?≪a??a??a?£a??a?¥</title>
<link>http://megawatt.blogdns.net/blog/242</link>
</item>

<item>
<title>a??e£2c?ca?aa?≪a?≪a??a??a??a???</title>
<link>http://megawatt.blogdns.net/blog/241</link>
</item>

<item>
<title>e??a??a≫2c?oa?≫a・±a・?a,?a??a??</title>
<link>http://megawatt.blogdns.net/blog/240</link>
</item>

<item>
<title>a??a??a??a??a??a??a??a??</title>
<link>http://megawatt.blogdns.net/blog/239</link>
</item>

<item>
<title>c?-a-?c?±</title>
<link>http://megawatt.blogdns.net/blog/238</link>
</item>

<item>
<title>a??a??a??a??a??a??a?aa??a?§</title>
<link>http://megawatt.blogdns.net/blog/237</link>
</item>

</channel>
</rss>
====================
...End
プロセス終了。
データベースOracle10g R1 glasscatfishから切断中です。

以下は、AMIS Technology Blogのサンプルコードを参考にして作成したプロシージャのソースである。
URLで指定されたRSSソースをDBMS_XMLPARSERパッケージのXMLパーサーでパースし、生成されたXMLDOMオブジェクトからRSSソースをリストするしている。


CREATE OR REPLACE PROCEDURE RSS_FEED_READER
(
i_url IN VARCHAR2
)
AS
--============ TYPEs/VARIABLEs ===============================================
TYPE rss_type IS RECORD
(
title VARCHAR2(2000),
link VARCHAR2(200),
pubDate VARCHAR2(200),
category VARCHAR2(500),
description VARCHAR2(4000)
);
v_rss_item rss_type;
v_rss_empty_item rss_type; -- v_rss_item初期化用


v_url VARCHAR2(32767);

-- XML PARSER
v_myParser DBMS_XMLPARSER.Parser;

-- XMLDOM
v_rssDoc DBMS_XMLDOM.DomDocument;

-- for UTL_HTTP
v_req UTL_HTTP.REQ;
v_resp UTL_HTTP.RESP;
v_source VARCHAR2(1024);

-- RSSソース用CLOB(SYS.XMLTYPE型にしたほうがよかったかもしれないが、現時点ではCLOBで!)
v_tempClob CLOB;

--========== Internal PROCEDUREs/FUNCTIONs ===================================
--
--****************************************************************************
--* VARCHAR2の文字列を最大で255文字または、改行コード毎にプリントする内部プロシージャ
--****************************************************************************
--
PROCEDURE println
(
i_text IN VARCHAR2
)
IS
v_src VARCHAR2(32767);
v_tempStr VARCHAR2(32767);
BEGIN
v_src := i_text;
IF INSTR(v_src, UTL_TCP.CRLF) <> 0 THEN
v_tempStr := SUBSTR(v_src, 1, INSTR(v_src, UTL_TCP.CRLF)-1);
v_src := SUBSTR(v_src, INSTR(v_src, UTL_TCP.CRLF)+LENGTH(UTL_TCP.CRLF));
ELSE
v_tempStr := v_src;
END IF;

WHILE v_tempStr IS NOT NULL LOOP
IF LENGTH(v_tempStr)> 255 THEN
FOR i IN 1..(TRUNC(LENGTH(v_tempStr)/255)+1) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_tempStr,1+ 255*(i-1),255));
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(v_tempStr);
END IF;

IF INSTR(v_src, UTL_TCP.CRLF) <> 0 THEN
v_tempStr := SUBSTR(v_src, 1, INSTR(v_src, UTL_TCP.CRLF)-1);
v_src := SUBSTR(v_src, INSTR(v_src, UTL_TCP.CRLF)+LENGTH(UTL_TCP.CRLF));
ELSE
EXIT;
END IF;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'println(VARCHAR2) internal procedure:'
|| sqlerrm()
);
RAISE;
END println;

--****************************************************************************
--* CLOBの文字列を最大で255文字または、改行コード毎にプリントする内部プロシージャ
--****************************************************************************
--
PROCEDURE println
(
i_text IN CLOB
)
IS
v_src CLOB;
v_tempStr CLOB;
BEGIN
v_src := i_text;
IF INSTR(v_src, UTL_TCP.CRLF) = 0 THEN
v_src := REPLACE(v_src, '><', '>'||UTL_TCP.CRLF||'<');
END IF;

v_tempStr := SUBSTR(v_src, 1, INSTR(v_src, UTL_TCP.CRLF)-1);
v_src := TRIM(LEADING UTL_TCP.CRLF FROM SUBSTR(v_src, INSTR(v_src, UTL_TCP.CRLF)));

WHILE v_tempStr IS NOT NULL LOOP
IF LENGTH(v_tempStr) > 255 THEN
FOR i IN 1..(TRUNC(LENGTH(v_tempStr)/255)+1) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_tempStr,1+ 255*(i-1),255));
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(v_tempStr);
END IF;
v_tempStr := SUBSTR(v_src, 1, INSTR(v_src, UTL_TCP.CRLF)-1);
v_src := TRIM(LEADING UTL_TCP.CRLF FROM SUBSTR(v_src, INSTR(v_src, UTL_TCP.CRLF)));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'println(CLOB) internal procedure:'
|| sqlerrm()
);
RAISE;
END println;

--****************************************************************************
--* Main Procedure
--****************************************************************************
BEGIN
DBMS_OUTPUT.ENABLE(200000);
println('begin...');


v_url := i_url;
IF v_url IS NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'URLを指定してください。');
END IF;

v_myParser := DBMS_XMLPARSER.NEWPARSER();
DBMS_XMLPARSER.PARSE(v_myParser, v_url);
v_rssDoc := DBMS_XMLPARSER.GETDOCUMENT(v_myParser);
DBMS_LOB.CREATETEMPORARY(v_tempClob, FALSE);
DBMS_XMLDOM.WRITETOCLOB(v_rssDoc, v_tempClob);
println('DBMS_XMLPARSERバージョン:'||DBMS_XMLPARSER.GETRELEASEVERSION());
println('DBMS_XMLDOMバージョン :'||DBMS_XMLDOM.GETVERSION(v_rssDoc));
println(' ');
println('=== パース済みDOMから取得したRSSソース ===');
println(v_tempClob);
println('====================');
println(' ');
DBMS_LOB.FREETEMPORARY(v_tempClob);

DBMS_XMLPARSER.FREEPARSER(v_myParser);

println('...End');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'Main Procedure:'
|| sqlerrm()
);
RAISE;

END RSS_FEED_READER;


次回は、RSSをパースする際、RSS FEEDのURLを直指定したパースではなく、一旦、UTL_HTTPパッケージを利用してRSSを取得、CLOB変換する。そのCLOBをDBMS_XMLPARSERパッケージのパーサーでパースし、結果を確認してみることにする。
(ちなみに、WindowsのOracle10g R2 EEではXMLDOMが修正されたのか、DBMS_XMLPARSERパッケージかDBMS_XMLDOMパッケージが改良されたのか?。異なる結果をしてしている。それに関しては別記事にて書く予定)


今聞いている曲:
Dave Koz - Lucky Man Dave Koz - The Dance Richard Elliot - Metro Blue

| | コメント (0) | トラックバック (0)

2006年5月10日 (水)

Mac De SQL Developer #11

MacOSXでしか発生しない問題は、最新版では修正されています。他の問題も暇を見てみて見る予定。(しかし、忙しいのか、そうじゃないのか微妙な状態。。。)

| | コメント (0) | トラックバック (0)

2006年5月 8日 (月)

Mac De SQL Developer #10

本家OTNで Oracle SQL Developer 1.0, patch 1 (1.0.0.15.27) がリリースされていた。

あとで、ダウンロードしておきますかね。

| | コメント (0) | トラックバック (0)

2006年5月 6日 (土)

Mac De SQL Developer #9

前回に引き続き、SQL Developer 1.0 (Build 1.0.0.14.67)の問題点。文字化けに比べると、軽微なUI上の問題について。

「接続」Paneでオブジェクトを削除すると、展開していたツリーが閉じてしまうという問題と
一括削除時に、各オブジェクト毎に毎回、確認ダイアログが表示されてしまう。

以下は複数のTYPEを選択し一括削除した場合の例。
MacOSXの場合、SHIFTキー又は、コマンドキーを押しながら該当オブジェクトをクリックすると複数のオブジェクトを選択できる。

Sqldev91


「メニュー」→「削除」を選択して該当オブジェクトを削除する。

Sqldev92


複数選択で一括削除なのだが、各オブジェクト毎に「Drop Confirmation」ダイアログが表示される。この例では2つのオブジェクトなので手間にはならないが、数が多いと確認ダイアログに答えるという操作はかなり面倒。
(この確認ダイアログ上に、「以降の確認ダイアログを表示しない。とかいうチェックボックスを付けてくれると便利だと思うが。。」)

「ばい」ボタンをクリックして削除する。

Sqldev93 Sqldev94


さて、問題のUI挙動はここで発生する。削除確認ダイアログで「はい」ボタンをクリックすると該当オブジェクトが削除されるのだが、それと同時に、展開していたツリーが閉じてしまう。

Sqldev95 Sqldev96


次に操作をするには、「接続ツリー」を再度展開しないといけない。

Sqldev97

| | コメント (0) | トラックバック (0)

2006年5月 5日 (金)

Mac De SQL Developer #8

前回、Oracle SQL Developer 1.0 (build 1.0.0.14.67)で気付いた問題がいくつかあると書いたが、今回はその一つについて書く事にする。
無名PL/SQLブロックのデバックの際(ストアドプロシージャでも同じかもしれないが未確認だが、気がついたことがあればコメントとして追加する予定)、実行方法によっては結果が文字化けするのだ。(正式版では修正されるだろうけど。。)

以下の実行結果では 無名PL/SQLブロックを「Execute Statement」ボタンをクリックして実行した結果なのだが、実行した結果は、「DBMS Output」Paneに表示され文字化けもない。

Sqldev81_1


ところが、「Run Script」ボタンをクリックして実行した場合、「Script Output」paneに表示される実行結果は文字化けする。

Sqldev82


いろいろPreferenceを見てみたものの、それに関連しそうな設定はなさそうだ。

| | コメント (0) | トラックバック (0)

2006年5月 1日 (月)

PL/SQL De SORT

なんと、ストアドプロシージャ内で配列をソートしてほしいという要求。
Oracle7のころからPL/SQLで開発をしてきてはいるが、そんな要求、はじめてじゃ!


え。。。。なんで? 


データを問い合わせるときに ORDER BY句でソートしちゃえば早いじゃん。
(扱うデータは表から問い合わせたものでは無く、呼び出し元から引数で渡される配列ということらしい。 )

え?、呼出し元って、javaじゃないの? java側でできるでしょう、そんなこと?。。。

まぁ、あちらさんにもいろいろと事情があるようで。。。。。そのシステムでは必要だ、というのだからサンプルだけは作ってあげましょう。
(ちなみに、Oracle8のころに、こんな事言われたら、即、荷物纏めて撤収してたと思うぞ。。。本気で。)


ということで、Oracle10gを利用する前提で考えてみたが、マニュアルなどの資料では、Oracle8i 8.1.6以降であればできるとなっている。
(以降のサンプルで示すTABLE演算子や、CAST関数が登場するのは、Oracle8i 8.1.6のSQLリファレンスマニュアルから登場している。)
珍しい要求だったこともあり備忘録として書き留めておく。
尚、PL/SQLの言語仕様上、コレクションの要素数に上限はないタイプのコレクションもあるので、設定する要素のサイズや、要素数によっては、それ相当のメモリが消費されることも頭の片隅に置いておくこと。


PL/SQLで扱える3タイプのコレクションの簡単な特徴は以下の通り。

(1)結合配列


Oracle7のころからあるコレクション。索引付き表とも呼ばれる。添字には任意の数字、文字列を利用できる。Perlの連想配列や、JavaのHashTableなどをイメージすればわかりやすいだろう。任意の数の要素を持てる。
この配列でVARCHAR2などの文字型を添字にした場合には要素数の上限は無くなる。binary_integerなどの数値型を利用した場合には各数値型の範囲が要素数の上限となる。

(2)ネスト表

Oracle8以降で登場したコレクション。定義は結合配列に似ているが、配列の添字は1から始まる連続した正の整数のみ(1〜2147483647)であるため要素数には上限がある。結合配列と同様に任意の数の要素を持てる。また、結合配列とは異なり表に格納することもできる。

(3)VARRAY表

Oracle8以降で登場したコレクション。配列の添字はネスト表と同じであるが、要素は固定数保持することができる点が異なる。ネスト表と同様に表に格納することもできる。

Oracle8までならば、PL/SQLの結合配列をソートしろなんてことになると、ロジックをガリガリ書いてみたり、場合によっては、一旦、作業用表へ格納後、取り出す際にORDER BYでソートといった方法が考えられる。いずれにしても、あまりイケてないわけ。

そこで、ネスト表とオブジェクト型を利用し、PL/SQLでコレクションをソートするという方法の簡単な例。(この例ではプロシージャにはしていない)


Oracle10g R1を使用。

※ソート条件として、同値の場合は、元のデータ順が維持されること。(つまり、安定ソートが条件。ちなみに、クイックソートは不安定ソート。)

このソートは、コレクション(ネスト表又はVARRAY)のネスト解除と呼ばれる方法を利用して行う。(マニュアル「SQLリファレンス 10g リリース1」SELECT文の table_collection_expression P19-22 などを参照のこと。)

(1)オブジェクト型の作成


2つの属性を持たせ、user_idまたは、user_nameいずれかでソートすることにする。
SCOTT> l
1 create type UserInfo_typ as object
2 (
3 user_id number(5),
4 user_name varchar2(40)
5* )
SCOTT> /

型が作成されました。


(2)コレクションの定義。

UserInfo_typ型のネスト表を定義した。
SCOTT> create type UserList_typ as table of UserInfo_typ;
2 /

型が作成されました。

(3)コレクション UserList_typを user_nameで昇順にソートする。
'みた'というデータが2件存在するが、元の順序が変わらない安定したソートである点に注目。
SCOTT> set timi on
SCOTT> set serveroutput on
SCOTT> l
1 DECLARE
2 UserList UserList_typ := UserList_typ(
3 UserInfo_typ(1,'あいだ'),
4 UserInfo_typ(2,'さえき'),
5 UserInfo_typ(3,'やおか'),
6 UserInfo_typ(8,'おだ'),
7 UserInfo_typ(5,'みた'),
8 UserInfo_typ(6,'みた'),
9 UserInfo_typ(null,null)
10 );
11 BEGIN
12 FOR user_rec IN (
13 SELECT *
14 FROM
15 TABLE(CAST(UserList as UserList_typ))
16 ORDER BY
17 user_name
18 )
19 LOOP
20 dbms_output.put_line(
21 nvl(to_char(user_rec.user_id),'NULL') ||
22 '=' ||
23 nvl(user_rec.user_name,'NULL')
24 );
25 END LOOP;
26* END;
SCOTT> /
1=あいだ
8=おだ
2=さえき
5=みた
6=みた
3=やおか
NULL=NULL

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

経過: 00:00:00.01
SCOTT>


(4)コレクション UserList_typを user_nameで降順にソートする。

'みた'というデータが2件存在するが、元の順序が変わらない安定したソートである点に注目。
SCOTT> l
1 DECLARE
2 UserList UserList_typ := UserList_typ(
3 UserInfo_typ(1,'あいだ'),
4 UserInfo_typ(2,'さえき'),
5 UserInfo_typ(3,'やおか'),
6 UserInfo_typ(8,'おだ'),
7 UserInfo_typ(5,'みた'),
8 UserInfo_typ(6,'みた'),
9 UserInfo_typ(null,null)
10 );
11 BEGIN
12 FOR user_rec IN (
13 SELECT *
14 FROM
15 TABLE(CAST(UserList as UserList_typ))
16 ORDER BY
17 user_name DESC
18 )
19 LOOP
20 dbms_output.put_line(
21 nvl(to_char(user_rec.user_id),'NULL') ||
22 '=' ||
23 nvl(user_rec.user_name,'NULL')
24 );
25 END LOOP;
26* END;
SCOTT> /
NULL=NULL
3=やおか
5=みた
6=みた
2=さえき
8=おだ
1=あいだ

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

経過: 00:00:00.03


(5)コレクション UserList_typを user_idで降順にソートする。
SCOTT> l
1 DECLARE
2 UserList UserList_typ := UserList_typ(
3 UserInfo_typ(1,'あいだ'),
4 UserInfo_typ(2,'さえき'),
5 UserInfo_typ(3,'やおか'),
6 UserInfo_typ(8,'おだ'),
7 UserInfo_typ(5,'みた'),
8 UserInfo_typ(6,'みた'),
9 UserInfo_typ(null,null)
10 );
11 BEGIN
12 FOR user_rec IN (
13 SELECT *
14 FROM
15 TABLE(CAST(UserList as UserList_typ))
16 ORDER BY
17 user_id DESC
18 )
19 LOOP
20 dbms_output.put_line(
21 nvl(to_char(user_rec.user_id),'NULL') ||
22 '=' ||
23 nvl(user_rec.user_name,'NULL')
24 );
25 END LOOP;
26* END;
SCOTT> /
NULL=NULL
8=おだ
6=みた
5=みた
3=やおか
2=さえき
1=あいだ

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

経過: 00:00:00.03

(6)コレクション UserList_typを user_idで昇順にソートする。

SCOTT> l
1 DECLARE
2 UserList UserList_typ := UserList_typ(
3 UserInfo_typ(1,'あいだ'),
4 UserInfo_typ(2,'さえき'),
5 UserInfo_typ(3,'やおか'),
6 UserInfo_typ(8,'おだ'),
7 UserInfo_typ(5,'みた'),
8 UserInfo_typ(6,'みた'),
9 UserInfo_typ(null,null)
10 );
11 BEGIN
12 FOR user_rec IN (
13 SELECT *
14 FROM
15 TABLE(CAST(UserList as UserList_typ))
16 ORDER BY
17 user_id
18 )
19 LOOP
20 dbms_output.put_line(
21 nvl(to_char(user_rec.user_id),'NULL') ||
22 '=' ||
23 nvl(user_rec.user_name,'NULL')
24 );
25 END LOOP;
26* END;
SCOTT> /
1=あいだ
2=さえき
3=やおか
5=みた
6=みた
8=おだ
NULL=NULL

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

経過: 00:00:00.03

となる。
ソートはOracleにお任せなので、特に小難しいロジックを書く事も無く、PL/SQL内で、簡単にコレクションをソートすることができる。
小難しいロジックの記述はないので単体テストは簡単だろう。
しかし、もし、このような方法を採用するのであれば、本番で想定されるコレクションのサイズ、プログラムの並列実行数でのメモリ消費量のチェックやシステム全体への影響などを確認するための十分な負荷テストが必要だと考える。(Oracle側で Resource Managerや、Profileを使用し、リソース制限をしていれば最悪の事態は防げるが、このような要求をしてくる所にかぎって、Resource ManagerやProfileによるリソース管理が行われていない、ということが多いように感じる。)

この例では、SQL*Plusで実行した例を載せたのだが、当初、Oracle SQL Developer 1.0で試していたものがあるので次回は、その時に気付いたSQL Developerの問題について書く予定。

Sqldev81

| | コメント (0) | トラックバック (0)

2006年4月15日 (土)

Mac De SQL Developer #7

Oracle SQL Developer 1.0 for MacOSXネタというより、OTN-Jで公開していたCodeTips #33関連のネタで、あるユーザが特定のアプリケーションでのみデータベースに接続できるようにしたいというもの。 無理矢理 SQL Developerネタにしてみた。(笑)

尚、OTN-JのCodeTipsを参照するには、OTN-Jにユーザ登録する必要がある。(無料)

CodeTips #33は、JOB QUEUEにジョブを登録し即座に実行、該当する接続を切断するというサンプルである。

JOB QUEUEに登録して実行するという方式であるため ”即座”とは言っても、LOGONクライアントイベントと完全に同期して接続を切断している訳ではない。

JOB QUEUEに関して、Oracle8iまでは、JOB_QUEUE_INTERVALというパラメータでCJQ0プロセス(コーディネータ・ジョブ・プロセス)が、ジョブキューをスキャンする間隔を制御できたが、9i以降廃止されたためこのスキャン間隔を調整することはできない。

また、多数のジョブがエンキューされた場合、登録されたJOBの実行が多少遅延する可能性もある。(JOB_QUEUE_PROCESSESパラメータで、ジョブを実行するJnnn(ジョブ・キュー・プロセス。nnnは数字で000から999まで)プロセス数を調整し、遅延を少なくするという方法はあるが、ただ多くすればいいってものでもない。)

ということで、あるユーザが接続してから接続が切断されるまでには、ある程度のタイムラグが発生する可能性がある。データベースサーバーの負荷が低い状態であれば、ログインとほぼ同時に、接続は切断されることが多いが、当然、そうならない場合もある。

尚、バックグランド・プロセスについては、マニュアル「データベース管理者ガイド Oracleプロセスの管理」及び、「データベース概要の第三部 Oracleプロセスの概要」、
  データベーストリガーについては、マニュアル「アプリケーション開発者ガイド 基礎編」、
  v$ 表に関しては、マニュアル「データース・リファレンス」などを参照すると良いだろう。

その結果、接続が切断されるまでの間にいくつかのDML文などを実行することも可能なのである。
この投稿では、あるユーザが SQL*Plus以外で接続してきた場合、何も出来ないようにしたいと言うことだったため、CodeTips#33のような ジョブキューを利用する方式では要件に合わなかったということなのである。(CodeTipsに載せたコメントに、”ログオンしたとたんに・・・接続を切る”と書いてしまったことも混乱させた原因ではあると思うが。。。。反省)

そこで、私が考えた方法は、接続されてしまってから何とかしようという方式ではなく、接続自体を拒否してしまえばよいのでは?
と思い、以下の方法を提示した。

以下の内容は、OTN-Jの会議室に載せた内容に加えて、Oracle SQL Developerによる接続が拒否され接続エラーとなった画面のスナップショットや、該当トリガーを管理する専用ユーザの作成例も載せておく。

この例では、ログオントリガー管理専用ユーザを作成することにする。

SQL*Plus: Release 10.2.0.1.0 - Production on 水 4月 12 23:30:16 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
接続されました。
SQL> create user caretaker identified by xxxxx
2 default tablespace users
3 temporary tablespace temp
4 quota 1m on users;

ユーザーが作成されました。

SQL> grant create session to caretaker;

権限付与が成功しました。

SQL> grant create trigger to caretaker;

権限付与が成功しました。

SQL> grant select any dictionary to caretaker;

権限付与が成功しました。

SQL> grant administer database trigger to caretaker;

権限付与が成功しました。

作成したcaretakerユーザに接続しトリガーを作成する。

SQL> conn caretaker/xxxxxxx
接続されました。
SQL> CREATE OR REPLACE TRIGGER restrict_logon
2 --
3 -- administer database triggerシステム権限を持つユーザでは
4 -- LOGONクライアントイベントは発生しないのでご注意ください。
5 --
6 AFTER LOGON
7 ON DATABASE
8 DECLARE
9 CURSOR cs_session
10 IS
11 SELECT
12 username,
13 program
14 FROM
15 v$session
16 WHERE
17 audsid = (
18 SELECT
19 USERENV('SESSIONID')
20 FROM
21 DUAL
22 )
23 AND username IS NOT NULL;
24 --
25 c_sqlplus CONSTANT VARCHAR2(7) := 'SQLPLUS';
26 c_target_user CONSTANT VARCHAR2(30) := 'SCOTT';
27 --
28 BEGIN
29 FOR session_rec IN cs_session LOOP
30 IF session_rec.username = c_target_user
31 AND INSTR(UPPER(NVL(session_rec.program, ' ')), c_sqlplus) = 0 THEN
32 RAISE_APPLICATION_ERROR(-20000,'SQL*Plus以外からの接続はできません。');
33 END IF;
34 END LOOP;
35 END;
36 /

トリガーが作成されました。

SQL>


以下、Oracle SQL Developer 1.0でトリガーを作成している画面。(トリガー名やコードの一部が異なりますが気にしないでください)
Mac_sql_dev_restrict_logon_source


SQL*Plusから接続し操作できるかを確認する。
SQL> conn / as sysdba
接続されました。
SQL> select * from dual;

DU
--
X

SQL> conn system/xxxxxx@catfish
接続されました。
SQL> select * from dual;

DU
--
X

SQL> conn scott/tiger
接続されました。
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> conn caretaker/xxxxxx@catfish
接続されました。
SQL> select * from dual;

DU
--
X

SQL>

以下、SQL*Plus以外から接続した場合は

ORA-00604: 再帰SQLレベル1でエラーが発生しました。 
ORA-20000: SQL*Plus以外からの接続はできません。
ORA-06512: 行24

のようなエラーが返され接続できなくなっていることを確認。
以下 Oracle SQL Developer 1.0から接続した際のエラー画面。(MacOSX以外のプラットフォームの画面もあります)

尚、SQL Developerの”接続”設定は、新規作成で接続が確立できないと保存できない為、ログオントリガーを無効化した状態で作成し、その後、ログオントリガーを有効化してテストしたものである。

Oracle SQL Developer 1.0 for MacOSX版のスナップショット
Macosx_sql_dev_connect_error


Oracle SQL Developer 1.0 for Linux版のスナップショット
Linux_sql_dev_connect_error


Oracle SQL Developer 1.0 for Windows版のスナップショット
Win_sql_dev_connect_error

おまけ
Oracle 9i R2 EE for Linuxでも同じトリガーを作成し、SQL Developerから接続するとエラーが発生し接続できないことも確認できた。

Oracle9i_r2_connect_error


尚、このトリガーは、OTN-JのCodeTips#1531 でも公開しています。

| | コメント (0) | トラックバック (0)

2006年4月10日 (月)

Mac De SQL Developer #6

WindowsやLinux版では発生することのない問題点。

MacOSXのGUIには完全に対応していないことが理由だと思うが、ショートカット(command key - Q key) でプログラムが終了しようとするとメニューバーの「SQL Developer」がハイライトしたままでOracle SQL Developerは終了しない。また、同じく、メニューバーの「SQL Developer」から 「Quit SQL Developer」を選択しても終了しない。
Problem0
Problem1


終了させるには、Oracle SQL Developerのアプリケーションメニューの「ファイル」->「終了」を選ぶしかありません。
Problem2


Oracle JDeveloper10gは、MacOSXに最適化されているようで、このようなことはない。(そのうち修正されるでしょうね。)

#他のショートカットは、効くようだ。

| | コメント (0) | トラックバック (0)

2006年4月 7日 (金)

Mac De SQL Developer #5

アイコンが。。

動作がどうのではなく、アイコンがチープというところだけなのですが、
MacOSXの美しいアイコンに慣れてしまうと、すごく気になるわけですね。このアイコンだと。

Sqldevelopericon


で、XCodeの金槌とODBCアドミニストレータのアイコン、それと、XCodeで作成したERDを込み合わせたような感じのアイコンなんてどう?。
OdbcadminXcodeErd

JDeveloper10gのアイコンは、Appleのページにあるアイコンに似ているので、
Macjava Jdev10g
この際、Appleさんにも協力してもらってアイコンを作ってほしいのもですね。

ということで、こんな感じ? になったらよいかもね。(アイコンにも著作権がありますからぼかしてます。きれいなアイコンは 3つの元画像から勝手に ”想像”してくださいませ。Strata 3D をMacOSX版にアップデートしていなかったので、Classic環境ではうまく動かなくなっていた。。。。Classic環境で動けばオリジナルアイコン作ろうかと思っていたのに。。。とほほ。。 )

Icon_1


とお遊びはこのくらいにして、
MacOSXのSQL Developerだけなのかと思っていたら、他のプラットフォームのsql formaterダイアログのレイアウトも真ん中にグチャっと纏まっている。操作上の問題ないが、少々気になるところ。(他のダイアログはきれいに配置されているのでね。。。)
MacOSX版でテーマを切り替えて表示したスナップショット2点と、Linux版(KDE)のスナップショット1点。

Sqlformat1
Sqlformat2
Sqlformat_linux


==================
Boot Campが発表されたので、
そのうち、 "Mac の Windows De Oracle" なんてことになるかもと言った瞬間、妻からは、ん〜〜〜〜っ。
"Mac の Linux De Oracle" のほうがいいんじゃない? ですと、、、さすが、Slackware、Debian育ちの妻らしい答え。(^^;; (今は、Mac好きな妻になってますがね)

私としては、"Mac の MacOSX De Oracle" 路線で行くのは間違いないんですがね。
(と、その前に、 Mac/Intelは、まだうちには無いし。。。。)

-------------
その後、 Mac No Windows De Oracle の Noが、英語の Noに見えちゃうという妻の突っ込みがあり、"No -> の" に変更。

| | コメント (0) | トラックバック (0)

2006年4月 6日 (木)

Mac De SQL Developer #4

前回軽く触れた程度だったが、本格的に試そうかということで以下のようなお遊び環境を準備
Dev

OTN-JでもSQL Developer早期アクセスプログラムを開始したようですね。
http://otn.oracle.co.jp/products/database/sql_developer/index.html

ただ、OTN-Jのページからは、WindowsとLinuxだけがダウンロードできるみたいです。。
http://otn.oracle.co.jp/software/products/sql/index.html

まあ、本家OTNのものをOTN-JでダウンロードできるようにしただけなのでMacOSX版は、本家からダウンロードすればいいとは思います。
しかし、
> Windows 及び、Linuxでサポートされます。
MacOSX版は?
(サポートしないってことになってしまうのかどうかは知りませんが・・・)

とりあえず、Oracle SQL Developer 1.0を試してみた環境の情報など。

MacOSX Serverで試した記事だけ載せますが、Server以外でも動作します。

G5Server:˜ discus$ sw_vers
ProductName: Mac OS X Server
ProductVersion: 10.4.6
BuildVersion: 8I127

G5Server:˜ discus$
G5Server:˜ discus$ /System/Library/Frameworks/JavaVM.framework/Versions/1.5/Home/bin/java -version
java version "1.5.0_05"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_05-83)
Java HotSpot(TM) Client VM (build 1.5.0_05-48, mixed mode, sharing)

JDKに関して、
MacOSXでのデフォルトは、JDK1.4.2のままで J2SE 5.0と共存。
G5Server:˜ discus$ java -version
java version "1.4.2_09"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_09-232)
Java HotSpot(TM) Client VM (build 1.4.2-54, mixed mode)

ライブラリフォルダ以下の内容は以下

Jdk

MacOSXだけでは少々物足りないので、LinuxとWindowsでも軽く遊んでみました。使い勝手には少々問題のあるところもありますが好印象です。(JDeveloperでPL/SQLを開発したことのある方ならすぐにでも移行できるでしょう。また、データベースオブジェクトの操作でも重宝しそうです)

SQL Developerの起動時間は、Linux、MacOSX、Windowsとも意外とサクッと起動するかなという感じで、もたつき感はない。

尚、マシンスペックは以下の通り、
MacOSX 10.4.6 : Apple PowerMac G5 Dual 2.7Ghz RAM:1GB 
United Linux 1.0 (KDE) : Toshiba DynaBook SS SX/210LNLW PentiumM 1Ghz RAM:768MB
Windows XP Professional SP2 : AMD Dual AthronMP 2800+ RAM:1GB

SQL Developerのバージョンは 1.0.0.14.67

#United LinuxからRedHatに乗り換えなきゃな〜そろそろ。(^^;;

前回の記事で、MacOSX版のSQL Developerでは、スプラッシュが表示されないということを書いたが、LinuxとWindows版では見事に表示されていたので、MacOSX版だけの問題のようだ。

使い込んでいくうちにいろいろな問題が見つかるかもしれないので、随時載せる予定。

| | コメント (0) | トラックバック (0)

2006年3月14日 (火)

Mac De Mo Oracle SQL Developer #3

素早いリリースが続いていた Oracle SQL Developer。 1.0がリリースされたようですね。

EA6からみてもそれほど大きな変更はなく安定感がありそう。(そんなに使い込んでいるわけではないので、ただそう感じているだけですが・・・。)http://www.oracle.com/technology/

でも、ICONがちょいと気になるMacOSXユーザなのです。。。
まぁ、ICONなんてちょちょいのちょいとカスタマイズできるので自分で作っちゃえばいいわけですが・・・。

SQLDeveloperICON

SQLDeveloper1



でツリーの下に目をやると、Recycle BINのノードがありました。
お〜〜、Flashback tableもここから行えちゃうのですね。何かと便利そうです。
他にもいろいろな機能がてんこ盛りといった感じがあるのでこれからいろいろ遊んでみる予定。

SQLDeveloper1



そして、これまた素早いというか、タイミングもよく、
SQL Developerがらみの Podcastingもやってますね

| | コメント (0) | トラックバック (0)

2006年3月 1日 (水)

Mac De Mo Oracle SQL Developer #2(遊んでみた)

ダウンロードしたOracle SQL Developer for MacOSXを簡単に操作してみた。意外にサクサク動くのね。

ちなみに、PowerBook G4 1Ghz MacOSX Tiger 10.4.5 RAM:1GB。

ダウンロードしたアーカイブをダブルクリックして解凍すると以下のようなアイコンが登場する。
インストーラかと思ったら、これがSQL Developer の本体でした。
extracted


EA5版なのでアイコンは、もっと、イケてるものに換えてくれると期待しつつ、ダブルクリックして起動。すると、JDeveloperの環境を移行するか?と聞いてくる。これはご愛嬌かな。移行はしたくないので「いいえ」を答えた。
img_00


無事に起動した。(ばんざ〜〜い!)
外見や使用感はJDeveloperのそれに近い。いろいろいじり倒してみると、DB操作やレポート機能など、JDeveloperで行うより使いやすいと感じる点が多い。
img_01
version


以下、Oracle SQL Developer EA5 for MacOSXをいろいろいじっているところ(Flash)






ちなみに、Oracle Open World 2006が今日から始まりました。

3/1(本日)は、
JDeveloperのハンズオンは、Java One 2005で行われたハンズオンと同じタイトルだったので、それ以外を受講。
backup系2つ、OEM系2つ、それと BI系1つの系5つのセッションを受講した。
睡眠不足から、1E-9 データベースパフォーマンス徹底解説の開始前に少々居眠り。気がつくと両サイドに立ち見の方が沢山いてビックリ。パフォーマンス維持やチューニング方法等への関心が高い証拠だろう。

あと一点、今年は遂に、 Oracle Master専用受付や専用ラウンジなども無くなりましたね。ちょいと寂しい感じはするけど。 
それと Java One 祭り的な要素があってもいいのではないか? と感じた次第。(遊び心も大切さ〜っ。俺だけかもそんなこと考えるは?)
一日目の感想はこのぐらいにして、続きは、atsu-logで。

| | コメント (4) | トラックバック (0)

2006年2月28日 (火)

Mac De Mo Oracle SQL Developer

http://www.oracle.com/technology/software/products/sql/index.html

Project Raptor改め、Oracle SQL DeveloperのMacOSX版 EA5がリリースされていますね。

意外に早い対応ですね。
開発ツールのサポートはスムーズなのですが、XEも含めたデータベースのリリースが楽しみですね。(XEはリリースされるとはアナウンスされていませんが・・・・。Macユーザの要望が多ければ・・・・なんとかなったりして・・・)

ダウンロードはしたので暇をみて試してみる予定。

| | コメント (0) | トラックバック (0)