Getting data from CLOB

Hi Experts,

I have below data in CLOB column, and I want to get xyz.com from below data. DOMAIN NAME LIST will not change and it will be same all the time

{
    "OBJECT ID":"71864756",
    "OBJECT NAME":"MESSENGER",
    "PARENT ID":"71865529",
    "OBJECT STATUS":"O",
    "WHITE LISTED":"N",
    "LAST UPLOAD":"",
    "PARENT CLASS":"ORDER",
    "IS A LINK OBJECT IND":"N",
    "AOL IM FEDERATION":"false",
    "ATTRIBUTE 1":"0",
    "ATTRIBUTE 2":"0",
    "ATTRIBUTE 3":"0",
    "ATTRIBUTE 4":"0",
    "ATTRIBUTE 5":"0",
    "CLASSIC CENTER INTEGRATION URL":"",
    "COMMENTS":"",
    "DISPLAY NAME":"",
    "DOMAIN NAME LIST":"xyz.com",
    "ICON NAME":"SERVICE_16.PNG",
    "IM LICENSES":"500",
}

Please help me by using dbms_lob.substr.....
Edward ThomasAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Pawan's suggestion would only work if the data you are looking for is always within the first 4,000 characters of the CLOB.  Given that you are using a CLOB and not a VARCHAR2, that may not be the case.  If your data is always less than 4,000 characters, you probably don't want the extra overhead of a CLOB.

Oracle starts indexing for INSTR and SUBSTR at 1, not 0.  While SUBSTR is documented to treat 0 as 1, INSTR is not.  Personally, I would explicitly put 1 as the starting position, only because Oracle could change it's mind on what it wants to do with 0, it wouldn't be the first time they changed something like that.

Now, assuming that your field could contain more than 4,000 characters and you want to use the DBMS_LOB package to get the data (as stated in your question), this should work (using Pawan's test case):
SELECT dbms_lob.Substr(t, dbms_lob.Instr(t, '"', 
                                 dbms_lob.Instr(t, ':', dbms_lob.Instr(t, 
                                                    'DOMAIN NAME LIST')) 
                                                 + 2) - dbms_lob.Instr(t, ':', 
                                 dbms_lob.Instr(t, 'DOMAIN NAME LIST')) - 2, 
              dbms_lob.Instr(t, ':', dbms_lob.Instr(t, 'DOMAIN NAME LIST')) 
              + 2) 
FROM   testlob; 

Open in new window

What that does is look for the : after DOMAIN NAME LIST as the starting point.  Go 2 characaters past that, then find the next " and use that as the ending point.  If those rules don't hold true for all cases, then you can tweak that to get to where you need to go, but it gets you started.
0
 
Pawan KumarDatabase ExpertCommented:
You can try like below-

select to_char(SUBSTR(columnname,0,4000)) from yourtableName

the apply the string functions (insrt and substr) to extract the DOMAIN NAME LIST
0
 
Edward ThomasAuthor Commented:
Hi Pawan,
can you give me solution to get particular data from CLOB datatype as i asked in my above question.
Thanks
ED T
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
Please try tested solution for oracle-

CREATE TABLE testLOB
(
   Id INTEGER
  ,t CLOB
);

INSERT INTO testLOB VALUES ( 1 , '{
    "OBJECT ID":"71864756",
    "OBJECT NAME":"MESSENGER",
    "PARENT ID":"71865529",
    "OBJECT STATUS":"O",
    "WHITE LISTED":"N",
    "LAST UPLOAD":"",
    "PARENT CLASS":"ORDER",
    "IS A LINK OBJECT IND":"N",
    "AOL IM FEDERATION":"false",
    "ATTRIBUTE 1":"0",
    "ATTRIBUTE 2":"0",
    "ATTRIBUTE 3":"0",
    "ATTRIBUTE 4":"0",
    "ATTRIBUTE 5":"0",
    "CLASSIC CENTER INTEGRATION URL":"",
    "COMMENTS":"",
    "DISPLAY NAME":"",
    "DOMAIN NAME LIST":"xyz.com",
    "ICON NAME":"SERVICE_16.PNG",
    "IM LICENSES":"500",
}');

Open in new window


Solution
select id, 
SUBSTR(
to_char(SUBSTR(t,0,4000))
,  
 INSTR( to_char(SUBSTR(t,0,4000)) , 'DOMAIN NAME LIST' ) + LENGTH('DOMAIN NAME LIST') + 3 
,INSTR( to_char(SUBSTR(t,0,4000)), 'ICON NAME' , INSTR( to_char(SUBSTR(t,0,4000)) , 'DOMAIN NAME LIST' )) 
  -
 INSTR( to_char(SUBSTR(t,0,4000)) , 'ICON NAME' ) + LENGTH('ICON NAME') - 2
 ) t  
from testLOB;

Open in new window


OUTPUT
| ID |       T |
|----|---------|
|  1 | xyz.com |

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Changed columnName for you
SELECT id,SUBSTR(str,st,et) domainanmelist FROM
(
select id, 
to_char(SUBSTR(t,0,4000)) str
,
 INSTR( to_char(SUBSTR(t,0,4000)) , 'DOMAIN NAME LIST' ) + LENGTH('DOMAIN NAME LIST') + 3 
st
,INSTR( to_char(SUBSTR(t,0,4000)), 'ICON NAME' , INSTR( to_char(SUBSTR(t,0,4000)) , 'DOMAIN NAME LIST' )) 
  -
 INSTR( to_char(SUBSTR(t,0,4000)) , 'ICON NAME' ) + LENGTH('ICON NAME') - 2
et
from testLOB
)o

Open in new window


OUTPUT
| ID | DOMAINANMELIST |
|----|----------------|
|  1 |        xyz.com |

Open in new window

0
 
Edward ThomasAuthor Commented:
Hi Johnsone/Pawan,

You guys are awesome. Thanks for the solution.
As Johnsone said I got issue with morethan 4k data and its solved my issue with Johnsone query.

Once again thanks a lot.

ED T
0
 
slightwv (䄆 Netminder) Commented:
Even though you've already accepted this I wanted to mention 12c has some JSON parsing built into it:
https://docs.oracle.com/database/122/ADJSN/using-PLSQL-object-types-for-JSON.htm#ADJSN-GUID-F0561593-D0B9-44EA-9C8C-ACB6AA9474EE

In 11g it appears there is some APEX provided methods and an open source method.

Both are talked about here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9529416000346458881
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.