problem in finding pattern in regular expression in clob column

I have a problem with regex in oracle.
I am trying to detect the pattern in the text below
Suspect(s) detected by OFAC-Agent:25 
SystemId: 
Associate: 
=============================
Suspect detected #1

OFAC ID:AS06762733
MATCH: 0.00
TAG: NAM
MATCHINGTEXT: Masked,**,Masked, 
RESULT: (0)

BATCH: 2018/03/24_0001_IN_ONDM_PRI3_2
NAME: Masked,Masked
  Synonyms: none
ADDRESS: Masked
  Synonyms: none
CITY: Masked
  Synonyms: none
COUNTRY: Masked
  Synonyms:
   - REPUBLIC OF Masked
   - Masked
   - Masked
   - Masked
   - Masked
STATE: Masked
  Synonyms: none
ORIGIN: 
EDA
DESIGNATION: 
GWL
TYPE: 
I
SEARCH CODES: 
none
USER DATA 1: 
none
USER DATA 2: 
none
OFFICIAL REF: 
2017-09-05 20:28:59 EDA
PASSPORT: 
none
BIC CODES: 
none
NATID: 
none
PLACE OF BIRTH: 
none
DATE OF BIRTH: 
none
NATIONALITY: 
none
ADDITIONAL INFOS: 
List ID: 1106 / Create Date: 09/05/2017 20:28:59 / Last Update Date: 09/05/2017 20:28:59 / Org_PID: 8388550 / Title: ARRESTED FOR BURGLARY - AUGUST, 2017 / Gender: MALE / OtherInformation: NickName: Masked; According to the timesofindia.indiatimes.com; August 25, 2017: In August, 2017, Masked was arrested for burglary. blah blha  blah blah blah blah. They / Relationship: Co-Defendant / OriginalID: 8388619
FML TYPE: 
1
FML PRIORITY: 
0
FML CONFIDENTIALITY: 
0
FML INFO: 
none
PEP-FEP: 
0 0
KEYWORDS: 
OS:ADVERSE_MEDIA NS:NAMESOURCE_WEBSITE ENTITYLEVEL:LEVEL_NA SC:ORGANIZED_CRIME
HYPERLINKS: 
https://MASKED.MASKED.com/signin.aspx?ent=87272dba-54dc-4246-919c-9aab50619d79
TYS: 1
ISN: 0

=============================
Suspect detected #2

OFAC ID:AS05135512
MATCH: 0.00
TAG: NAM
MATCHINGTEXT: Masked, country 
RESULT: (0)

BATCH: 2018/02/22_0001_XX_XXXY_ABC_1
NAME: Masked, Masked
  Synonyms: none
ADDRESS: Masked Masked
  Synonyms: none
CITY: MASKED
  Synonyms: none
COUNTRY: MASKED
  Synonyms:
   - REPUBLIC OF MASKED
   - MASKEd
   - MASKEd
   - MASKED MASKED
   - MASKED
STATE: MASKED
  Synonyms: none
ORIGIN: 
EDA
DESIGNATION: 
GWL
TYPE: 
I
SEARCH CODES: 
none
USER DATA 1: 
none
USER DATA 2: 
none
OFFICIAL REF: 
2017-11-06 15:03:12 EDA
PASSPORT: 
none
BIC CODES: 
none
NATID: 
none
PLACE OF BIRTH: 
none
DATE OF BIRTH: 
none
NATIONALITY: 
none
ADDITIONAL INFOS: 
List ID: 1106 / Create Date: 10/16/2015 00:00:00 / Last Update Date: 11/06/2017 15:03:12 / Org_PID: 789137 / Title: SENTENCED TO PAY A FINE OF INR 2,000 FOR VIOLATION OF CIS REGULATIONS - SEPTEMBER 25, 2006. / Gender: MALE / OtherInformation: According to the Masked-Securities and Exchange Board of Masked; March 31, 2014: Court(Filed/ Declared PO): ACMM Tis Hazari Court Delhi / ASJ, Delhi Name of Case: M/s. Masked Ltd. and Others Name of Accused: 1.Mr. Masked Ltd. 2. / Relationship: Associate / OriginalID: 7116687
FML TYPE: 
1
FML PRIORITY: 
0
FML CONFIDENTIALITY: 
0
FML INFO: 
none
PEP-FEP: 
0 0
KEYWORDS: 
OS:ENFORCEMENT NS:NAMESOURCE_IN-SEBI ENTITYLEVEL:LEVEL_NA SC:DISCIPLINED
HYPERLINKS: 
https://masked.MASKED.com/signin.aspx?ent=e1e528ad-658e-4ce9-abd9-6a6315331576
TYS: 1
ISN: 0
=============================

