ora2pg and invalid command \N

On 03/16/2018 10:12 AM, Charlin Barak wrote:

Hi,
I'm using ora2pg to migrate our Oracle database to Postgres. I was able
to generate the data file using TYPE=COPY but when I attempted to load

What was the complete command you used to generate the data file?

the file via psql, I got lots of "invalid command \N" errors. The

Can we see the complete error?

Also a section of the data file that contains the data that caused the
error?

resolution on the internet was not clear and was  not ora2pg related.
How do I resolve this issue? Can someone provide some guidance?

Thanks.

--
Adrian Klaver
adrian.klaver@aklaver.com

2018-03-16 18:12 GMT+01:00 Charlin Barak <charlinbarak@gmail.com>:

Hi,
I'm using ora2pg to migrate our Oracle database to Postgres. I was able to
generate the data file using TYPE=COPY but when I attempted to load the
file via psql, I got lots of "invalid command \N" errors. The resolution on
the internet was not clear and was not ora2pg related. How do I resolve
this issue? Can someone provide some guidance?

\N is symbol for NULL. but \cmd are psql commands too. This behave means so
psql lost synchronization and try to eval data like commands. psql import
is tolerant - that means so source of this issue is lost usually. Try to
import data with option ON_ERROR_STOP

https://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error

Regards

Pavel

Thanks for your response.

the NULL values from Oracle were indeed replaced by \N in the data output.
How do I go about loading this file? I hope I do not have to temporary
replace \N with a string in the flat file and then later update in Postgres.

Thanks.

102 48299 50 C 3 \N
103 48299 \N G 3 45
...
...

On Fri, Mar 16, 2018 at 2:06 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

On 03/16/2018 11:36 AM, Charlin Barak wrote:

Thanks for your response.

the NULL values from Oracle were indeed replaced by \N in the data
output. How do I go about loading this file? I hope I do not have to
temporary replace \N with a string in the flat file and then later
update in Postgres.

That should not be necessary:

https://www.postgresql.org/docs/10/static/sql-copy.html

"NULL

Specifies the string that represents a null value. The default is
\N (backslash-N) in text format, and an unquoted empty string in CSV
format. You might prefer an empty string even in text format for cases
where you don't want to distinguish nulls from empty strings. This
option is not allowed when using binary format."

To solve this is going to require more information. From my previous post:

What was the complete command you used to generate the data file?

Can we see the complete error?

Also a section of the data file that contains the data that caused the
error?

Thanks.

102     48299   50  C       3      \N
103     48299   \N  G       3      45
...
...

On Fri, Mar 16, 2018 at 2:06 PM, Pavel Stehule <pavel.stehule@gmail.com
<mailto:pavel.stehule@gmail.com>> wrote:

2018-03-16 18:12 GMT+01:00 Charlin Barak <charlinbarak@gmail.com
<mailto:charlinbarak@gmail.com>>:

Hi,
I'm using ora2pg to migrate our Oracle database to Postgres. I
was able to generate the data file using TYPE=COPY but when I
attempted to load the file via psql, I got lots of "invalid
command \N" errors. The resolution on the internet was not clear
and was  not ora2pg related. How do I resolve this issue? Can
someone provide some guidance?

\N is symbol for NULL. but \cmd are psql commands too. This behave
means so psql lost synchronization and try to eval data like
commands. psql import is tolerant - that means so source of this
issue is lost usually. Try to import data with option ON_ERROR_STOP

https://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error
<https://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error&gt;

Regards

Pavel

Thanks.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, Mar 16, 2018 at 8:28 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 03/16/2018 11:36 AM, Charlin Barak wrote:

Thanks for your response.

the NULL values from Oracle were indeed replaced by \N in the data output.
How do I go about loading this file? I hope I do not have to temporary
replace \N with a string in the flat file and then later update in Postgres.

That should not be necessary:

https://www.postgresql.org/docs/10/static/sql-copy.html

"NULL

Specifies the string that represents a null value. The default is \N
(backslash-N) in text format, and an unquoted empty string in CSV format.
You might prefer an empty string even in text format for cases where you
don't want to distinguish nulls from empty strings. This option is not
allowed when using binary format."

To solve this is going to require more information. From my previous post:

What was the complete command you used to generate the data file?

Can we see the complete error?

Also a section of the data file that contains the data that caused the
error?

I pinged the original author, and he thinks it might be because of a
nested user defined type. If that's the case, you should try with
current development version
(https://github.com/darold/ora2pg/archive/master.zip) where the issue
should be fixed. If not, you should try to open an issue on
https://github.com/darold/ora2pg/issues.