如何使用 PL/SQL 从 Oracle 中的 Clob 列读取 CSV 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7842025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read CSV data from a Clob column in Oracle using PL/SQL
提问by user1006030
I'm getting a clob as parameter from a procedure and it contains a CSV file. I need to read this data and load it into another Oracle table.
我从一个过程中获取一个 clob 作为参数，它包含一个 CSV 文件。我需要读取这些数据并将其加载到另一个 Oracle 表中。
Could someone please explain how to do this.
有人可以解释一下如何做到这一点。
回答by user272735
AFAIK Oracle has no ready made goodies for this. One promising candidate is DBMS_UTILITY.COMMA_TO_TABLE, but it's heavily limitedto a very special task making it no-option. So you have to roll your sleeves and make your own.
AFAIK Oracle 没有现成的好东西。一个很有前途的候选者是DBMS_UTILITY.COMMA_TO_TABLE，但它严重受限于一项非常特殊的任务，因此没有选择。所以你必须卷起袖子，自己做。
Your specification is a bit vague, but one option is a SPLITfunction:
您的规范有点含糊，但一个选项是SPLIT函数：
create table so18t (
id number,
csv clob
);
insert all
into so18t values(1,'1,2,3'||chr(10)||
'40,5,6'||chr(10)||
'700,80,9'||chr(10))
into so18t values(2,'aaa,bbb,ccc'||chr(10)||
'ddd,eee,fff'||chr(10)||
'ggg,hhh,iii'||chr(10))
select 1 from dual;
declare
v_lines jh_util.stringlist_t;
v_values jh_util.stringlist_t;
begin
for rec in (select * from so18t order by id) loop
v_lines := jh_util.split(rec.csv, chr(10));
for i in v_lines.first .. v_lines.last loop
dbms_output.put_line('line ' || i || ':');
v_values := jh_util.split(v_lines(i));
/* Do what you want with the values - I just print them */
for j in v_values.first .. v_values.last loop
dbms_output.put_line('v_values(' || j || ') = ' || v_values(j));
end loop;
end loop;
end loop;
end;
/
show errors
Prints:
印刷：
line 1:
v_values(1) = 1
v_values(2) = 2
v_values(3) = 3
line 2:
v_values(1) = 40
v_values(2) = 5
v_values(3) = 6
line 3:
v_values(1) = 700
v_values(2) = 80
v_values(3) = 9
line 1:
v_values(1) = aaa
v_values(2) = bbb
v_values(3) = ccc
line 2:
v_values(1) = ddd
v_values(2) = eee
v_values(3) = fff
line 3:
v_values(1) = ggg
v_values(2) = hhh
v_values(3) = iii
PL/SQL procedure successfully completed.
Of cource Oracle doesn't provide split but SO helps. In the example above I have used my own one.
当然 Oracle 不提供拆分，但SO 有帮助。在上面的例子中，我使用了我自己的一个。
Other interesting resources:
其他有趣的资源：
回答by DwB
Do not export the data to a file.
不要将数据导出到文件。
You will need to convert the clob into something useful, parse it, then write to the other table. Here is the steps you need to do:
您需要将 clob 转换为有用的东西，解析它，然后写入另一个表。以下是您需要执行的步骤：
- Convert the Clob from a Clob to a something useful. CLOB.getCharacterStream() seems useful.
- Parse the CSV data from the converted Clob object.
CSVReader reader = new CSVReader(the_reader_from_getCharacterStream);ftw - Store the desired data in the other table.
- 将 Clob 从 Clob 转换为有用的东西。CLOB.getCharacterStream() 似乎很有用。
- 从转换后的 Clob 对象解析 CSV 数据。
CSVReader reader = new CSVReader(the_reader_from_getCharacterStream);ftw - 将所需数据存储在另一个表中。
Oracle's CLOB Objectprovides some useful methods.CSVReaderis from Open CSV.
Oracle 的 CLOB 对象提供了一些有用的方法。CSVReader来自Open CSV。
回答by Burhan Ali
I don't know of an immediate way of parsing a clob as a CSV, but Oracle provides a number of tools for working with CSV filessuch as External Tablesand SQL*Loader.
我不知道将 clob 解析为 CSV 的直接方法，但 Oracle 提供了许多用于处理 CSV文件的工具，例如External Tables和SQL*Loader。
So an approach might be to:
因此，一种方法可能是：
