Re: ERROR: invalid byte sequence for encoding "UTF8": 0x00

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



PropAAS DBA wrote:
> All;

That's me :^)

> we are doing an oracle to Postgresql conversion, lots and lots of the 
> oracle columns throw this error:
> 
> ERROR: invalid byte sequence for encoding "UTF8": 0x00
> 
> CONTEXT: converting column [colname] for foreign table scan of 
> [tablename] row xxx
> 
> We are using the ora_fdw oracle foreign data wrapper extension to 
> perform the data conversion, we create the foreign tables via the 
> "IMPORT FOREIGN SCHEMA" option, then do an insert in [postgres_table] 
> select * from [ora_fdw_table]
> 
> I've tried a number of fixes but they all seem to replace the data for 
> ALL rows instead of just the broken ones.
> 
> 
> For example:
> 
> select id, cname from ora_fdw_schema.cust_names
> 
> ERROR: invalid byte sequence for encoding "UTF8": 0x00
> 
> CONTEXT: converting column cname for foreign table scan of 
> ora_fdw_schema.cust_names, row 122

The reason is that there are some zero bytes (ASCII NUL) in the Oracle
table.  While these are allowed in Oracle, they are not allowed in PostgreSQL
because PostgreSQL regards zero bytes as string terminators.

That is why oracle_fdw will not allow you to transfer these data
to PostgreSQL.

> If I use replace then ALL rows are NULL:

Any attempt to fix the data on the PostgreSQL side is doomed.

The error message is thrown when the data are converted to PostgreSQL
string types, which is *before* you can manipulate them in PostgreSQL.

You'll have to fix the data on the Oracle side before you migrate
the database.  Usually, such zero bytes in strings are introduced
into the Oracle table by mistake, and no harm is done in removing them.

Use a condition like
   WHERE cname LIKE '%' || CHR(0) || '%'
in Oracle to spot the affected rows.

Yours,
Laurenz Albe


-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux