如何将csv转换为oracle中的表 [英] how to convert csv to table in oracle

问题描述

当传入csv值时，如何制作一个以表格式返回结果的包.

How can I make a package that returns results in table format when passed in csv values.

select * from table(schema.mypackage.myfunction('one, two, three'))

应该返回

one
two
three

我尝试了询问tom ，但仅适用于sql类型.

I tried something from ask tom but that only works with sql types.

我正在使用oracle 11g.有内置的东西吗?

I am using oracle 11g. Is there something built-in?

推荐答案

以下作品 调用为 从表中选择*(splitter('a，b，c，d'))

The following works invoke it as select * from table(splitter('a,b,c,d'))

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;
/

这篇关于如何将csv转换为oracle中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！