ORACLE 字元串超長問題解決方案

前兩天我在工作中遇到這樣一個問題，我們有一個程式是用來增量抽取EBS 中的表資料的，有的是全量抽取，即先删除原表中的資料，然後重新抽取資料，示例代碼如下：

1 truncate table ods_emp drop storage;
2 insert into ods_emp select * from emp;      

另外一種方式是增量抽取，用的是merge語句，這裡就不寫了;)

接觸過EBS庫存子產品的同志們知道，INV中的物料表是MTL_SYSTEM_ITEM_B，這個表的字段那叫一個多！我之前搞錯了，用的是第一種方案提取的，這就會導緻我的程式運作之後資料會大量減少（原因是臨時表隻有一兩天的資料，大家懂得）。恰好這問題是在調試程式時出現的，并且不隻是我負責的INV出現了問題，其他同僚的子產品也有。于是項目經理怒了，發話：今天誰搞不完，加班！

      我可不想加班，趕緊把語句由INSERT寫成MERGE。寫完一運作，報錯：

哦，原來是字元超長了。在這裡我就不把之前的程式寫這裡了，以免吓到大家。我用下面的代碼來模拟這個錯誤吧：

SQL> DECLARE
  2    v_str VARCHAR2(32767);
  3    v_cnt NUMBER;
  4  BEGIN
  5    v_str := RPAD('select count(*) from emp',32768);
  6    EXECUTE IMMEDIATE v_str INTO v_cnt;
  7    dbms_output.put_line('v_cnt: '||v_cnt);
  8  END;
  9  /
DECLARE
*
第 1 行出現錯誤:
ORA-06502: PL/SQL: 數字或值錯誤 :  字元串緩沖區太小
ORA-06512: 在 line 5      

我想這還不好辦嘛，直接把varchar2改成long。可是還是報錯了：

SQL> DECLARE
  2    v_str LONG;
  3    v_cnt NUMBER;
  4  BEGIN
  5    v_str := RPAD('select count(*) from emp',32768);
  6    EXECUTE IMMEDIATE v_str INTO v_cnt;
  7    dbms_output.put_line('v_cnt: '||v_cnt);
  8  END;
  9  /
DECLARE
*
第 1 行出現錯誤:
ORA-06502: PL/SQL: 數字或值錯誤 :  字元串緩沖區太小
ORA-06512: 在 line 5      

我之前在網上查的是LONG類型支援2G的大小，不知為啥，這次報錯了。平時由于工作的限制，很少接觸像LONG,LOB,CLOB等大資料類型，以後可得仔細研究下了。

自己當時有點兒着急了，也想不出合适的辦法來，隻能請教同僚了。還真有一個同僚遇到過，她把字元串變量定義成CLOB類型，具體請看代碼：

1 DECLARE 
 2   v_str CLOB;
 3   v_temp_str VARCHAR2(32767);
 4   v_cnt NUMBER;
 5 BEGIN
 6   dbms_lob.createtemporary(v_str,true);--建立一個臨時lob
 7   v_temp_str := RPAD('select count(*) ',32767);
 8   dbms_lob.append(v_str,v_temp_str);--把臨時字元串付給v_str
 9   v_temp_str := RPAD('from emp ',32767);
10   dbms_lob.append(v_str,v_temp_str);--把臨時字元串付給v_str
11   EXECUTE IMMEDIATE v_str INTO v_cnt;
12   dbms_output.put_line('v_cnt: '||v_cnt);
13   dbms_lob.freetemporary(v_str);--釋放lob
14 END;      

運作結果：v_cnt: 14

總結：1.用CLOB類型可以處理字元串超長的情況；

         2.ORACLE 的大資料類型還須研究。

程咬金姓逗