« Mac De Oracle Heterogeneous! #19 | トップページ | Mac De Oracle Heterogeneous! #21 »

2006年1月23日 (月) / Author : Hiroshi Sekiguchi.

Mac De Oracle Heterogeneous! #20

前回は、PostgreSQL上で日付や時間型がどのように扱われるか確認してみた。今日はGenneric Connectivity経由でPostgreSQLに日付や時間を登録してみる。

いつものように、date型とtimestamp型の書式を設定しておく

CORYDORAS> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

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

CORYDORAS> alter session set nls_timestamp_format = 'yyyy/mm/dd hh24:mi:ss.ff';

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

Oracle10g R1 (MacOSX Server : PowerMac G5) --> oracle10g_win(データベースリンク) --> Oracle10g R1 (Windows XP Professional)のdate_test_postgresql749_mac(シノニム)--> generic connectivity agent --> MyODBCドライバ --> PostgreSQL7.4.9 の date_test表(MacOSX : PowerBook G4)という経路で問い合わせている。

PostgreSQL7.4.9の date型、time型、timestamp型すべてが、Oracleのdate型にマップされているため以下のような点に注意。

PostgreSQLのtime型は時間だけだが、Oracle側ではdate型になるため、0001/01/01+時間となる。
PostgreSQLのdate型は日付だけだが、Oracle型ではdate型なので 時間が00:00:00になる。
PostgreSQLのtimestamp型は、Oracleのdate型にマップされてしまうため、秒の少数部が欠落する。

ということになる。
以下は、generic connectivity経由でOracleから問い合わせた結果である。dump()関数を利用してデータタイプも確認してある。

CORYDORAS> select * from date_test_postgresql749_mac@oracle10g_win;

r_date r_timestamp r_time
------------------- ------------------- -------------------
1900/01/01 00:00:00
2006/01/20 00:00:00
1981/01/01 00:00:00
1990/12/01 00:00:00
0001/01/01 00:00:00
2007/03/07 00:00:00
0001/01/01 00:00:00
2007/09/11 00:00:00
2006/01/01 00:00:00
0001/01/01 23:59:59
2006/01/02 00:00:00
2006/01/02 03:01:00
2006/01/22 00:00:00 2006/01/22 21:57:25 0001/01/01 21:57:25
2008/11/07 04:50:03

14行が選択されました。

CORYDORAS>
CORYDORAS> list
1 select
2 dump("r_date") as r_date,
3 dump("r_timestamp") as r_timestamp,
4 dump("r_time") as r_time
5 from
6* date_test_postgresql749_mac@oracle10g_win
CORYDORAS> /

R_DATE R_TIMESTAMP R_TIME
---------------------------------------- ---------------------------------------- ----------------------------------------
Typ=12 Len=7: 119,100,1,1,1,1,1 NULL NULL
Typ=12 Len=7: 120,106,1,20,1,1,1 NULL NULL
NULL Typ=12 Len=7: 119,181,1,1,1,1,1 NULL
NULL Typ=12 Len=7: 119,190,12,1,1,1,1 NULL
Typ=12 Len=7: 100,101,1,1,1,1,1 NULL NULL
Typ=12 Len=7: 120,107,3,7,1,1,1 NULL NULL
NULL Typ=12 Len=7: 100,101,1,1,1,1,1 NULL
NULL Typ=12 Len=7: 120,107,9,11,1,1,1 NULL
NULL Typ=12 Len=7: 120,106,1,1,1,1,1 NULL
NULL NULL Typ=12 Len=7: 100,101,1,1,24,60,60
NULL Typ=12 Len=7: 120,106,1,2,1,1,1 NULL
NULL Typ=12 Len=7: 120,106,1,2,4,2,1 NULL
Typ=12 Len=7: 120,106,1,22,1,1,1 Typ=12 Len=7: 120,106,1,22,22,58,26 Typ=12 Len=7: 100,101,1,1,22,58,26
NULL Typ=12 Len=7: 120,108,11,7,5,51,4 NULL

