Re: Oracle pro*c compiler error using REGEXP_LIKE
Date: 04 Nov 2008 09:18:04 GMT
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/
> 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
> = * < > + - / ^= | != <= >= <> 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.
"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?
- avoid REGEXP_LIKE
- 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;
Laurenz Albe Received on Tue Nov 04 2008 - 03:18:04 CST