Re: [gdal-dev] ORA-01406 error when reading many attributes from Oracle [SOLVED]
Jukka Rahkonen jukka.rahkonen at mmmtike.fi writes: Jukka Rahkonen jukka.rahkonen at mmmtike.fi writes: I suspect that the reason for the trouble is that this field is a 17 character wide VARCHAR2 and I have in the data values like ÖVRE SÖDERGÄRDAN . Database is using UTF-8 and Ö,Ö and Ä are taking more than one byte each. Perhaps OCI driver develops come from some ASCII country and did not bother to think about Oracle's character and byte semantics throughly. It seems somehow fuzzy for me even after reading this article http://myorastuff.blogspot.fi/2009/02/character-and-byte- semantics-in-oracle.html I can repeat the error with a minimal one-row test table having a field NAME VARCHAR2(6) and value ÄäÖöÅå I was following a wrong track and I think I need to add one more mail to t his thread if someone happens to read it later. I had discoved earlier with the trial and error method that I could write äöåÄÖÅ characters into this Oracle database right with ogr2ogr by setting the Windows environment variable as SET NLS_LANG=finnish_finland.utf-8 That made me think that it was the correct setting and good to be used also for reading data with ogr2ogr. That was not the case. The correct NLS_LANG for the database is really finnish_finland.ISO8859-P15 By setting the environment to use that ogr2ogr is reading all the data from Oracle. However, I must do the character encoding conversion as another process because ogr2ogr cannot handle it. This means that I am totally happy because I have not found any way yet to make ogr2ogr to write the non-ASCII characters for example into Spatialite correctly. Direct writing leads to carbage in the Spatialite db. Somehow usable workaround is to write a temporary GML file first by using --config ORG_FORCE_ASCII=NO. By doing this the resulting GML file will be in ISO-8859-15. However, ogr2ogr writes always into the first line of GML files that the character encoding is UTF-8. That must be corrected by hand for making a valid GML file which can then be converted correctly into Spatialite. Not a big deal really but some of the resulting GML files are 2-3 GB in size and plain opening and saving the file takes some time. While wasting time for this I have been thinking about two options: - For writing out GML user could have an option to set the character encoding manually - For reading GML user could give an option to treat the encoding as something else than what the file itself is advertising. However, I think that the real solution would be to have some common OGR wide way to handle character encodings and conversions instead of different implementations for each driver. -Jukka Rahkonen- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] ORA-01406 error when reading many attributes from Oracle
Eli Adam eadam at co.lincoln.or.us writes: Jukka, Could it be possible that OCI driver reserves too little of some resoursesand fails because of that with large records? I am now running a query with minimum set of columns in my query and ogr2ogr is running well and I believe it will convert all the million rows from the view now. The view schema does not look extra hard for me. There are 21 attributes in the view and the longest VARCHAR2 is just 100 chararters long. This may be overly simplistic, but does the cast operator do anything? SELECT *, CAST(OGR_STYLE AS character(255)) FROM rivers It does not do anything with OGR_STYLE because I do not have such but I believe your purpose was just to give an example. CAST did not help me with the problematic VARCHAR2 field I managed to put into jail because of making troubles but perhaps I do not know how to use it correctly. I suspect that the reason for the trouble is that this field is a 17 character wide VARCHAR2 and I have in the data values like ÖVRE SÖDERGÄRDAN . Database is using UTF-8 and Ö,Ö and Ä are taking more than one byte each. Perhaps OCI driver develops come from some ASCII country and did not bother to think about Oracle's character and byte semantics throughly. It seems somehow fuzzy for me even after reading this article http://myorastuff.blogspot.fi/2009/02/character-and-byte- semantics-in-oracle.html ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] ORA-01406 error when reading many attributes from Oracle
Jukka, If the field is 17 char wide (rather than 17 bytes wide) then that is your problem. A varchar2 column can use either char or byte specifications. http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#i 45694 If neither char or byte is specified, it defaults to your NLS_LENGTH_SEMANTICS in the session that was in place when the table was created. Mike -- Michael Smith US Army Corps Remote Sensing GIS/Center On 10/17/12 5:03 AM, Jukka Rahkonen jukka.rahko...@mmmtike.fi wrote: Eli Adam eadam at co.lincoln.or.us writes: Jukka, Could it be possible that OCI driver reserves too little of some resoursesand fails because of that with large records? I am now running a query with minimum set of columns in my query and ogr2ogr is running well and I believe it will convert all the million rows from the view now. The view schema does not look extra hard for me. There are 21 attributes in the view and the longest VARCHAR2 is just 100 chararters long. This may be overly simplistic, but does the cast operator do anything? SELECT *, CAST(OGR_STYLE AS character(255)) FROM rivers It does not do anything with OGR_STYLE because I do not have such but I believe your purpose was just to give an example. CAST did not help me with the problematic VARCHAR2 field I managed to put into jail because of making troubles but perhaps I do not know how to use it correctly. I suspect that the reason for the trouble is that this field is a 17 character wide VARCHAR2 and I have in the data values like ÖVRE SÖDERGÄRDAN . Database is using UTF-8 and Ö,Ö and Ä are taking more than one byte each. Perhaps OCI driver develops come from some ASCII country and did not bother to think about Oracle's character and byte semantics throughly. It seems somehow fuzzy for me even after reading this article http://myorastuff.blogspot.fi/2009/02/character-and-byte- semantics-in-oracle.html ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] ORA-01406 error when reading many attributes from Oracle
Jukka Rahkonen jukka.rahkonen at mmmtike.fi writes: I suspect that the reason for the trouble is that this field is a 17 character wide VARCHAR2 and I have in the data values like ÖVRE SÖDERGÄRDAN . Database is using UTF-8 and Ö,Ö and Ä are taking more than one byte each. Perhaps OCI driver develops come from some ASCII country and did not bother to think about Oracle's character and byte semantics throughly. It seems somehow fuzzy for me even after reading this article http://myorastuff.blogspot.fi/2009/02/character-and-byte- semantics-in-oracle.html I can repeat the error with a minimal one-row test table having a field NAME VARCHAR2(6) and value ÄäÖöÅå VARCHAR2(6) means the same as VARCHAR2(6 CHAR) but OGR OCI driver thinks that it means the same as VARCHAR2(6 BYTE) and that leads to ORA-01406 error if the character string has more that 6 bytes even it has 6 or less characters. I will make a ticket about this. Now, can anybody suggest a handy workaround? I can take the IDs and names out into a text file and join names with the rest of the data with Spatialite or some other tool but I have 14 layers to process, 1.1 million rows on each and that would probably be too slow. I do not have rights to add a new attribute NAME2 VARCHAR(40 BYTE) into Oracle but perhaps I will need to ask DB admin to create that for me. -Jukka- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
[gdal-dev] ORA-01406 error when reading many attributes from Oracle
Hi, I have one view in Oracle 11 that I just cannot convert to another format with ogr2ogr. If I use -sql parameter SELECT * FROM view the conversion stops after couple of thousands of lines with the following error ERROR 1: ORA-01406: fetched column value was truncated in OCIStmtFetch I found this explanation from internet ORA-01406: fetched column value was truncated Cause: In a host language program, a FETCH operation was forced to truncate a character string. The program buffer area for this column was not large enough to contain the entire string. The cursor return code from the fetch was +3. Action: Increase the column buffer area to hold the largest column value or perform other appropriate processing. Could it be possible that OCI driver reserves too little of some resourses and fails because of that with large records? I am now running a query with minimum set of columns in my query and ogr2ogr is running well and I believe it will convert all the million rows from the view now. The view schema does not look extra hard for me. There are 21 attributes in the view and the longest VARCHAR2 is just 100 chararters long. -Jukka Rahkonen- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] ORA-01406 error when reading many attributes from Oracle
Jukka, Could it be possible that OCI driver reserves too little of some resourses and fails because of that with large records? I am now running a query with minimum set of columns in my query and ogr2ogr is running well and I believe it will convert all the million rows from the view now. The view schema does not look extra hard for me. There are 21 attributes in the view and the longest VARCHAR2 is just 100 chararters long. This may be overly simplistic, but does the cast operator do anything? SELECT *, CAST(OGR_STYLE AS character(255)) FROM rivers Eli -Jukka Rahkonen- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
