Home » RDBMS Server » Server Utilities » sql*loader: 2 geometry columns in 1 table
sql*loader: 2 geometry columns in 1 table [message #529806] Thu, 03 November 2011 02:30 Go to next message
bibber
Messages: 38
Registered: August 2006
Member
Hello,

I having difficulties loading data into a table that contains 2 geometry columns using sql*loader. With geometry column I have no problem, but although I'm using the same syntax I'm not succesful with 2 geometry columns.
The error could be in the control-file, or in the data-file. The log-file states: record 1: refused - error in table dummy, column sps_geometrie.sdo_gtype (ORA-01722: invalid number). Apparently, when loading the second geometry column, it's not loading '2001', but another value.

Anyone has the solution?
thanks, Rob

data-file:
89993|$$R. Müller, tandarts$$|$$Koninginneweg 150$$|$$Tandartsen$$|J|2001|90112|J|119177|485130||;:|N||N|
92183|$$Kinderdagverblijf Daantjuh$$|$$James Wattstraat 70$$|$$Kinderopvang$$|J|2001|90112|J|123422|484923||;:|J|2001|90112|J|123422|484923||;:|J|2001|90112|J|123422|484923||;:

control-file:
LOAD DATA
INFILE 'dummy.dat'
APPEND INTO TABLE dummy
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
 ID DECIMAL EXTERNAL
,NAAM CHAR "replace(replace(replace(:NAAM,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,OMSCHRIJVING CHAR "replace(replace(replace(:OMSCHRIJVING,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,CODE CHAR "replace(replace(replace(:CODE,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
, heeft_vbo_geometrie FILLER
,VBO_GEOMETRIE COLUMN OBJECT NULLIF heeft_vbo_geometrie="N"
  ( sdo_gtype INTEGER EXTERNAL,
    sdo_srid INTEGER EXTERNAL,
     isPunt FILLER,
    SDO_POINT COLUMN OBJECT NULLIF VBO_GEOMETRIE.isPunt="N"
    ( X INTEGER EXTERNAL,
      Y INTEGER EXTERNAL,
      Z INTEGER EXTERNAL),
    SDO_ELEM_INFO VARRAY terminated by ';' NULLIF VBO_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)),
    SDO_ORDINATES VARRAY terminated by ':' NULLIF VBO_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)) 
  )
, heeft_sps_geometrie FILLER
,SPS_GEOMETRIE COLUMN OBJECT NULLIF heeft_sps_geometrie="N"
  ( sdo_gtype INTEGER EXTERNAL,
    sdo_srid INTEGER EXTERNAL,
     isPunt FILLER,
    SDO_POINT COLUMN OBJECT NULLIF SPS_GEOMETRIE.isPunt="N"
    ( X INTEGER EXTERNAL,
      Y INTEGER EXTERNAL,
      Z INTEGER EXTERNAL),
    SDO_ELEM_INFO VARRAY terminated by ';' NULLIF SPS_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)),
    SDO_ORDINATES VARRAY terminated by ':' NULLIF SPS_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)) 
  )
, heeft_lps_geometrie FILLER
,LPS_GEOMETRIE COLUMN OBJECT NULLIF heeft_lps_geometrie="N"
  ( sdo_gtype INTEGER EXTERNAL,
    sdo_srid INTEGER EXTERNAL,
     isPunt FILLER,
    SDO_POINT COLUMN OBJECT NULLIF LPS_GEOMETRIE.isPunt="N"
    ( X INTEGER EXTERNAL,
      Y INTEGER EXTERNAL,
      Z INTEGER EXTERNAL),
    SDO_ELEM_INFO VARRAY terminated by ';' NULLIF LPS_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)),
    SDO_ORDINATES VARRAY terminated by ':' NULLIF LPS_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)) 
  )
)

log-file:

