using a clob in a where clause

db ver 9.2.0.5
os AIX 5.2.0.0

I would like to use a clob in a where clause. Basicly it would look like:

select count(a.REPORT_EXECUTION_REQUEST_ID) from cp_rep_exe_fil a, res_report_execution_filter b where a.REPORT_EXECUTION_REQUEST_ID = b.REPORT_EXECUTION_REQUEST_ID and a.FILTER_NM = b.FILTER_NM
and a.REPORT_EXPRESSION_DE = b.REPORT_EXPRESSION_DE -- here's really what I would do if it was a varchar2 and a.REPORT_EXPRESSION_TX = ' '

here's what the tables look like
CREATE TABLE CP_REP_EXE_FIL (

  REPORT_EXECUTION_REQUEST_ID  NUMBER (19)   NOT NULL,
  FILTER_NM                    VARCHAR2 (80),
  REPORT_EXPRESSION_DE         VARCHAR2 (4000),
  REPORT_EXPRESSION_TX         CLOB          NOT NULL)

CREATE TABLE REPORT_EXECUTION_FILTER (

  REPORT_EXECUTION_REQUEST_ID  NUMBER (19)   NOT NULL,
  FILTER_NM                    VARCHAR2 (80),
  REPORT_EXPRESSION_TX         VARCHAR2 (4000)  NOT NULL,
  REPORT_EXPRESSION_DE         VARCHAR2 (4000))

The examples I have looked at all are to simple. They show a PL/SQL block with a DBMS_OUTPUT.put_line. I see I need to use the READ procedure of DBMS_LOB. Just not quite sure how to do it without do a PL/SQL block.

My question is there a way to put the DBMS_LOB.read in the where clause as I have outlined above.

thank you
Ox Received on Wed Apr 27 2005 - 14:11:33 CDT