[Oracle Debug] 解決ORA-06502的問題

* 發生背景

1. Table欄位過多，且欄位名稱有規則
2. Column名稱每個都10個字元以上
3. 要一次更新此Table的所有欄位
4. 採用Dynamic SQL，透過Loop組出Update語法

* 發生原因

在v_sql_update這個變數發生ORA-06502錯誤

程式如下：

DECLARE
    v_sql_update                varchar2(30000);
    v_sql_where                 varchar2(5000);
    v_sql                       varchar2(30000);
    v_sql_clob                  clob;
    v_rate                      number;

    CURSOR c1 IS
        SELECT colA
        FROM TableA
BEGIN
    v_sql_update := ' UPDATE TableA '
                   ||'SET ColB = 1'
                   ||', ColC = 2'
                   ||', ColD = 3'
                   ||', ColE = 4'
                   ||', ColF = 5'
                   ||', ColG = 6'
                   ...  --一直到Z欄位
                   ||', ColZ = 26'
                   ;

    FOR r1 IN c1 LOOP
        v_sql_update := v_sql_update
                        r1.colA||'='||r1.colA||' * '||to_char(v_rate)||','
                        ;
    END LOOP;

    v_sql := v_sql_update || v_sql_where
    dbms_lob.createtemporary(v_sql_clob, false);
    dbms_lob.append(v_sql_clob, to_clob(v_sql));

    execute immediate v_sql_clob;

EXCEPTION WHEN others THEN
    dbms_output.put_line(sqlerrm);
END;

* 解決方式

1. 將變數從varchar2改為clob
2. 將v_sql_update語法拆開兩段字串，使用concat連接

最後程式如下：

DECLARE
    v_sql_update                clob;
    v_sql_where                 clob;
    v_sql                       clob;
    v_sql_clob                  clob;
    v_rate                      number;

    CURSOR c1 IS
        SELECT colA
        FROM TableA
BEGIN
    v_sql_update := concat(' UPDATE TableA '
                   ||'SET ColB = 1'
                   ||', ColC = 2'
                   ...
                   ||', ColJ = 12'
                   ,
                   ', ColK = 13'
                   ||', ColL = 14'
                   ...
                   ||', ColZ = 26'
                   );

    FOR r1 IN c1 LOOP
        v_sql_update := concat(v_sql_update,
                            r1.colA||'='||r1.colA||' * '||to_char(v_rate)||','
                        );
    END LOOP;

    v_sql := v_sql_update || v_sql_where
    dbms_lob.createtemporary(v_sql_clob, false);
    dbms_lob.append(v_sql_clob, to_clob(v_sql));

    execute immediate v_sql_clob;

EXCEPTION WHEN others THEN
    dbms_output.put_line(sqlerrm);
END;

參考資料

Stackoverflow - I take CLOB datatype variable still getting this error