SQL*Loader: Release 10.2.0.1.0 - Production on Wo Nov 2 16:10:38 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control-bestand:   dummy.ctl
Gegevensbestand :     dummy.dat
  Foutenbestand :      dummy.bad  (`bad file' met niet-ingevoegde records)
  Uitvalbestand:  niets opgegeven
             (`discard file')
 (Sta alle uitvalrecords toe)

Aantal te laden   : ALL
Aantal over te slaan: 0
Fouten toegestaan   : 50
Bind-array:     64 rijen, maximum 256000 bytes
Vervolg:    niets opgegeven
Gebruikt pad:      Conventioneel

Tabel DUMMY, geladen van elk logisch record.
Van kracht zijnde invoegoptie voor deze tabel: APPEND
TRAILING NULLCOLS-optie staat aan.

   Kolomnaam                    Positie    Lngt Eind Insl Gegevenstype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   |       CHARACTER            
    Insluitingsstring: '$$'
NAAM                                 NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
    SQL-string voor kolom: "replace(replace(replace(:NAAM,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
OMSCHRIJVING                         NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
    SQL-string voor kolom: "replace(replace(replace(:OMSCHRIJVING,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
CODE                                 NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
    SQL-string voor kolom: "replace(replace(replace(:CODE,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
HEEFT_VBO_GEOMETRIE                  NEXT     *   |       CHARACTER            
  (FILLER FIELD)
    Insluitingsstring: '$$'
VBO_GEOMETRIE                     DERIVED     *           COLUMN OBJECT        
    NULL indien HEEFT_VBO_GEOMETRIE = 0X4e(teken 'N')

*** Velden in VBO_GEOMETRIE
SDO_GTYPE                            NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
SDO_SRID                             NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
ISPUNT                               NEXT     *   |       CHARACTER            
  (FILLER FIELD)
    Insluitingsstring: '$$'
SDO_POINT                         DERIVED     *           COLUMN OBJECT        
    NULL indien VBO_GEOMETRIE.ISPUNT = 0X4e(teken 'N')

*** Velden in VBO_GEOMETRIE.SDO_POINT
X                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
Y                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
Z                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in VBO_GEOMETRIE.SDO_POINT

SDO_ELEM_INFO                     DERIVED     *   ;       VARRAY               
    NULL indien VBO_GEOMETRIE.ISPUNT = 0X4a(teken 'J')

*** Velden in VBO_GEOMETRIE.SDO_ELEM_INFO
ELEMENTS                            FIRST     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in VBO_GEOMETRIE.SDO_ELEM_INFO

SDO_ORDINATES                     DERIVED     *   :       VARRAY               
    NULL indien VBO_GEOMETRIE.ISPUNT = 0X4a(teken 'J')

*** Velden in VBO_GEOMETRIE.SDO_ORDINATES
ELEMENTS                            FIRST     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in VBO_GEOMETRIE.SDO_ORDINATES

*** Einde van velden in VBO_GEOMETRIE

HEEFT_SPS_GEOMETRIE                  NEXT     *   |       CHARACTER            
  (FILLER FIELD)
    Insluitingsstring: '$$'
SPS_GEOMETRIE                     DERIVED     *           COLUMN OBJECT        
    NULL indien HEEFT_SPS_GEOMETRIE = 0X4e(teken 'N')

*** Velden in SPS_GEOMETRIE
SDO_GTYPE                            NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
SDO_SRID                             NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
ISPUNT                               NEXT     *   |       CHARACTER            
  (FILLER FIELD)
    Insluitingsstring: '$$'
SDO_POINT                         DERIVED     *           COLUMN OBJECT        
    NULL indien SPS_GEOMETRIE.ISPUNT = 0X4e(teken 'N')

*** Velden in SPS_GEOMETRIE.SDO_POINT
X                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
Y                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
Z                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in SPS_GEOMETRIE.SDO_POINT

SDO_ELEM_INFO                     DERIVED     *   ;       VARRAY               
    NULL indien SPS_GEOMETRIE.ISPUNT = 0X4a(teken 'J')

*** Velden in SPS_GEOMETRIE.SDO_ELEM_INFO
ELEMENTS                            FIRST     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in SPS_GEOMETRIE.SDO_ELEM_INFO

SDO_ORDINATES                     DERIVED     *   :       VARRAY               
    NULL indien SPS_GEOMETRIE.ISPUNT = 0X4a(teken 'J')

*** Velden in SPS_GEOMETRIE.SDO_ORDINATES
ELEMENTS                            FIRST     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in SPS_GEOMETRIE.SDO_ORDINATES

*** Einde van velden in SPS_GEOMETRIE

HEEFT_LPS_GEOMETRIE                  NEXT     *   |       CHARACTER            
  (FILLER FIELD)
    Insluitingsstring: '$$'
LPS_GEOMETRIE                     DERIVED     *           COLUMN OBJECT        
    NULL indien HEEFT_LPS_GEOMETRIE = 0X4e(teken 'N')

*** Velden in LPS_GEOMETRIE
SDO_GTYPE                            NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
SDO_SRID                             NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
ISPUNT                               NEXT     *   |       CHARACTER            
  (FILLER FIELD)
    Insluitingsstring: '$$'
SDO_POINT                         DERIVED     *           COLUMN OBJECT        
    NULL indien LPS_GEOMETRIE.ISPUNT = 0X4e(teken 'N')

*** Velden in LPS_GEOMETRIE.SDO_POINT
X                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
Y                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
Z                                    NEXT     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in LPS_GEOMETRIE.SDO_POINT

SDO_ELEM_INFO                     DERIVED     *   ;       VARRAY               
    NULL indien LPS_GEOMETRIE.ISPUNT = 0X4a(teken 'J')

*** Velden in LPS_GEOMETRIE.SDO_ELEM_INFO
ELEMENTS                            FIRST     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in LPS_GEOMETRIE.SDO_ELEM_INFO

SDO_ORDINATES                     DERIVED     *   :       VARRAY               
    NULL indien LPS_GEOMETRIE.ISPUNT = 0X4a(teken 'J')

*** Velden in LPS_GEOMETRIE.SDO_ORDINATES
ELEMENTS                            FIRST     *   |       CHARACTER            
    Insluitingsstring: '$$'
*** Einde van velden in LPS_GEOMETRIE.SDO_ORDINATES

*** Einde van velden in LPS_GEOMETRIE


waarde voor parameter ROWS is gewijzigd van 64 in 31
Record 1: geweigerd - fout in tabel DUMMY, kolom SPS_GEOMETRIE.SDO_GTYPE.
ORA-01722: invalid number

Record 2: geweigerd - fout in tabel DUMMY, kolom SPS_GEOMETRIE.SDO_GTYPE.
ORA-01722: invalid number


Tabel DUMMY:
  Laden van 0 Rijen is  geslaagd.
  2 Rijen is niet geladen vanwege gegevensfouten.
  0 Rijen is niet geladen omdat alle WHEN-clausules mislukten.
  0 Rijen is niet geladen omdat alle velden leeg waren.

Ruimte gereserveerd voor bind-array:          248682 bytes (31 rijen)
Bytes leesbuffer: 1048576

Totaal aantal overgeslagen logische records:          0
Totaal aantal gelezen logische records:                 2
Totaal aantal geweigerde logische records:         2
Totaal aantal uitgevallen logische records:        0

Uitvoering begon op Wo Nov 02 16:10:38 2011.
Uitvoering eindigde op Wo Nov 02 16:10:38 2011.

Verstreken tijd was:  00:00:00.21
CPU-tijd was:         00:00:00.04
Re: sql*loader: 2 geometry columns in 1 table [message #529818 is a reply to message #529806] Thu, 03 November 2011 03:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
It is not expecting an extra | after ;: in the data file. Please see the demonstration below with the extra | removed between objects in dummy.dat. In the future please remember to provide your table structure.

-- dummy.dat:
89993|$$R. Müller, tandarts$$|$$Koninginneweg 150$$|$$Tandartsen$$|J|2001|90112|J|119177|485130||;:N||N|
92183|$$Kinderdagverblijf Daantjuh$$|$$James Wattstraat 70$$|$$Kinderopvang$$|J|2001|90112|J|123422|484923||;:J|2001|90112|J|123422|484923||;:J|2001|90112|J|123422|484923||;:


-- test.ctl:
LOAD DATA
INFILE 'dummy.dat'
APPEND INTO TABLE dummy
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
ID DECIMAL EXTERNAL
,NAAM CHAR "replace(replace(replace(:NAAM,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,OMSCHRIJVING CHAR "replace(replace(replace(:OMSCHRIJVING,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,CODE CHAR "replace(replace(replace(:CODE,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,heeft_vbo_geometrie FILLER
,VBO_GEOMETRIE COLUMN OBJECT NULLIF heeft_vbo_geometrie="N"
( sdo_gtype INTEGER EXTERNAL,
sdo_srid INTEGER EXTERNAL,
isPunt FILLER,
SDO_POINT COLUMN OBJECT NULLIF VBO_GEOMETRIE.isPunt="N"
( X INTEGER EXTERNAL,
Y INTEGER EXTERNAL,
Z INTEGER EXTERNAL),
SDO_ELEM_INFO VARRAY terminated by ';' NULLIF VBO_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38)),
SDO_ORDINATES VARRAY terminated by ':' NULLIF VBO_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38))
)
, heeft_sps_geometrie FILLER
,SPS_GEOMETRIE COLUMN OBJECT NULLIF heeft_sps_geometrie="N"
( sdo_gtype INTEGER EXTERNAL,
sdo_srid INTEGER EXTERNAL,
isPunt FILLER,
SDO_POINT COLUMN OBJECT NULLIF SPS_GEOMETRIE.isPunt="N"
( X INTEGER EXTERNAL,
Y INTEGER EXTERNAL,
Z INTEGER EXTERNAL),
SDO_ELEM_INFO VARRAY terminated by ';' NULLIF SPS_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38)),
SDO_ORDINATES VARRAY terminated by ':' NULLIF SPS_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38))
)
, heeft_lps_geometrie FILLER
,LPS_GEOMETRIE COLUMN OBJECT NULLIF heeft_lps_geometrie="N"
( sdo_gtype INTEGER EXTERNAL,
sdo_srid INTEGER EXTERNAL,
isPunt FILLER,
SDO_POINT COLUMN OBJECT NULLIF LPS_GEOMETRIE.isPunt="N"
( X INTEGER EXTERNAL,
Y INTEGER EXTERNAL,
Z INTEGER EXTERNAL),
SDO_ELEM_INFO VARRAY terminated by ';' NULLIF LPS_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38)),
SDO_ORDINATES VARRAY terminated by ':' NULLIF LPS_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38))
)
)


