SCOTT@orcl_11gR2> -- create extract policy with default rules:
SCOTT@orcl_11gR2> EXEC	CTX_ENTITY.CREATE_EXTRACT_POLICY ('pol1')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- add user-supplied rule to policy
SCOTT@orcl_11gR2> -- to add Canadian provinces and territoreis:
SCOTT@orcl_11gR2> BEGIN
  2    CTX_ENTITY.ADD_EXTRACT_RULE
  3  	  ('pol1',
  4  	   1,
  5  	   '<rule>
  6  	      <expression>(Alberta|British Columbia|Manitoba|New Brunswick|Newfoundland|Labrador|Nova Scotia|Ontario|Prince Edward Island|Quebec|Saskatchewan|Northwest Territories|Nunavut|Yukon)</expression>
  7  	      <type refid="1">state</type>
  8  	    </rule>');
  9  END;
 10  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- compile policy:
SCOTT@orcl_11gR2> EXEC ctx_entity.compile ('pol1')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- create address_typ object:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE address_typ AS OBJECT
  2    (street_address	   VARCHAR2 (23),
  3  	city		   VARCHAR2 (14),
  4  	province_or_state  VARCHAR2 (19),
  5  	zip_code	   VARCHAR2 (20),
  6  	country 	   VARCHAR2 (13));
  7  /

Type created.

SCOTT@orcl_11gR2> -- create function to parse a single address and return address_typ:
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION parse_address
  2    (p_full_address	    IN	VARCHAR2)
  3    RETURN			address_typ
  4  AS
  5    v_entities		CLOB;
  6    v_address		address_typ;
  7  BEGIN
  8    CTX_ENTITY.EXTRACT ('pol1', p_full_address, null, v_entities);
  9    SELECT address_typ
 10  		(null,
 11  		 MAX (DECODE (foo.type, 'city', foo.text)),
 12  		 MAX (DECODE (foo.type, 'state', foo.text)),
 13  		 MAX (DECODE (foo.type, 'zip_code', foo.text)),
 14  		 MAX (DECODE (foo.type, 'country', foo.text)))
 15    INTO   v_address
 16    FROM   (SELECT XMLTYPE (v_entities) ents
 17  	       FROM   DUAL) e,
 18  	      XMLTABLE
 19  		('/entities/entity'
 20  		 PASSING e.ENTS
 21  		 COLUMNS
 22  		 offset      NUMBER	  PATH '@offset',
 23  		 lngth	     NUMBER	  PATH '@length',
 24  		 text	     VARCHAR2(50) PATH 'text/text()',
 25  		 type	     VARCHAR2(50) PATH 'type/text()',
 26  		 source      VARCHAR2(50) PATH '@source') as foo;
 27    v_address.province_or_state :=
 28  	 NVL
 29  	   (v_address.province_or_state,
 30  	    SUBSTR (v_address.zip_code, 1, INSTR (v_address.zip_code, ' ') - 1));
 31    v_address.zip_code := SUBSTR (v_address.zip_code, INSTR (v_address.zip_code, ' ') + 1);
 32    v_address.street_address :=
 33  	 RTRIM
 34  	   (SUBSTR
 35  	     (p_full_address,
 36  	      1,
 37  	      INSTR (p_full_address, v_address.city) - 1));
 38    RETURN v_address;
 39  END parse_address;
 40  /

Function created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> -- example usage of function:
SCOTT@orcl_11gR2> WITH
  2    addresses (full_address) AS
  3  	 (SELECT '1339 Concourse St. Ottawa Ontario Canada' FROM DUAL UNION ALL
  4  	  SELECT '144 - 2600 - 4th Ave SW CALGARY, Alberta Canada' FROM DUAL UNION ALL
  5  	  SELECT '500 Oracle Parkway Redwood Shores, California United States' FROM DUAL)
  6  SELECT t.a.street_address,
  7  	    t.a.city,
  8  	    t.a.province_or_state,
  9  	    t.a.country
 10  FROM   (SELECT parse_address (full_address) a
 11  	     FROM   addresses) t
 12  /

A.STREET_ADDRESS        A.CITY         A.PROVINCE_OR_STATE A.COUNTRY
----------------------- -------------- ------------------- -------------
1339 Concourse St.      Ottawa         Ontario             Canada
144 - 2600 - 4th Ave SW CALGARY        Alberta             Canada
500 Oracle Parkway      Redwood Shores California          United States

3 rows selected.

SCOTT@orcl_11gR2>