Home » RDBMS Server » Server Utilities » SQL*LOADER (2 Merged) (10g)
SQL*LOADER (2 Merged) [message #564534] Fri, 24 August 2012 22:49 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
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 Go to previous message
Barbara Boehmer
Messages: 8024
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

Previous Topic: How to load data in oracle from Microsoft Access Database (.mdb) file.
Next Topic: Export only Procedures as *.proc file
Goto Forum:
  


Current Time: Fri Dec 19 04:22:19 CST 2014

Total time taken to generate the page: 0.16003 seconds