Good afternoon,

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' );
.................................................. .................................................. .........................
1
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.

Best regards,
Brian St. John

Brian StJohn <oneiromancer.org (AT) gmail (DOT) com> wrote:
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?

a) avoid REGEXP_LIKE
b) 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

Brian StJohn <oneiromancer.org (AT) gmail (DOT) com> wrote:
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?

a) avoid REGEXP_LIKE
b) 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

Brian StJohn <oneiromancer.org (AT) gmail (DOT) com> wrote:
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?

a) avoid REGEXP_LIKE
b) 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

Brian StJohn <oneiromancer.org (AT) gmail (DOT) com> wrote:
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?

a) avoid REGEXP_LIKE
b) 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