11 Replies Latest reply on Nov 28, 2012 9:31 AM by 976665

    Problems with shp2sdo (.shp -> table)

      I'm having difficulties to load .shp into oracle. This it what I do:

      1) shp2sdo.exe bel7__________nw -g geom -d -x (-180,180) -y (-90,90) -s 8307 -v

      -> this works perfect, .sql and .ctl are created

      2) on my isqlplus webserver: I load bel7__________nw.sql and execute it

      -> this works perfect, table is created

      3) sqlldr login/password bel7__________a8

      -> this doesn't work; when I check the .log file, it says:

      Record 1: geweigerd - fout in tabel BEL7__________NW, kolom GEOM.SDO_ORDINATES.X. (english: denied - error in table .., column ...)
      Fout bij converteren gegevens. (english: error converting data)
      ORA-01722: Ongeldig getal. (english: invalid number)

      Record 2: geweigerd - fout in tabel BEL7__________NW, kolom GEOM.SDO_ORDINATES.X.
      Fout bij converteren gegevens.
      ORA-01722: Ongeldig getal.


      Does anybody what is going on and what is wrong? I assume something is wrong with the spatial reference system id (srid), so Oracle isn't able to convert data, but I also tried setting -s to 1003, without success. Leaving the -s options resulted in the same errors.

      The full log is available at http://www.clueless.be/Misc/bel7__________nw.log, feel free to ask for translations!
        • 1. Re: Problems with shp2sdo (.shp -> table)
          Ignore my assumption about srid. 8307 is the correct value, got it confirmed from the teleatlas manuals.
          • 2. Re: Problems with shp2sdo (.shp -> table)
            Hello Peter,

            I've seen this problem when shp2sdo is run on one platform (windows) and loaded on another platform (unix) without running dos2unix or going through ftp.

            Also, i've seen it when there is a problem between character sets - shp2sdo generates data in us ascii format. If this is the problem:
            On a WINDOWS NT/2000/XP system, fix it by setting the NLS_LANG registry entry for the Oracle Home to: AMERICAN_AMERICA.WE8ISO8859P1. On UNIX systems, the Oracle initialization parameters can be set as follows:
            nls_language AMERICAN
            nls_territory AMERICA

            Hope one fo these fixes it.

            • 3. Re: Problems with shp2sdo (.shp -> table)
              Cool Dan, setting NLS_LANG to AMERICAN_AMERICA.WE8ISO8859P1 did the trick! Thanks a lot, I would never have found that on my own ;)

              thanks again,

              • 4. Re: Problems with shp2sdo (.shp -> table)
                Hmmmm, another problem :(

                Some columns of type NUMBER appear to have negative numbers for all rows in the created table and the positive value of these numbers are not the same as they are in the .dbf-file. If the latter was true, then I could easily fix this by multiplying the columns by -1.

                It looks like this only happens with 'big' numbers in the .dbf-file. There must happen a conversion somewhere, since all the values for that particular column in the table are distinct, so the reader/oracle is able the handle/read 'big' numbers. (select distinct(id) from ... == #rows in .dbf)

                For example: 10560001587671 (in .dbf) becomes -1322993193 (in table).

                Is this a known problem?

                thanks a lot,

                • 5. Re: Problems with shp2sdo (.shp -> table)
                  This is the first time I've heard of it, and I do most of the support for this tool.

                  Is the data in file correct, and the problem is during the load, or is the data incorrect in the file?

                  Can you make your shape file available?


                  • 6. Re: Problems with shp2sdo (.shp -> table)
                    Sorry, by "file" in the previous message I meant the data file that the tool creates.
                    • 7. Re: Problems with shp2sdo (.shp -> table)
                      When I open the .dbf file in MS Excel, the values are 'big' and positive (10560001587671), but when I select the same columns in the oracle dbase, the values are 'small' and negative (-1322993193). I suppose something goes wrong while loading the .shp/.dbf into the database.

                      Can you please send me a mail at 88058_AT_clueless.be (this mail is correct, I made a forward to avoid future spam), so I can send you an example of files where it goes wrong.

                      Maybe it has something to do with the NLS_LANG registry setting, which I had to set to AMERICAN_AMERICA.WE8ISO8859P1 (see above)?

                      Thanks a lot Dan,

                      • 8. Re: Problems with shp2sdo (.shp -> table)
                        I uploaded an example to oracle-ftp.oracle.com, in /incoming/for_88058_by_peter/ and the file is called 88058.zip.


                        • 9. Re: Problems with shp2sdo (.shp -> table)

                          a converter with a potential fix for this problem has been forwarded to Peter.

                          • 10. Re: Problems with shp2sdo (.shp -> table)
                            Jup! Got it in my box.


                            • 11. Re: Problems with shp2sdo (.shp -> table)
                              I'm having the same problem! This is an incredible bug.

                              In my case:

                              0. I have in shapefiles some ID's fields (for identify each tables or join to some other) with 12 or 14 digit length (NUMBER type).
                              1. I correctly exported data from shp to different files with shp2sdo utility.
                              2. When I open .dat file I realised ID's values are completely different and shorter than It must to do.
                              3. If I import all data to Oracle DB, data and ID's values are still shorter than would be.

                              Ex: Original ID: 17240034001163
                              After shp2sdo in .dat: 35275019

                              I was looking for a solution but I did not have not found!!

                              Could you share your "converter with a potential fix" for that bug?