Hi Thomas,
A couple of suggestions :
Firstly, with regard to LongReadLen, the simplest solution is to perform
an extra fetch *prior* to your existing one. For example, if your
existing fetch is something like this :
select col1, col2, ... clob_col
from some_table
where some_where_clause
The 'pre-fetch' would be :
select max (length (clob_col))
from some_table
where some_where_clause
Fetch the value into some local variable (say $long_read_len), and then
issue :
$dbh->{'LongReadLen'} = $long_read_len
Prior to preparing your 'original' statement.
However, if feasible, a much more efficient solution is to use Oracle to
do the comparison. You will need a database link in one DB so that it
can 'see' the other database, and then it is simply a matter of :
select a.*
from local_table a, [EMAIL PROTECTED] b
where ((a.col1 != b.col1)
or (a.col1 is null
and b.col1 is null))
or ((a.col2 != b.col2)
or (a.col2 is null
and b.col2 is null))
:
:
This will run many times faster than your current approach. If your
CLOB's are HUGE (several GB or bigger), it may be that the first
approach is simply not practical, and you need to consider the second.
Hope this helps,
Steve
> -----Original Message-----
> From: Thomas Porschberg [mailto:[EMAIL PROTECTED]
> Sent: Monday, 12 January 2004 7:32 PM
> To: [EMAIL PROTECTED]
> Subject: CLOB question
>
>
> Hi,
> I wrote a script which does compare the table content of a
> certain table on different ORACLE-9i databases. For this
> purpose I fetch all fields from the table in database one and
> include the fetched values in the WHERE clause for execute
> the statement at database two. For numeric/char/varchar2
> fields there are no problems. However there are some tables
> with CLOB fields. The first problem is that following binding
> in the WHERE clause does not work: WHERE my_clob_field =
> :value_from_database_1
>
> Surprisingly the LIKE clause works for CLOB contents < 4kByte.
> A transitional solution up to around 4kByte is to write
> WHERE my_clob_field LIKE ':value_from_database_1'.
>
> My questions:
> - How can I get over this limit ?
>
> I also had noticed that I had to specify a certain value for
> $dbh->{LongReadLen}.
> If the content of the database CLOB field is larger than the
> specified value the fetch process fails.
> Because CLOB fields can grow up to 4 GByte and I don't know
> the largest content how can I avoid such a hard coded limit
> for $dbh->{LongReadLen} ?
>
> I hope the questions are not too much Oracle specific and
> belong to DBI/DBD::Oracle.
>
> Thomas
>
>
> --
> Bitte senden Sie mir keine Word- oder PowerPoint-Anh�nge.
> See http://www.fsf.org/philosophy/no-word-attachments.de.html
> -------------------------------------------------------------
> NO ePATENTS: http://swpat.ffii.org/index.de.html
>
