search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
1.01k stars 342 forks source link

Ora2pg fails on bytea table export #132

Closed hersh17 closed 8 years ago

hersh17 commented 9 years ago

I am trying to INSERT a table with BLOB data type directly to a PostgreSQL database. It is failing with following error

ora2pg --debug --allow xxxxxx --type INSERT Using character set: NLS_LANG=AMERICAN_AMERICA.AL32UTF8, NLS_NCHAR=AL32UTF8. Using Perl output encoding :utf8. Using PostgreSQL client encoding UTF8. Trying to connect to database: dbi:Oracle:host=xxxxx;sid=xxxx Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Retrieving table information... [1] Scanning table xxxx (450 rows)... Trying to connect to database: dbi:Oracle:host=xxx;sid=xxx Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Retrieving partitions information...

Looking how to retrieve data from xxx... Fetching all data from xxx tuples... Dumping data from table xxx into PostgreSQL... Disabling synchronous commit when writing to PostgreSQL... DEBUG: Creating output for 10000 tuples DEBUG: Sending INSERT bulk output directly to PostgreSQL backend DBD::Pg::st execute failed: ERROR: invalid input syntax for type bytea at /usr/lib/perl5/site_perl/5.22/Ora2Pg.pm line 9054. FATAL: ERROR: invalid input syntax for type bytea Aborting export...

I have following settings on my ora2pg.conf DATA_TYPE RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea LONGREADLEN 1047552 LONGTRUNCOK 0

darold commented 9 years ago

Hi,

Please use COPY export type, it is really the export type to use for what you want to do:

ora2pg --debug --allow xxxxxx --type COPY

INSERT export type will generate insert statement which is only useful if you want to import data to an other SGBD than PostgreSQL. The COPY export type do not suffer of encoding issue and is far speedier to load data than the INSERT export type.

Do not use INSERT export type, even with file export, unless you really need it.

hersh17 commented 9 years ago

Thanks Darold. I have tried your suggestion and it has failed with following error. Any pointers would be of great help.

DEBUG: Creating output for 10000 tuples DEBUG: Sending COPY bulk output directly to PostgreSQL backend DBD::Pg::db pg_putcopyend failed: ERROR: invalid byte sequence for encoding "UTF8": 0xbf CONTEXT: COPY xxx, line 8267 at /usr/lib/perl5/site_perl/5.22/Ora2Pg.pm line 9011. FATAL: ERROR: invalid byte sequence for encoding "UTF8": 0xbf CONTEXT: COPY xxx, line 8267 DBI::db=HASH(0x80b4e1d0)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /usr/lib/perl5/site_perl/5.22/Ora2Pg.pm line 8561. Aborting export...

darold commented 9 years ago

Please port here the result of linux command:

 echo $LANG

and the version of ora2pg.

Also, what are the values of the dollowing configuration directives: NLS_LANG, NLS_NCHAR and CLIENT_ENCODING?

hersh17 commented 9 years ago

I apologize for late response. Here are the correct values $ echo $LANG en_US.UTF-8 $ ora2pg --version Ora2Pg v15.3

NLS_LANG AMERICAN_AMERICA.AL32UTF8 NLS_NCHAR AL32UTF8 CLIENT ENCODING =UTF8

darold commented 8 years ago

I'm closing this issue, I can not reproduce the problem.

bsislow commented 6 years ago

@darold - I am experiencing this issue now with v18.1:

$ ora2pg -v Ora2Pg v18.1

DBD::Pg::db pg_putcopyend failed: ERROR: invalid byte sequence for encoding "UTF8": 0xed 0xbe 0xa4 XXXXXXXX in progress.

darold commented 6 years ago

Please upgrade to Ora2Pg v19.0 first then report the result of the following command:

ora2pg -c config/ora2pg.conf -t SHOW_ENCODING
bsislow commented 6 years ago

@darold ... It is still failing on 19.0. Here is the output you requested.

$ ora2pg -v Ora2Pg v19.0 $ ora2pg -c my.conf -t SHOW_ENCODING Current encoding settings that will be used by Ora2Pg: Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8 Oracle NLS_NCHAR AL32UTF8 Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6 Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS PostgreSQL CLIENT_ENCODING UTF8 Perl output encoding '' Showing current Oracle encoding and possible PostgreSQL client encoding: Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8 Oracle NLS_NCHAR AL32UTF8 Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6 Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS PostgreSQL CLIENT_ENCODING UTF8

darold commented 6 years ago

Looks that everything is UTF8 encoded so I'm afraid that you have data that are not part of UTF8 in your Oracle Database. You may want to isolate the export of the failing table and try with an other encoding.