great, quick response. Thank you very much.

Hi,

I'm using Oracle version 10.2.0.4.0.

I was searching for how to use a CLOB in an external table. The first thing I found was an article on how to do it, linking a file in the CLOB column and using a COLUMN TRANSFORMS on it.

http://www.oracle-base.com/articles/10g/ExternalTablesContainingLobData.php

Here is the main idea, but I simplified slightly:

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

CREATE TABLE lob_tab (
colid NUMBER(10),
clob_content CLOB
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY temp_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE temp_dir:'data.bad'
LOGFILE temp_dir:'data.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
colid CHAR(10),
clob_filename CHAR(100)
)
COLUMN TRANSFORMS (clob_content FROM LOBFILE (clob_filename) FROM (temp_dir) CLOB)
)
LOCATION ('data.txt')
)
PARALLEL 2
REJECT LIMIT UNLIMITED
/

Content of data.txt:
1,clob_test1.txt
2,clob_test2.txt

Content of clob_test1.txt:
This is a clob test1

Content of clob_test2.txt:
This is a clob test2

I have tried this and it works. I have also tried with a file containing 10K of text for the CLOB column, and it works too.

I understand an sqlloader would be preferable, and I passed the idea to the DBA who is in charge and that deals with the architecture team.

Meanwhile, I would like to know why you are saying it is not possible - or if Oracle does not support such a thing even if it works? Maybe I did not understand correctly what you meant...?

Thank you very much

Hi Tom + Hi Michele

I, too, had found the same URL + tested it.

Yes - you can load a ONE SINGLE CLOB with the transform ONE External file.

However - it is entirely different from trying to map a file which would have a few fields separated from one another and one of them being of a size > 4000 chars - and trying to create as many rows in a table as there are lines within the file.

Even if you split a file (say with a split UNIX cmd) you would be limited to 26x26 = 676 files containing ONE line each - - then more processing + and more ...

The alternative - if you want to use External table at any rate - would be to load the entire file as a CLOB and then invoke a PL/SQL procedure to split the one huge clob into a table with a CLOB column containing the contents of that large (> 4000 bytes) field.

What Tom says - is still true with Oracle 11gR1

Thanks

Kind regards
Andre

Tom, Michele

Sorry - I was too hasty with my former response.

Perhaps this was not working with 10g.

But it does work with 11gR1 +

Initially I got stuck with loading via external table LOB data as I had failed to define the receiving column data type or specified it as CHAR(4000) - as this would reject any lines with more than 4000 chars.

However - I have successfully managed to load ANY size LOB - testing it against a UNIX file with data field of 44k and this had worked perfectly as long as I specified CHAR(50000)

I also saw one of the Oracle forums where someone defined the receiving CHAR(10000000).

Cheers

Andre

Hi Tom,

Thanks for answering questions on Oracle.

I read your response to original question which says external tables do not support LOB's. But it is long time since you answered So asking this query expecting you might come out with some thing new :).

I have files that needs to be uploaded into system, few lines of these files have lakhs of characters in each line due to which I am facing problems using external tables.

To resolve the issue I have used COLUMN TRANSFORM option as below

1. Used dummy source file in table definition which directs to original file with CLOB data.
External Table:

CREATE TABLE ET_RECORDS
(
  NATION_NAME   VARCHAR2(10 BYTE),
  WHOLE_RECORD  CLOB
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY TEST_DIR
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE
       FIELDS terminated by ','
       (  
     nation_name char(10),
     clob_filename char(1000)   )
        COLUMN TRANSFORMS (whole_record FROM LOBFILE (clob_filename) FROM (TEST_DIR) CLOB)
         )
     LOCATION (TEST_DIR:'dummy.txt')
  )
REJECT LIMIT UNLIMITED

2. Dummy.txt contains
europe,data.int

where data.int is my actual data file

My problem is :
Now when I query external table firstly the response(time to execute query) is slow and single record is returned(as expected) column value as HUGECLOB in toad but when I double click it outofmemory exception is thrown.

I have tested same with much lesser data in data.int file and it worked fine. Is there a way to resolve these issues?

2nd problem:
Since entire file is shown as single record now I have written a query to break whole file into multiple records(using newline chr(10)) but again the query did not complete execution even after 1/2 hr(performance issue) . Please suggest changes to query below

with t as (select whole_record as wr from et_and_crh_int_records)
select regexp_substr(wr,'[^'||CHR(10)||']+',1,rownum) wh_rec ,rownum rn
from t connect by rownum <= length(regexp_replace(wr,'[^'||CHR(10)||']'))+1

again - query worked fine for lesser data.

Lastly I think it is worth to inform you that I am already using SQLLDR to load the data but still exploring external tables because

- Once the data is parsed and loaded into next level of tables the data loaded is of no use hence external tables are a better option since they do not occupy space and much easier to use.
- I felt using external tables time will be saved since we need not have to run loader to load data.

Please let me know you opinion.

Thanks,
Imran.

With 2) you may want to switch to a different function. Here's an example how that would affect runtime.

Old Version

SET timing ON

WITH t AS(SELECT listagg(ROWNUM, chr(10)) within group (order by rownum) wr
            FROM dual
          CONNECT BY LEVEL <= 500
          )
 SELECT regexp_substr(wr, '[^' || chr(10) || ']+', 1, ROWNUM) wh_rec
       ,ROWNUM rn
   FROM t
 CONNECT BY rownum <= LENGTH(regexp_replace(wr,'[^'||CHR(10)||']'))+1
;

Elapsed: 00:00:01.375

New version

SET timing ON

WITH t AS(SELECT listagg(ROWNUM, chr(10)) within group (order by rownum) wr
            FROM dual
          CONNECT BY LEVEL <= 500
          )
SELECT substr(wr, pos_start, instr(wr || chr(10), chr(10), pos_start + 1) - pos_start) wh_rec
  FROM (
SELECT t.wr,
       instr(chr(10) || t.wr, chr(10), 1, ROWNUM) pos_start
  FROM t 
 CONNECT BY ROWNUM <= length(t.wr) - length(replace(t.wr, chr(10))) + 1 
       )
 ;

Elapsed: 00:00:00.033

I ran those SQL statements a couple of times, so the numbers seem to reflect the load. Looks like regex does take some time.

Yeah!!

Thanks Tom once again for showing me the right direction!!

The bulk data processing logic in the link you provided helped. As suggested I made few changes accordingly to bulk data logic for newline char[chr(10) and chr(13)]. Later successfully tested the new written function which took around 55mins Vs earlier SQLLDR+Parsing Logic together took more than 2hr for same data. I hit right solution I hope.

Though while implementing this function I was also exploring PREPROCESSOR option using which I tried splitting lines in file which are greater than 4000 characters and later join them while reading from ET table but was unable to do so since I was getting error(s) with use of FOLD command(unix).

Error: KUP-04095: preprocessor command /home/oracle/Test_Dir/filename_1.sh encountered error "/home/oracle/Test_Dir/filename_1.sh: line 6: fold: No such file or

The logic in shell script I used was:

#!/bin/sh
for FILE in $*
do
#/bin/awk '{$0 = FILENAME "|" $0; print}' $FILE|fold -w 4000
done

I though may be you could improvise this?

dc, thank you for the suggestion. I have tried query provided by you as well but was facing same performance issue, query did not fetch the results even after 1hr. My data file has around 7 million records.
Thanks,
Imran.