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)

rusrays.dbf (670.4 KB ) - added by aryndin99 15 years ago.
rusrays.rar (671.5 KB ) - added by aryndin99 15 years ago.

Download all attachments as: .zip

Change History (10)

by aryndin99, 15 years ago

Attachment: rusrays.dbf added

by aryndin99, 15 years ago

Attachment: rusrays.rar added

comment:1 by warmerdam, 15 years ago

Milestone: 1.6.0
Priority: highnormal

in reply to:  1 comment:2 by aryndin99, 15 years ago

Replying to warmerdam:

Hi Frank!

Can I help to correct this problem?

Alexander

comment:3 by warmerdam, 15 years ago

Cc: ilucena 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 ilucena, 15 years ago

Milestone: 1.6.1
Owner: changed from warmerdam to ilucena
Status: newassigned

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 warmerdam, 15 years ago

Cc: warmerdam added
Owner: changed from ilucena to chaitanya
Status: assignednew

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 aryndin99, 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 warmerdam, 14 years ago

Cc: ilucena removed
Milestone: 1.6.4
Owner: changed from chaitanya to ilucena

Ivan,

Can you review?

comment:8 by Jukka Rahkonen, 8 years ago

Resolution: invalid
Status: newclosed

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.

Note: See TracTickets for help on using tickets.