oracle - Use connect by statement with CLOB parameter

Question

My procedure

CREATE OR REPLACE PROCEDURE my_procedure(res OUT SYS_REFCURSOR , p_LstKH CLOB)
AS
CURSOR c_dsKH
  IS
    SELECT TO_NUMBER(REGEXP_SUBSTR(p_LstKH,'[^,]+', 1, level)) value FROM dual
    CONNECT BY REGEXP_SUBSTR(p_LstKH, '[^,]+', 1, level) IS NOT NULL;
BEGIN
...
END;

I would like to split long string p_LstKH and then put into CURSOR c_dsKH. Example: p_LstKH = '1,2,....,10000'

c_dsKH.value
1
2
...
10000

However, when I execute that procedure, I get error "not enough memory for connect by operation". I've try to replace parameter p_LstKH CLOB with p_LstKH VARCHAR2, then I get other error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small".

What should I do now ? Simply, I would like to split a long string. Thanks all !

regular expression can contain up to 512 bytes [check here](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm). You can create a global temporary table and write a procedure to store the result. and query from GT table. — simplify_life, Mar 05 '14 at 12:37
@simplify_life - the *pattern* can be up to 512 bytes; the pattern here is `'[^,]+'`, which is only 5. It's the number of levels this generates for a large input string that's a problem. — Alex Poole, Mar 05 '14 at 12:45

Alex Poole · Accepted Answer · 2014-03-05T12:42:52.847

You could do your own splitting based on the commas, and use a pipelined function to return the values:

create or replace function split_clob(p_lstkh clob)
return sys.odcivarchar2list pipelined
as
  start_pos pls_integer := 0;
  end_pos pls_integer := 0;
  clob_length pls_integer;
  str varchar2(4000);
begin
  clob_length := dbms_lob.getlength(p_lstkh);
  while end_pos <= clob_length loop
    start_pos := end_pos + 1;
    end_pos := dbms_lob.instr(p_lstkh, ',', start_pos, 1);
    if end_pos <= 0 then
      end_pos := clob_length + 1;
    end if;
    str := dbms_lob.substr(p_lstkh, end_pos - start_pos, start_pos);
    pipe row (str);
  end loop;
end;
/

Then you can treat that as a table:

select * from table(split_clob('X,Y,Z'));

COLUMN_VALUE
------------
X            
Y            
Z

If you still want is as a ref cursor your procedure can use that select for the cursor, instead of the connect-by.

As a demo to show it working with an actual (made-up) clob of more than 32k:

declare
  clob_val clob := 'A,B,C,D,E,F,G,H,I';
begin
  for i in 1..2000 loop
    dbms_lob.append(clob_val, ',A,B,C,D,E,F,G,H,I');
  end loop;
  dbms_output.put_line('Clob size: ' || dbms_lob.getlength(clob_val));
  for r in (select * from table(split_clob(clob_val)) where rownum < 6) loop
    dbms_output.put_line(r.column_value);
  end loop;
end;
/

anonymous block completed
Clob size: 36017
A
B
C
D
E

Thank you so much. You are my hero Alex Poole :) – Leo Le Mar 06 '14 at 01:51 — Leo Le, Mar 06 '14 at 01:51

score 1 · Answer 2 · answered Mar 05 '14 at 16:15

this should work, but it needs a function that returns the refcursor. if you can work with it:

create or replace
function splitter(pc CLOB) return sys_refcursor
as
  lc sys_refcursor;
begin
  open lc for
    with s(tot, sub, offset) as (
      select pc||',' tot, 
        dbms_lob.substr(pc, dbms_lob.instr(pc, ',', 1)-1, 1) sub, 
        dbms_lob.instr(pc, ',', 1)+1 offset
      from dual
      union all
      select tot, 
        dbms_lob.substr(tot, dbms_lob.instr(tot, ',', offset+1)-offset, offset), 
        dbms_lob.instr(tot, ',', offset+1)+1
      from s
      where offset < dbms_lob.getlength(tot)
    )
    select sub
    from s;

  return lc;
end;
/

var c refcursor;
exec :c := splitter('a,b,c,d,e,f');
print c;

oracle - Use connect by statement with CLOB parameter

2 Answers2