紧急求助:如何在存储过程中处理CLOB[网上没查到……]

Question

项目中遇到一个问题，需要向一个存储过程传入一个大的数据，开始是想作为一个vachar2传入，结果发现可能是数据过大，总是报ORA-01460: 转换请求无法实现或不合理错误，所以想把数据作为一个CLOB传入，数据格式为A,AA,AAA,AAAAA@B,BB,BBB,BBBB@，我想在存储过程中能不能解析一下传入的CLOB,然后，以@为分隔符，取出数据，就是说，对传入的CLOB做一个操作，最后能把其中的数据分割成如下形势
A,AA,AAA,AAAAA
B,BB,BBB,BBBB谢谢赐教！！！

Answer 1

你在前端进行处理成
A,AA,AAA,AAAAA
B,BB,BBB,BBBB
这样的形式不行么?

Answer 2

CREATE OR REPLACE PROCEDURE splitstr(i_str CLOB, i_sep CHAR) IS
  v_getstr VARCHAR2(32000);
  v_str    CLOB;
BEGIN
  v_str := i_sep || i_str || i_sep;
  FOR i IN 1 .. length(v_str) - length(REPLACE(v_str, i_sep)) - 1 LOOP
    v_getstr := substr(v_str,
                       instr(v_str, i_sep, 1, i) + 1,
                       instr(v_str, i_sep, 1, i + 1) -
                       instr(v_str, i_sep, 1, i) - 1);
    --v_getstr即为分离的字符串
    dbms_output.put_line(v_getstr);
  END LOOP;
END splitstr;

Answer 3

我根据你的回复改写了我的代码
procedure saveSmsHistory(v_sms_history in clob)is
  v_getstr VARCHAR2(32000);
  v_str    CLOB;
  BEGIN
  v_str :='@'|| v_sms_history || '@';
  FOR i IN 1 .. length(v_str) - length(REPLACE(v_str, '@')) - 1 LOOP
    v_getstr := substr(v_str,
                       instr(v_str, '@', 1, i) + 1,
                       instr(v_str, '@', 1, i + 1) -
                       instr(v_str, '@', 1, i) - 1);
    --v_getstr即为分离的字符串
    dbms_output.put_line(v_getstr);
   
  END LOOP;    
  END;   
运行后报
java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 ,错误指向
FOR i IN 1 .. length(v_str) - length(REPLACE(v_str, '@')) - 1 LOOP行
我确定我传入的数据确实是
A,AA,AAA,AAAAA@B,BB,BBB,BBBB@请大虾赐教!!!       

Answer 4

最好不要传入clob,如果要用clob可以转为临时表,操作clob很烦，用dbms_lob包
--clob测试SQL> create or replace type varchar2_tt as table
  2       of varchar2 (1000);
  3  /
 
Type created
 
SQL> 
SQL> create or replace
  2       function in_list( p_string in  clob ,p_delimiter in varchar2 default ',',len in out number)
  3   return varchar2_tt
  4  
  5    as
  6  
  7          l_data          varchar2_tt := varchar2_tt();
  8          n               number;
  9          l_string clob:=p_string ;
 10          l_delimiter clob:=to_clob(p_delimiter);
 11          v_str varchar2(32767);
 12      begin
 13          dbms_lob.append(dest_lob => l_string,src_lob =>l_delimiter)  ;
 14        loop
 15           n :=  dbms_lob.instr(lob_loc => l_string,pattern => l_delimiter);
 16           v_str:= trim(dbms_lob.substr(lob_loc => l_string,amount => n-1,offset => 1));
 17         exit when v_str is null;
 18           l_data.extend;
 19           l_data(l_data.count) :=  v_str ;
 20           len := length( l_data(l_data.count))+dbms_lob.getlength(l_delimiter)+len;
 21          dbms_lob.erase(lob_loc => l_string,amount => len);
 22      end loop;
 23      return l_data;
 24    end;
 25  /
 
Function created
SQL>    declare
  2  lens number default 0;
  3   l_data          varchar2_tt := varchar2_tt();
  4   l_string clob:=to_clob('A,AA,AAA,AAAAAB,BB,BBB,BBBB');
  5  begin
  6   l_data := in_list(p_string => l_string,len => lens);
  7   for i in l_data.first .. l_data.last loop
  8     dbms_output.put_line(l_data(i));
  9   end loop;
 10  end;
 11  /
 
A
AA
AAA
AAAAAB
BB
BBB
BBBB
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>  declare
  2  lens number default 0;
  3   l_data          varchar2_tt := varchar2_tt();
  4   l_string clob:=to_clob('A,AA,AAA,AAAAA@B,BB,BBB,BBBB@');
  5  begin
  6   l_data := in_list(p_string => l_string,p_delimiter =>'@',len => lens);
  7   for i in l_data.first .. l_data.last loop
  8     dbms_output.put_line(l_data(i));
  9   end loop;
 10  end;
 11  
 12  /
 
A,AA,AAA,AAAAA
B,BB,BBB,BBBB
 
PL/SQL procedure successfully completed