【Oracle】ORA-06502 の原因と解決方法完全ガイド｜PL/SQL 数値または値のエラー・VARCHAR2バッファ・NUMBER精度・暗黙変換まで解説

DECLARE
    v_name VARCHAR2(10);   -- 最大10文字で宣言
BEGIN
    v_name := 'Hello, World!';   -- 13文字 → ORA-06502
    DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
-- ORA-06502: PL/SQL: numeric or value error: character string buffer too small

DECLARE
    v_name VARCHAR2(100);   -- 十分な長さで宣言
BEGIN
    v_name := 'Hello, World!';   -- 13文字 → 問題なし
    DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

DECLARE
    -- テーブルの列定義から自動的に型・長さを継承する（最も安全）
    v_name employees.last_name%TYPE;
BEGIN
    SELECT last_name INTO v_name FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
-- %TYPE を使えば列の定義が変わっても自動追従する

DECLARE
    v_short VARCHAR2(5);
    v_long  VARCHAR2(4000) := 'ABCDEFGHIJ';
BEGIN
    -- SUBSTR(v_long, 1, 10) の戻り値は 10 文字 → v_short(5) に入らない
    v_short := SUBSTR(v_long, 1, 10);   -- ORA-06502
END;
/

DECLARE
    v_short VARCHAR2(5);
    v_long  VARCHAR2(4000) := 'ABCDEFGHIJ';
BEGIN
    v_short := SUBSTR(v_long, 1, 5);   -- 5文字以内に切り出す
    -- または
    v_short := SUBSTR(v_long, 1, LENGTH(v_short));  -- 変数長で制限
END;
/

-- NUMBER(precision, scale) の仕様
-- precision: 全体の有効桁数（1〜38）
-- scale: 小数点以下の桁数（-84〜127）

-- 例: NUMBER(5, 2) → 最大 999.99（整数部3桁、小数部2桁）
--   → 1000.00 を代入しようとすると ORA-06502
--   → 999.999 は 999.99 に丸められる（スケール超過は四捨五入されエラーにならない）
--   → 1000 を代入しようとすると ORA-06502（整数部4桁で超過）

DECLARE
    v_amount NUMBER(5, 2);   -- 最大 999.99
BEGIN
    v_amount := 10000;   -- 5桁の整数 → ORA-06502: number precision too large
END;
/

DECLARE
    v_amount NUMBER;       -- 精度・スケール指定なし → 最大38桁、制限なし
    v_price  NUMBER(10, 2);  -- 億単位まで対応するなら余裕を持たせる
BEGIN
    v_amount := 10000;     -- OK
    v_price  := 9999999.99;  -- OK
END;
/

DECLARE
    v_num NUMBER;
BEGIN
    v_num := 'ABC';    -- ORA-06502: character to number conversion error
    v_num := '12.3.4'; -- ORA-06502: 小数点が2つ
    v_num := '';       -- NULL として扱われる（エラーにはならない）
END;
/

DECLARE
    v_input  VARCHAR2(100) := '1234';
    v_num    NUMBER;
BEGIN
    -- Oracle 12.2 以降: 変換可能かチェックしてから代入
    IF VALIDATE_CONVERSION(v_input AS NUMBER) = 1 THEN
        v_num := TO_NUMBER(v_input);
        DBMS_OUTPUT.PUT_LINE('変換成功: ' || v_num);
    ELSE
        DBMS_OUTPUT.PUT_LINE('変換不可: ' || v_input);
    END IF;
END;
/

DECLARE
    v_input VARCHAR2(100) := 'ABC';
    v_num   NUMBER;
BEGIN
    -- 変換できない場合は指定したデフォルト値を使う
    v_num := TO_NUMBER(v_input DEFAULT 0 ON CONVERSION ERROR);
    DBMS_OUTPUT.PUT_LINE('結果: ' || v_num);  -- 結果: 0
END;
/

DECLARE
    v_date DATE;
BEGIN
    -- NLS_DATE_FORMAT が 'DD-MON-RR' の環境で以下を実行すると ORA-06502
    v_date := '2025-04-01';   -- ハイフン区切りはデフォルト形式と異なる場合がある

    -- NLS_DATE_FORMAT が 'YYYY/MM/DD' でも以下はエラー
    v_date := '2025年4月1日';  -- 日本語月日表記はデフォルト変換不可
END;
/

DECLARE
    v_date DATE;
