Dinanath wrote:
> 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
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_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sun Nov 28 2004 - 12:49:46 CST
