« 2008年2月 | トップページ | 2008年4月 »

2008年3月31日 (月)

タイムゾーン de 自動調整のおまけ

今回はタイムゾーンネタというより、前回、前々回のおまけネタ。

前回、前々回ともほとんど解説なしにサンプルSQLを載せていたので、今回は表を利用せずにカレンダーを生成している部分の説明を少々。
 
前々回の例で利用した階層問合せによるカレンダー生成、前回の例で利用した集計関数cubeによるカレンダー生成いずれも連番を生成するとという点では同じなのですが、階層問合せとcube関数の性格の違いから使い方も少々異なる。いずれにしても知っていると便利なことも多々ありますよ。

では、まず最初は階層問合せから。

カレンダーを生成する前に階層と合わせで一連番号を生成するには、level疑似列を用いて必要なlevelまで階層を作り出せばOK。
(但し、Oracle9iまではこの方法は想定通りに動作しないのでご注意を。10g以降であれば想定通りの結果を返してくれます。)

以下の例では階層問合せを利用し1〜27までの一連番号を生成したものです。

SCOTT> select level
2 from dual
3 connect by level <= 27;

LEVEL
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

27行が選択されました。

SCOTT>

上記の一連番号を利用し、実行同日を含む27日前までの日付を求めてみます。

SCOTT> l
1 select trunc(sysdate) - (level-1)
2 from dual
3 connect by level <= 27
4* order by 1
SCOTT> /