BEGIN
    -- 書式を明示することで NLS 設定に依存しなくなる（最も安全）
    v_date := TO_DATE('2025-04-01', 'YYYY-MM-DD');
    v_date := TO_DATE('2025年4月1日', 'YYYY"年"MM"月"DD"日"');
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_date, 'YYYY-MM-DD'));
END;
/

DECLARE
    v_input VARCHAR2(100) := 'not-a-date';
    v_date  DATE;
BEGIN
    -- 変換失敗時にデフォルト値（SYSDATE）を使う
    v_date := TO_DATE(v_input DEFAULT SYSDATE ON CONVERSION ERROR, 'YYYY-MM-DD');
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_date, 'YYYY-MM-DD'));
END;
/

DECLARE
    -- VARCHAR2(10) は BYTE 単位: UTF-8では日本語1文字 = 3バイト
    -- → 10バイトに入る日本語は最大3文字
    v_str VARCHAR2(10);
BEGIN
    v_str := '日本語テスト';  -- 6文字 = 18バイト → ORA-06502
END;
/

DECLARE
    -- VARCHAR2(10 CHAR) → 10文字（バイト数でなく文字数で制限）
    v_str VARCHAR2(10 CHAR);
BEGIN
    v_str := '日本語テスト';  -- 6文字 → OK（10文字まで許容）
    DBMS_OUTPUT.PUT_LINE(v_str);
END;
/
-- または NLS_LENGTH_SEMANTICS = CHAR に設定することでデフォルトを変えられる
-- ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

-- 文字セット AL32UTF8 （UTF-8）の環境での比較
-- 日本語1文字 = 最大3バイト（UTF-8）

-- BYTE セマンティクス（デフォルト）
DECLARE v_byte VARCHAR2(10);       BEGIN v_byte := '日本語テスト';  END;  -- ORA-06502（18バイト > 10）
DECLARE v_byte VARCHAR2(10);       BEGIN v_byte := '日本語';        END;  -- OK（9バイト <= 10）

-- CHAR セマンティクス
DECLARE v_char VARCHAR2(10 CHAR);  BEGIN v_char := '日本語テスト';  END;  -- OK（6文字 <= 10）
DECLARE v_char VARCHAR2(10 CHAR);  BEGIN v_char := 'ABCDEFGHIJK';  END;  -- ORA-06502（11文字 > 10）

DECLARE
    v_str VARCHAR2(5);
BEGIN
    v_str := 'ABCDE';     -- 行4: OK
    v_str := 'ABCDEFGH';  -- 行5: ORA-06502 発生
EXCEPTION
    WHEN VALUE_ERROR THEN
        -- SQLERRM: エラーメッセージ
        -- FORMAT_ERROR_BACKTRACE: 例外が発生した行番号を含むスタックトレース
        DBMS_OUTPUT.PUT_LINE('エラー: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('発生箇所: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        -- 出力例:
        -- エラー: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
        -- 発生箇所: ORA-06512: at line 5
END;
/

DECLARE
    v_val NUMBER;
BEGIN
    v_val := 'XYZ';  -- ORA-06502 発生
EXCEPTION
    -- ORA-06502 は PL/SQL の VALUE_ERROR として捕捉できる
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('VALUE_ERROR をキャッチしました');
        DBMS_OUTPUT.PUT_LINE('詳細: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('行番号: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    WHEN OTHERS THEN
        -- 予期しないエラーはログに記録して再発生させる
        DBMS_OUTPUT.PUT_LINE('予期しないエラー: ' || SQLERRM);
        RAISE;
END;
/

CREATE OR REPLACE PROCEDURE process_data(p_id IN NUMBER) AS
    v_name  employees.last_name%TYPE;
    v_email VARCHAR2(200 CHAR);  -- CHAR セマンティクスで余裕を持って宣言
BEGIN
    SELECT last_name, email
    INTO v_name, v_email
    FROM employees
    WHERE employee_id = p_id;

    -- メイン処理...

EXCEPTION
    WHEN VALUE_ERROR THEN
        -- エラーログテーブルに記録（再発分析のため backtrace も保存）
        INSERT INTO error_log (log_dt, proc_name, input_id, err_code, err_msg, backtrace)
        VALUES (SYSDATE, 'PROCESS_DATA', p_id,
                SQLCODE, SQLERRM,
                DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        COMMIT;
    WHEN OTHERS THEN
        INSERT INTO error_log (log_dt, proc_name, input_id, err_code, err_msg, backtrace)
        VALUES (SYSDATE, 'PROCESS_DATA', p_id,
                SQLCODE, SQLERRM,
                DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        COMMIT;
        RAISE;
END;
/