Comments (19)
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.
Related Issues (20)
- Bug Issue migrating data in json column (like https://github.com/darold/ora2pg/issues/1745) HOT 1
- lo_import _table_name.sh script issue
- geometry wrongly exported when there is a "hole" in the geometry
- Mysql migration HOT 1
- Migrating From SQL Server to Postgres with Ora2pg Is very Slow HOT 1
- Request for sha256sum on downloads HOT 1
- Config option to override the NLS_TIMESTAMP_FORMAT or such encoding parameters used by Ora2pg HOT 1
- Invalid check constraint conversion to PostgreSQL format while exporting schema from Oracle HOT 1
- OUT OF MEMORY EVEN THOUGH SERVER HAS OVER 16GB FREE RAM HOT 2
- [ORACLE]Slow export of BLOB and CLOB from HOT 7
- Reading the ora2pg assessment report HOT 6
- Mysql to postgresql migration HOT 9
- ERROR : DBI::db=HASH(0x29c1784f2e8)->disconnect invalidates 1 active statement handle HOT 3
- Wrong argument "extra_param OPAQUE" in function with OUT arguments (regression in release 24.3) HOT 1
- Wrong change in translation of "is not null" clause (regression in release 24.3) HOT 1
- Extract data query errorring out HOT 6
- Oracle column aliases (after TRIM function) are syntactically incorrect due to small bug introduced in commit 66f4fc37 HOT 1
- Issue in migrating from SQL Server to CloudSQL Postgres HOT 4
- Foreign server always generated with default port HOT 3
- Export data with CLOB is too slow HOT 7
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from ora2pg.