-- table, load, and results:
SCOTT@orcl_11gR2> create table dummy
  2    (id	       NUMBER,
  3  	naam	       VARCHAR2(26),
  4  	omschrijving   VARCHAR2(19),
  5  	code	       VARCHAR2(12),
  6  	VBO_GEOMETRIE  MDSYS.SDO_GEOMETRY,
  7  	SPS_GEOMETRIE  MDSYS.SDO_GEOMETRY,
  8  	LPS_GEOMETRIE  MDSYS.SDO_GEOMETRY)
  9  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from dummy
  2  /

        ID NAAM                       OMSCHRIJVING        CODE
---------- -------------------------- ------------------- ------------
VBO_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
SPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
LPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
     89993 R. Müller, tandarts        Koninginneweg 150   Tandartsen
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(119177, 485130, NULL), NULL, NULL)

SDO_GEOMETRY(NULL, NULL, SDO_POINT_TYPE(NULL, NULL, NULL), SDO_ELEM_INFO_ARRAY()
, SDO_ORDINATE_ARRAY())

     92183 Kinderdagverblijf Daantjuh James Wattstraat 70 Kinderopvang
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)


2 rows selected.

SCOTT@orcl_11gR2>


Re: sql*loader: 2 geometry columns in 1 table [message #529917 is a reply to message #529818] Thu, 03 November 2011 10:40 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
Thanks for the reply. It helped me a lot, but didn't solve the entire problem.

Your code successfully imported point geometries, when all columns have a value. I tried to do same with both point, line and area geometries and got the following result:
Inputdata contains:
id=89993: area, point, point
id=92183: point, point, point
id=1: null, area, area
id=2: null, point, line

Import was successfully according to the log-file, but the result:
id=89993: area, point, point
id=92183: point, point, point
id=1: null, area, null
id=2: null, line, null

Apparently, it still has problems with null values and it seems that for id's 1 and 2, the second geometry is ignored and the third geometry 'moved' to the second position.

data-file:
89993|$$R. Müller, tandarts$$|$$Koninginneweg 150$$|$$Tandartsen$$|J|2003|90112|N||||1|1003|1|;121537,291152505|485613,546907746|120207,14689318|485286,584073052|119939,631846612|483822,682290444|120636,78697966|484604,14455586|120994,830085853|483770,665475833|121752,789384463|484751,553979916|121537,291152505|485613,546907746|:J|2001|90112|J|119177|485130||;:J|2001|90112|J|119177|485130||;:
92183|$$Kinderdagverblijf Daantjuh$$|$$James Wattstraat 70$$|$$Kinderopvang$$|J|2001|90112|J|123422|484923||;:J|2001|90112|J|123422|484923||;:J|2001|90112|J|123422|484923||;:
1|$$area$$|$$$$|$$$$|N||J|2003|90112|N||||1|1003|1|;121537,291152505|485613,546907746|120207,14689318|485286,584073052|119939,631846612|483822,682290444|120636,78697966|484604,14455586|120994,830085853|483770,665475833|121752,789384463|484751,553979916|121537,291152505|485613,546907746|:J|2003|90112|N||||1|1003|1|;121537,291152505|485613,546907746|120207,14689318|485286,584073052|119939,631846612|483822,682290444|120636,78697966|484604,14455586|120994,830085853|483770,665475833|121752,789384463|484751,553979916|121537,291152505|485613,546907746|:
2|$$lijn$$|$$$$|$$$$|N||J|2001|90112|J|123422|484923||;:J|2002|90112|N||||1|2|1|;123714,566392628|485591,253987199|122637,07523284|484892,742476716|123915,202677554|484142,214151622|122012,873457515|483718,648661223|:



control-file:
LOAD DATA
INFILE 'dummy.dat'
APPEND INTO TABLE dummy
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
 ID DECIMAL EXTERNAL
,NAAM CHAR "replace(replace(replace(:NAAM,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,OMSCHRIJVING CHAR "replace(replace(replace(:OMSCHRIJVING,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,CODE CHAR "replace(replace(replace(:CODE,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
, heeft_vbo_geometrie FILLER
,VBO_GEOMETRIE COLUMN OBJECT NULLIF heeft_vbo_geometrie="N"
  ( sdo_gtype INTEGER EXTERNAL,
    sdo_srid INTEGER EXTERNAL,
     isPunt FILLER,
    SDO_POINT COLUMN OBJECT NULLIF VBO_GEOMETRIE.isPunt="N"
    ( X INTEGER EXTERNAL,
      Y INTEGER EXTERNAL,
      Z INTEGER EXTERNAL),
    SDO_ELEM_INFO VARRAY terminated by ';' NULLIF VBO_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)),
    SDO_ORDINATES VARRAY terminated by ':' NULLIF VBO_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)) 
  )
