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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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. Commented Mar 27, 2015 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.