14行が選択されました。

CORYDORAS>

次に、日付、時間型データを登録してみた。
パススルーされているのだろうか、to_date()関数は利用できない。(HS_CALL_NAME初期化パラメータでPostgreSQLのto_date関数が利用できるかは未確認。)

CORYDORAS> insert into date_test_postgresql749_mac@oracle10g_win
2 values(to_date('00000000','yyyymmdd'),null,null);
insert into date_test_postgresql749_mac@oracle10g_win
*
行1でエラーが発生しました。:
ORA-02070: データベースPOSTGRESQL749_MACはこのコンテキストではTO_DATEをサポートしません。
ORA-02063: 先行のエラー・メッセージを参照してくださいline(ORACLE10G_WIN)。

PostgreSQL上でもエラーになったINSERT文を実行してみると、これはPostgreSQLまで届いているようだ。PostgreSQL上で実行した時と同じエラーが返ってくる。

CORYDORAS> insert into date_test_postgresql749_mac@oracle10g_win
2 values('00000000',null,null);
insert into date_test_postgresql749_mac@oracle10g_win
*
行1でエラーが発生しました。:
ORA-28500: OracleからOracle以外のシステムへの接続で次のメッセージが戻されました:
[Generic Connectivity Using ODBC]ERROR: date/time field value out of range: "00000000" (SQL State: S1000; SQL Code: 7)
ORA-02063: 先行のエラー・メッセージを参照してください2 lines(POSTGRESQL749_MAC)。
ORA-02063: 先行のエラー・メッセージを参照してください3 lines(ORACLE10G_WIN)。

Oracleのsysdate,systimestampを利用してみる。これは予想通りエラーとなる。

CORYDORAS> 
CORYDORAS> insert into date_test_postgresql749_mac@oracle10g_win
2 values(sysdate,systimestamp,sysdate);
insert into date_test_postgresql749_mac@oracle10g_win
*
行1でエラーが発生しました。:
ORA-02070: データベースPOSTGRESQL749_MACはこのコンテキストではspecial functionsをサポートしません。
ORA-02063: 先行のエラー・メッセージを参照してくださいline(ORACLE10G_WIN)。

to_char()関数を利用してみたが、これはto_date()関数と同じで利用できない。

CORYDORAS> insert into date_test_postgresql749_mac@oracle10g_win
2 values(to_char(sysdate,'yyyymmdd'), to_char(systimestamp,'yyyymmddhh24miss.ff'), to_char(sysdate,'hh24miss'));
insert into date_test_postgresql749_mac@oracle10g_win
*
行1でエラーが発生しました。:
ORA-02070: データベースPOSTGRESQL749_MACはこのコンテキストではTO_CHARをサポートしません。
ORA-02063: 先行のエラー・メッセージを参照してくださいline(ORACLE10G_WIN)。

では、暗黙型変換に任せて、単純に日付や時間を文字列指定した場合はどうか? 
やっと、登録できた。(よかった、よかった。と安心ばかりはしていられない。。。)


CORYDORAS> insert into date_test_postgresql749_mac@oracle10g_win
2  values('20061231','20060101 125959','235959');

1行が作成されました。

CORYDORAS> select * from date_test_postgresql749_mac@oracle10g_win;

r_date r_timestamp r_time
---------------------------------------- ---------------------------------------- ----------------------------------------
1900/01/01 00:00:00
2006/01/20 00:00:00
1981/01/01 00:00:00
1990/12/01 00:00:00
0001/01/01 00:00:00
2007/03/07 00:00:00
0001/01/01 00:00:00
2007/09/11 00:00:00
2006/01/01 00:00:00
0001/01/01 23:59:59
2006/01/02 00:00:00
2006/01/02 03:01:00
2006/01/22 00:00:00 2006/01/22 21:57:25 0001/01/01 21:57:25
2008/11/07 04:50:03
2006/12/31 00:00:00 2006/01/01 12:59:59 0001/01/01 23:59:59