, heeft_sps_geometrie FILLER
,SPS_GEOMETRIE COLUMN OBJECT NULLIF heeft_sps_geometrie="N"
  ( sdo_gtype INTEGER EXTERNAL,
    sdo_srid INTEGER EXTERNAL,
     isPunt FILLER,
    SDO_POINT COLUMN OBJECT NULLIF SPS_GEOMETRIE.isPunt="N"
    ( X INTEGER EXTERNAL,
      Y INTEGER EXTERNAL,
      Z INTEGER EXTERNAL),
    SDO_ELEM_INFO VARRAY terminated by ';' NULLIF SPS_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)),
    SDO_ORDINATES VARRAY terminated by ':' NULLIF SPS_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)) 
  )
, heeft_lps_geometrie FILLER
,LPS_GEOMETRIE COLUMN OBJECT NULLIF heeft_lps_geometrie="N"
  ( sdo_gtype INTEGER EXTERNAL,
    sdo_srid INTEGER EXTERNAL,
     isPunt FILLER,
    SDO_POINT COLUMN OBJECT NULLIF LPS_GEOMETRIE.isPunt="N"
    ( X INTEGER EXTERNAL,
      Y INTEGER EXTERNAL,
      Z INTEGER EXTERNAL),
    SDO_ELEM_INFO VARRAY terminated by ';' NULLIF LPS_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)),
    SDO_ORDINATES VARRAY terminated by ':' NULLIF LPS_GEOMETRIE.isPunt="J"
      (SDO_ORDINATES char(38)) 
  )
)


