Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

日付の文字列が正しい日付かチェックする

Oracle 12c R2 以降の場合 ⇒ VALIDATE_CONVERSION という組み込み関数がある。

VALIDATE_CONV_DATE ファンクション (日付文字列の有効性チェック)

文字列型の日付の整合性をチェックする関数は組み込み関数およびパッケージに用意されていない。 不正な日付文字をチェックなしに TO_DATE 関数 を使用すると例外が発生する。 これは外部入力データの投入バッチ処理などにおいて、トランザクション がロールバックすることにつながる。

CSV や文字列、テキストベースで用意されたデータをデータベースに投入する際の日付のチェック処理は欠かせない。

• VALIDATE_CONV_DATE ファンクションのソース

  移行しやすいように VALIDATE_CONVERSION の仕様に近づくように名称と引数、引数が NULL 場合の戻り値などの仕様を変更。

日付文字列のチェック形式は引数で変更できるが、セッションの NLS 関連パラメータ依存ではなくハードコーディング

CREATE OR REPLACE FUNCTION RIVUS.VALIDATE_CONV_DATE(
  P_DATE         IN VARCHAR,
  P_FORMAT       IN VARCHAR := 'YYYY-MM-DD HH24:MI:SS' ,
  P_NLS_PARAMTER IN VARCHAR2 := q'{NLS_CALENDAR = 'GREGORIAN'}' 
)
RETURN NUMBER
AS
  vDate          DATE;
BEGIN
  vDate := TO_DATE(P_DATE, P_FORMAT, P_NLS_PARAMTER);
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

VALIDATE_CONV_DATE 使用例

SELECT
    DECODE(VALIDATE_CONV_DATE(DATE_STR), 1, TO_DATE(DATE_STR), TO_DATE('9999/12/31'))
FROM DATE_STRING ;

q'{NLS_CALENDAR = 'GREGORIAN'}' は代替引用符を使った記述方法

関連事項

• リテラル内での単一引用符の表現方法:代替引用符
• 日付型と日付文字列型の使い分け
• 汎用 CSV 出力プロシージャ