Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select * from table(schema.mypackage.myfunction('one, two, three'))
- one
- two
- three
- create or replace function splitter(p_str in varchar2) return sys.odcivarchar2list
- is
- v_tab sys.odcivarchar2list:=new sys.odcivarchar2list();
- begin
- with cte as (select level ind from dual
- connect by
- level <=regexp_count(p_str,',') +1
- )
- select regexp_substr(p_str,'[^,]+',1,ind)
- bulk collect into v_tab
- from cte;
- return v_tab;
- end;
- /
- create or replace function string_tokenizer
- (p_string in varchar2
- , p_separator in varchar2 := ',')
- return sys.dbms_debug_vc2coll
- is
- return_value SYS.DBMS_DEBUG_VC2COLL;
- pattern varchar2(250);
- begin
- pattern := '[^('''||p_separator||''')]+' ;
- select trim(regexp_substr (p_string, pattern, 1, level)) token
- bulk collect into return_value
- from dual
- where regexp_substr (p_string, pattern, 1, level) is not null
- connect by regexp_instr (p_string, pattern, 1, level) > 0;
- return return_value;
- end string_tokenizer;
- /
- SQL> select * from table (string_tokenizer('one, two, three'))
- 2 /
- COLUMN_VALUE
- ----------------------------------------------------------------
- one
- two
- three
- SQL>
- SELECT regexp_substr('one,two,three','[^,]+', 1, level) abc
- FROM dual
- CONNECT BY regexp_substr('one,two,three', '[^,]+', 1, level) IS NOT NULL
- CREATE OR REPLACE FUNCTION row2col(p_clob_text IN VARCHAR2)
- RETURN sys.dbms_debug_vc2coll PIPELINED
- IS
- next_new_line_indx PLS_INTEGER;
- remaining_text VARCHAR2(20000);
- next_piece_for_piping VARCHAR2(20000);
- BEGIN
- remaining_text := p_clob_text;
- LOOP
- next_new_line_indx := instr(remaining_text, ',');
- next_piece_for_piping :=
- CASE
- WHEN next_new_line_indx <> 0 THEN
- TRIM(SUBSTR(remaining_text, 1, next_new_line_indx-1))
- ELSE
- TRIM(SUBSTR(remaining_text, 1))
- END;
- remaining_text := SUBSTR(remaining_text, next_new_line_indx+1 );
- PIPE ROW(next_piece_for_piping);
- EXIT WHEN next_new_line_indx = 0 OR remaining_text IS NULL;
- END LOOP;
- RETURN;
- END row2col;
- /
- SQL> SET TIMING ON
- SQL>
- SQL> WITH SRC AS (
- 2 SELECT rownum||',a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'||rownum txt
- 3 FROM DUAL
- 4 CONNECT BY LEVEL <=10000
- 5 )
- 6 SELECT NULL
- 7 FROM SRC, TABLE(SYSTEM.row2col(txt)) t
- 8 HAVING MAX(t.column_value) > 'zzz'
- 9 ;
- no rows selected
- Elapsed: 00:00:00.93
- SQL>
- SQL> WITH SRC AS (
- 2 SELECT rownum||',a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'||rownum txt
- 3 FROM DUAL
- 4 CONNECT BY LEVEL <=10000
- 5 )
- 6 SELECT NULL
- 7 FROM SRC, TABLE(splitter(txt)) t
- 8 HAVING MAX(t.column_value) > 'zzz'
- 9 ;
- no rows selected
- Elapsed: 00:00:14.90
- SQL>
- SQL> SET TIMING OFF
- SQL>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement