EXTRACT の ORA-30076

EXTRACT という関数がある。Oracle や PostgreSQL に実装されているが、Oracleでの挙動が少々わかりにくい。

注意: 抽出を実行するフィールドは、datetime_value_expr または interval_value_expr フィールドである必要があります。たとえば、DATE 値からは、YEAR、MONTH およびDAY のみを抽出できます。同様に、TIMESTAMP WITH TIME ZONE データ型からは、TIMEZONE_HOUR および TIMEZONE_MINUTE のみを抽出できます。

という注意書きが付いているが意味が良くわかんないのである。確かに、SELECT EXTRACT(SECOND FROM SYSDATE) FROM DUAL を実行しようとすると

ORA-30076: 抽出ソースの抽出フィールドが無効です

と叱られる。じゃあいったい SECOND ってキーワードは何なの? である。

C:\>sqlplus system/*******

SQL*Plus: Release 9.2.0.1.0 - Production on 水 Jan 5 19:26:30 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.



Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
に接続されました。
SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(YEAR FROM SYSDATE)      AS YEAR
  4  FROM V$INSTANCE
  5  /

DB_VERSION              YEAR
----------------- ----------
9.2.0.1.0               2005

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(MONTH FROM SYSDATE)     AS MONTH
  4  FROM V$INSTANCE
  5  /

DB_VERSION             MONTH
----------------- ----------
9.2.0.1.0                  1

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(DAY FROM SYSDATE)       AS DAY
  4  FROM V$INSTANCE
  5  /

DB_VERSION               DAY
----------------- ----------
9.2.0.1.0                  5

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(HOUR FROM SYSDATE)      AS HOUR
  4  FROM V$INSTANCE
  5  /
        EXTRACT(HOUR FROM SYSDATE)      AS HOUR
                          *
行3でエラーが発生しました。:
ORA-30076: 抽出ソースの抽出フィールドが無効です


SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(MINUTE FROM SYSDATE)    AS MINUTE
  4  FROM V$INSTANCE
  5  /
        EXTRACT(MINUTE FROM SYSDATE)    AS MINUTE
                            *
行3でエラーが発生しました。:
ORA-30076: 抽出ソースの抽出フィールドが無効です


SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(SECOND FROM SYSDATE)    AS SECOND
  4  FROM V$INSTANCE
  5  /
        EXTRACT(SECOND FROM SYSDATE)    AS SECOND
                            *
行3でエラーが発生しました。:
ORA-30076: 抽出ソースの抽出フィールドが無効です


SQL>

長いことこれに悩んでしまった。EXTRACT って使えないんじゃ、みたいな結論に達しかけたが、上の実行結果はこのように修正すると動く。

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(YEAR FROM CAST(SYSDATE AS TIMESTAMP))   AS YEAR
  4  FROM V$INSTANCE
  5  /

DB_VERSION              YEAR
----------------- ----------
9.2.0.1.0               2005

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(MONTH FROM CAST(SYSDATE AS TIMESTAMP))  AS MONTH
  4  FROM V$INSTANCE
  5  /

DB_VERSION             MONTH
----------------- ----------
9.2.0.1.0                  1

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(DAY FROM CAST(SYSDATE AS TIMESTAMP))    AS DAY
  4  FROM V$INSTANCE
  5  /

DB_VERSION               DAY
----------------- ----------
9.2.0.1.0                  5

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP))   AS HOUR
  4  FROM V$INSTANCE
  5  /

DB_VERSION              HOUR
----------------- ----------
9.2.0.1.0                 19

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(MINUTE FROM CAST(SYSDATE AS TIMESTAMP)) AS MINUTE
  4  FROM V$INSTANCE
  5  /

DB_VERSION            MINUTE
----------------- ----------
9.2.0.1.0                 54

SQL> SELECT
  2     VERSION AS DB_VERSION,
  3     EXTRACT(SECOND FROM CAST(SYSDATE AS TIMESTAMP)) AS SECOND
  4  FROM V$INSTANCE
  5  /

DB_VERSION            SECOND
----------------- ----------
9.2.0.1.0                 24

SQL>

要するに、TIMESTAMP 型でないと時刻は取れないらしい。じゃあそう書いといてほしいものだ。

frog.raindrop.jp.knowledge

January 5, 2005

トラックバック

コメント

コメントする