Re: ora2pg and invalid command \N

[Julien Rouhaud]

On Fri, Mar 16, 2018 at 8:28 PM, Adrian Klaver
 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.

Re: ora2pg and invalid command \N

[Adrian Klaver]

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 > wrote:




2018-03-16 18:12 GMT+01:00 Charlin Barak mailto:charlinba...@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.









--
Adrian Klaver
adrian.kla...@aklaver.com

Re: ora2pg and invalid command \N

[Charlin Barak]

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 
wrote:

>
>
> 2018-03-16 18:12 GMT+01:00 Charlin Barak :
>
>> 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.
>>
>>
>>
>>
>>
>

Re: ora2pg and invalid command \N

[Pavel Stehule]

2018-03-16 18:12 GMT+01:00 Charlin Barak :

> 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.
>
>
>
>
>

Re: ora2pg and invalid command \N

[Adrian Klaver]

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.kla...@aklaver.com

ora2pg and invalid command \N

[Charlin Barak]

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?

Thanks.