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

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