【Oracle】NLS設定完全ガイド｜NLS_DATE_FORMAT・NLS_CHARACTERSET・NLS_SORT・文字コード・日付エラー対処まで解説

-- セッションレベルの NLS 設定（現在接続しているセッション）
SELECT parameter, value
FROM nls_session_parameters
ORDER BY parameter;

-- インスタンスレベルの NLS 設定
SELECT parameter, value
FROM v$nls_parameters
ORDER BY parameter;

-- データベースデフォルトの NLS 設定
SELECT parameter, value
FROM nls_database_parameters
ORDER BY parameter;

-- 自分のセッション環境変数（クライアント側の NLS 設定も含む）
SELECT * FROM nls_instance_parameters;

-- よく確認するパラメータだけを抽出
SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN (
    'NLS_DATE_FORMAT',
    'NLS_TIMESTAMP_FORMAT',
    'NLS_LANGUAGE',
    'NLS_TERRITORY',
    'NLS_CHARACTERSET',
    'NLS_SORT',
    'NLS_COMP',
    'NLS_LENGTH_SEMANTICS',
    'NLS_DATE_LANGUAGE'
)
ORDER BY parameter;

-- 現在の日付フォーマット確認
SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';

-- セッションレベルで変更（その接続だけに有効）
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

-- 変更後の動作確認
SELECT SYSDATE FROM dual;
-- → 2025-04-03 10:30:00（フォーマット通りに表示）

-- システムレベルで変更（全セッションのデフォルトが変わる。再起動後も有効）
ALTER SYSTEM SET NLS_DATE_FORMAT = 'YYYY/MM/DD' SCOPE = BOTH;

-- ORA-01861: リテラルが書式文字列に一致しません
-- 原因: 文字列を DATE に暗黙変換するとき NLS_DATE_FORMAT と一致しない

-- NG: NLS_DATE_FORMAT が 'DD-MON-RR' の環境で以下を実行
SELECT * FROM orders WHERE order_date > '2025-04-01';
-- → ORA-01861（ハイフン区切りが DD-MON-RR と一致しない）

-- OK: TO_DATE で書式を明示する（推奨・NLS設定に依存しない）
SELECT * FROM orders WHERE order_date > TO_DATE('2025-04-01', 'YYYY-MM-DD');

-- OK: DATE リテラル構文（ANSI準拠、固定で YYYY-MM-DD を使用）
SELECT * FROM orders WHERE order_date > DATE '2025-04-01';
-- DATE 'YYYY-MM-DD' 形式は NLS_DATE_FORMAT に関係なく常に動作する

-- TIMESTAMP の場合
SELECT * FROM orders
WHERE created_at > TIMESTAMP '2025-04-01 00:00:00';

-- TO_CHAR / TO_DATE の第3引数で NLS パラメータを直接指定
-- セッション設定を変えずに一時的に言語を切り替えたいときに有効

-- 英語の月名で表示
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY',
               'NLS_DATE_LANGUAGE = AMERICAN') AS date_en
FROM dual;
-- → 03-APR-2025

-- 日本語の曜日で表示
SELECT TO_CHAR(SYSDATE, 'YYYY年MM月DD日（DY）',
               'NLS_DATE_LANGUAGE = JAPANESE') AS date_jp
FROM dual;
-- → 2025年04月03日（木）

-- データベースの文字セット確認
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
-- NLS_CHARACTERSET      : CHAR/VARCHAR2/CLOB の文字セット
-- NLS_NCHAR_CHARACTERSET: NCHAR/NVARCHAR2/NCLOB の文字セット（通常 AL16UTF16）

-- V$NLS_PARAMETERS でも確認可能
SELECT * FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET';

-- 文字列のバイト数を確認して文字コード変換の影響を把握する
SELECT
    'テスト' AS str,
    LENGTH('テスト')  AS char_len,   -- 文字数: 3
    LENGTHB('テスト') AS byte_len    -- バイト数: AL32UTF8なら9、JA16SJISなら6
FROM dual;

