I was googling for this problem, I came accross few posting where the string that i matching is large and if there is an alternation in the regex, it would cause stack over flow exception.
Here is the SQL i am using:
SELECT
P4.NAME REGION,
P3.NAME AREA,
P2.NAME AS MARKET,
P1.NAME AS CHANNEL,
SR.NO AS SR_NUMBER,
SR.NAME AS SR_NAME,
SR.ID SRID,
P.NO SITE_NUMBER,
P.NAME SITE_NAME,
P.ID SITEID,
S.NAME AS STATUS_NAME,
T.NAME AS TYPE_NAME,
AD.STREET AS ADDRESS,
AD.CITY_NAME AS CITY_NAME,
AD.STATE_NAME AS STATE_NAME,
AD.ZIP_CODE AS ZIP_CODE,
AD.LATITUDE_COORDINATE AS LATITUDE,
AD.LONGITUDE_COORDINATE AS LONGITUDE,
AD.COUNTRY_NAME AS COUNTRY_NAME,
AD.COUNTY AS COUNTY_NAME,
TEXT17 AS POINT_TO_POINT_TYPE,
A_STR.TEXT1 AS POINT_TO_POINT_DATA,
(select blah.id from blah where name = P4.NAME)
Test,
TEXT18 AS FACILITY_TYPE,
BOOLEAN1 AS LAND_LEASED,
BOOLEAN2 AS TOWER_LEASED,
BOOLEAN3 AS BUILDING_LEASED,
BOOLEAN4 AS SPECTRUM_LEASED,
TEXT2 AS LEGACY_SITE_ID
FROM
GEMINI.PRP_PROPERTIES P,
GEMINI.PRP_PROPERTIES_EXT_STRING PES,
GEMINI.PRP_PROPERTIES_EXT_DATE PED,
GEMINI.PRP_PROPERTIES_EXT_BOOLEAN PEB,
GEMINI.PRP_PROPERTIES_EXT_NUMBER PEN,
GEMINI.PRP_PROPERTY_STATUSES S,
GEMINI.PRP_PROPERTY_TYPES T,
GEMINI.GEN_ADDRESSES AD,
GEMINI.PRP_PROPERTIES_ORG_UNITS SP,
GEMINI.HUR_ORGANIZATION_UNITS P1,
GEMINI.HUR_ORGANIZATION_UNITS P2,
GEMINI.HUR_ORGANIZATION_UNITS P3,
GEMINI.SCR_SEARCH_RINGS SR
WHERE A_STR.ID = P.ID
AND A_BOOL.ID = P.ID
AND S.ID = P.STATUS_ID
AND T.ID = P.TYPE_ID
AND AD.ID = P.PRIMARY_ADDRESS_ID
AND SR.ID = P.SEARCH_RING_ID
AND SP.PROPERTY_ID (+) = P.ID
AND P1.ID (+) = SP.ORGANIZATION_UNIT_ID
AND P2.ID (+) = P1.PARENT_ORGANIZATION_UNIT_ID
AND P3.ID (+) = P2.PARENT_ORGANIZATION_UNIT_ID
AND P4.ID (+) = P3.PARENT_ORGANIZATION_UNIT_ID
AND P.IS_DELETED = 0
AND P.DOMAIN_ID = <%DOMAIN_ID%>