Re: Multiple CLOB uploads with SQLLDR

Dinanath wrote:
> Hi,
> I want to upload a protein sequence (read it as a string) which is
> sometimes greater than 4000 characters into a clob field in a table.
>
> create table sequences
> (
> ID NUMBER NOT NULL,
> SEQUENCE CLOB NOT NULL,
> );
>
> The values are comma seperated inside a file:
> Eg: sequence.txt
> 123,asdflkdflsdfsdlfksdlfkasdlsdflf.....(more than 4000)
> 2342,lkjkakdkfl...>4000
> 3234,alkldklakdf...
>
> And there are about 2 million rows of data to be uploaded.
>
> Could someone give me an efficient way of uploading this data. Could
> anyone give me a syntax of the control file to upload this data?
> Currently i am using the insert commands inside a perl script to
> upload this data but it takes hours to do this.
>
> Most of the examples that i saw here are for uploading some lob or
> clob from a file (one clob entry per file)!
>
> Please help..
> Thanks,
> Dina

Apart from the fact you column name is bound to get you into trouble sooner or later, as sequence is a reserved word in Oracle, you can load these using SQL*Loader.

Depending on the size of the sequences, you may need to override the size in the control file; I have used char(128000) and char(512000) quite happily.

-- 

Regards,
Frank van Bortel