ORACLE 超長字串問題的解決辦法_oracle

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

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

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

接觸過EBS庫存模組的同志們知道，INV中的物料表是MTL_SYSTEM_ITEM_B，這個表的欄位那叫一個多！我之前搞錯了，用的是第一種方案提取的，這就會導致我的程式運行之後資料會大量減少（原因是暫存資料表只有一兩天的資料，大家懂得）。恰好這問題是在偵錯工具時出現的，並且不只是我負責的INV出現了問題，其他同事的模組也有。於是專案經理怒了，發話：今天誰搞不完，加班！

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

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

SQL> DECLARE  v_str VARCHAR();  v_cnt NUMBER; BEGIN  v_str := RPAD('select count(*) from emp',);  EXECUTE IMMEDIATE v_str INTO v_cnt;  dbms_output.put_line('v_cnt: '||v_cnt); END; /DECLARE*

第 1 行出現錯誤:

ORA-06502: PL/SQL: 數字或值錯誤 :  字串緩衝區太小
ORA-06512: 在 line 5

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

SQL> DECLARE  v_str LONG;  v_cnt NUMBER; BEGIN  v_str := RPAD('select count(*) from emp',);  EXECUTE IMMEDIATE v_str INTO v_cnt;  dbms_output.put_line('v_cnt: '||v_cnt); END; /DECLARE*

第 1 行出現錯誤:

ORA-06502: PL/SQL: 數字或值錯誤 :  字串緩衝區太小
ORA-06512: 在 line 5

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

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

DECLARE  v_str CLOB; v_temp_str VARCHAR(); v_cnt NUMBER; BEGIN dbms_lob.createtemporary(v_str,true);--建立一個臨時lob v_temp_str := RPAD('select count(*) ',); dbms_lob.append(v_str,v_temp_str);--把臨時字串付給v_str v_temp_str := RPAD('from emp ',); dbms_lob.append(v_str,v_temp_str);--把臨時字串付給v_str EXECUTE IMMEDIATE v_str INTO v_cnt; dbms_output.put_line('v_cnt: '||v_cnt); dbms_lob.freetemporary(v_str);--釋放lob END;

運行結果：v_cnt: 14

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

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