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
.
Best Answer
Here is a general way of getting a table with
bytea
data from postgres into a similar Oracle table with ablob
.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:
insert test data and check hashes:
generate Oracle script:
contents of /tmp/oracle.sql:
remove testbed:
2) Oracle testbed:
run script:
check hashes:
credit to Vincent Malgrat for his concat_blob function I adapted. See here for the random bytea function by itself