CODEWITHSUNDEEP
xmloracle-databaseclob
Aditya
Aditya

Reputation: 165

Update Clob column in Oracle

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>

Upvotes: 2

Views: 3692

Answers (2)

furman87
furman87

Reputation: 968

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 ...

Upvotes: 2

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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).

Upvotes: 4

Related Questions