result query:
SQL> select * from dummy;

        ID NAAM                                               OMSCHRIJVING                                       CODE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
VBO_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     89993 R. Müller, tandarts                                Koninginneweg 150                                  Tandartsen                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
SDO_GEOMETRY(2003, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(121537,291, 485613,547, 120207,147, 485286,584, 119939,632, 483822,682, 120636,787, 484604,145, 120994,83, 483770,665, 121752,789, 484751,554, 121537,291, 485613,547))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(119177, 485130, NULL), NULL, NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(119177, 485130, NULL), NULL, NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

        ID NAAM                                               OMSCHRIJVING                                       CODE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
VBO_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     92183 Kinderdagverblijf Daantjuh                         James Wattstraat 70                                Kinderopvang                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

        ID NAAM                                               OMSCHRIJVING                                       CODE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
VBO_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 area                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
SDO_GEOMETRY(2003, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(121537,291, 485613,547, 120207,147, 485286,584, 119939,632, 483822,682, 120636,787, 484604,145, 120994,83, 483770,665, 121752,789, 484751,554, 121537,291, 485613,547))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
SDO_GEOMETRY(NULL, NULL, SDO_POINT_TYPE(NULL, NULL, NULL), SDO_ELEM_INFO_ARRAY(), SDO_ORDINATE_ARRAY())                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

        ID NAAM                                               OMSCHRIJVING                                       CODE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
