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

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

Frank van Bortel <fvanbortel@netscape.net> wrote in message news:<co22kf$959$1@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

Dinanath wrote:
> Frank van Bortel <fvanbortel@netscape.net> wrote in message news:<co22kf$959$1@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

You might also note that ID is also a reserved word in Oracle.

A good statement to keep handy is:
SELECT keyword
FROM v$reserved_words
WHERE keyword LIKE '% <a few upper case letters here> %';

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)