15行が選択されました。

各列はnullableなのでNULLを設定することも問題ない。

CORYDORAS> 
CORYDORAS> insert into date_test_postgresql749_mac@oracle10g_win
2 values(null,null,null);

1行が作成されました。

Oracleでは、今のところ、空文字=NULLなのでNULLが登録されると予想していたがDML文がパススルーされているようで、PostgreSQL上で実行した場合と同様の結果(エラー)になる。(尚、本来は、空文字とNULLは別もの。オラクルのマニュアルにも記載があり空文字=NULLという扱いにはなっているが将来は変わる可能性があるというようなニュアンスのコメントが記載されている)

CORYDORAS> insert into date_test_postgresql749_mac@oracle10g_win
2 values('',null,null);
insert into date_test_postgresql749_mac@oracle10g_win
*
行1でエラーが発生しました。:
ORA-28500: OracleからOracle以外のシステムへの接続で次のメッセージが戻されました:
[Generic Connectivity Using ODBC]ERROR: invalid input syntax for type date: "" (SQL State: S1000; SQL Code: 7)
ORA-02063: 先行のエラー・メッセージを参照してください2 lines(POSTGRESQL749_MAC)。
ORA-02063: 先行のエラー・メッセージを参照してください3 lines(ORACLE10G_WIN)。

しかし、リテラルでしか登録できないとなると、現在時間や日付を設定したい場合には非常に都合が悪い。スカラー副問合せを利用してみたがこれもエラーとなった。これは困った。

CORYDORAS> 
CORYDORAS> list
1 insert into date_test_postgresql749_mac@oracle10g_win
2 values(
3 (select to_char(sysdate,'yyyymmdd') from dual),
4 (select to_char(systimestamp,'yyyymmdd hh24miss.ff') from dual),
5 (select to_char(sysdate,'hh24miss') from dual)
6* )
CORYDORAS> /
insert into date_test_postgresql749_mac@oracle10g_win
*
行1でエラーが発生しました。:
ORA-02070: データベースPOSTGRESQL749_MACはこのコンテキストではsubqueriesをサポートしません。
ORA-02063: 先行のエラー・メッセージを参照してくださいline(ORACLE10G_WIN)。

PL/SQLからなら、なんとかなるかとも知れないと以下のようにしてみたが、今度はシノニム変換で問題が発生した。

CORYDORAS> 
CORYDORAS> declare
2 v_datetime_string varchar2(25);
3 v_date_string varchar2(8);
4 v_time_string varchar2(6);
5 begin
6 v_date_string := to_char(sysdate,'yyyymmdd');
7 v_datetime_string := to_char(systimestamp, 'yyyymmdd hh24miss.ff');
8 v_time_string := to_char(sysdate,'hh24miss');
9 --
10 insert into date_test_postgresql749_mac@oracle10g_win
11 values(v_date_string, v_datetime_string, v_time_string);
12 commit;
13 end;
14 /
insert into date_test_postgresql749_mac@oracle10g_win
*
行10でエラーが発生しました。:
ORA-06550: 行10、列3:
PL/SQL: ORA-00980: シノニム変換が無効です。
ORA-06550: 行10、列3:
PL/SQL: SQL Statement ignored

CORYDORAS>

WindowsのOracle10g R1を踏み台にしている関係なのか、無理矢理構築したOracle10g R1 for MacOSX Serverだからなのか確かなことは言えないが、以下のような流れではシノニムの解決ができないようである。

もちろん、Oracle10g for MacOSX Serverで Oracle Heterogeneous ServiceのGeneric connectivityが正式にサポートされればこのような構成

gencon_blog_img1

にする必要はない。。。。趣味というか興味だけでやっていることなので、こんな”おもしろい”(変わっているとも言う)構成してあるのでご注意を。

ちなみにMacOSX Serverの先にあるOracle10g R1 for Windowsからだと上記PL/SQLブロックで実行可能であった。やはり踏み台方式もしくは無理矢理構築したOracle10g for MacOSX Server側の問題のような気がする。。