VBO_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         2 lijn                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
SDO_GEOMETRY(2002, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(123714,566, 485591,254, 122637,075, 484892,742, 123915,203, 484142,214, 122012,873, 483718,649))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
SDO_GEOMETRY(NULL, NULL, SDO_POINT_TYPE(NULL, NULL, NULL), SDO_ELEM_INFO_ARRAY(), SDO_ORDINATE_ARRAY())                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> spool off
Re: sql*loader: 2 geometry columns in 1 table [message #529987 is a reply to message #529917] Thu, 03 November 2011 18:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
The problem appears to be with the delimiters in your data file. You have commas in your sdo_ordinates that should be pipes. You are missing delimiters in your null values. Please see the corrected data below, followed by a demonstration of load. The only thing that I changed was the data. I used your latest control file and the same table that I used previously.

-- dummy.dat:
89993|$$R. Müller, tandarts$$|$$Koninginneweg 150$$|$$Tandartsen$$|J|2003|90112|N||||1|1003|1|;121537|291152505|485613|546907746|120207|14689318|485286|584073052|119939|631846612|483822|682290444|120636|78697966|484604|14455586|120994|830085853|483770|665475833|121752|789384463|484751|553979916|121537|291152505|485613|546907746|:J|2001|90112|J|119177|485130||;:J|2001|90112|J|119177|485130||;:
92183|$$Kinderdagverblijf Daantjuh$$|$$James Wattstraat 70$$|$$Kinderopvang$$|J|2001|90112|J|123422|484923||;:J|2001|90112|J|123422|484923||;:J|2001|90112|J|123422|484923||;:
1|$$area$$|$$$$|$$$$|N|||||||;:J|2003|90112|N||||1|1003|1|;121537|291152505|485613|546907746|120207|14689318|485286|584073052|119939|631846612|483822|682290444|120636|78697966|484604|14455586|120994|830085853|483770|665475833|121752|789384463|484751|553979916|121537|291152505|485613|546907746|:J|2003|90112|N||||1|1003|1|;121537|291152505|485613|546907746|120207|14689318|485286|584073052|119939|631846612|483822|682290444|120636|78697966|484604|14455586|120994|830085853|483770|665475833|121752|789384463|484751|553979916|121537|291152505|485613|546907746|:
2|$$lijn$$|$$$$|$$$$|N|||||||;:J|2001|90112|J|123422|484923||;:J|2002|90112|N||||1|2|1|;123714|566392628|485591|253987199|122637|07523284|484892|742476716|123915|202677554|484142|214151622|122012|873457515|483718|648661223|:


-- test.ctl:
LOAD DATA
INFILE 'dummy.dat'
APPEND INTO TABLE dummy
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
ID DECIMAL EXTERNAL
,NAAM CHAR "replace(replace(replace(:NAAM,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,OMSCHRIJVING CHAR "replace(replace(replace(:OMSCHRIJVING,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,CODE CHAR "replace(replace(replace(:CODE,'^10',chr(10)),'^13',chr(13)),'^36',chr(36))"
,heeft_vbo_geometrie FILLER
,VBO_GEOMETRIE COLUMN OBJECT NULLIF heeft_vbo_geometrie="N"
( sdo_gtype INTEGER EXTERNAL,
sdo_srid INTEGER EXTERNAL,
isPunt FILLER,
SDO_POINT COLUMN OBJECT NULLIF VBO_GEOMETRIE.isPunt="N"
( X INTEGER EXTERNAL,
Y INTEGER EXTERNAL,
Z INTEGER EXTERNAL),
SDO_ELEM_INFO VARRAY terminated by ';' NULLIF VBO_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38)),
SDO_ORDINATES VARRAY terminated by ':' NULLIF VBO_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38))
)
,heeft_sps_geometrie FILLER
,SPS_GEOMETRIE COLUMN OBJECT NULLIF heeft_sps_geometrie="N"
( sdo_gtype INTEGER EXTERNAL,
sdo_srid INTEGER EXTERNAL,
isPunt FILLER,
SDO_POINT COLUMN OBJECT NULLIF SPS_GEOMETRIE.isPunt="N"
( X INTEGER EXTERNAL,
Y INTEGER EXTERNAL,
Z INTEGER EXTERNAL),
SDO_ELEM_INFO VARRAY terminated by ';' NULLIF SPS_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38)),
SDO_ORDINATES VARRAY terminated by ':' NULLIF SPS_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38))
)
,heeft_lps_geometrie FILLER
,LPS_GEOMETRIE COLUMN OBJECT NULLIF heeft_lps_geometrie="N"
( sdo_gtype INTEGER EXTERNAL,
sdo_srid INTEGER EXTERNAL,
isPunt FILLER,
SDO_POINT COLUMN OBJECT NULLIF LPS_GEOMETRIE.isPunt="N"
( X INTEGER EXTERNAL,
Y INTEGER EXTERNAL,
Z INTEGER EXTERNAL),
SDO_ELEM_INFO VARRAY terminated by ';' NULLIF LPS_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38)),
SDO_ORDINATES VARRAY terminated by ':' NULLIF LPS_GEOMETRIE.isPunt="J"
(SDO_ORDINATES char(38))
)
)


