Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> questions about regular expression in pro*c

questions about regular expression in pro*c

From: <ouyang.jie_at_gmail.com>
Date: Thu, 04 Oct 2007 17:25:11 -0000
Message-ID: <1191518711.022507.118430@50g2000hsm.googlegroups.com>


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:

Any idea? Please help. Received on Thu Oct 04 2007 - 12:25:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US