-- 文字コード変換関数
-- CONVERT(文字列, 変換先文字セット[, 変換元文字セット])
SELECT CONVERT('Oracle日本語テスト', 'AL32UTF8', 'JA16SJIS') FROM dual;
-- ※ CONVERT は文字データをRAW経由で変換する。Oracleが内部で文字コード変換する場合と挙動が異なる

-- 文字コードポイントの確認
SELECT ASCIISTR('あ') FROM dual;  -- '\3042' (Unicodeコードポイント)
SELECT UNISTR('\3042') FROM dual; -- 'あ'（Unicodeエスケープから文字列に変換）

-- デフォルト（大文字小文字を区別）
SELECT * FROM products WHERE product_name = 'oracle';
-- → 'ORACLE' や 'Oracle' はヒットしない

-- セッションで大文字小文字を区別しない設定に変更
ALTER SESSION SET NLS_SORT = BINARY_CI;   -- CI = Case Insensitive
ALTER SESSION SET NLS_COMP = LINGUISTIC;   -- NLS_SORT を比較に使用

-- 設定後: 大文字小文字を区別しない
SELECT * FROM products WHERE product_name = 'oracle';
-- → 'ORACLE' も 'Oracle' も 'oracle' もヒット

-- 元に戻す
ALTER SESSION SET NLS_SORT = BINARY;
ALTER SESSION SET NLS_COMP = BINARY;

-- BINARY（デフォルト）: バイナリ比較（最高速）
ALTER SESSION SET NLS_SORT = BINARY;

-- BINARY_CI: 大文字小文字を区別しない
ALTER SESSION SET NLS_SORT = BINARY_CI;

-- BINARY_AI: 大文字小文字 + アクセント記号を区別しない
ALTER SESSION SET NLS_SORT = BINARY_AI;

-- JAPANESE_M: 日本語ソート（ひらがな・カタカナを同一視 + 濁点順）
ALTER SESSION SET NLS_SORT = JAPANESE_M;

-- JAPANESE_M_CI: 日本語 + 大文字小文字区別なし
ALTER SESSION SET NLS_SORT = JAPANESE_M_CI;

-- NLS_SORT を使った比較には NLS_COMP = LINGUISTIC が必要
ALTER SESSION SET NLS_COMP = LINGUISTIC;

-- REGEXP_LIKE の 3 番目の引数に 'i' を指定（Case Insensitive）
-- NLS_COMP の設定に依存しない、より確実な方法
SELECT * FROM products
WHERE REGEXP_LIKE(product_name, '^oracle', 'i');
-- → 'ORACLE', 'Oracle', 'oracle' すべてにマッチ

-- WHERE 句で UPPER/LOWER を使う方法（古典的だが確実）
SELECT * FROM products
WHERE UPPER(product_name) = UPPER('oracle');
-- インデックスを使いたい場合: 関数ベースインデックスを作成
CREATE INDEX idx_prod_name_upper ON products (UPPER(product_name));

-- 現在の設定確認
SELECT value FROM nls_session_parameters
WHERE parameter = 'NLS_LENGTH_SEMANTICS';
-- → BYTE（デフォルト）

-- CHAR セマンティクスに変更（以降に作成する列に影響）
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

-- CHAR セマンティクスで作成したテーブルの例
-- VARCHAR2(100) が「100文字まで」になる
CREATE TABLE user_profiles (
    user_id     NUMBER        PRIMARY KEY,
    full_name   VARCHAR2(100),  -- CHAR セマンティクス: 100文字（AL32UTF8なら最大300バイト）
    bio         VARCHAR2(500)   -- CHAR セマンティクス: 500文字
);

-- 元の BYTE セマンティクスに戻す
ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE;

-- 注意: NLS_LENGTH_SEMANTICS は既存テーブルの列定義は変更しない。
-- CREATE TABLE 実行時点の設定が適用される。

-- USER_TAB_COLUMNS の CHAR_USED 列で確認
-- B = BYTE セマンティクス、C = CHAR セマンティクス
SELECT column_name, data_type,
       char_length,  -- 文字単位の長さ
       char_used     -- B: BYTE / C: CHAR