-- table, load, and results:
SCOTT@orcl_11gR2> create table dummy
  2    (id	       NUMBER,
  3  	naam	       VARCHAR2(26),
  4  	omschrijving   VARCHAR2(19),
  5  	code	       VARCHAR2(12),
  6  	VBO_GEOMETRIE  MDSYS.SDO_GEOMETRY,
  7  	SPS_GEOMETRIE  MDSYS.SDO_GEOMETRY,
  8  	LPS_GEOMETRIE  MDSYS.SDO_GEOMETRY)
  9  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from dummy
  2  /

        ID NAAM                       OMSCHRIJVING        CODE
---------- -------------------------- ------------------- ------------
VBO_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
SPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
LPS_GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
     89993 R. Müller, tandarts        Koninginneweg 150   Tandartsen
SDO_GEOMETRY(2003, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(121537, 291152505, 485613, 546907746, 120207, 14689318, 485286, 584073052, 1
19939, 631846612, 483822, 682290444, 120636, 78697966, 484604, 14455586, 120994,
 830085853, 483770, 665475833, 121752, 789384463, 484751, 553979916, 121537, 291
152505, 485613, 546907746))
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(119177, 485130, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(119177, 485130, NULL), NULL, NULL)

     92183 Kinderdagverblijf Daantjuh James Wattstraat 70 Kinderopvang
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)

         1 area

