3

I'm working on migrating data from an oracle database to a postgresql database. I running into a problem where I export the data from oracle, using sql developer, and importing it into postgresql. When I try to import the data into postgresql I get this error:

ERROR:  invalid byte sequence for encoding "UTF8": 0xcb 0xcf
CONTEXT:  COPY project, line 646

********** Error **********

ERROR: invalid byte sequence for encoding "UTF8": 0xcb 0xcf
SQL state: 22021
Context: COPY project, line 646

The data in the oracle server looks like this in sql developer:

Ã9ÃýÃ0Ã

When sql developer exports the data to a file, it looks like this:

enter image description here

When I try to set the encoding in sql developer when I export the data I get this loading menu item:

enter image description here

and it doesn't change.

What do I need to do to properly export and import the data from oracle and postgresql?

1

Sounds like the data you're exporting isn't utf-8 encoded, or is being chopped up / mangled in transit.

This: Ã9ÃýÃ0Ã looks like what happens when you decode utf-8 as iso-8859-15, cp1252, or related 1-byte encodings. But it's not valid utf-8 when demangled. Perhaps you've cut it part way through a string, rather than copying from the beginning of the value?

0xcb 0xcf is indeed nonsense as utf-8. It's a continuation byte without any appropriate context.

Trying other encodings, it's:

  • ËÏ in latin-1. Unlikely. Same in iso-8859 3,4,9,10,14,15, and in the default Windows encoding for Western Europe / US, cp1252.
  • ËĎ in iso-8859-2
  • ЫЯ in iso-8859-5
  • ثد in iso-8859-6
  • ΛΟ in iso-8859-7
  • หฯ in iso-8859-11
  • ĖĻ in iso-8859-13

Any of those look like likely candidates, given what you know about the original data?

You need to either export in a known encoding, or determine the encoding of the input reliably.

It's also possible that the data is in fact starting with a utf-8 continuation byte and was supposed to be utf-8, but something has mangled it by cutting utf-8 strings up byte-wise.

If you can post a complete line of exported CSV it might help.

  • Yes, I believe you are right, because in sql developer the export encoding is cp1252, but I don't have a way to change that, as far as I know. – j will Mar 27 '15 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.