Merge BLOB data in oracle

Question

We have a table which has blob data (uncompressed ,text in xml form).

we cannot change the data type to clob or any other.

i want to merge 2 rows of blob data to create a new single row.

since it is xml simple concat will not work ,i will need to download them to unix then modify them then again insert back to same table.

there is no limitation of blob (can be greater than 4000 characters).

i am struggling to find a solution to download entire blob in a file.

Why do you want to do the merge on the operating system rather than within Oracle? There are ways to convert a BLOB to a CLOB and back again (at least if you know the character set), but do you have a reason to do it outside the DB? And are you doing more than concatenating one XML [fragment? doc?] onto the other - e.g. adding a new root node, or merge two root nodes into one? — Alex Poole
i cannot change the column type as it is a core product table. there is a tag called "ABC" which is inside "ADC" in both XMLs ,i want it to be ADC<ABC1><ABC2> (1 and 2 being xml of 1st and 2nd row) — utkarsh
I didn't suggest you change the column data type. I was talking about converting the data from BLOB to CLOB, manipulating it as text/XML, then converting the result back to BLOB to store it back in your existing column. Which kfinity has shown how to do in an answer (saving me some work). — Alex Poole

kfinity kfinity · Accepted Answer · 2019-03-08T14:37:39

Following up on Alex's comment, here's an example of merging XML rows (stored as BLOB) in plain Oracle SQL. You haven't given us many details about your table structure and data, so I just made an example table and data. If they're stored as binary XML we'd have to do this a bit differently.

-- simple table, just a row id and a blob, and insert 2 rows
create table xml_test (rnum number, x blob);
insert into xml_test values (1, UTL_RAW.CAST_TO_RAW('<ADC><ABC value="1"></ABC></ADC>'));
insert into xml_test values (2, UTL_RAW.CAST_TO_RAW('<ADC><ABC value="2"></ABC></ADC>'));

-- look at the values we just inserted (using my charset id, 873 - for AL32UTF8)
select rnum, xmltype(x, 873)
from xml_test;

-- merge the rows as described and insert as new row with rnum=3
insert into xml_test (rnum, x)
with cs as -- find your charset ID to decode the blob. 873 for me.
    (select NLS_CHARSET_ID(value) as id from nls_database_parameters where parameter='NLS_CHARACTERSET')
SELECT 3 as rn, 
   XMLQuery('copy $i := $row1 modify
                    (for $j in $i/ADC
                     return insert nodes $row2 as last into $j)
                 return $i'
                PASSING xmltype(x1.x, cs.id) as "row1",
                    XMLQuery('ADC/ABC' passing xmltype(x2.x, cs.id) returning content) as "row2"
                RETURNING CONTENT).getBlobVal(cs.id) as x
  FROM xml_test x1
  JOIN xml_test x2 on x2.rnum = 2 -- row 2
  cross join cs
  WHERE x1.rnum = 1; -- row 1

-- look at the new row
select xmltype(x, 873)
from xml_test
where rnum = 3;

-- output:
<?xml version="1.0" encoding="UTF-8"?>
<ADC>
  <ABC value="1"/>
  <ABC value="2"/>
</ADC>

Merge BLOB data in oracle

1 Answers