This discussion is archived
7 Replies Latest reply: Feb 10, 2013 3:36 PM by Simon Greener RSS

sqlldr error when attempting to upload shapefiles

990156 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points