Re: Oracle pro*c compiler error using REGEXP_LIKE

Brian StJohn <oneiromancer.org_at_gmail.com> wrote:
> I have a c/c++ api into some oracle database tables. I'm trying to
> perform a bulk cursor select using the REGEXP_LIKE function, but I am
> getting an interesting pre-compiler problem:
>
> Pro*C/C++: Release 10.2.0.1.0 - Production on Mon Nov 3 14:43:28 2008
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
> System default option values taken from: /opt/oracle/product/10.2.0/
> precomp/admin/pcscfg.cfg
>
> Syntax error at line 133, column 125, file xcd.pc:
> Error at line 133, column 125 in file xcd.pc
> EXEC SQL DECLARE xdcdcsrl CURSOR FOR SELECT doc_id FROM xcd WHERE
> REGEXP_LIKE( tc, 'elect', 'i' );

> PCC-S-02201, Encountered the symbol ";" when expecting one of the
> following:
>
> = * < > + - / ^= | != <= >= <> at, not, between, in, is,
> like, day, hour, minute, month, second, year
>
> But interestingly enough I can run the command in SQL plus and it
> works as expected. Does anyone have any suggestions for me? I'm
> running Oracle 10.2.0.

I assume that this is the problem described in Metalink Note 283146.1. Basically, the parser of Pro*C in Oracle versions before 11 is a different parser from the one in the SQL engine, and development did not keep pace with recent SQL features.

Quote:
"This means that the Precompiler's before Oracle11g PL/SQL Parser engine  is limited to 8.0 and some 8i syntax."
(the weird wording is Oracle's and not mine)

REGEXP_LIKE is a new feature, and I guess the parser does not know it.

What can you do?

1. avoid REGEXP_LIKE
2. use dynamic SQL like this:

EXEC SQL PREPARE astmt FROM

     "SELECT doc_id FROM xcd WHERE REGEXP_LIKE(tc, 'elect', 'i')"; EXEC SQL DECLARE xdcdcsrl CURSOR FOR astmt;

Yours,
Laurenz Albe Received on Tue Nov 04 2008 - 03:18:04 CST