7 Replies Latest reply: Feb 10, 2013 5:36 PM by Simon Greener RSS

    sqlldr error when attempting to upload shapefiles

    990156
      This is my control file:

      LOAD DATA
      INFILE finalskye.dat
      TRUNCATE
      CONTINUEIF NEXT(1:1) = '#'
      INTO TABLE FINALSKYE
      FIELDS TERMINATED BY '|'
      TRAILING NULLCOLS (
      NATURAL      NULLIF NATURAL = BLANKS,
      NAME      NULLIF NAME = BLANKS,
      GEOM COLUMN OBJECT
      (
      SDO_GTYPE INTEGER EXTERNAL,
      SDO_ELEM_INFO VARRAY TERMINATED BY '|/'
      (X FLOAT EXTERNAL),
      SDO_ORDINATES VARRAY TERMINATED BY '|/'
      (X FLOAT EXTERNAL)
      )
      )


      But this produces

      Record 2: Rejected - Error on table FINALSKYE, column GEOM.SDO_ELEM_INFO.X.
      error converting data
      ORA-01722: invalid number

      Does anyone have a fix?
        • 1. Re: sqlldr error when attempting to upload shapefiles
          Ivan Bush
          Hi,

          It would help if you could provide a bit more information.

          Oracle version, data details and it is also better to reply to a name rather than a number.

          Regards

          Ivan
          • 2. Re: sqlldr error when attempting to upload shapefiles
            990156
            Well its a shapefile of a polygon.
            SQL*Plus: Release 11.2.0.3.0
            Copyright (c) 1982, 2011, Oracle

            And I will work on the name change!

            Does this help?
            • 3. Re: sqlldr error when attempting to upload shapefiles
              990156
              shoreline| |
              #2002|
              #1|2|1|/
              #-6.777060|57.444010|-6.777080|57.443760|-6.777120|57.442990|
              #-6.776900|57.442730|-6.777850|57.442750|-6.778120|57.442240|
              #-6.778370|57.441990|-6.780270|57.441890|-6.781240|57.441650|
              #-6.781730|57.441400|-6.782200|57.441410|-6.783150|57.441430|
              #-6.783640|57.441180|-6.784110|57.441070|-6.784160|57.440550|
              #-6.785100|57.440700|-6.785560|57.440830|-6.785370|57.440060|
              #-6.784930|57.439540|-6.784950|57.439280|-6.785210|57.438780|
              #-6.782630|57.422600|/
              # shoreline| |
              #2002|
              #1|2|1|/

              This is a shortened section of the data file.
              • 4. Re: sqlldr error when attempting to upload shapefiles
                990156
                Any help with this?
                I am using shp2sdo.exe to create the data, table creation and control files.
                • 5. Re: sqlldr error when attempting to upload shapefiles
                  John O'Toole
                  Hi 987153,

                  Its been years since I used shp2sdo.exe to load a ShapeFile. These days I use GeoRaptor (http://sourceforge.net/projects/georaptor/) when I need to load a ShapeFile to Oracle using a GUI.
                  If I wanted to script a batch loading process I'd use GDAL (http://www.gdal.org/ogr/drv_oci.html).
                  Record 2: Rejected - Error on table FINALSKYE, column GEOM.SDO_ELEM_INFO.X.
                  error converting data
                  ORA-01722: invalid number
                  The error here is with record 2, but you didn't provide enough of the data file to see where the problem is.

                  John
                  • 6. Re: sqlldr error when attempting to upload shapefiles
                    Simon Greener
                    Edinburgh,

                    You should have supplied the CREATE TABLE statement for the FINALSKYE table.

                    Why are you using two TERMINATORS for the GEOM elements? FIELDS TERMINATED BY '|' is enough, you don't need to repeat it in TERMINATED BY '|/'

                    Also, I don't have my notes with me but I can't recall if you are supposed to supply the SDO_SRID (I provided 8307 - you provide yours) in the CTL file:
                    LOAD DATA 
                     INFILE finalskye.dat
                     TRUNCATE
                     CONTINUEIF NEXT(1:1) = '#'
                     INTO TABLE FINALSKYE
                     FIELDS TERMINATED BY '|' TRAILING NULLCOLS (
                       NATURAL NULLIF NATURAL = BLANKS,
                       NAME NULLIF NAME = BLANKS,
                       GEOM COLUMN OBJECT (
                         SDO_GTYPE INTEGER EXTERNAL, 
                         SDO_SRID CONSTANT 8307, 
                         SDO_ELEM_INFO VARRAY TERMINATED BY '/'  (E FLOAT EXTERNAL), 
                         SDO_ORDINATES VARRAY TERMINATED BY '/' (O FLOAT EXTERNAL) 
                       )
                     )
                    regards
                    Simon
                    • 7. Re: sqlldr error when attempting to upload shapefiles
                      Simon Greener
                      Edinburgh,

                      Here is the answer you were after.

                      Create Table
                      DROP TABLE finalskye;
                      CREATE TABLE finalskye( 
                        natural varchar2(30),
                        name    varchar2(30),
                        geom    sdo_geometry);
                      Control File Definition
                      LOAD DATA 
                       INFILE finalskye.dat
                       TRUNCATE
                       CONTINUEIF NEXT(1:1) = '#'
                       INTO TABLE FINALSKYE
                       FIELDS TERMINATED BY '|'
                       TRAILING NULLCOLS (
                         NATURAL NULLIF NATURAL = BLANKS,
                         NAME NULLIF NAME = BLANKS,
                         GEOM COLUMN OBJECT (
                           SDO_GTYPE INTEGER EXTERNAL, 
                           SDO_ELEM_INFO VARRAY TERMINATED BY '/' (e FLOAT EXTERNAL), 
                           SDO_ORDINATES VARRAY TERMINATED BY '/' (o FLOAT EXTERNAL) 
                         )
                      )
                      Data File Structure

                      Note that each new record does NOT have a # at the beginning of it, rather it has a single space.
                       shoreline| |
                      #2002|
                      #1|2|1|/
                      #-6.777060|57.444010|-6.777080|57.443760|-6.777120|57.442990|
                      #-6.776900|57.442730|-6.777850|57.442750|-6.778120|57.442240|
                      #-6.778370|57.441990|-6.780270|57.441890|-6.781240|57.441650|
                      #-6.781730|57.441400|-6.782200|57.441410|-6.783150|57.441430|
                      #-6.783640|57.441180|-6.784110|57.441070|-6.784160|57.440550|
                      #-6.785100|57.440700|-6.785560|57.440830|-6.785370|57.440060|
                      #-6.784930|57.439540|-6.784950|57.439280|-6.785210|57.438780|
                      #-6.782630|57.422600|/
                       shoreline| |
                      #2002|
                      #1|2|1|/
                      #-6.781730|57.441400|-6.782200|57.441410|-6.783150|57.441430|/
                      Result
                      select * from finalskye;
                      -- Result
                      --
                      NATURAL   NAME   GEOM
                      --------- ------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      shoreline (NULL) SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-6.77706,57.44401,-6.77708,57.44376,-6.77712,57.44299,-6.7769,57.44273,-6.77785,57.44275,-6.77812,57.44224,-6.77837,57.44199,-6.78027,57.44189,-6.78124,57.44165,-6.78173,57.4414,-6.7822,57.44141,-6.78315,57.44143,-6.78364,57.44118,-6.78411,57.44107,-6.78416,57.44055,-6.7851,57.4407,-6.78556,57.44083,-6.78537,57.44006,-6.78493,57.43954,-6.78495,57.43928,-6.78521,57.43878,-6.78263,57.4226))
                      shoreline (NULL) SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-6.78173,57.4414,-6.7822,57.44141,-6.78315,57.44143))
                      This is how to set out a request to this forum - provide the table structure, the control file, the data.....

                      This answer is correct, please award points.

                      regards
                      Simon