Home » SQL & PL/SQL » SQL & PL/SQL » Find multiple stings in a CLOB
Find multiple stings in a CLOB [message #233361] Wed, 25 April 2007 16:59 Go to next message
felixp
Messages: 2
Registered: April 2007
Junior Member
Hi,
I have been trying to read multiple lines from a clob and need help:

I have clobs with XML data inside and I want to find multiple lines of a certain string.
I have experimented with "instr", but it only returns the FIRST value and I need to find ALL values.


Example CLOB:

<POInDtl>
<ref_item>58537046</ref_item>
<physical_location>2010</physical_location>
<physical_qty_ordered>2</physical_qty_ordered>
<unit_cost>15.02</unit_cost>
<physical_tsf_location>20097</physical_tsf_location>
</POInDtl>
<POInDtl>
<ref_item>58537345</ref_item>
<physical_location>2010</physical_location>
<physical_qty_ordered>1</physical_qty_ordered>
<unit_cost>16.06</unit_cost>
<physical_tsf_location>20097</physical_tsf_location>
</POInDtl>
<POInDtl>
<ref_item>58537345</ref_item>
<physical_location>2010</physical_location>
<physical_qty_ordered>1</physical_qty_ordered>
<unit_cost>16.06</unit_cost>
<physical_tsf_location>20097</physical_tsf_location>
</POInDtl>


Using:
select substr(data,instr(data,'<ref_item>',100)+10,instr(data,'</ref_item>',100)-instr(data,'<ref_item>',1)-10)
from table
where id = 1

I would only get 1 value "58537046". How would I get all values, if I do not know, how large the CLOB is ??

Thanks for your help,
Best Felix
Re: Find multiple stings in a CLOB [message #233405 is a reply to message #233361] Thu, 26 April 2007 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle has functions and package to parse XML, I don't know them but I'm pretty sure they can help you.

Regards
Michel
Re: Find multiple stings in a CLOB [message #233425 is a reply to message #233361] Thu, 26 April 2007 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Found it:
SQL> with 
  2    data as (
  3      select 
  4  '<POInDtl>
  5  <ref_item>58537046</ref_item>
  6  <physical_location>2010</physical_location>
  7  <physical_qty_ordered>2</physical_qty_ordered>
  8  <unit_cost>15.02</unit_cost>
  9  <physical_tsf_location>20097</physical_tsf_location>
 10  </POInDtl>
 11  <POInDtl>
 12  <ref_item>58537345</ref_item>
 13  <physical_location>2010</physical_location>
 14  <physical_qty_ordered>1</physical_qty_ordered>
 15  <unit_cost>16.06</unit_cost>
 16  <physical_tsf_location>20097</physical_tsf_location>
 17  </POInDtl>
 18  <POInDtl>
 19  <ref_item>58537345</ref_item>
 20  <physical_location>2010</physical_location>
 21  <physical_qty_ordered>1</physical_qty_ordered>
 22  <unit_cost>16.06</unit_cost>
 23  <physical_tsf_location>20097</physical_tsf_location>
 24  </POInDtl>' val
 25      from dual
 26    ),
 27    xml as ( -- keep only first one and convert it to xml type
 28      select xmltype(substr(val,1,instr(val,'<POInDtl>',1,2)-1)) val from data
 29    )
 30  select extract(value(t),'//POInDtl/ref_item').getStringVal() val
 31  from table(xmlsequence(cursor(select val from xml))) t
 32  /
VAL
------------------------------------------------------------------------------------
<ref_item>58537046</ref_item>

1 row selected.

Regards
Michel
Re: Find multiple stings in a CLOB [message #233437 is a reply to message #233425] Thu, 26 April 2007 02:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Without converting it to XML:
SQL> drop table faq;

Table dropped.

SQL> create table faq
  2  ( col1 clob
  3  );

Table created.

SQL> insert into faq values ('<POInDtl>
  2  <ref_item>58537046</ref_item>
  3  <physical_location>2010</physical_location>
  4  <physical_qty_ordered>2</physical_qty_ordered>
  5  <unit_cost>15.02</unit_cost>
  6  <physical_tsf_location>20097</physical_tsf_location>
  7  </POInDtl>
  8  <POInDtl>
  9  <ref_item>58537345</ref_item>
 10  <physical_location>2010</physical_location>
 11  <physical_qty_ordered>1</physical_qty_ordered>
 12  <unit_cost>16.06</unit_cost>
 13  <physical_tsf_location>20097</physical_tsf_location>
 14  </POInDtl>
 15  <POInDtl>
 16  <ref_item>58537345</ref_item>
 17  <physical_location>2010</physical_location>
 18  <physical_qty_ordered>1</physical_qty_ordered>
 19  <unit_cost>16.06</unit_cost>
 20  <physical_tsf_location>20097</physical_tsf_location>
 21  </POInDtl>');

1 row created.

SQL> 
SQL> 
SQL> select instr(col1, '<ref_item>', 1, rownum) position
  2  ,	    substr( col1, instr(col1, '<ref_item>', 1, rownum) + 10
  3  		  , (instr(col1, '</ref_item>', 1, rownum) - (instr(col1, '<ref_item>', 1, rownum) + 10))
  4  		  )
  5  from   faq
  6  where  instr(col1, '<ref_item>', 1, rownum) > 0
  7  connect by level <= length(col1)
  8  ;

  POSITION SUBSTR(COL1,INSTR(COL1,'<REF_ITEM>',1,ROWNUM)+10,(INSTR(COL1,'</REF_ITEM>',1,ROW
---------- --------------------------------------------------------------------------------
        11 58537046
       235 58537345
       459 58537345



[Edit: typo in tags]

[Updated on: Thu, 26 April 2007 02:44]

Report message to a moderator

Re: Find multiple stings in a CLOB [message #233441 is a reply to message #233437] Thu, 26 April 2007 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I used XML because Oracle has some optimizations in XML functions code that may improve performances.

Regards
Michel
Re: Find multiple stings in a CLOB [message #233585 is a reply to message #233361] Thu, 26 April 2007 13:41 Go to previous message
felixp
Messages: 2
Registered: April 2007
Junior Member
Thanks guys,
this workes well for me !!!

You rock and saved my day !

Felix
Previous Topic: how to generate text file using spool command using Pl/SQL procedure
Next Topic: List all constraints in a database
Goto Forum:
  


Current Time: Mon Dec 05 10:50:37 CST 2016

Total time taken to generate the page: 0.05022 seconds