Dennis,
You simply need to ensure you set the database handle attribute -
LongReadLen to a value big enough to read the LOB in one go. What I
usually do is to issue an initial SELECT to obtain the max length of a
LOB I'm about to fetch, use that value to set LongReadLen, and then
execute the statement to fetch the actual LOB value.
For example, if your current statement is something like this :
SELECT non_lob_col_1, non_lob_col_2, ..., lob_col_1, lob_col_2
FROM some_table
WHERE some_condition
You would execute this statement first :
SELECT GREATEST (MAX (LENGTH (lob_col_1)), MAX (LENGTH (lob_col_2)))
FROM some_table
WHERE some_condition
The value you get back from this select (say $max_lob) you would use
like this :
$dbh->{'LongReadLen'} = $max_lob
Then, you *prepare*, execute, and fetch your original statement, and you
will have all your LOB data.
A word of caution - if your LOB's are large, make sure you set the
RowCacheSize attribute to 1 otherwise your memory usage will go through
the roof. Also, settings for LongReadLen (and RowCacheSize for that
matter) are only referenced by DBD::Oracle before calling ->prepare.
I'm pretty sure this stuff is covered in the DBI docs (perldoc DBI), but
I hope this helps.
Steve
P.S.
I'm sure you meant DBD::Oracle not DBI::Oracle ;-)
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 26 February 2004 1:29 PM
> To: [EMAIL PROTECTED]
> Subject: Reading BLOBs DBI::Oracle
>
>
>
>
>
>
> I am sure this has been covered but I am a newbie to both
> DBI::Oracle and BLOBs. I have done a few Google searches and
> have found several useful code example on how to write to a
> BLOB datatype but nothing that shows me a technique for
> reading BLOB data and storing it into a file.
>
> What I have are several BLOB columns containing XML code that
> has been zipped. I want to retrieve the BLOB and store it in
> a file to be unzipped and then compared to the original for testing.
>
> --------------------------------------------------------------
> ------------------------
> Important Warning: If you have received this email in
> error, please advise the sender and delete the message and
> attachments immediately. �This email, including attachments,
> may contain confidential, legally privileged and/or copyright
> information, the unauthorised use of which is prohibited. Any
> views expressed in this email are those of the individual
> sender, except where the sender expressly, and with
> authority, states them to be the view of the Department of
> Immigration and Multicultural and Indigenous Affairs (DIMIA).
> �DIMIA respects your privacy and has obligations under the
> Privacy Act 1988 (see www.immi.gov.au).
> --------------------------------------------------------------
> ------------------------
>
