Opened 15 years ago
Closed 8 years ago
#2667 closed defect (invalid)
Incorrect retrieving NUMBER width and precision for Oracle
Reported by: | aryndin99 | Owned by: | ilucena |
---|---|---|---|
Priority: | normal | Milestone: | |
Component: | OGR_SF | Version: | |
Severity: | normal | Keywords: | Oracle Spatial OCI Number ogr2ogr |
Cc: | warmerdam |
Description
Here I list command generated by ogr2ogr, it finishes with error:
INSERT INTO RUSRAYS9 (ORA_GEOMETRY, OGR_FID, "AREA", "PERIMETER", "RUSRAY_", "RUSRAY_ID", "ID", "SUBJECT", "SUBJ_ID", "STATUS", "SUB_SUBJEC", "SUB_SUBJ_S", "SUB_SUBJ_I", "N1996T", "N1996U", "N1996R", "TURNOUT_07", "YELTSIN_07", "ZYUGANOV_0", "AGAINST_07") VALUES (MDSYS.SDO_GEOMETRY(3003,NULL,NULL,:elem_info,:ordinates), 100, 35794390000.000000, 1173690.000000, 101, 40089, 720, 'Magadan', 57, 'Oblast', 'Olskiy', 'Rayon', 2, 14.7, 9.4, 5.3, 65.85, 53.26, 36.52, 9.32) ERROR 1: ORA-01438: value larger than specified precision allowed for this column
The structure of table, generated by ogr2ogr:
Name Null? Type ----------------------------------------- -------- --------------------------- OGR_FID NUMBER(38) ORA_GEOMETRY MDSYS.SDO_GEOMETRY AREA NUMBER(13,6) PERIMETER NUMBER(13,6) RUSRAY_ NUMBER(11) RUSRAY_ID NUMBER(11) ID NUMBER(6) SUBJECT VARCHAR2(25 CHAR) SUBJ_ID NUMBER(9) STATUS VARCHAR2(24 CHAR) CAPITAL VARCHAR2(26 CHAR) SUB_SUBJEC VARCHAR2(31 CHAR) SUB_SUBJ_S VARCHAR2(28 CHAR) SUB_SUBJ_I NUMBER(13) N1996T NUMBER(8,1) N1996U NUMBER(8,1) N1996R NUMBER(8,1) TURNOUT_07 NUMBER(12,2) YELTSIN_07 NUMBER(11,2) ZYUGANOV_0 NUMBER(14,2) AGAINST_07 NUMBER(12,2)
There is a problem in AREA field: 35794390000.000000 It should be NUMBER(13,6). The whole number should have 13 positions (including comma). But the original number has 18 digits. This number comes from here (file ogrociwritablelayer.cpp):
if( bPreservePrecision && oField.GetWidth() != 0 ) sprintf( szFieldType, "NUMBER(%d,%d)", oField.GetWidth(), oField.GetPrecision() );
I think the correct code should be
if( bPreservePrecision && oField.GetWidth() != 0 ) sprintf( szFieldType, "NUMBER(%d,%d)", oField.GetWidth()+oField.GetPrecision()+1, oField.GetPrecision() );
(+1 - is for comma).
Attachments (2)
Change History (10)
by , 15 years ago
Attachment: | rusrays.dbf added |
---|
by , 15 years ago
Attachment: | rusrays.rar added |
---|
follow-up: 2 comment:1 by , 15 years ago
Milestone: | 1.6.0 |
---|---|
Priority: | high → normal |
comment:2 by , 15 years ago
comment:3 by , 15 years ago
Cc: | added |
---|
I think you have done pretty much what you can. I expect to do another pass on Oracle work in the coming months at which point I hope to review existing oracle tickets like this. I hesitate to incorporate your change without any testing.
I'll add Ivan Lucena as a cc:. If he is confident of the fix he can apply it. He is working with Oracle regularly.
It would be nice to address this before the upcoming 1.6.1 release.
comment:4 by , 15 years ago
Milestone: | → 1.6.1 |
---|---|
Owner: | changed from | to
Status: | new → assigned |
I am not too familiar with OGR driver but I think I help on PL/SQL issues.
According to that http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm:
"""The syntax of a NUMBER datatype is:
NUMBER[(precision,scale)]
Precision is the total number of digits and scale is the number of digits to the right of the decimal point. You cannot use constants or variables to specify precision and scale; you must use integer literals.
To declare fixed-point numbers, for which you must specify scale, use the following form that includes both precision and scale:
NUMBER(precision,scale)
To declare floating-point numbers, for which you cannot specify precision or scale because the decimal point can float to any position, use the following form without precision and scale:
NUMBER
To declare integers, which have no decimal point, use this form with precision only:
NUMBER(precision) -- same as NUMBER(precision,0)
"""
So it looks like the concept of 'precision' in OGRFieldDefn is not the same as in PL/SQL. It is the total number of digits. While 'scale' is more like the what GetPrecision() means.
What Alexander suggest looks good. I can do some tests and I commit that afterwards if that is OK.
comment:5 by , 15 years ago
Cc: | added |
---|---|
Owner: | changed from | to
Status: | assigned → new |
Ivan,
Thanks for looking into this.
On reflection, I believe OGR and Oracle use the same approach though the names are a bit different. What OGR calls "width and precision" Oracle calls "precision and scale".
However, looking at the DBF file it appears that the AREA field is declared with a total width of 13, and a 6 decimal places, but has values that cannot be represented this way. So the problem is really that the .dbf file is improper. I'm reclassifying this as a shapelib/dbf issue, and I'll have Chaitanya look a bit deeper to see if there is something that could or should be done in the .dbf driver.
comment:6 by , 15 years ago
Hi All!!
I have compiled and tried new 1.6.2 RC2, but still with same result :( May be we can use my workaround?
Thanks, Alexander
comment:7 by , 14 years ago
Cc: | removed |
---|---|
Milestone: | 1.6.4 |
Owner: | changed from | to
Ivan,
Can you review?
comment:8 by , 8 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
The area field in the dbf file is really defined as (13,6) and the first thing to do would have been to correct the dbf file.
Replying to warmerdam:
Hi Frank!
Can I help to correct this problem?
Alexander