converting from oracle to postgresql the table data is not imported properly about ora2pg HOT 19 CLOSED

Hi,

This seems to be a character set issue. What NLS_LANG value are you using in ora2pg.conf and what is the output of the following command:

ora2pg -t SHOW_ENCODING

Please, also add your version of Ora2Pg and Oracle database.

from ora2pg.

Thanks a lot for reply

after executing this ora2pg -t SHOW_ENCODING

NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
CLIENT ENCODING WIN1252

I got this output

On Thu, Oct 17, 2013 at 6:45 PM, Darold [email protected] wrote:

Hi,

This seems to be a character set issue. What NLS_LANG value are you using
in ora2pg.conf and what is the output of the following command:

ora2pg -t SHOW_ENCODING

Please, also add your version of Ora2Pg and Oracle database.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26502947
.

Thanks & Regards,
Saritha

from ora2pg.

Ok,

Could you try with NLS_LANG set to AMERICAN_AMERICA.UTF8 and CLIENT_ENCODING to UTF8 in your ora2pg.conf file.

Let us know.

from ora2pg.

Hi,

After changing also I am getting the same error.

DBD::Oracle::st fetchall_arrayref failed: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 147 (U+0093)
Error at line 2 (DBD ERROR: OCIStmtFetch) [for Statement "SELECT
"ID","USERID","
FF_ID",a."HISTORY".extract('/').getClobVal(),"CURENT_VAL","SHARE_PERMS","SHARE_W
ORKING_GROUP","CREATED_BY","MODIFIED_BY",to_char("CREATED_DATE",
'YYYY-MM-DD HH2
4:MI:SS'),to_char("MODIFIED_DATE", 'YYYY-MM-DD HH24:MI:SS') FROM
KMUSER.KM_REL_F
F_USR_DATA a"] at C:/Perl/site/lib/Ora2Pg.pm line 5705.

On Thu, Oct 17, 2013 at 10:15 PM, Darold [email protected] wrote:

Ok,

Could you try with NLS_LANG set to AMERICAN_AMERICA.UTF8 and
CLIENT_ENCODING to UTF8 in your ora2pg.conf file.

Let us know.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26525496
.

Thanks & Regards,
Saritha

from ora2pg.

Hi ,
The table KMUSER.KM_REL_FF_USR_DATA is having arround 23,000 rows but its
fetching only 12 rows.The table column HISTORY is xml type.To support xml
type I have to change any thing.Please help me out................

On Fri, Oct 18, 2013 at 10:19 AM, Saritha N <
[email protected]> wrote:

Hi,

After changing also I am getting the same error.

DBD::Oracle::st fetchall_arrayref failed: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 147 (U+0093)
Error at line 2 (DBD ERROR: OCIStmtFetch) [for Statement "SELECT
"ID","USERID","

FF_ID",a."HISTORY".extract('/').getClobVal(),"CURENT_VAL","SHARE_PERMS","SHARE_W
ORKING_GROUP","CREATED_BY","MODIFIED_BY",to_char("CREATED_DATE",
'YYYY-MM-DD HH2
4:MI:SS'),to_char("MODIFIED_DATE", 'YYYY-MM-DD HH24:MI:SS') FROM
KMUSER.KM_REL_F
F_USR_DATA a"] at C:/Perl/site/lib/Ora2Pg.pm line 5705.

On Thu, Oct 17, 2013 at 10:15 PM, Darold [email protected] wrote:

Ok,

Could you try with NLS_LANG set to AMERICAN_AMERICA.UTF8 and
CLIENT_ENCODING to UTF8 in your ora2pg.conf file.

Let us know.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26525496
.

Thanks & Regards,
Saritha

Thanks & Regards,
Saritha

from ora2pg.

Hi Saritha,

As I say the issue comes from Oracle encoding. Here the characters that generate the error seems not to be an UTF8 character or at least it is not supported by Oracle's UTF8 character set (Unicode 3.1). You may give an other try with NLS_LANG set to AMERICAN_AMERICA.AL32UTF8 in ora2pg.conf. Those characters should be supported by AL32UTF8 Unicode 5.0 character set. Let us know.

Best regards,

from ora2pg.

After getting also I am getting the same error

On Fri, Oct 18, 2013 at 1:09 PM, Darold [email protected] wrote:

Hi Saritha,

As I say the issue comes from Oracle encoding. Here the characters that
generate the error seems not to be an UTF8 character or at least it is not
supported by Oracle's UTF8 character set (Unicode 3.1). You may give an
other try with NLS_LANG set to AMERICAN_AMERICA.AL32UTF8 in ora2pg.conf.
Those characters should be supported by AL32UTF8 Unicode 5.0 character set.
Let us know.

Best regards,

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26577099
.

Thanks & Regards,
Saritha

from ora2pg.

after changing also I am getting the same error

On Fri, Oct 18, 2013 at 1:44 PM, Saritha N <
[email protected]> wrote:

After getting also I am getting the same error

On Fri, Oct 18, 2013 at 1:09 PM, Darold [email protected] wrote:

Hi Saritha,

As I say the issue comes from Oracle encoding. Here the characters that
generate the error seems not to be an UTF8 character or at least it is not
supported by Oracle's UTF8 character set (Unicode 3.1). You may give an
other try with NLS_LANG set to AMERICAN_AMERICA.AL32UTF8 in ora2pg.conf.
Those characters should be supported by AL32UTF8 Unicode 5.0 character set.
Let us know.

Best regards,

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26577099
.

Thanks & Regards,
Saritha

Thanks & Regards,
Saritha

from ora2pg.

Well, this is always the same remaining question: How is it possible to insert into and Oracle field a non supported character using a certain character set encoding without any error ? If anyone have the answer I will be please to learn it. Are you are inserting those XML data through a CLOB ?

If that character is not part of the AL32UTF8 unicode character set, you may try others and if you have a charset definition in your xml data try with the same encoding. Do you have the same error using sqlplus or an other client ? If not, try to figure out what encoding they used. You can also try to open an issue to Oracle support to see why retrieving data using the following query:

SELECT "ID","USERID","FF_ID",a."HISTORY".extract('/').getClobVal(),"CURENT_VAL","SHARE_PERMS","SHARE_WORKING_GROUP","CREATED_BY","MODIFIED_BY",to_char("CREATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),to_char("MODIFIED_DATE", 'YYYY-MM-DD HH24:MI:SS') FROM KMUSER.KM_REL_FF_USR_DATA a

is returning the following error:

DBD::Oracle::st fetchall_arrayref failed: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00217: invalid character 147 (U+0093) Error at line 2 (DBD ERROR: OCIStmtFetch) [for Statement "..."

It is better to not talk about the tool you are using :-)

This is an Oracle issue, so you have two news. One bad and one good news. The bad is that you have to edit the failing tuples, the good is that you will never have this problem with PostgreSQL :-)

Please let us know here the result of your further tries because lot of people encounter the same kind of error. Also I'm curious to know if you have the same issue with sqlplus.

If anyone can help too, don't hesitate to post to this thread as I've sold out my Oracle knowledge.

Best regards.

from ora2pg.

After some discussion with a colleague with a better Oracle knowledge, if you can reproduce the issue with sqlplus and you can't find any useful NLS_ENCODING charset the solution could be to transform your column into a CLOB. Of course don't do that if it is your production database or take care to copy the table into a new one that will not be used by your applications. Ora2Pg will export CLOB as text by default but it will be easy to force it to export as xml type.

from ora2pg.

After exporting table data from sqlplus ,If I import in postgres its
throwing the encoding problems.

On Fri, Oct 18, 2013 at 3:06 PM, Darold [email protected] wrote:

Well, this is always the same remaining question: How is it possible to
insert into and Oracle field a non supported character using a certain
character set encoding without any error ? If anyone have the answer I will
be please to learn it. Are you are inserting those XML data through a CLOB ?

If that character is not part of the AL32UTF8 unicode character set, you
may try others and if you have a charset definition in your xml data try
with the same encoding. Do you have the same error using sqlplus or an
other client ? If not, try to figure out what encoding they used. You can
also try to open an issue to Oracle support to see why retrieving data
using the following query:

SELECT "ID","USERID","FF_ID",a."HISTORY".extract('/').getClobVal(),"CURENT_VAL","SHARE_PERMS","SHARE_WORKING_GROUP","CREATED_BY","MODIFIED_BY",to_char("CREATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),to_char("MODIFIED_DATE", 'YYYY-MM-DD HH24:MI:SS') FROM KMUSER.KM_REL_FF_USR_DATA a

is returning the following error:

DBD::Oracle::st fetchall_arrayref failed: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00217: invalid character 147 (U+0093) Error at line 2 (DBD ERROR: OCIStmtFetch) [for Statement "..."

It is better to not talk about the tool you are using :-)

This is an Oracle issue, so you have two news. One bad and one good news.
The bad is that you have to edit the failing tuples, the good is that you
will never have this problem with PostgreSQL :-)

Please let us know here the result of your further tries because lot of
people encounter the same kind of error. Also I'm curious to know if you
have the same issue with sqlplus.

If anyone can help too, don't hesitate to post to this thread as I've sold
out my Oracle knowledge.

Best regards.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26583056
.

Thanks & Regards,
Saritha

from ora2pg.

How did you export it and what is your sqlplus client encoding ?

from ora2pg.

I used this command to export the table data

set nls_lang= AMERICAN_AMERICA.WE8MSWIN1252
exp username/pwa@oracle11 tables=tablename file='filepath\tablename.sql'

On Fri, Oct 18, 2013 at 4:43 PM, Darold [email protected] wrote:

How did you export it and what is your sqlplus client encoding ?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26587848
.

Thanks & Regards,
Saritha

from ora2pg.

Hi,

I exportted table data from sqldeveloper tool in insert fromat.Its
importing into postgresql but the date format is changing.
its throwing an error
2013-10-18 11:44:01 IST ERROR: hour "0" is invalid for the 12-hour clock
2013-10-18 11:44:01 IST HINT: Use the 24-hour clock, or give an hour
between 1 and 12.

I am getting all the data values as "0001-11-30 08:23:10 BC".
How to change the date fromat in Postgresql from 24 hrs to 12 hrs or 12
hrs to 24 hrs

On Fri, Oct 18, 2013 at 4:57 PM, Saritha N <
[email protected]> wrote:

I used this command to export the table data

set nls_lang= AMERICAN_AMERICA.WE8MSWIN1252
exp username/pwa@oracle11 tables=tablename
file='filepath\tablename.sql'

On Fri, Oct 18, 2013 at 4:43 PM, Darold [email protected] wrote:

How did you export it and what is your sqlplus client encoding ?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26587848
.

Thanks & Regards,
Saritha

Thanks & Regards,
Saritha

from ora2pg.

Please do not change the subject, this is not a sqldeveloper mailing list and the way you are trying to solve the issue with PostgreSQL is definitively the wrong way.

When I asked for sqlplus export I was talking about a SELECT statement to see if you have the same error, not the exp utility that do not deal with encoding.

Could you tell me if you had enabled XML_PRETTY in ora2pg.conf ?

If you activate this directive, ora2pg will use the extract('/').getStringVal() to get the XML value. If is it not enabled ora2pg will use extract('/').getClobVal().

from ora2pg.

You don't reply to me about the Oracle database version you are using, if you are using 11.2 please take a look at this blog post http://oraclespot.wordpress.com/2012/01/27/bug11877267/

from ora2pg.

Hi Saritha,

Have you been able to solve the issue ? I'm pretty interested by the resut.

Regards,

from ora2pg.

Hi,

--I changed the date format in SQLDeveloper to 'YYYY-MM-DD HH24.MI.SSXFF
AM'
--From SqlDeveloper I have exported the data in the form of insert as *.sql
file.
--Then Again I have imported the *.sql file in postgres.
Now its working fine...

On Tue, Oct 22, 2013 at 5:35 PM, Darold [email protected] wrote:

Hi Saritha,

Have you been able to solve the issue ? I'm pretty interested by the resut.

Regards,

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/42#issuecomment-26797083
.

Thanks & Regards,
Saritha

from ora2pg.

Well we have loose a chance to find a fix and to save our time, but I'm glad you have found a workaround.

Regards,

from ora2pg.