How to concatenate BLOB fields (Oracle)?

Question

IS it possible to concatenate A1 and A2 from the particular table (for example):

CREATE TABLE MY_SCHEME.CONC_BLOB
(
  A1       BLOB,
  A1_SORT  NUMBER(20),
  T_TYPE   VARCHAR2(9 BYTE),
  A2       BLOB,
  A2_SORT  NUMBER(20),
  A3       VARCHAR2(32 BYTE),
  A4       BLOB,
  A5       VARCHAR2(8 BYTE)
)

? How?

Idolater

Answer 1

BLOBs can be concatenated with the DBMS_LOB package, in particular with the APPEND procedure. But you will need to use some PL/SQL that iterates over the relevant rows and calls the procedure.

I don't quite understand what you mean by next table so I can't give you an example.

Update:

The relevant piece of PL/SQL could look like this:

DECLARE
  a1_lob BLOB;
  a2_lob  BLOB;

BEGIN
  SELECT A1, A2 INTO a1_lob, a2_lob
  FROM CONC_BLOB
  WHERE A1_SORT = 'some value'
  FOR UPDATE;

  dbms_lob.append(a1_lob, a2_lob);
  COMMIT;
END;

Zorina

Thank you!!! MY_SCHEME.CONC_BLOB is just for example. I tried to concatenat A1 and A2 fileds (select A1||A2 from MY_SCHEME.CONC_BLOB), but with out result - oracle said: ORA-00932: inconsistent datatypes.... =( – Idolater

The concatenation operator won't work with BLOBs. You'll have to use THE DBMS_LOB package. – Zorina

I've added a PL/SQL sample for concatenating two BLOBs. – Zorina

Answer 2

Here is my solution for joining any number of BLOBs into single BLOB using helper table type and stored function:

create or replace type blobs as table of blob;

create or replace function concat_blobs(parts in blobs) return blob
is
    temp blob;
begin
    if parts is null or parts.count = 0 then
       return null;
    end if;
    dbms_lob.createtemporary(temp, false, dbms_lob.CALL);
    for i in parts.first .. parts.last
    loop
        dbms_lob.append(temp, parts(i));
    end loop;
    return temp;
end;

-- usage example:
select concat_blobs(blobs(to_blob(hextoraw('CAFE')), to_blob(hextoraw('BABE')))) from dual;


-- bonus
create or replace type raws as table of raw(2000);

create or replace function raws_to_blobs(arg in raws) return blobs
is
    res blobs;
begin
    select to_blob(column_value) bulk collect into res from table(arg);
    return res;
end;

-- usage example:
select concat_blobs(raws_to_blobs(raws(hextoraw('CAFE'), hextoraw('BABE'))) from dual;

See also multiple RAWs concatenation in Oracle 10: Using HEXTORAW to fill in blob data.

Superscribe

Answer 3

FYI: if you intent to use blob to store large text (that's why I suppose you would like to concatenate them) I suggest using CLOB. It will permit you to use || for the best part of concatenations. Unfortunately you could face with the issue of || when the length of clob exceeds 32767

Humectant

yeap, you are right! i am going to write data to the file (it looks like you are sitting on the next workplace or you may read thoughts=) ). but i need to write it in raw format, i means that i would like to see a nonprintable chars in the file ([link]dl.dropbox.com/u/25164865/file.jpg)) – Idolater

CLOB permits to store non-printable characters so just try to use clob. – Humectant