SQL> declare
2 v_datetime_string varchar2(25);
3 v_date_string varchar2(8);
4 v_time_string varchar2(6);
5 begin
6 v_date_string := to_char(sysdate,'yyyymmdd');
7 v_datetime_string := to_char(systimestamp, 'yyyymmdd hh24miss.ff');
8 v_time_string := to_char(sysdate,'hh24miss');
9 --
10 insert into date_test_postgresql749_mac
11 values(v_date_string, v_datetime_string, v_time_string);
12 commit;
13 end;
14 /

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

そして最後の悪あがき! 
insert文をexecute immediate文で実行してみた。おお〜〜っ!、Oracle10g for MacOSX ServerからPL/SQLブロックが実行できた瞬間だった。(爆!)

CORYDORAS> list
1 declare
2 v_timestamp_string varchar2(25);
3 v_date_string varchar2(8);
4 v_time_string varchar2(6);
5 v_sql varchar2(1000);
6 begin
7 v_date_string := to_char(sysdate,'yyyymmdd');
8 v_timestamp_string := to_char(systimestamp,'yyyymmdd hh24miss.ff');
9 v_time_string := to_char(sysdate,'hh24miss');
10 v_sql :=
11 'insert into date_test_postgresql749_mac@oracle10g_win values('||
12 '''' || v_date_string ||
13 ''',''' || v_timestamp_string ||
14 ''',''' || v_time_string || ''')'
15 ;
16 execute immediate v_sql;
17 commit;
18* end;
CORYDORAS> /

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

CORYDORAS> select * from date_test_postgresql749_mac@oracle10g_win;

r_date r_timestamp r_time
------------------- ------------------- -------------------
1900/01/01 00:00:00
2006/01/20 00:00:00
1981/01/01 00:00:00
1990/12/01 00:00:00
0001/01/01 00:00:00
2007/03/07 00:00:00
0001/01/01 00:00:00
2007/09/11 00:00:00
2006/01/01 00:00:00
0001/01/01 23:59:59
2006/01/02 00:00:00
2006/01/02 03:01:00
2006/01/22 00:00:00 2006/01/22 21:57:25 0001/01/01 21:57:25
2008/11/07 04:50:03
2006/12/12 00:00:00
2006/12/12 12:01:01
0001/01/01 23:59:59
2006/01/23 00:00:00 2006/01/23 23:38:56 0001/01/01 23:38:56
2006/01/23 00:00:00 2006/01/23 23:50:34 0001/01/01 23:50:34

19行が選択されました。

CORYDORAS>


今日はここまで。

======
Generic Connectivity
日付、時間型のまとめ(PostgresSQL7.4.9編)

参照時のポイント
・PostgreSQL7.4.9の date型、time型、timestamp型すべてが、Oracleのdate型にマップされる。
・PostgreSQLのtime型は時間だけだが、Oracle側ではdate型になるため、0001/01/01+時間となる。
・PostgreSQLのdate型は日付だけだが、Oracle型ではdate型なので 時間が00:00:00になる。
・PostgreSQLのtimestamp型は、Oracleのdate型にマップされてしまうため、秒の少数部が欠落する。

検索時のポイント
・to_date()を利用してdate型で検索することも、日付、時間を文字列として検索することも可能(Oracleの動きに類似している)

登録時のポイント
・日付、時間を文字列(リテラル)として指定ことしかできない。
・現在時間や、日付を設定する場合は、PL/SQLからINSERT文や、UPDATE文を実行する。
 (但し、このテストで利用しているような特殊構成ではPL/SQLから実行するDMLは、execute immediate文を利用したダイナミックSQLにしたほうがよいかも。この環境では、たまたま動作しただけかもしれないのでご注意を。)

次回はMySQLで同様の確認を行う予定。

| |

トラックバック


この記事へのトラックバック一覧です: Mac De Oracle Heterogeneous! #20:

コメント

コメントを書く