How to read CSV data from a Clob column in Oracle using PL/SQL?

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: 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 Oracle's CLOB Object provides some useful methods CSVReader is from Open CSV.

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: 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.

Oracle's CLOB Object provides some useful methods. CSVReader is from Open CSV.

AFAIK Oracle has no ready made goodies for this. One promising candidate is DBMS_UTILITY. COMMA_TO_TABLE, but it's heavily limited to a very special task making it no-option.So you have to roll your sleeves and make your own.

Your specification is a bit vague, but one option is a SPLIT function: 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. Other interesting resources: Parsing CSV string CSV - comma separated values - and PL/SQL Converting delimited lists to collections (and vice versa) Ask Tom "DBMS_UTILITY.

COMMA_TO_TABLE limitations?

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 files such as External Tables and SQL*Loader. So an approach might be to: Export the clob as a file using DBMS_LOB and UTL_FILE Load the file using one of the CSV tools mentioned above.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.