Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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