Re: Multiple CLOB uploads with SQLLDR

Frank van Bortel <fvanbortel_at_netscape.net> wrote in message news:<co22kf$959$1_at_news1.zwoll1.ov.home.nl>...
> 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.

Oh..wow, that helps and it also worked.. Thanks for the help. i will change the column name.

Do you know what is the max length i can use for char? ofcourse, i am sure I would never find a sequence bigger than 512000.

Thanks alot,
Dina Received on Wed Nov 24 2004 - 21:47:04 CET