CODEWITHSUNDEEP
sqloracle-databasecsvplsql
user1006030
user1006030

Reputation: 1

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

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.

Could someone please explain how to do this.

Upvotes: 0

Views: 14228

Answers (3)

user272735
user272735

Reputation: 10648

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:

Upvotes: 3

DwB
DwB

Reputation: 38300

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:

  1. Convert the Clob from a Clob to a something useful. CLOB.getCharacterStream() seems useful.
  2. Parse the CSV data from the converted Clob object. CSVReader reader = new CSVReader(the_reader_from_getCharacterStream); ftw
  3. Store the desired data in the other table.

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

Upvotes: 2

Burhan Ali
Burhan Ali

Reputation: 2229

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:

  1. Export the clob as a file using DBMS_LOB and UTL_FILE
  2. Load the file using one of the CSV tools mentioned above.

Upvotes: 0

Related Questions