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  |
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  |
 |
Barbara Boehmer
Messages: 9102
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 |
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 |
|
Barbara Boehmer
Messages: 9102
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 #530080 is a reply to message #530030] |
Fri, 04 November 2011 09:21 |
|
Barbara Boehmer
Messages: 9102
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.
|
|
|
|
Goto Forum:
Current Time: Sun Jan 12 18:00:20 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