FROM user_tab_columns
WHERE table_name = 'USER_PROFILES'
ORDER BY column_id;

-- 結果例（CHAR セマンティクスで作成した場合）:
-- COLUMN_NAME  DATA_TYPE  CHAR_LENGTH  CHAR_USED
-- USER_ID      NUMBER     (null)       (null)
-- FULL_NAME    VARCHAR2   100          C
-- BIO          VARCHAR2   500          C

-- 日本語設定
ALTER SESSION SET NLS_LANGUAGE  = 'JAPANESE';
ALTER SESSION SET NLS_TERRITORY = 'JAPAN';

-- 日本語環境での動作確認
SELECT TO_CHAR(SYSDATE, 'MONTH') FROM dual;  -- → '4月        '
SELECT TO_CHAR(SYSDATE, 'DAY')   FROM dual;  -- → '木曜日    '

-- 英語設定に切り替え
ALTER SESSION SET NLS_LANGUAGE  = 'AMERICAN';
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

SELECT TO_CHAR(SYSDATE, 'MONTH') FROM dual;  -- → 'APRIL     '
SELECT TO_CHAR(SYSDATE, 'DAY')   FROM dual;  -- → 'THURSDAY  '

-- NLS_TERRITORY が決定するデフォルト書式
-- JAPAN の場合:
--   NLS_DATE_FORMAT = 'RR-MM-DD'
--   NLS_NUMERIC_CHARACTERS = '.'（小数点は . 、桁区切りは ,）
--   NLS_CURRENCY = '\'（通貨記号 TO_CHAR(num, 'L999') で使用）

-- 使用可能な言語の一覧
SELECT nls_language, iso_abbreviation, iso3_abbreviation
FROM v$nls_valid_values
WHERE parameter = 'LANGUAGE'
ORDER BY nls_language;

-- 使用可能な地域の一覧
SELECT value
FROM v$nls_valid_values
WHERE parameter = 'TERRITORY'
ORDER BY value;

-- NG: NLS_DATE_FORMAT に依存（環境によって動く/動かないが変わる）
WHERE order_date = '2025-04-01'

-- OK: TO_DATE で書式を明示
WHERE order_date = TO_DATE('2025-04-01', 'YYYY-MM-DD')

-- OK: DATE リテラル（ANSI SQL 標準、常に YYYY-MM-DD）
WHERE order_date = DATE '2025-04-01'

-- OK: TIMESTAMP リテラル（日時まで含める場合）
WHERE created_at >= TIMESTAMP '2025-04-01 00:00:00'

-- 方法1: 接続URLに NLS パラメータを含める（非推奨・バージョン依存）
-- jdbc:oracle:thin:@host:1521/SID?oracle.jdbc.timezoneAsRegion=false

-- 方法2: 接続後に ALTER SESSION で統一（推奨）
-- Java コード例（コメントとして記載）
-- Connection conn = DriverManager.getConnection(url, user, pass);
-- Statement stmt = conn.createStatement();
-- stmt.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
-- stmt.execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6'");

-- 方法3: Oracleの接続ログインスクリプト（LOGIN.SQL）で設定
-- DB サーバーの LOGIN.SQL に記述すると全セッションで自動設定される

-- 最終手段: PreparedStatement と java.sql.Date/Timestamp を使い、
-- 文字列変換を経由しないバインド変数で渡す（最も安全）

-- デフォルト（BINARY）: バイナリ値でソート → ひらがな・カタカナが混在すると意図しない順になる
SELECT name FROM t ORDER BY name;

-- JAPANESE_M: 日本語の辞書順ソート（ひらがな/カタカナを同一視）
ALTER SESSION SET NLS_SORT = JAPANESE_M;
ALTER SESSION SET NLS_COMP = LINGUISTIC;
SELECT name FROM t ORDER BY name;

-- NLSSORT 関数を使ったソート（セッション設定を変えずに一時的に適用）
SELECT name
FROM t
ORDER BY NLSSORT(name, 'NLS_SORT=JAPANESE_M');