Home » RDBMS Server » Server Utilities » SQL*LOADER (2 Merged) (10g)
| SQL*LOADER (2 Merged) [message #564534] |
Fri, 24 August 2012 22:49  |
 |
tabj
Messages: 20
Registered: June 2012
|
Junior Member |
|
|
Hi all,
I want to load geometry into a table using sql*loader.
my table is test_line
CREATE TABLE TEST_LINE(ID NUMBER(5),TYPE VARCHAR2(20),SIZE NUMBER(4),GEOM SDO_GEOMETRY);
my data having below format.
123;LINE;0;
LINE;20;30;40;60;
LINE;70;80;90;100;
LINE;110;120;130;140;
678;LINE;20;
LINE;7;8;10;20;
LINE;25;26;
Here
DEFAULT SDO_GTYPE IS 2001
DEFAULT SDO_ELEM_INFO IS 1,2,1
SDO_ORDINATES ARE
LINE;20;30;40;60;
LINE;70;80;90;100;
LINE;110;120;130;140;
help me on this.
regards,
tabj.
|
|
|
|
| Re: SQL*LOADER (2 Merged) [message #564804 is a reply to message #564534] |
Tue, 28 August 2012 16:16  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following loads everything except your sdo_ordinates. I don't understand how you want the sdo_ordinates loaded. Should there be two rows or more? Please provide an example of the desired results. Also note that size is not a valid column name, so I used the_size instead.
-- test.dat:
123;LINE;0;
LINE;20;30;40;60;
LINE;70;80;90;100;
LINE;110;120;130;140;
678;LINE;20;
LINE;7;8;10;20;
LINE;25;26;
-- test.ctl:
LOAD DATA
INFILE test.dat
CONTINUEIF NEXT PRESERVE (1:5) = 'LINE;'
INTO TABLE test_line
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(id
,type CHAR
,the_size INTEGER EXTERNAL
,geom COLUMN OBJECT
(SDO_GTYPE CONSTANT 2001
,SDO_ELEM_INFO EXPRESSION "SDO_ELEM_INFO_ARRAY (1,2,1)"))
-- table, load, and results:
SCOTT@orcl_11gR2> CREATE TABLE test_line
2 (id NUMBER(5),
3 type VARCHAR2(20),
4 the_size NUMBER(4),
5 geom SDO_GEOMETRY)
6 /
Table created.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> SELECT * FROM test_line
2 /
ID TYPE THE_SIZE
---------- -------------------- ----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
123 LINE 0
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), NULL)
678 LINE 20
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), NULL)
2 rows selected.
|
|
|
|
| Re: SQL*LOADER (2 Merged) [message #564871 is a reply to message #564804] |
Wed, 29 August 2012 04:19 |
|
tabj
Messages: 20
Registered: June 2012
|
Junior Member |
|
|
hi,
I want result like below.
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(20,30,40,60,70,80,90,100,110,120,130,140)
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(7,8,10,20,25,26)
help me on this.
regards,
tabj.
|
|
|
|
| Re: SQL*LOADER (2 Merged) [message #564989 is a reply to message #564871] |
Wed, 29 August 2012 19:27 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following loads the data into a staging table, then inserts the data from the staging table to the target table. There may be a simpler way, but I can't think of one right now.
-- test.dat:
123;LINE;0;
LINE;20;30;40;60;
LINE;70;80;90;100;
LINE;110;120;130;140;
678;LINE;20;
LINE;7;8;10;20;
LINE;25;26;
-- test.ctl:
LOAD DATA
INFILE test.dat
CONTINUEIF NEXT PRESERVE (1:5) = 'LINE;'
INTO TABLE staging
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(id
,type CHAR
,the_size INTEGER EXTERNAL
,the_rest TERMINATED BY WHITESPACE "',' || REPLACE (REPLACE (:the_rest, 'LINE;',''), ';', ',')")
-- staging table, load into staging table, and results:
SCOTT@orcl_11gR2> CREATE TABLE staging
2 (id NUMBER(5),
3 type VARCHAR2(20),
4 the_size NUMBER(4),
5 the_rest VARCHAR2(4000))
6 /
Table created.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> SELECT * FROM staging
2 /
ID TYPE THE_SIZE
---------- -------------------- ----------
THE_REST
--------------------------------------------------------------------------------
123 LINE 0
,20,30,40,60,70,80,90,100,110,120,130,140,
678 LINE 20
,7,8,10,20,25,26,
2 rows selected.
-- target table, insert into target table, and results:
SCOTT@orcl_11gR2> CREATE TABLE test_line
2 (id NUMBER(5),
3 type VARCHAR2(20),
4 the_size NUMBER(4),
5 geom SDO_GEOMETRY)
6 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO test_line (id, type, the_size, geom)
2 SELECT id, type, the_size,
3 SDO_GEOMETRY
4 (2001, null, null,
5 SDO_ELEM_INFO_ARRAY (1,2,1),
6 elems)
7 FROM (SELECT id, type, the_size,
8 CAST
9 (MULTISET
10 (SELECT SUBSTR
11 (the_rest,
12 INSTR (the_rest, ',', 1, COLUMN_VALUE) + 1,
13 INSTR (the_rest, ',', 1, COLUMN_VALUE + 1)
14 - INSTR (the_rest, ',', 1, COLUMN_VALUE) - 1) elements
15 FROM staging s1,
16 TABLE
17 (CAST
18 (MULTISET
19 (SELECT LEVEL
20 FROM DUAL
21 CONNECT BY LEVEL <= REGEXP_COUNT (s1.the_rest, ',') - 1)
22 AS SYS.ODCINUMBERLIST))
23 WHERE s1.id = s2.id)
24 AS MDSYS.SDO_ORDINATE_ARRAY) elems
25 FROM staging s2)
26 /
2 rows created.
SCOTT@orcl_11gR2> SELECT * FROM test_line
2 /
ID TYPE THE_SIZE
---------- -------------------- ----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
123 LINE 0
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
20, 30, 40, 60, 70, 80, 90, 100, 110, 120, 130, 140))
678 LINE 20
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
7, 8, 10, 20, 25, 26))
2 rows selected.
|
|
|
|
| Re: SQL*LOADER (2 Merged) [message #564992 is a reply to message #564989] |
Wed, 29 August 2012 21:11 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is another method that uses a function instead of a staging table.
-- test.dat:
123;LINE;0;
LINE;20;30;40;60;
LINE;70;80;90;100;
LINE;110;120;130;140;
678;LINE;20;
LINE;7;8;10;20;
LINE;25;26;
-- test.ctl:
LOAD DATA
INFILE test.dat
CONTINUEIF NEXT PRESERVE (1:5) = 'LINE;'
INTO TABLE test_line
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(id
,type CHAR
,the_size INTEGER EXTERNAL
,the_rest BOUNDFILLER TERMINATED BY WHITESPACE
,geom COLUMN OBJECT
(SDO_GTYPE CONSTANT 2001
,SDO_ELEM_INFO EXPRESSION "SDO_ELEM_INFO_ARRAY (1,2,1)"
,SDO_ORDINATES EXPRESSION "str_to_ordinates (:the_rest)"))
-- table, function, load, and results:
SCOTT@orcl_11gR2> CREATE TABLE test_line
2 (id NUMBER(5),
3 type VARCHAR2(20),
4 the_size NUMBER(4),
5 geom SDO_GEOMETRY)
6 /
Table created.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION str_to_ordinates
2 (p_string IN VARCHAR2)
3 RETURN MDSYS.SDO_ORDINATE_ARRAY
4 AS
5 v_string VARCHAR2(4000);
6 v_ordinates MDSYS.SDO_ORDINATE_ARRAY;
7 BEGIN
8 v_string := ',' || REPLACE (REPLACE (p_string, 'LINE;', ''), ';', ',');
9 SELECT CAST
10 (MULTISET
11 (SELECT SUBSTR
12 (v_string,
13 INSTR (v_string, ',', 1, rn) + 1,
14 INSTR (v_string, ',', 1, rn + 1)
15 - INSTR (v_string, ',', 1, rn) - 1) elements
16 FROM (SELECT ROWNUM rn
17 FROM DUAL
18 CONNECT BY LEVEL <=
19 LENGTH (v_string) - LENGTH (REPLACE (v_string, ',', '')) - 1))
20 AS MDSYS.SDO_ORDINATE_ARRAY) elems
21 INTO v_ordinates
22 FROM DUAL;
23 RETURN v_ordinates;
24 END str_to_ordinates;
25 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> SELECT * FROM test_line
2 /
ID TYPE THE_SIZE
---------- -------------------- ----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
123 LINE 0
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
20, 30, 40, 60, 70, 80, 90, 100, 110, 120, 130, 140))
678 LINE 20
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
7, 8, 10, 20, 25, 26))
2 rows selected.
[Updated on: Wed, 29 August 2012 21:15] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Feb 11 18:42:29 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
