Solución:
Hay apex_util.string_to_table
– vea mi respuesta a esta pregunta.
Además, antes de la existencia de la función anterior, una vez publiqué una solución aquí en mi blog.
Actualizar
En versiones posteriores de APEX, apex_util.string_to_table
está en desuso y se prefiere una función similar apex_string.split.
Si APEX_UTIL
no está disponible, tienes una solución usando REGEXP_SUBSTR()
.
Inspirado en http://nuijten.blogspot.fr/2009/07/splitting-comma-delimited-string-regexp.html:
DECLARE
I INTEGER;
TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
MY_ARRAY T_ARRAY_OF_VARCHAR;
MY_STRING VARCHAR2(2000) := '123,456,abc,def';
BEGIN
FOR CURRENT_ROW IN (
with test as
(select MY_STRING from dual)
select regexp_substr(MY_STRING, '[^,]+', 1, rownum) SPLIT
from test
connect by level <= length (regexp_replace(MY_STRING, '[^,]+')) + 1)
LOOP
DBMS_OUTPUT.PUT_LINE(CURRENT_ROW.SPLIT);
MY_ARRAY(MY_ARRAY.COUNT) := CURRENT_ROW.SPLIT;
END LOOP;
END;
/
Tienes que enrollar el tuyo. P.ej,
/* from :http://www.builderau.com.au/architect/database/soa/Create-functions-to-join-and-split-strings-in-Oracle/0,339024547,339129882,00.htm
select split('foo,bar,zoo') from dual;
select * from table(split('foo,bar,zoo'));
pipelined function is SQL only (no PL/SQL !)
*/
create or replace type split_tbl as table of varchar2(32767);
/
show errors
create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
show errors;
/* An own implementation. */
create or replace function split2(
list in varchar2,
delimiter in varchar2 default ','
) return split_tbl as
splitted split_tbl := split_tbl();
i pls_integer := 0;
list_ varchar2(32767) := list;
begin
loop
i := instr(list_, delimiter);
if i > 0 then
splitted.extend(1);
splitted(splitted.last) := substr(list_, 1, i - 1);
list_ := substr(list_, i + length(delimiter));
else
splitted.extend(1);
splitted(splitted.last) := list_;
return splitted;
end if;
end loop;
end;
/
show errors
declare
got split_tbl;
procedure print(tbl in split_tbl) as
begin
for i in tbl.first .. tbl.last loop
dbms_output.put_line(i || ' = ' || tbl(i));
end loop;
end;
begin
got := split2('foo,bar,zoo');
print(got);
print(split2('1 2 3 4 5', ' '));
end;
/
¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)