[Bioperl-l] need help urgently

[Jonathan Crabtree]

Hi Neeti-

neeti somaiya wrote:
> We are using BioPerl to parse a BLAST output file, and then we want to load
> full alignments into a CLOB column in one of our database tables. We are
> trying to use sql loader for the same. Anyone has an idea how we can go
> about it?
>   

This doesn't sound like a BioPerl issue per se, so this list might not
be the best venue for your question.  Since SQL*Loader is an Oracle
utility you may have better luck in a forum frequented by Oracle DBAs
and/or general bioinformatics people.  (Not that this isn't such a
forum, but unless your difficulty is actually being caused by BioPerl,
or there's some kind of SQL*Loader wrapper in BioPerl--which I don't
think is the case--you run the risk of having people complain that your
question doesn't have enough to do with BioPerl.)

> We have tried loading sequences into CLOB columns using sql loader, and that
> works fine, but the same syntax when used for loading alignments, is not
> working.
>   

It's been a while since I've done any work with SQL*Loader, but I'd
guess that the reason it works with sequences and not alignments is
because there are characters in the alignments (newlines, perhaps?) that
SQL*Loader is incorrectly interpreting as either column (field) or row
(record) delimiters.  You may need to change your flat file encoding to
use delimiters other than the defaults (and alter the SQL*Loader control
file accordingly.)  As Sean pointed out, however, it's difficult to be
much help without seeing an example of a failed input and the
corresponding error(s)!  One other thing I remember about SQL*Loader (as
of Oracle 8-9 or so) is that all the CLOB values had to appear *last* in
the SQL*Loader record, at least if you were using variable-length
fields.  But since you've loaded sequences successfully, I doubt this is
the issue.  One final thought is that I believe SQL*Loader has an option
whereby you can place your LOB values in files external to the main
SQL*Loader input file, which sidesteps the field/row delimiter issue
completely; you may want to look into this if you're not already loading
your Oracle database this way.

Jonathan