Open in new window


 the above data is stored in clob column named details in a table ABC
I am using the below regex

select regexp_substr(details,'[^=]+',1,rownum+1) from ABC

Output:

First Row

Suspect detected #1

OFAC ID:AS06762733
MATCH: 0.00
TAG: NAM
MATCHINGTEXT: Masked,**,Masked,
RESULT: (0)

BATCH: 2018/03/24_0001_IN_ONDM_PRI3_2
NAME: Masked,Masked
  Synonyms: none
ADDRESS: Masked
  Synonyms: none
CITY: Masked
  Synonyms: none
COUNTRY: Masked
  Synonyms:
   - REPUBLIC OF Masked
   - Masked
   - Masked
   - Masked
   - Masked
STATE: Masked
  Synonyms: none
ORIGIN:
EDA
DESIGNATION:
GWL
TYPE:
I
SEARCH CODES:
none
USER DATA 1:
none
USER DATA 2:
none
OFFICIAL REF:
2017-09-05 20:28:59 EDA
PASSPORT:
none
BIC CODES:
none
NATID:
none
PLACE OF BIRTH:
none
DATE OF BIRTH:
none
NATIONALITY:
none
ADDITIONAL INFOS:
List ID: 1106 / Create Date: 09/05/2017 20:28:59 / Last Update Date: 09/05/2017 20:28:59 / Org_PID: 8388550 / Title: ARRESTED FOR BURGLARY - AUGUST, 2017 / Gender: MALE / OtherInformation: NickName: Masked; According to the timesofindia.indiatimes.com; August 25, 2017: In August, 2017, Masked was arrested for burglary. blah blha  blah blah blah blah. They / Relationship: Co-Defendant / OriginalID: 8388619
FML TYPE:
1
FML PRIORITY:
0
FML CONFIDENTIALITY:
0
FML INFO:
none
PEP-FEP:
0 0
KEYWORDS:
OS:ADVERSE_MEDIA NS:NAMESOURCE_WEBSITE ENTITYLEVEL:LEVEL_NA SC:ORGANIZED_CRIME
HYPERLINKS:
https://MASKED.MASKED.com/signin.aspx?ent

Second Row
87272dba-54dc-4246-919c-9aab50619d79
TYS: 1
ISN: 0

Where in i expect second row to be starting from Suspect Detected #2 like the first row,
pls help and let me know if question is not clear

I am trying to use it in joining with the other table where there is a co-relation of data with the output of this query, hence the rownum condition is there...

I want to find all strings between(=============================) there are exactly 29 = signs( but somehow) couldn't do this, tried to search but no expected results
LVL 16
Wasim Akram ShaikAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try something like this
select regexp_substr(details,'(\={14}'||CHR(10)||')(.*?)('||CHR(10)||'\={14})',1,rownum+1,'n',2) as f from ABC

Open in new window

REgards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wasim Akram ShaikAuthor Commented:
Thanks Rgonzo, it worked perfectly
0
Wasim Akram ShaikAuthor Commented:
really appreciate this.. excellent.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wasim Akram ShaikAuthor Commented:
if you don't mind.. could you please explain me.. why you have counted for only {14} occurences rather than 28..
0
Rgonzo1971Commented:
since I cannot use non capturing Groups If I used 29 I couldn't find the second part
1
Wasim Akram ShaikAuthor Commented:
understood, thanx for clarification
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.