SDO_GEOMETRY(2003, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(121537, 291152505, 485613, 546907746, 120207, 14689318, 485286, 584073052, 1
19939, 631846612, 483822, 682290444, 120636, 78697966, 484604, 14455586, 120994,
 830085853, 483770, 665475833, 121752, 789384463, 484751, 553979916, 121537, 291
152505, 485613, 546907746))
SDO_GEOMETRY(2003, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(121537, 291152505, 485613, 546907746, 120207, 14689318, 485286, 584073052, 1
19939, 631846612, 483822, 682290444, 120636, 78697966, 484604, 14455586, 120994,
 830085853, 483770, 665475833, 121752, 789384463, 484751, 553979916, 121537, 291
152505, 485613, 546907746))

         2 lijn

SDO_GEOMETRY(2001, 90112, SDO_POINT_TYPE(123422, 484923, NULL), NULL, NULL)
SDO_GEOMETRY(2002, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY
(123714, 566392628, 485591, 253987199, 122637, 7523284, 484892, 742476716, 12391
5, 202677554, 484142, 214151622, 122012, 873457515, 483718, 648661223))


4 rows selected.

SCOTT@orcl_11gR2>


Re: sql*loader: 2 geometry columns in 1 table [message #530030 is a reply to message #529987] Fri, 04 November 2011 04:15 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
thanks a lot. That does the trick.
So, 'only' have the change my data export tool.
Re: sql*loader: 2 geometry columns in 1 table [message #530080 is a reply to message #530030] Fri, 04 November 2011 09:21 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
I don't know what data export tool you are using. I got the impression from prior posts that you were able to change the format of the data file pretty easily. If you can't find a way to get it in the format that Oracle expects, there are always workarounds. Worst case scenario is that you can either use SQL*Loader to load into a staging table with all the data from one row in one column or use an external table to do the same, then use SQL to parse out the data and insert into your target table.
Previous Topic: EXP-00078, EXP-00008, ORA-20000, ORA-06512
Next Topic: Virtualizing a Server running with ASM Storage.
Goto Forum:
  


Current Time: Wed Feb 19 13:09:27 CST 2020