dbms_lob.instr() call problem from within Pro*C

I am trying to do a pattern search of an Oracle LOB from with in Pro*C by calling dbms_lob.instr()

The function works ok if the pattern is hardcoded i.e.

EXEC SQL AT :pc_dbAlias

       DECLARE RefAuthorCursor CURSOR FOR
           SELECT
             c.accession
	       FROM PUBLICATION a, DBENTRY_2_PUBLICATION b, DBENTRY c
	       WHERE (dbms_lob.instr(authors, 'Asano') ) > 0)
	       AND a.publication_id = b.publication_id
	       AND b.dbentry_id = c.dbentry_id
             and rownum<4;

However the same query fails to return any rows if a variable containing the search pattern is used instead i.e.

EXEC SQL BEGIN DECLARE SECTION; const char* pc_searchString = _strToSearch.c_str();

EXEC SQL END DECLARE SECTION; EXEC SQL AT :pc_dbAlias

       DECLARE RefAuthorCursor CURSOR FOR
           SELECT
             c.accession
	       FROM PUBLICATION a, DBENTRY_2_PUBLICATION b, DBENTRY c
	       WHERE (dbms_lob.instr(authors, :pc_searchString) ) > 0)
	       AND a.publication_id = b.publication_id
	       AND b.dbentry_id = c.dbentry_id
             and rownum<4;

Does anyone know what the problem might be?

Any help would be greatly appreciated.

Thanks

Alastair Received on Fri Oct 15 2004 - 06:30:54 CDT