search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
1.01k stars 342 forks source link

converting from oracle to postgresql the table data is not imported properly #42

Closed sarithaNeerukonda closed 10 years ago

sarithaNeerukonda commented 10 years ago

Hi , when I am converting from oracle to postgresql the xml data is not importing properly.I am getting error

DBD::Oracle::st fetchall_arrayref failed: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00217: invalid character 145 (U+0091) Error at line 2 (DBD ERROR:

) [for Statement "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"] at C:/Perl/site/lib/Ora2Pg.pm line 5705. [> ] dumped 7 of 23298 rows (0.0%) table KM_REL_FF_USER_DATA

I am new to ora2pg.Please helpme out to resolve this.

darold commented 10 years ago

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.

sarithaNeerukonda commented 10 years ago

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 notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26502947 .

Thanks & Regards, Saritha

darold commented 10 years ago

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.

sarithaNeerukonda commented 10 years ago

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 notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26525496 .

Thanks & Regards, Saritha

sarithaNeerukonda commented 10 years ago

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 < sarithaneerukonda@india.cogbooks.com> 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 notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26525496 .

Thanks & Regards, Saritha

Thanks & Regards, Saritha

darold commented 10 years ago

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,

sarithaNeerukonda commented 10 years ago

After getting also I am getting the same error

On Fri, Oct 18, 2013 at 1:09 PM, Darold notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26577099 .

Thanks & Regards, Saritha

sarithaNeerukonda commented 10 years ago

after changing also I am getting the same error

On Fri, Oct 18, 2013 at 1:44 PM, Saritha N < sarithaneerukonda@india.cogbooks.com> wrote:

After getting also I am getting the same error

On Fri, Oct 18, 2013 at 1:09 PM, Darold notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26577099 .

Thanks & Regards, Saritha

Thanks & Regards, Saritha

darold commented 10 years ago

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.

darold commented 10 years ago

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.

sarithaNeerukonda commented 10 years ago

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 notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26583056 .

Thanks & Regards, Saritha

darold commented 10 years ago

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

sarithaNeerukonda commented 10 years ago

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 notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26587848 .

Thanks & Regards, Saritha

sarithaNeerukonda commented 10 years ago

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 < sarithaneerukonda@india.cogbooks.com> 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 notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26587848 .

Thanks & Regards, Saritha

Thanks & Regards, Saritha

darold commented 10 years ago

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

darold commented 10 years ago

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/

darold commented 10 years ago

Hi Saritha,

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

Regards,

sarithaNeerukonda commented 10 years ago

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 notifications@github.com 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/darold/ora2pg/issues/42#issuecomment-26797083 .

Thanks & Regards, Saritha

darold commented 10 years ago

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,