2

I have an table with name ABC which has CLOB data. I want to update the column to insert string in specific position in this Clob column.

String to be inserted : 

<nv_pair>
<name identifier="XYZ"></name>
<value identifier="XYZ"></value>
</nv_pair>

Clob Data :

<form> <nv_pair></nv_pair> <nv_pair></nv_pair><nv_pair></nv_pair><nv_pair></nv_pair></form>

Position to be inserted : Just Before </form>

skaffman
  • 398,947
  • 96
  • 818
  • 769
Aditya
  • 165
  • 1
  • 5
  • 10

2 Answers2

4

Take a look at DBMS_LOB package.

BTW, it might be worth exploring the possibility of abandoning CLOB and using Oracle's built-in XML capabilities (I'm not familiar with that, though).

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • +1 for the xmltype suggestion although it will depend on the database version whether it's possible (and advisable) to switch. – Wivani Aug 23 '11 at 00:27
2

Depending on which version of Oracle you have, you can use the regexp_replace function:

update abc
set clob_val =
  regexp_replace(clob_val,
                 '^(.+)(</form>)',
                 '\1<nv_pair><name identifier="XYZ"></name><value identifier="XYZ">/value>/nv_pair>\2')
where ...
furman87
  • 928
  • 13
  • 20