【Oracle】ORA-12899 完全ガイド｜列の値が大きすぎるエラーの原因・VARCHAR2 BYTE/CHAR・マルチバイト文字の対処法まで解説

-- BYTE セマンティクス（デフォルト）: サイズはバイト数で指定する
CREATE TABLE t1 (
    name VARCHAR2(20)        -- 20バイトまで（BYTE セマンティクス）
    -- AL32UTF8 環境では全角文字は3バイトなので最大 6〜7 文字程度しか入らない
);

-- CHAR セマンティクス: サイズは文字数で指定する
CREATE TABLE t2 (
    name VARCHAR2(20 CHAR)   -- 20文字まで（CHAR セマンティクス）
    -- AL32UTF8 環境でも全角 20 文字を格納できる
);

-- 現在のデフォルトの長さセマンティクスを確認する
SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_LENGTH_SEMANTICS';
-- BYTE: BYTE セマンティクスがデフォルト（大多数のシステム）
-- CHAR: CHAR セマンティクスがデフォルト（セッションまたはシステム設定で変更済み）

-- データベースの文字セットを確認する
SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET';
-- AL32UTF8: ASCII=1バイト、全角（日本語・中国語など）=3バイト、一部記号=2バイト
-- JA16SJIS: ASCII=1バイト、全角=2バイト
-- WE8MSWIN1252: 西ヨーロッパ文字（日本語不可）

-- 例1: BYTE セマンティクスの列に全角文字を INSERT する
CREATE TABLE customer_names (
    name VARCHAR2(10)   -- 10バイト（BYTE）
);

-- 全角3文字（9バイト）→ 成功
INSERT INTO customer_names VALUES ('田中太郎');   -- 4文字 × 3B = 12バイト → エラー！
-- ORA-12899: value too large for column "HR"."CUSTOMER_NAMES"."NAME" (actual: 12, maximum: 10)
-- "actual: 12" はバイト数 = 全角4文字 × 3バイト

INSERT INTO customer_names VALUES ('田中太');     -- 3文字 × 3B = 9バイト → 成功

-- 例2: アプリケーションから変数長の文字列を INSERT する際に発生
-- Java で "山田花子" (5文字) を VARCHAR2(12 BYTE) に INSERT しようとするとエラー
-- 5文字 × 3バイト = 15バイト > 12バイト → ORA-12899

-- エラーの actual / maximum を確認して対処方針を決める
-- actual=18, maximum=10 → 全角6文字 × 3バイト = 18バイト（全角6文字が限度）

-- VSIZE() でバイト数を確認する
SELECT name, LENGTH(name) AS char_len, VSIZE(name) AS byte_len
FROM   customer_names;
-- LENGTH: 文字数, VSIZE: バイト数（AL32UTF8 では全角文字は VSIZE > LENGTH）

-- VARCHAR2 の列サイズは縮小できないが、拡大は可能
ALTER TABLE customer_names MODIFY (name VARCHAR2(60));   -- 60バイト（全角20文字相当）

-- BYTE → CHAR セマンティクスへの変更（最も根本的な対処）
-- 注意: VARCHAR2(20) → VARCHAR2(20 CHAR) は一旦大きくする必要がある場合がある
ALTER TABLE customer_names MODIFY (name VARCHAR2(20 CHAR));
-- 20文字分（AL32UTF8 では最大 60バイト相当）に変更される

-- 変更後に列の定義を確認する
SELECT column_name, data_type, data_length, char_length, char_used
FROM   USER_TAB_COLUMNS
WHERE  table_name = 'CUSTOMER_NAMES';
-- CHAR_USED: B = BYTE セマンティクス、C = CHAR セマンティクス
-- DATA_LENGTH: バイト数（CHAR セマンティクスの場合は char_length × 最大バイト数/文字）
-- CHAR_LENGTH: 文字数（CHAR セマンティクスで指定した文字数）

-- アプリ側でバイト数に合わせてトリミングする場合
-- LENGTHB: バイト数を返す（LENGTH は文字数を返す）
SELECT LENGTHB('田中太郎') FROM DUAL;   -- 出力: 12（4文字 × 3バイト）

-- SUBSTRB: バイト位置で切り出す
-- バイト単位で切り詰める（最大バイト数に合わせる）
SELECT SUBSTRB('田中太郎', 1, 10) FROM DUAL;  -- 10バイト内に収まる文字を返す
-- 出力: '田中太'（3文字 × 3バイト = 9バイト）
-- 注意: マルチバイト文字の途中で切ると文字化けするため SUBSTRB は境界に注意が必要

-- 安全にバイト制限内に収める（PL/SQL）
DECLARE
    v_input  VARCHAR2(100) := '田中太郎テスト';
    v_max    INTEGER := 10;  -- 列の最大バイト数
    v_result VARCHAR2(100);
    v_bytes  INTEGER;
BEGIN
    v_bytes := LENGTHB(v_input);
    IF v_bytes <= v_max THEN
        v_result := v_input;
    ELSE
        -- バイト数が超えている場合、文字単位で先頭から詰めていく
        v_result := NULL;
        FOR i IN 1..LENGTH(v_input) LOOP
            EXIT WHEN LENGTHB(SUBSTR(v_input, 1, i)) > v_max;
            v_result := SUBSTR(v_input, 1, i);
        END LOOP;
    END IF;
    DBMS_OUTPUT.PUT_LINE('結果: ' || v_result || ' (' || LENGTHB(v_result) || 'B)');
END;
/

-- セッション単位で変更する（以降 CREATE TABLE は CHAR セマンティクスがデフォルト）
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

-- この状態で CREATE TABLE すると VARCHAR2 がデフォルトで CHAR セマンティクスになる
CREATE TABLE new_table (
    name VARCHAR2(20),     -- CHAR セマンティクスとして作成される（20文字）
    address VARCHAR2(100)  -- CHAR セマンティクスとして作成される（100文字）
);

-- 確認: CHAR_USED が C（CHAR セマンティクス）になっているはず
SELECT column_name, char_used FROM USER_TAB_COLUMNS WHERE table_name = 'NEW_TABLE';

-- 注意: NLS_LENGTH_SEMANTICS を既存テーブルに遡って適用することはできない
-- 既存テーブルは ALTER TABLE MODIFY で個別に変更する必要がある

-- システム全体での変更（pfile/spfile）:
-- NLS_LENGTH_SEMANTICS = CHAR を初期化パラメータに設定する
-- ただし一部の内部テーブルへの影響があるため Oracle のサポートに確認することを推奨

-- 現在の列定義より大きなデータが入っているかどうかを事前に確認する
-- (例: VARCHAR2(20 BYTE) の列に 20バイトを超えるデータが格納されていないか)

-- 現在の列の最大バイト数を超えるデータを探す
SELECT employee_id, LENGTHB(last_name) AS byte_len, last_name
FROM   employees
WHERE  LENGTHB(last_name) > 20   -- 列の data_length を指定する
ORDER  BY LENGTHB(last_name) DESC;

-- テーブルの VARCHAR2 列のバイト使用量を一括確認する
SELECT column_name, data_length,
       MAX(LENGTHB(last_name)) AS max_byte_usage
FROM   USER_TAB_COLUMNS
CROSS  JOIN employees
WHERE  table_name = 'EMPLOYEES'
  AND  column_name = 'LAST_NAME'
GROUP  BY column_name, data_length;
-- max_byte_usage が data_length に近い列は ALTER TABLE MODIFY で余裕を持たせることを検討する