TRUNC(SY
--------
08-03-05
08-03-06
08-03-07
08-03-08
08-03-09
08-03-10
08-03-11
08-03-12
08-03-13
08-03-14
08-03-15
08-03-16
08-03-17
08-03-18
08-03-19
08-03-20
08-03-21
08-03-22
08-03-23
08-03-24
08-03-25
08-03-26
08-03-27
08-03-28
08-03-29
08-03-30
08-03-31

27行が選択されました。

SCOTT>

できましたよね。簡単です。


次に集計関数cubeを利用する方法です。
cubeに関するマニュアルも事前に読んでおくといいですよ。

一連版を生成するために、cube関数の以下のような性質を利用しています。

SCOTT> select 1
2 from dual
3 group by cube(1);

1
----------
1
1

SCOTT> select 1
2 from dual
3 group by cube(1,1);

1
----------
1
1
1
1

SCOTT> select 1
2 from dual
3 group by cube(1,1,1);

1
----------
1
1
1
1
1
1
1
1

8行が選択されました。

SCOTT>

階層問合せのような柔軟性はないのですが、32件のデータを生成するには cube(1,1,1,1,1)とすればいいわけです。

SCOTT> select 1
2 from dual
3 group by cube(1,1,1,1,1);

1
----------
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1

32行が選択されました。

SCOTT>

ただ一連番号にはなっていません。単に32行の結果を得たたけです。そこでronum疑似列を利用して1〜27までの一連番号を求めます。

SCOTT> select rownum
2 from (select 1 from dual group by cube(1,1,1,1,1))
3 where rownum <= 27;

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

27行が選択されました。

SCOTT>


ここまでできればあとは簡単。階層問合せと同じように日付を生成してみましょう。

SCOTT> select trunc(sysdate) - (rownum-1)
2 from (select 1 from dual group by cube(1,1,1,1,1))
3 where rownum <= 27
4 order by 1;

TRUNC(SY
--------
08-03-05
08-03-06
08-03-07
08-03-08
08-03-09
08-03-10
08-03-11
08-03-12
08-03-13
08-03-14
08-03-15
08-03-16
08-03-17
08-03-18
08-03-19
08-03-20
08-03-21
08-03-22
08-03-23
08-03-24
08-03-25
08-03-26
08-03-27
08-03-28
08-03-29
08-03-30
08-03-31

27行が選択されました。

SCOTT>

cube関数はOracle9iから利用できる関数なのでOracle9iでこんなことを行いたい場合には前述した階層問い合せの代役にもなりますね。


Enjoy SQL!

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

2008年3月29日 (土)

タイムゾーン de 自動調整 #2

タイムゾーン de 自動調整の続きです。 前回階層問合せを利用して実現した方法を集計関数cubeで置き換えた例。

クライアントのタイムゾーン(SESSIONTIMEZONE)を確認。(タイムゾーンはJST)。

SCOTT> select sessiontimezone from dual;

SESSIONTIMEZONE
-----------------------------------------------------------------
+09:00

SCOTT> l
1 select
2 cal."date",
3 nvl(logs."activity",0) as "activity"
4 from
5 (
6 select
7 "date",
8 count(ts_ltz) as "activity"
9 from
10 (
11 select
12 to_char(ts_ltz, 'YYYY/MM/DD') as "date",
13 ts_ltz
14 from
15 ts_test
16 )
17 group by
18 "date"
19 ) logs
20 right outer join
21 (
22 select
23 to_char(
24 trunc(sysdate) - (rownum - 1),
25 'YYYY/MM/DD'
26 ) as "date"
27 from
28 (
29 select 1
30 from
31 dual
32 group by
33 cube (1,1,1,1,1)
34 )
35 where
36 rownum <= 27
37 ) cal
38 on logs."date" = cal."date"
39 order by
40* cal."date"
SCOTT> /

date activity
---------- ----------
2008/03/02 2
2008/03/03 2
2008/03/04 2
2008/03/05 2
2008/03/06 2
2008/03/07 2
2008/03/08 2
2008/03/09 2
2008/03/10 2
2008/03/11 2
2008/03/12 2
2008/03/13 2
2008/03/14 2
2008/03/15 2
2008/03/16 2
2008/03/17 2
2008/03/18 2
2008/03/19 2
2008/03/20 2
2008/03/21 2
2008/03/22 2
2008/03/23 2
2008/03/24 0
2008/03/25 2
2008/03/26 2
2008/03/27 3
2008/03/28 0

27行が選択されました。

SCOTT>
<

クライアントのタイムゾーンをUTCへ変更

SCOTT> alter session set time_zone='UTC';

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

SCOTT>
SCOTT> l
1 select
2 cal."date",
3 nvl(logs."activity",0) as "activity"
4 from
5 (
6 select
7 "date",
8 count(ts_ltz) as "activity"
9 from
10 (
11 select
12 to_char(ts_ltz, 'YYYY/MM/DD') as "date",
13 ts_ltz
14 from
15 ts_test
16 )
17 group by
18 "date"
19 ) logs
20 right outer join
21 (
22 select
23 to_char(
24 trunc(sysdate) - (rownum - 1),
25 'YYYY/MM/DD'
26 ) as "date"
27 from
28 (
29 select 1
30 from
31 dual
32 group by
33 cube (1,1,1,1,1)
34 )
35 where
36 rownum <= 27
37 ) cal
38 on logs."date" = cal."date"
39 order by
40* cal."date"
SCOTT> /

date activity
---------- ----------
2008/03/02 2
2008/03/03 2
2008/03/04 2
2008/03/05 2
2008/03/06 2
2008/03/07 2
2008/03/08 2
2008/03/09 2
2008/03/10 2
2008/03/11 2
2008/03/12 2
2008/03/13 2
2008/03/14 2
2008/03/15 2
2008/03/16 2
2008/03/17 2
2008/03/18 2
2008/03/19 2
2008/03/20 2
2008/03/21 2
2008/03/22 2
2008/03/23 1
2008/03/24 1
2008/03/25 2
2008/03/26 2
2008/03/27 2
2008/03/28 0

27行が選択されました。

SCOTT>

階層問合せほどの柔軟性はないのですがcubeでも同様のことができるんですよ。

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

2008年3月28日 (金)

タイムゾーン de 自動調整

Python Challengeは気が向いた時にやるとして、今日は、タイムゾーンで日時の自動調整をして楽をしようというお話。

Oracleには、timestamp with local time zone型(マニュアルの説明はわかりにくいかも)という便利な日時データ型があります。この日時データ型で日時を保存すると、クライアント側ではクライアントのタイムゾーンに調整された日時を参照することができるようになります。
timestamp with local time zone型はデータベースの作成時に指定されたタイムゾーンで日時データを保持(クライアント側とタイムゾーンが異なる場合はデータベース側のタイムゾーンへ自動調整されます)します。

言葉で言っても分かり憎いでしょうから、早速、例をお見せしましょう。 といっても少々変った例なので(Oracleの)SQL初心者向きではないですが。。

●●●この例の環境及び条件の説明●●●

データベース(この例の場合はOracle10g R1)のタイムゾーン(DBTIMEZONE)は、UTC。
クライアント(この例の場合はSQL*Plus)のタイムゾーン(SESSIONTIMEZONE)は、+9(つまりJST)とUTCをalter sessionにて変更する。
ts_test表には、TIMESTAMP WITH LOCAL TIME ZONE型(今回の場合はUTCで)で2/28から3/27までのあるアクティビティのログ(日時データのみ)が記録されている。(尚、アクティビティの無い日のログは記録されていない。)

というデータ、環境で、実行当日を含む直近27日分のアクティビティ数を日毎に集計して日付順に取得する。アクティビティが無い日はアクティビティ数を0として日付を表示する。
(但し、ts_test以外の表は使わずに実現する。必須じゃないけど、できればSQL1文で!)

では、早速。

 
データベースタイムゾーン(DBTIMEZONE)は、UTCであることを確認。

(ちなみに、データベースに既にTIMESTAMP WITH LOCAL TIME ZONE型を持つ表が存在しなければ、alter database文で変更可能。)
Last login: Fri Mar 28 05:01:29 on console
Welcome to Darwin!
G5Server:˜ discus$ su - oracle

Password:
G5Server:˜ oracle$
G5Server:˜ oracle$ sqlplus /nolog

SQL*Plus: Release 10.1.0.3.0 - Production on 金 3月 28 07:52:49 2008

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

>
> conn scott/tiger@catfish
接続されました。
SCOTT> select dbtimezone from dual;

DBT
---
UTC

次にクライアントのタイムゾーン(SESSIONTIMEZONE)を確認。(JSTになっている)。

SCOTT> select sessiontimezone from dual;

SESSIONTIMEZONE
-----------------------------------------------------------------
+09:00


ts_test表の登録データを確認する。(クライアントのタイムゾーン(JST)へ自動調整されて表示される。)

TIMESTAMP WITH LOCAL TIME ZONE型として登録されている日時データはクライアントのタイムゾーンに自動調整されて表示される。
簡単に確認する方法としてCURRENT_TIMESTAMP疑似列を問い合わせて現状を確認することがもできる。

SCOTT> select current_timestamp from dual;

CURRENT_TIMESTAMP
-----------------------------------------------------------------
08-03-28 08:07:33.082296 +09:00

SCOTT> desc ts_test
名前 NULL? 型
-------------------------- -------- ----------------------------
TS_LTZ TIMESTAMP(6) WITH LOCAL TIME
ZONE


SCOTT> select * from ts_test order by ts_ltz;

TS_LTZ
-----------------------------------------------------------------
08-02-29 00:00:00.000000
08-02-29 23:59:59.999999
08-03-01 00:00:00.000000
08-03-01 23:59:59.999999
08-03-02 00:00:00.000000
08-03-02 23:59:59.999999
08-03-03 00:00:00.000000
08-03-03 23:59:59.999999
08-03-04 00:00:00.000000
08-03-04 23:59:59.999999
08-03-05 00:00:00.000000
08-03-05 23:59:59.999999
08-03-06 00:00:00.000000
08-03-06 23:59:59.999999
08-03-07 00:00:00.000000
08-03-07 23:59:59.999999
08-03-08 00:00:00.000000
08-03-08 23:59:59.999999
08-03-09 00:00:00.000000
08-03-09 23:59:59.999999
08-03-10 00:00:00.000000
08-03-10 23:59:59.999999
08-03-11 00:00:00.000000
08-03-11 23:59:59.999999
08-03-12 00:00:00.000000
08-03-12 23:59:59.999999
08-03-13 00:00:00.000000
08-03-13 23:59:59.999999
08-03-14 00:00:00.000000
08-03-14 23:59:59.999999
08-03-15 00:00:00.000000
08-03-15 23:59:59.999999
08-03-16 00:00:00.000000
08-03-16 23:59:59.999999
08-03-17 00:00:00.000000
08-03-17 23:59:59.999999
08-03-18 00:00:00.000000
08-03-18 23:59:59.999999
08-03-19 00:00:00.000000
08-03-19 23:59:59.999999
08-03-20 00:00:00.000000
08-03-20 23:59:59.999999
08-03-21 00:00:00.000000
08-03-21 23:59:59.999999
08-03-22 00:00:00.000000
08-03-22 23:59:59.999999
08-03-23 00:00:00.000000
08-03-23 23:59:59.999999
08-03-25 00:00:00.000000
08-03-25 23:59:59.999999
08-03-26 00:00:00.000000
08-03-26 23:59:59.999999
08-03-27 08:59:59.999999
08-03-27 09:00:00.000000
08-03-27 10:12:11.928490

55行が選択されました。

SCOTT>

では、上記のデータを直近27日分のアクティビティ数を日毎に集計。アクティビティの無い日はアクティビティ数を0として問い合わせるSQL文。

以下の例では、Oracle独自の階層問合せのlevel疑似列を利用しSYSDATEから27日分のカレンダーを生成しログと外部結合することで、ログデータが存在していない日の
アクティビティ数を0として取得している。

SCOTT> l
1 select
2 cal."date",
3 nvl(logs."activity",0) as "activity"
4 from
5 (
6 select
7 "date",
8 count(ts_ltz) as "activity"
9 from
10 (
11 select
12 to_char(ts_ltz, 'YYYY/MM/DD') as "date",
13 ts_ltz
14 from
15 ts_test
16 )
17 group by
18 "date"
19 ) logs
20 right outer join
21 (
22 select
23 to_char(trunc(sysdate) - (level - 1),
24 'YYYY/MM/DD') as "date"
25 from
26 dual
27 where
28 to_char(trunc(sysdate) - (level - 1),
29 'YYYY/MM/DD') <= trunc(sysdate)
30 connect by
31 level <= 27
32 ) cal
33 on logs."date" = cal."date"
34 order by
35* cal."date"
SCOTT> /

date activity
---------- ----------
2008/03/02 2
2008/03/03 2
2008/03/04 2
2008/03/05 2
2008/03/06 2
2008/03/07 2
2008/03/08 2
2008/03/09 2
2008/03/10 2
2008/03/11 2
2008/03/12 2
2008/03/13 2
2008/03/14 2
2008/03/15 2
2008/03/16 2
2008/03/17 2
2008/03/18 2
2008/03/19 2
2008/03/20 2
2008/03/21 2
2008/03/22 2
2008/03/23 2
2008/03/24 0
2008/03/25 2
2008/03/26 2
2008/03/27 3
2008/03/28 0

27行が選択されました。

SCOTT>


では、クライアントのタイムゾーンをJSTからUTCへ変更してみましょう。(表示しているデータは前述の例と同じts_test表の内容ですがUTCに自動調整されて表示されています。)

SCOTT> alter session set time_zone='UTC';

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

SCOTT> select * from ts_test order by ts_ltz;

TS_LTZ
-------------------------------------------------------
08-02-28 15:00:00.000000
08-02-29 14:59:59.999999
08-02-29 15:00:00.000000
08-03-01 14:59:59.999999
08-03-01 15:00:00.000000
08-03-02 14:59:59.999999
08-03-02 15:00:00.000000
08-03-03 14:59:59.999999
08-03-03 15:00:00.000000
08-03-04 14:59:59.999999
08-03-04 15:00:00.000000
08-03-05 14:59:59.999999
08-03-05 15:00:00.000000
08-03-06 14:59:59.999999
08-03-06 15:00:00.000000
08-03-07 14:59:59.999999
08-03-07 15:00:00.000000
08-03-08 14:59:59.999999
08-03-08 15:00:00.000000
08-03-09 14:59:59.999999
08-03-09 15:00:00.000000
08-03-10 14:59:59.999999
08-03-10 15:00:00.000000
08-03-11 14:59:59.999999
08-03-11 15:00:00.000000
08-03-12 14:59:59.999999
08-03-12 15:00:00.000000
08-03-13 14:59:59.999999
08-03-13 15:00:00.000000
08-03-14 14:59:59.999999
08-03-14 15:00:00.000000
08-03-15 14:59:59.999999
08-03-15 15:00:00.000000
08-03-16 14:59:59.999999
08-03-16 15:00:00.000000
08-03-17 14:59:59.999999
08-03-17 15:00:00.000000
08-03-18 14:59:59.999999
08-03-18 15:00:00.000000
08-03-19 14:59:59.999999
08-03-19 15:00:00.000000
08-03-20 14:59:59.999999
08-03-20 15:00:00.000000
08-03-21 14:59:59.999999
08-03-21 15:00:00.000000
08-03-22 14:59:59.999999
08-03-22 15:00:00.000000
08-03-23 14:59:59.999999
08-03-24 15:00:00.000000
08-03-25 14:59:59.999999
08-03-25 15:00:00.000000
08-03-26 14:59:59.999999
08-03-26 23:59:59.999999
08-03-27 00:00:00.000000
08-03-27 01:12:11.928490

55行が選択されました。

SCOTT>
SCOTT> l
1 select
2 cal."date",
3 nvl(logs."activity",0) as "activity"
4 from
5 (
6 select
7 "date",
8 count(ts_ltz) as "activity"
9 from
10 (
11 select
12 to_char(ts_ltz, 'YYYY/MM/DD') as "date",
13 ts_ltz
14 from
15 ts_test
16 )
17 group by
18 "date"
19 ) logs
20 right outer join
21 (
22 select
23 to_char(trunc(sysdate) - (level - 1),
24 'YYYY/MM/DD') as "date"
25 from
26 dual
27 where
28 to_char(trunc(sysdate) - (level - 1),
29 'YYYY/MM/DD') <= trunc(sysdate)
30 connect by
31 level <= 27
32 ) cal
33 on logs."date" = cal."date"
34 order by
35* cal."date"
SCOTT> /

date activity
---------- ----------
2008/03/02 2
2008/03/03 2
2008/03/04 2
2008/03/05 2
2008/03/06 2
2008/03/07 2
2008/03/08 2
2008/03/09 2
2008/03/10 2
2008/03/11 2
2008/03/12 2
2008/03/13 2
2008/03/14 2
2008/03/15 2
2008/03/16 2
2008/03/17 2
2008/03/18 2
2008/03/19 2
2008/03/20 2
2008/03/21 2
2008/03/22 2
2008/03/23 1
2008/03/24 1
2008/03/25 2
2008/03/26 2
2008/03/27 2
2008/03/28 0

27行が選択されました。

SCOTT>

はい、データベース側でクライアントのタイムゾーンに日時を自動調整してくれるお陰でタイムゾーンが変っても同一SQL文で処理できます。(楽でしょ。)

次回は、同じことを集計関数cubeを使って実現するSQL文の例をお見せします。(Oracle de Fizzbuzz #2を見れば想像できてしまうとは思いますが。。)


ところで、MySQLやPostgreSQLなど他のデータベースではどうするんだろう。。余裕があったら調べてみるか。。。。。

2008/3/29追記
当日から必要な日数分の過去日付を求める階層問合せ部分はもっと簡単にできることに今気付いた。帰宅したら改善したクエリも追加する予定!

ということで以下のように、階層問合せのwhere句の部分は無くてもOKということだったのです。はい。

SCOTT> l
1 select
2 cal."date",
3 nvl(logs."activity",0) as "activity"
4 from
5 (
6 select
7 "date",
8 count(ts_ltz) as "activity"
9 from
10 (
11 select
12 to_char(ts_ltz, 'YYYY/MM/DD') as "date",
13 ts_ltz
14 from
15 ts_test
16 )
17 group by
18 "date"
19 ) logs
20 right outer join
21 (
22 select
23 to_char(
24 trunc(sysdate) - (level - 1),
25 'YYYY/MM/DD'
26 ) as "date"
27 from
28 dual
29 connect by
30 level <= 27
31 ) cal
32 on logs."date" = cal."date"
33 order by
34* cal."date"
SCOTT> /

date activity
---------- ----------
2008/03/03 2
2008/03/04 2
2008/03/05 2
2008/03/06 2
2008/03/07 2
2008/03/08 2
2008/03/09 2
2008/03/10 2
2008/03/11 2
2008/03/12 2
2008/03/13 2
2008/03/14 2
2008/03/15 2
2008/03/16 2
2008/03/17 2
2008/03/18 2
2008/03/19 2
2008/03/20 2
2008/03/21 2
2008/03/22 2
2008/03/23 2
2008/03/24 0
2008/03/25 2
2008/03/26 2
2008/03/27 3
2008/03/28 0
2008/03/29 0

27行が選択されました。

SCOTT>

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

2008年3月26日 (水)

PL/SQL で Python Challenge Level 17

さて、ココログのデータベースのデフラグ?作業でログインできなかったので今書いてますが。
Level 17、少々ハマったが解き方は見えた(ような気がする)ので次回はその経過でも。

Level 17は、Web Applicationを作った経験のあるかたなら案外すんなり解けるかもしれない。
問題の画像にそのすべてのヒントが!


Enjoy! Python Challenge!
(Python じゃなくても楽しめるのだ!)

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

2008年3月25日 (火)

オブジェクト指向スクリプト言語Ruby(書籍)

以前、奥さんが所蔵している書籍「オブジェクト指向スクリプト言語Ruby」は、著者御本人からサインを頂いてるサイン本だった! と奥さんから「強〜〜く主張してくれ」と言われていた。(はい、載せるのを忘れていましたとも、認めます)

ということで、その当時のサイン。(この書籍も希少だと思うけどサイン付きはさらに希少でしょ。多分)

Cimg0165

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

2008年3月24日 (月)

PL/SQL で Python Challenge Level 16完

前回の続きです

さて、Level 16のページには、"let me get this straight" というタイトルが付けられている。これ自体が大きなヒント。なのでこれ以上は何も言うまい。

問題の解くjavaアプリケーションの内容は、以前、Level 11の処理の流れを書いていたが、その3、4以外の部分は同じ。
但し、Level7で利用した Level7.getBfile()とLevel9で作成したLevel9.writeImageFile()というファンクションとプロシージャを以下のPL/SQLパッケージに纏めた。

CREATE OR REPLACE PACKAGE PYTHONCHALLENGE_UTL AS

FUNCTION getBfile
(
i_dir_name IN VARCHAR2,
i_file_name IN vARCHAR2
) RETURN BFILE;

PROCEDURE writeImageFile(
directoryName IN VARCHAR2,
fileName IN VARCHAR2,
srcImage IN OUT NOCOPY BLOB
);

END PYTHONCHALLENGE_UTL;

CREATE OR REPLACE PACKAGE BODY PYTHONCHALLENGE_UTL AS

FUNCTION getBfile
(
i_dir_name IN VARCHAR2,
i_file_name IN VARCHAR2
) RETURN bfile AS
v_file BFILE;
BEGIN
v_file := BFILENAME(UPPER(i_dir_name),i_file_name);
RETURN v_file;
END getBfile;

PROCEDURE writeImageFile(
directoryName IN VARCHAR2,
fileName IN VARCHAR2,
srcImage IN OUT NOCOPY BLOB
) IS
file utl_file.file_type;
BEGIN
file := UTL_FILE.FOPEN(UPPER(directoryName), fileName, 'w', 32767);
DBMS_LOB.OPEN(srcImage, DBMS_LOB.LOB_READONLY);

DECLARE
v_raw RAW(32767);
amount INTEGER;
offset INTEGER;
BEGIN
offset := 1;
LOOP
amount := 32767;
DBMS_LOB.READ(srcImage, amount, offset, v_raw);
UTL_FILE.PUT_RAW(file, v_raw, true);
offset := offset + amount;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN OTHERS THEN
UTL_FILE.FCLOSE(file);
DBMS_LOB.CLOSE(srcImage);
RAISE_APPLICATION_ERROR(-20001, sqlerrm());
END;

UTL_FILE.FCLOSE(file);
DBMS_LOB.CLOSE(srcImage);
END writeImageFile;

END PYTHONCHALLENGE_UTL;
/

上記のパッケージをJava Stored Procedureから利用するわけです。javaのソースコードは載せませんよ。ネタバレになっちゃいますから。

次に、デプロイしたjava classは以下のようにしてJava Stored Procedureとして定義します。

CREATE OR REPLACE PACKAGE LEVEL16 AS
FUNCTION getResult RETURN NUMBER AS
LANGUAGE JAVA NAME 'jp.macdeoracle.Level16.getResult() return java.lang.boolean';
END LEVEL16;

では、SQL*Plusから実行してみましょう!

Last login: Sun Mar 23 20:29:41 on ttyp2
Welcome to Darwin!
G5Server:˜ discus$ ssh oracle@corydoras
oracle@corydoras's password:
Last login: Sun Mar 23 20:31:49 2008 from 192.168.1.19
[oracle@corydoras ˜]$ cd python_challenge
[oracle@corydoras python_challenge]$ ll
合計 264
-rw-r--r-- 1 oracle oinstall 254006 3月 23 16:26 level16.gif

[oracle@corydoras python_challenge]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on 日 3月 23 20:58:34 2008

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

> conn scott/tiger
接続されました。
SCOTT> l
1 select
2 case
3 when level16.getResult() = 1 then '成功!'
4 else '失敗!'
5 end as "Level16"
6* from dual
SCOTT> /

Level16
---------
成功!

SCOTT> !ls -l
合計 504
-rw-r--r-- 1 oracle oinstall 254006 3月 23 16:26 level16.gif
-rw-r--r-- 1 oracle oinstall 239515 3月 23 21:02 level16_result.png

SCOTT>

これだけでもかなりのヒントになっているような気もしますが。。。
MacOSXのChicken of the VNC(MacOSX向けのVNC Client)経由でLinux(CentOS5)にある結果を確認!


Level16_res1

Level16_res2


ということで、Python Challenge Level 16完。 Level 17は気が向いたら載せると思います。 
Enjoy Python Challenge!

| | | コメント (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年3月20日 (木)

Installation of Oracle 11g R1 (11.1.0.6.0) on SuSE Linux Enteprise Server (SLES) 10 and OpenSuSE 10.3 (x86_64)

少々前に、Oracle10g R2 on OpenSuSE10.2に関するインストレーション手順のドキュメントがNovelのサイトから入手できることを書いたが、Oracle ACE Memberのivan kartik氏のサイトには、Oracle11g R1をSuSE Linux Enterprise Server(SLES) 10 及び OpenSuSE 10.3(但し今のところ、64ビットのみ)へインストールする際のHOW TOが紹介されている。近いうちに32ビット版のドキュメントも公開する予定とのこと。
前回の記事も含めて備忘録ということで。


Installation of Oracle 11g R1 (11.1.0.6.0) on SuSE Linux Enteprise Server (SLES) 10 and OpenSuSE 10.3

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

2008年3月15日 (土)

Replace the HD of the 3rd generation iPod by oneself!

先日drmarさん宅へお邪魔した際、iPod落としたらHD認識しなくなった!と言っていてどうも自分で交換しそうな勢いだったんですが、やっぱり自分で交換しちゃったのね〜〜〜。
第3世代のiPodのHD交換自分でできるんだねぇ。
(尚、drmarさんのブログにも書いてある通りご自身の責任で行ってくださいね。)

しかし私のiPodはまだ、第一世代がバリバリ元気だ!(drmarさんのブログによれば、第一世代や第二世代のiPodではそう簡単にはいかないようだし)うちの奥さんのは第3世代かもしれないから壊れた時はやってみようかと。

iPhone待ちの私としては、とりあえず備忘録ということで。 :)

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

2008年3月14日 (金)

WIF2008 - Webdesign International Festival - Finalまであと1ヶ月余り

3月も半ば、つい先日も4月中旬の陽気だったりと花見(源氏山ー鎌倉)は何時頃か?と調べていたら、フランスはリモージュで行われるWIF2008 - Webdesign International Festival のWebJam決勝まであと一ヶ月余りであることを思い出した!

決勝のお題は? ということも気になるのですが、24時間缶詰でキツい戦いを制するのはどの国のどのチームなんでしょうね。

日本から参加する2チームの御健闘をお祈りいたします!

2

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

2008年3月13日 (木)

Oracle10g R2 on OpenSuSE 10.2

少々前の事だが、OTN-JでOpenSuSEへOracleをインストールしたいという漠然とした質問があった。
OpenSUSEのバージョンやOracleのバージョンも記載さていない質問なので答える方も大変だと思うが、「サポートされた組み合わせではないから云々」と言うような後ろ向きなレスより、「こんなこと(方法で)すればできるようだ?!」というちょっとだけ前向きな回答が OTN-Jに多くなればいいなぁと日頃から感じていた私はOpenSuSE10.2へOracle10g R2をインストール参考になるドキュメントがあることをレスしておいた。

私も備忘録としてそれらのドキュメントのURLメモっておくとするか。
(SUSEを使うこともあるあもしれないし。)

Oracle Database10g R2 (10.2.0.1) on openSUSE 10.2 (PDF)


その他リリースに関連するドキュメントなどもある。

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

2008年3月10日 (月)

Mac de Ruby on Rails - #9 - ClubDB2番外編

Mac で Oracleな私ですが、第37回 ClubDB2番外編!Ruby on Rails 入門へ行ってきました。

いや〜〜、株式会社万葉の大場さんのセミナー面白かったですね。Mac使ってたし(笑)。
予定があったのでセミナー後の食事会?には参加できませんでしたが、RoRなどDB2から脱線?したネタがあればまた参加したいと思います。XMLDBネタが近々あるようだが、それはそれで聞いてみたい気もする。。


ちなみに、DB2の知識は、DB2 UDB 5.2で止まってる。。はず、と言いつつDBマガジンの記事は読んでますよ。


今回仕入れたネタも使って遊んだ結果などはいずれ。。。

2008/3/15追記
そう言えば、このセミナーで頂いたDB2 9:pureXML overview and fast startの紹介を忘れていたので追記しておきます。 (^^;;;
IBMさんのサイトではPDF版やHTML版が公開されている。

Db2_9_purexml_overview

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

Mac de Ruby on Rails - #8 - Oracleと遊ぶ #7

以前の記事で以下のように書いていたことを覚えていますか?

注)
赤太字で示したシンボリックリンクは今回事前に作成したもので、それらのシンボリックリンクは作成しなくても通常利用には問題ない。以前の記事参照のこと。
尚、今回は事前に参照していた資料にシンボリックリンクの作成に関する記述があったため前述のシンボリックリンクを作成した。
(ほんとうに必要なのか? という点については未検証であることを書き加えておく。余計なものだとしても悪さはしないでしょう。きっと。)


バックナンバー:
Mac de Ruby on Rails - #1
Mac de Ruby on Rails - #2 - Oracleと遊ぶ #1
Mac de Ruby on Rails - #3 - Oracleと遊ぶ #2
Mac de Ruby on Rails - #4 - Oracleと遊ぶ #3
Mac de Ruby on Rails - #5 - Oracleと遊ぶ #4
Mac de Ruby on Rails - #6 - Oracleと遊ぶ #5
Mac de Ruby on Rails - #7 - Oracleと遊ぶ #6

先日、興味本位で、もし、前述したシンボリックリンクを作成しなかったらどうなるかを試してみた。

Macintosh:ruby-oci8-1.0.0 discus$ make
ruby setup.rb config
---> lib
---> lib/DBD
---> lib/DBD/OCI8
<--- lib/DBD/OCI8
<--- lib/DBD
<--- lib
---> ext
---> ext/oci8
/System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/bin/ruby /Users/discus/ruby-oci8-1.0.0/ext/oci8/extconf.rb
checking for load library path... (DYLD_LIBRARY_PATH)...
found: /Users/Shared/OracleInstantClient/libclntsh.dylib.10.1 looks like an instant client.
checking for cc... ok
checking for gcc... yes
checking for LP64... no
checking for ruby header... ok
---------------------------------------------------
error messages:
Could not compile with Oracle instant client.
/Users/Shared/OracleInstantClient/libclntsh.dylib could not be found.
You may need to make a symbolic link.
cd /Users/Shared/OracleInstantClient
ln -s libclntsh.dylib.10.1 libclntsh.dylib

---------------------------------------------------
See:
* http://ruby-oci8.rubyforge.org/ja/HowToInstall.html
* http://ruby-oci8.rubyforge.org/ja/ReportInstallProblem.html

*** /Users/discus/ruby-oci8-1.0.0/ext/oci8/extconf.rb failed ***

ご覧の通りエラーになりますし、シンボリックリンクが無いのが原因かもしれないと示唆される。分かり易くていいな〜。
当然、指摘された通りにシンボリックリンクを作成してあげれば問題は解消される。

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

2008年3月 7日 (金)

Oracle de XMLDB #26 - XMLTYPEの更新

OTN-Jで、PL/SQLでXMLTYPEのあるの要素の値を変更したり、ある要素の値を参照したいというスレッドがあったのでレスしておいた。

そういえば、XQueryやXPathを使ったOracle XMLDBネタもやっているが更新系のはやってないな(というかまだ、XMLの更新、追加、削除に関する標準が定まっていないわけだから仕方ない。)

ということで、前述したOTN-Jのネタをもう少しフラッシュアップして?!(for updateなどが抜けていたので。。)残しておく事にした。

まず、hogehoge表に以下のようなXMLを登録する。

SCOTT> select * from v$version;

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

SCOTT>
SCOTT> desc hogehoge
名前 NULL? 型
----------------------------------------- -------- ----------------------------
SEQNO NOT NULL NUMBER
XMLINFO XMLTYPE

SCOTT>
SCOTT> insert into hogehoge values(1,xmltype('<?xml version="1.0"?><addressbook>
<person id="1"><name>岡田</name><age>30</age>
<address zipcode="123456">東京都ホゲ市ホゲ町1−1</address></person><person id="2">
<name>安倍</name><age>50</age>
<address zipcode="1050011">東京都ホゲ区ホゲ田町1−1−1</address>
</person></addressbook>'));

1行が作成されました。

SCOTT> commit;

コミットが完了しました。

SCOTT>
SCOTT>
SCOTT> set timi on
SCOTT> set long 4000
SCOTT> col xmlinfo for a80
SCOTT> set linesize 132
SCOTT> set pagesize 1000
SCOTT> select * from hogehoge where seqno=1;

SEQNO XMLINFO
---------- ------------------------------------------------------------
1 <?xml version="1.0"?><addressbook><person id="1"><name>岡田<
/name><age>30</age><address zipcode="123456">東京都ホゲ市ホ
ゲ町1−1</address></person><person id="2"><name>安倍</name>
<age>50</age><address zipcode="1050011">東京都ホゲ区ホゲ田
町1−1−1</address></person></addressbook>


経過: 00:00:00.01
SCOTT>

上記のようなXML文書を対象に特定のid属性値を持つperson要素に含まれるname要素の値を変更するストアドファンクション。
(もうすこし平たく言うと、指定した名前を変更するストアドファンクション。略し過ぎか・・・)

XML文書の変更方法は他にもあるが、PL/SQLパッケージで行うにはDOM APIであるDBMS_XMLDOMパッケージを利用して行う。
尚、XMLTYPEをDBMS_XMLDOMパッケージで更新する際のポイントは、XMLTYPEインスタンスからDOMインスタンスを生成し、DOMインスタンに対して必要な更新操作行った上で、XMLTYPEインスタンスを表へ書き戻すという所。(該当部分は赤太字にしてあります。)

SCOTT> l
1 create or replace
2 function changePersonName
3 (
4 iKey in number,
5 iPersonId in varchar2,
6 iNewName in varchar2
7 ) return xmltype
8 is
9 xml xmlType;
10 personNodeList dbms_xmldom.DomNodeList;
11 personNode dbms_xmldom.DomNode;
12 nameNodeList dbms_xmldom.DomNodeList;
13 personAttrNodeMap dbms_xmldom.DomNamedNodeMap;
14 idAttrNode dbms_xmldom.DomNode;
15 wKey number;
16 wPersonId varchar2(38);
17 wNewName varchar2(60);
18 begin
19 wKey := iKey;
20 wPersonId := iPersonId;
21 wNewName := iNewName;
22
23 select xmlinfo into xml from hogehoge where seqno = wKey for update;
24
25 personNodeList := dbms_xmldom.getElementsByTagName(
26 dbms_xmldom.newDomDocument(xml)
27 ,'person'
28 );
29
30 if not dbms_xmldom.isNull(personNodeList) then
31 for i in 0..dbms_xmldom.getLength(personNodeList) loop
32 personNode := dbms_xmldom.item(personNodeList, i);
33 personAttrNodeMap := dbms_xmldom.getAttributes(personNode);
34 idAttrNode := dbms_xmldom.getNamedItem(personAttrNodeMap, 'id');
35 if dbms_xmldom.getNodeValue(idAttrNode) = wPersonId then
36 nameNodeList := dbms_xmldom.getChildrenByTagName(
37 dbms_xmldom.makeElement(personNode)
38 ,'name'
39 );
40 dbms_xmldom.setNodeValue(
41 dbms_xmldom.getFirstChild(dbms_xmldom.item(nameNodeList, 0))
42 ,wNewName
43 );
44 update hogehoge set xmlinfo = xml where seqno = wKey;
45 exit;
46 end if;
47 end loop;
48 end if;
49 return xml;
50* end;
SCOTT> /

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

経過: 00:00:00.04
SCOTT>
SCOTT>
SCOTT>
SCOTT> l
1 declare
2 xml xmlType;
3 begin
4 xml := changePersonName(1, '1', '福田ほげ蔵');
6 commit;
7* end;
SCOTT> /

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

経過: 00:00:00.03
SCOTT>
SCOTT>
SCOTT> select * from hogehoge where seqno=1;
SEQNO XMLINFO
---------- --------------------------------------------------------------------------------
1 <?xml version="1.0"?>
<addressbook>
<person id="1">
<name>福田ほげ蔵</name>
<age>30</age>
<address zipcode="123456">東京都ホゲ市ホゲ町1−1</address>
</person>
<person id="2">
<name>安倍</name>
<age>50</age>
<address zipcode="1050011">東京都ホゲ区ホゲ田町1−1−1</address>
</person>
</addressbook>


経過: 00:00:00.00
SCOTT>

もうお気づきだと思うが、Oracle11gでも発生しているこちらからは操作できないところでwhitespaceが追加されたり、されなかったりする。まあこれ自体は大抵の場合問題にはならないのだが。。

あともうひとつの話題は特定の要素の値を取り出したいということ。これだけならDBMS_XMLDOMパッケージを使わなくても、SQL/XMLを使えば簡単ですよね。
XMLQuery()関数を使って、id属性値が "1"であるperson要素以下にあるage要素の値を参照する例です。

SCOTT> set linesize 80
SCOTT> l
1 select
2 xmlquery(
3 'let $n := $xmldoc//person[@id="1"]/age/text()
4 return $n'
5 passing by value xmlinfo as "xmldoc"
6 returning content
7 ) as "age"
8* from hogehoge where seqno=1
SCOTT> /

age
--------------------------------------------------------------------------------
30

経過: 00:00:00.04
SCOTT>

ちなみに、Oracleのマニュアルは以下の辺りを参照するとよいと思いますが、DOM、XQueryやXPathに関してはXML関連のサイトもググって調べたほうがよいと思います。XMLの操作の基本を理解していれば、あとはどんなツールを使ってそれを行うかということだけですから。

マニュアル
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 10g R2 - DBMS_XMLDOM
Oracle Database SQLリファレンス 10g R2 - XMLQUERY

Enjoy!

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

2008年3月 5日 (水)

UMTP L3 ついに!

ついに、UMTP L3 (UMLモデリング技能認定試験 L3) が4/21(予定)からプロメトリックにて開始されるといことで、L3試験オープニングセミナーに参加してきた。

問題数は、知識問題5問+モデリング問題3問で120分(かなりタフな試験ですね)
120分の内、モデリング問題3問で90分程度費やされるような内容になっているとのこと。
L2では、モデルが正しいか? 間違っているか?を問われたが、L3では、モデルが良いか? より良いか? より正確な記述を問う問題となっているとのこと。

L3の試験もコンピュータ試験で、L2までのかなり使いづらいユーザインターフェースに比べると随分改善されているように感じた。操作は空白になっているクラス図にドラッグ&ドロップでクラス名や属性名を配置していくようになっているとのこと。

L3問題の方針として、

・ビジネスモデルや要求分野の専門的な知識と思われる内容は問わない。
・ブジネスモデルや要求モデルの品質は問わない。
・プラットフォームや言語依存の問題は避ける。
・組み込み開発分野では、分析レベルの問題しか問わない。
 (今後方針を変える可能性もある。とのこと)

尚、L3試験開始キャンペーンとして、6月30日までに申し込んだ方は、11,550円で受験できるとのこと。

最後に、現時点ではL3試験の対策本は無いが、テクノロジックアートの照井氏が独自の観点でセレクトした書籍が紹介されたので以下に備忘録として列挙しておきます。(対策本が出版されたらそちらの方を買う方も多そうだが、以下の書籍の幾つかは試験対策に関係なく持っていても損はない書籍がいくつかありますよね。

ちなみに、私と奥さん所有の書籍は背景を淡いピンクにしておいた。

テクノロジックアートの照井氏が「独断・偏見御免」ということで紹介されていた書籍の数々。
UML 2.x 仕様の一冊

UML 2.x 仕様のもう一冊

モデリング実践の一冊

分析モデリングの一冊

分析モデリングのもう一冊

アーキテクトの一冊

GRASPの一冊

設計原理原則の一冊
 

デザインパターンの一冊
 

組み込みモデリングの一冊

組み込みモデリングだけじゃない一冊

羅針盤としての一冊


羅針盤としてのもう一冊

今後の一冊

MDA以外書籍はまあまあ持っている書籍もあるなあ。。。

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

2008年3月 3日 (月)

Mercedes-Benz Mixed Tape TV

Mercedes-Benz Mixted Tapeのサイトがバージョンアップして、かな〜〜〜りイケてるサイトに生まれ変わった!
iTunesのカバーフローを意識したインタ−フェースや、メルセデスに関する情報も上手くリンクされている。



ダウンロードした曲は、iTunesやiPodへ入れちゃいます。ジャンルは幅広いのですが、いい楽曲が非常に多いので私のお気に入りです。

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

2008年3月 2日 (日)

whos.amung.usのmapsに2つの表示モードが追加された

Amung.usのmapsがVersionアップされ、Normal map、Heat mapに加え新たに2つの表示モードが加わった。

1つは、Cluster map
4


もう一つは、Day/Night map(Normal mapに現時点における昼と夜のエリアを示す)
5

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