Re: questions about regular expression in pro*c

On Thu, 04 Oct 2007 17:25:11 -0000, ouyang.jie_at_gmail.com wrote:

>I have the following section of code in the pro*c program
>
>exec sql declare provLicExpCursor cursor for
>select unique hblic.num_prov_lic,
> hblic.dte_end,
> decode(nam.ind_name_type,
> 'P', (rtrim(substr(nam.name, 26, 13)) || ' ' ||
> rtrim(substr(nam.name, 39, 1)) || ' ' ||
> rtrim(substr(nam.name, 1, 25)) || ' ' ||
> rtrim(nam.nam_title) ),
> 'B', nam.name ) fmt,
> pradr.adr_mail_strt1,
> pradr.adr_mail_strt2,
> pradr.adr_mail_city,
> pradr.adr_mail_state,
> pradr.adr_mail_zip,
> nvl(trim(pradr.adr_mail_zip_4), '0000'),
> pradr.adr_email,
> prid.id_provider
>from t_pr_hb_lic hblic,
> t_pr_type prtype,
> t_pr_loc_nm_adr nmadr,
> t_pr_adr pradr,
> t_pr_nam nam,
> t_pr_identifier prid
>where hblic.dte_end = :date_30days
> and hblic.num_prov_lic = prtype.num_prov_lic
> and prtype.sak_prov_loc = nmadr.sak_prov_loc
> and prtype.sak_prov = nmadr.sak_prov
> and nmadr.sak_prov = pradr.sak_prov
> and nmadr.sak_short_address = pradr.sak_short_address
> and REGEXP_LIKE (pradr.adr_email,'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+
>\.[a-zA-Z]{2,4}$')
> and nmadr.sak_prov = nam.sak_prov
> and nmadr.sak_short_name = nam.sak_short_name
> and nmadr.ind_addr_type = 'M'
> and nmadr.sak_prov = prid.sak_prov
> and nmadr.sak_prov_loc = prid.sak_prov_loc
> and prid.cde_prov_id_type = 'MCD'
>;
>
>The REGEXP_LIKE clause does not pass compilation while it can be run
>in sql developer. The error message looks like this:
>
>Syntax error at line 74, column 4, file prvp450d.sc:
>Error at line 74, column 4 in file prvp450d.sc
> and nmadr.sak_prov = pradr.sak_prov
>...1
>PCC-S-02201, Encountered the symbol "and" when expecting one of the
>following:
>
> = * < > + - / ^= | != <= >= <> at, not, between, in, is,
> like, day, hour, minute, month, second, year,
>
>Any idea? Please help.

Versions are as usual important (up to 4 digits please). Not all versions of Oracle support regexp function. Either your version doesn't support it, or the value of the compatible parameter in your database is too low. Maybe sql developer ignores this parameter.

-- 

Sybrand Bakker
Senior Oracle DBA