Home » SQL & PL/SQL » SQL & PL/SQL » Import CSV in clob to table (11g)
Import CSV in clob to table [message #563281] Fri, 10 August 2012 15:42 Go to next message
ufzc3mj8
Messages: 3
Registered: August 2012
Location: United States
Junior Member
Hi all,

I'm trying to import data from a csv file format which is located in a CLOB column in a single record in the database. I want to import the data that is contained in this CLOB into a table. I am having limited success using JH_UTIL. Here's the script that I am running (which works):

set serveroutput on;
declare
  v_lines jh_util.stringlist_t;
  v_values jh_util.stringlist_t;
begin
for rec in (select 1 id, ac.clob_content csv 
FROM PSM_ASSET a INNER JOIN PSM_ASSETCONTENT ac ON ac.asset_id = a.id  
WHERE name = 'teachercommentupload1.txt') 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), chr(9)); --Yes, I know this is a tab CHR and not CMA
      /* I will eventually insert the values into the table */
      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;


The problem is when the file gets too big, I get a the following error:

Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 6
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:


I assume this means because the file size is too big. Is there any way to process larger "files" (CLOB data)

Thanks in advance for any assistance you can provide.

[Updated on: Sat, 11 August 2012 00:27] by Moderator

Report message to a moderator

Re: Import CSV in clob to table [message #563282 is a reply to message #563281] Fri, 10 August 2012 15:50 Go to previous messageGo to next message
w0rtez
Messages: 6
Registered: August 2012
Junior Member
Why dont use External Tables... Search in Google for External Tables you will get plenty examples...
Re: Import CSV in clob to table [message #563284 is a reply to message #563281] Fri, 10 August 2012 21:54 Go to previous messageGo to next message
ufzc3mj8
Messages: 3
Registered: August 2012
Location: United States
Junior Member
Hi w0rtez,

Thank you for the quick response. The problem is the "file" does not exist on the server at all, and there is no way for me to get the file other than through this clob column. It seems that the external table examples are loading a file that is located on the server itself. If there is a way for me to export that clob column to a flat file, then process it this way, then I'm all for that, but I was unable to find a method to process this clob column which could be up to 12 or 15MB in size.
Re: Import CSV in clob to table [message #563285 is a reply to message #563284] Fri, 10 August 2012 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
SQL*Loader (sqlldr) can load files from client system into remote DB

http://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Import CSV in clob to table [message #563304 is a reply to message #563284] Sat, 11 August 2012 18:30 Go to previous messageGo to next message
w0rtez
Messages: 6
Registered: August 2012
Junior Member
ufzc3mj8 wrote on Fri, 10 August 2012 21:54
Hi w0rtez,

Thank you for the quick response. The problem is the "file" does not exist on the server at all, and there is no way for me to get the file other than through this clob column. It seems that the external table examples are loading a file that is located on the server itself. If there is a way for me to export that clob column to a flat file, then process it this way, then I'm all for that, but I was unable to find a method to process this clob column which could be up to 12 or 15MB in size.


of course... you just have to save it using:
DBMS_XSLPROCESSOR.clob2file(
cl => l_clob
, flocation => 'flocation'
, fname => myfile_name
);

Then you process it with external tables... Razz
Re: Import CSV in clob to table [message #563392 is a reply to message #563304] Mon, 13 August 2012 08:00 Go to previous messageGo to next message
ufzc3mj8
Messages: 3
Registered: August 2012
Location: United States
Junior Member
Hi w0rtez,

Thank you very much for your help. I wasn't able to use clob2file because of buffer limitations but I did find a great procedure out there to get the job done. Here is my final code to dump the clob to a file on the server:

set serveroutput on;

CREATE OR REPLACE DIRECTORY DIRECTORY AS 'C:\';

DECLARE
 l_clob CLOB;
BEGIN
  SELECT ac.clob_content INTO l_clob
  FROM PSM_ASSET a
  INNER JOIN PSM_ASSETCONTENT ac ON ac.asset_id = a.id
  WHERE name = 'file.txt';
  
  dpr_clobToFile('file.txt','DIRECTORY',l_clob);
  
END;


The dpr_clobToFile procedure loops through the clob in 32k chunks and writes a single file. Performance was good, too.
Re: Import CSV in clob to table [message #563410 is a reply to message #563392] Mon, 13 August 2012 10:39 Go to previous message
w0rtez
Messages: 6
Registered: August 2012
Junior Member
Glad i could help, now you just need to process your data with External Tables and done... Razz

[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Mon, 13 August 2012 14:09] by Moderator

Report message to a moderator

Previous Topic: delete and insert at same time [merged 2 by jd]
Next Topic: INSTR
Goto Forum:
  


Current Time: Tue Sep 02 03:08:53 CDT 2014

Total time taken to generate the page: 0.08567 seconds