Re: Like Operator with CLOB datatype

bala wrote:
> Hi Gurus
>
> Scenario
> MS Access Frontend Application with Oracle 8i Backend (linked tables)
>
> Have a Select query with the 'Like' operator on a column with CLOB
> datatype in the WHERE clause. It raises an error that Datatype is
> inconsistent.
>
> It is there any way to make the Like operator work on a column with
> CLOD datatype without converting the column to Varchar2 Datatype.
>
> Also is there any other way apart from using dbms_lob.instr? Since
> these linked tables Stored Procedures or any backend functions can't be
> used.
>
> If using dbms_lob.instr is the only way, can you please give me an
> example. There can be upto three to four column of CLOB datatype in the
> where clause with LIKE operator with other filter conditions.
>
> Thanx in advance
>
> Regards
> Bala

Hi Bala,

If at all possible, I would use passthrough queries from Access to Oracle to actually use Oracle as an RDBMS, as linked tables treat it essentially as a, ISAM database like Access. To confirm this, trace a session where you pass your query criteria in Access against a linked Oracle table. In the Oracle trace, you will never see the query filter, as Access applies it *after* it gets back the entire resultset (full table scan) from Oracle.

If you *have* to use the linked table approach, you could try creating a view in Oracle (still ugly, IMHO), like the following:

create or replace view v1120 as select
dbms_lob.substr(c,dbms_lob.getlength(c)) c from t1120

Select against this linked view in Access (not the table), and you should be able to use your query filter. There may be limitations to this as well, but it's something to research.

HTH, Steve Received on Mon Nov 20 2006 - 09:03:44 CST