Take the 2-minute tour ×
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.

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

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
testbed:

begin;
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 $$
declare
  o bytea := '';
begin 
  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;
end;$$;
--
create function bytea_to_oracle_sql(p_id in integer, p_data in bytea)
       returns text language plpgsql set search_path to 'stack' as $$
declare
  o text := 'insert into foo(id, bar) values('||p_id||', empty_blob());';
begin 
  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;
end;$$;
--
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;
\o

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:

rollback;

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;
begin
  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;
end;
/

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;
/*
 ID DBMS_CRYPTO.HASH(BAR,2)         
--- --------------------------------
  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

 
discard

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.