I have a db on Postgres 9.1. The db collected some binary data that are stored in bytea file. I need to copy the data to Oracle 10g. How to do it?

The details

I have a table plines in Postgres9.1. plines contains a bytea field shape.
The number of records is about 500.000.

I have a table olines in Oracle10g. olines contains a blob field shape.

I need to copy data from plines.shape to olines.shape.

  • 1
    So the Postgres datatype is a bytea and the Oracle datatype is a blob? This isn't trivial
    – Philᵀᴹ
    Aug 13, 2012 at 11:24
  • Are you on Linux or Windows? This will take some command-line file manipulation
    – Philᵀᴹ
    Aug 13, 2012 at 11:30
  • Postgres and Orcale are under linux
    – ysa
    Aug 13, 2012 at 14:21
  • Thank you everybody. I decided to code a simple Java program for that task.
    – ysa
    Aug 13, 2012 at 14:27
  • If you get this working and the code is simple enough, please share it here as an answer. Aug 14, 2012 at 8:08

1 Answer 1


Here is a general way of getting a table with bytea data from postgres into a similar Oracle table with a blob.

It should be trivial to adapt my test to your real tables, and you should probably increase the size of the 'chunks' that I used to keep the output readable, from 20 to something more like the 2000 limit for string literals (4000/2 as each byte is 2 bytes hex).

1) postgres

set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
create function random_bytea(p_length in integer)
       returns bytea language plpgsql set search_path to 'stack' as $$
  o bytea := '';
  for i in 1..p_length loop
    o := o||decode(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0'), 'hex');
  end loop;
  return o;
create function bytea_to_oracle_sql(p_id in integer, p_data in bytea)
       returns text language plpgsql set search_path to 'stack' as $$
  o text := 'insert into foo(id, bar) values('||p_id||', empty_blob());';
  for i in 0..(length(p_data)-1)/20 loop
    o := o||chr(10)||'update foo set bar=append_to_blob(bar,'''
          ||encode(substr(p_data,i*20+1,20),'hex')||''') where id='||p_id||';';
  end loop;
  return o;
create table foo(id serial, bar bytea);

insert test data and check hashes:

insert into foo(bar) values(random_bytea(90));
insert into foo(bar) values(random_bytea(90));
select id, md5(bar) from foo;
 id |               md5
  1 | 32a24c8827047eff517e8a40bf3ad8b4
  2 | 25ac852000889ab782ad6437accf1546

generate Oracle script:

\o '/tmp/oracle.sql'
select bytea_to_oracle_sql(id, bar) from foo;

contents of /tmp/oracle.sql:

insert into foo(id, bar) values(1, empty_blob());
update foo set bar=append_to_blob(bar,'ddbe858f7905551862507ddaa3b5410cba8013d4') where id=1;
update foo set bar=append_to_blob(bar,'089d6e66f16b10ba1204a4efc22a7f3c2fd45492') where id=1;
update foo set bar=append_to_blob(bar,'24d26dc887afd4412fe8163786859e77f0af3202') where id=1;
update foo set bar=append_to_blob(bar,'b3d7f2750172b2314606c36bd8313360e008a20f') where id=1;
update foo set bar=append_to_blob(bar,'f0b847763de6ee2e9521') where id=1;
insert into foo(id, bar) values(2, empty_blob());
update foo set bar=append_to_blob(bar,'3148f823befa95712bdc77ef4750207bb0018352') where id=2;
update foo set bar=append_to_blob(bar,'10730b58ea483ed876d4faa81df3ccdbed624d18') where id=2;
update foo set bar=append_to_blob(bar,'3ec40886142901c52a85173bf92393e36bd2bce2') where id=2;
update foo set bar=append_to_blob(bar,'a6b68ac3a9569f97b8ecaff7b1b87dc6e17f8b0b') where id=2;
update foo set bar=append_to_blob(bar,'04a347fdc6dae132ad9d') where id=2;

remove testbed:


2) Oracle testbed:

create table foo(id integer, bar blob);
create or replace
function append_to_blob(p_data in blob, p_append_hex in varchar) return blob is
  o blob;
  dbms_lob.createtemporary(o, TRUE);
  dbms_lob.append(o, p_data);
  dbms_lob.writeappend(o, length(p_append_hex)/2, hextoraw(p_append_hex));
  return o;

run script:

insert into foo(id, bar) values(1, empty_blob());
update foo set bar=append_to_blob(bar,'ddbe858f7905551862507ddaa3b5410cba8013d4') where id=1

check hashes:

select id, dbms_crypto.hash(bar,2) from foo;
--- --------------------------------
  1 32A24C8827047EFF517E8A40BF3AD8B4
  2 25AC852000889AB782AD6437ACCF1546

credit to Vincent Malgrat for his concat_blob function I adapted. See here for the random bytea function by itself

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.