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

[PropAAS DBA <dba@xxxxxxxxxxx>]

All;


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


If I use replace then ALL rows are NULL:

select id, replace(cname,'',NULL)  from ora_fdw_schema.cust_names

    id    |         replace

---------+----------------------

1         |

2         |

3         |

4         |

5         |

6         |

7         |




Same thing if I use translate:

select id, translate(cname,'',NULL)  from ora_fdw_schema.cust_names


Help?


Thanks in advance





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