Home » RDBMS Server » Server Utilities » Re: Load WKT using sql*loader (split from http://www.orafaq.com/forum/t/155866/ by bb)
Re: Load WKT using sql*loader (split from http://www.orafaq.com/forum/t/155866/ by bb) [message #666590] Wed, 15 November 2017 09:29 Go to next message
frank.reimann
Messages: 8
Registered: November 2017
Junior Member
Hello Barbara,
let me ask a question to the format referenced by bibber. I found this data as some kind of examples. Now I'm not really sure how these data was generated (terminated by '|' and continued lines start with '#').
We've to load some data w/o using datapump or imp/exp utility, only sqlplus is in this case usable and I can tell You, that we've to deal with geometry objects (polygons) that are defined by more than 500 points.
So, every hint or advise is very appreciated.

Thank You
Frank
Re: Load WKT using sql*loader [message #666596 is a reply to message #666590] Wed, 15 November 2017 12:45 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Load data without using imp, or impdp?
you didn't mention sqlloader or external tables, you said only sql*plus. Well, sql*plus is not a loader, it is a query language, so you must be leaving something out. If you are saying you can only use sql*plus as a tool, you would have to write pl/sql and use UTL_FILE, but the files would have to reside on the server.

Please provide more information as the answer to your literal question is that it cannot be done unless you create a script with ACCEPT commands and have the user type in all the data while interactively running the script.
Re: Load WKT using sql*loader [message #666597 is a reply to message #666596] Thu, 16 November 2017 01:13 Go to previous messageGo to next message
frank.reimann
Messages: 8
Registered: November 2017
Junior Member
Hello joy_division,
thank You for response.
Let me tell something more to the situation.
Yes, sqlplus isn't a tool for importing data. So in the described scenario we only have access to the database via a sqlplus connection, no further acces to database tool such as datapump or imp/exp ore something. Even utl_file isn't an option because of not permitted access to local resources on database server.
The only way to get data out of the system is to connect via a client system (oracle instant client incl. sqlplus) to the database host.
From the technical point of view we've to unload geometrical data (polygons) describing areas (ocassionally huge amount of vertices > 500) for processing issues and data matching in our local enviroment.
So our aproach is to spool out tha data via sqlplus into a local file on the client side host. This file will be transfered in the local enviroment for further processing as mentioned earlier. To load the data into out loacal database we'll use sql loader.
So far so good.
Assuming the working of a couple of examples in the oracle documentations (spatial, sdo_geometry objects, bulk loading geometrical data, ...) the question cames up, how to get the data into the format as given in the loader control file examples:
"LOAD DATA
INFILE *
...
begindata
1|2003|1|1003|1|/
#-122.4215|37.7862|-122.422|37.7869|-122.421|37.789|-122.42|37.7866|
#-122.4215|37.7862|/
2|2003|1|1003|1|/
#-122.4019|37.8052|-122.4027|37.8055|-122.4031|37.806|-122.4012|37.8052|
#-122.4019|37.8052|/
3|2003|1|1003|1|/
#-122.426|37.803|-122.4242|37.8053|-122.42355|37.8044|-122.4235|37.8025|
#-122.426|37.803|/
"

At the moment I do not have any idea, how to get the data with a "simple" select into this format. Ok, we could transform it with some text processing (sed or awk). So this is what I wanted to know about target format in the referenced in examples.

Thanks for being patient Wink
Ciao Frank
Re: Load WKT using sql*loader [message #666598 is a reply to message #666597] Thu, 16 November 2017 02:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you can connect to the database with SQL*Plus, then you must have Oracle Net configured correctly. So I would use the legacy exp/imp tools, they should be able to extract what you want and save it to your local system no problem. I don't think exp/imp is included in the Instant Client, you'll need to install the full client if that has it, or just install a database home (which is what I always do).
Re: Load WKT using sql*loader [message #666606 is a reply to message #666596] Thu, 16 November 2017 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
joy_division wrote on Wed, 15 November 2017 18:45
Well, sql*plus is not a loader, it is a query language
?
Re: Load WKT using sql*loader [message #666610 is a reply to message #666597] Thu, 16 November 2017 05:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Instead of trying to get your data into the format that somebody else's data happened to be in, it might be better to start with whatever format your data is in. Please post a copy and paste of what you get when you select a few simple records, then we can see how that can be loaded or how the select statement can be modified to make it easier to load.


[Updated on: Thu, 16 November 2017 05:58]

Report message to a moderator

Re: Load WKT using sql*loader [message #666611 is a reply to message #666606] Thu, 16 November 2017 08:22 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Thu, 16 November 2017 05:45
joy_division wrote on Wed, 15 November 2017 18:45
Well, sql*plus is not a loader, it is a query language
?
Yeah, yeah, yeah, bad choice of words. I was trying to make the point that it is not a command line tool for loading data like the others. I failed miserably. And if the emojis were working, I'd post one of a dog with it's tail between it's legs. Thankfully, they ain't, so I don't have to.
Re: Load WKT using sql*loader [message #666613 is a reply to message #666610] Thu, 16 November 2017 09:12 Go to previous messageGo to next message
frank.reimann
Messages: 8
Registered: November 2017
Junior Member
Hello and tahnks for all responses,
...
@John Watson:
unfortunately the access on tools is restricted to the use of instant client or sqlplus. So no exp/imp functionality, sorry.

@Barbara:
We executed a select and we received some files with spooled data in. After some basic transformation we've got the following (You ask for some sample lines):

 1062855537641571522|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(669066.8736127, 5793671.4546435, 669105.1839686, 5793710.044637, 669091.5126917, 5793724.5777019, 669088.8882946, 5793727.2650303, 669079.4172321, 5793736.9634712, 669181.6056487, 5793835.49253, 669204.2388145, 5793857.3149339, 669209.2094669, 5793855.1993833, 669215.6271489, 5793862.1332542, 669187.2532584, 5793873.3263198, 669154.1222577, 5793886.3959927, 669110.8767115, 5793903.7067864, 669016.6453966, 5793941.1893832, 668983.1799101, 5793954.4404563, 668967.1496823, 5793960.8723442, 668965.1622643, 5793924.6152744, 668964.6138034, 5793903.5604697, 668961.9109217, 5793784.03893, 668960.8006845, 5793725.0729991, 668959.2715314, 5793667.3249793, 668958.6479436, 5793639.7499012, 668958.5266101, 5793589.4728605, 668958.0945732, 5793555.9643007, 669066.8736127, 5793671.4546435))
 1062855537641571519|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 39, 2003, 1), SDO_ORDINATE_ARRAY(671295.3986489, 5794146.3087128, 671294.5815726, 5794152.1508086, 671293.1604897, 5794161.3232528, 671291.8999348, 5794162.0795857, 671291.0321766, 5794162.4449576, 671226.3319301, 5794154.6385883, 671222.8748716, 5794154.2505512, 671187.6127874, 5794149.8254002, 671187.6684831, 5794150.2988137, 671187.4323954, 5794150.272314, 671180.9560352, 5794123.6799865, 671178.8238143, 5794114.421771, 671211.6669471, 5794115.3468049, 671243.4000933, 5794117.4591011, 671269.7744557, 5794119.1435831, 671289.3395718, 5794121.7763051, 671290.9969412, 5794122.0846529, 671298.2614089, 5794123.0090272, 671295.3986489, 5794146.3087128, 671226.1259999, 5794140.8129466, 671225.995095, 5794143.9723272, 671229.1544794, 5794144.10323, 671229.2853842, 5794140.9438494, 671226.1259999, 5794140.8129466))
  -54037204203259280|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(689559.8011975, 5672364.2559434, 689557.4642833, 5672363.369525, 689570.054728, 5672318.4776917, 689579.2702821, 5672298.1285621, 689589.1756461, 5672268.6315194, 689596.0252765, 5672244.2306245, 689606.6365525, 5672209.0668987, 689612.7318988, 5672195.3933617, 689618.7111999, 5672184.5628324, 689617.0384607, 5672191.5761073, 689615.3658051, 5672202.2770235, 689611.0513018, 5672219.3139846, 689605.1973598, 5672239.465882, 689601.6454122, 5672255.1215352, 689595.3076796, 5672276.3128392, 689591.5451314, 5672286.3105473, 689586.4501435, 5672300.6988749, 689559.8011975, 5672364.2559434))
  -54037204203259278|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 475, 2003, 1), SDO_ORDINATE_ARRAY(689178.599027, 5672282.9508707, 689176.4947509, 5672281.9086575, 689165.9626015, 5672277.7271185, 689150.3159048, 5672271.7923389, 689136.3421536, 5672268.1325362, 689115.6487595, 5672264.1984827, 689113.2314449, 5672263.8156026, 689112.3065927, 5672263.6002931, 689108.3433661, 5672263.0413766, 689092.1880457, 5672260.4825245, 689004.0502973, 5672246.8441851, 688987.3645809, 5672244.0781038, 688926.5375489, 5672234.4197575, 688923.0260607, 5672234.2078573, 688919.9396743, 5672233.7017081, 688910.3696812, 5672232.9800268, 688897.2522081, 5672232.6652507, 688895.1106135, 5672232.6198844, 688876.8073403, 5672231.7483, 688858.1318895, 5672229.5511881, 688824.3995781, 5672225.2784512, 688810.4121604, 5672223.5879136, 688786.5942759, 5672220.4459799, 688740.1520149, 5672213.2535943, 688739.1683355, 5672213.243711, 688725.9113375, 5672210.609368, 688724.7347724, 5672210.4209417, 688721.3567656, 5672209.5417235, 688722.2640151, 5672194.1144568, 688722.4792962, 5672193.5259082, 688722.9254021, 5672193.1189365, 688740.0053419, 5672197.8904258, 688750.7150613, 5672200.4239513, 688759.8174475, 5672201.7884995, 688767.7261477, 5672202.6629666, 688773.8902743, 5672203.0248691, 688779.5846251, 5672201.381617, 688779.9661364, 5672200.926693, 688780.0183805, 5672200.9237905, 688780.2349599, 5672200.6061407, 688782.7161693, 5672197.6474816, 688787.3875168, 5672191.328072, 688790.7619994, 5672187.0523459, 688793.7031353, 5672185.1499921, 688797.8589849, 5672183.4799905, 688804.6189014, 5672182.7628309, 688822.4236433, 5672184.4825477, 688825.2771922, 5672184.9089463, 688840.2279165, 5672187.1429952, 688840.3528318, 5672187.1661276, 688840.3929148, 5672187.1676504, 688841.7252146, 5672187.3667326, 688857.9264851, 5672187.8337778, 688860.1302423, 5672187.8973071, 688862.4560599, 5672187.7978288, 688863.2910428, 5672187.4799545, 688869.7786402, 5672186.6233313, 688876.2452788, 5672185.9107064, 688880.0369423, 5672185.4928642, 688887.3905916, 5672184.3376827, 688889.3570255, 5672184.193872, 688894.7358622, 5672183.8005029, 688909.7162875, 5672185.8765828, 688929.4994245, 5672195.1026968, 688934.527611, 5672199.5889372, 688944.2518154, 5672204.7277444, 688951.6833211, 5672207.2576187, 688962.2771698, 5672210.4199616, 688971.9223157, 5672212.3173673, 688980.5397, 5672213.2660702, 688986.9434444, 5672213.2660702, 689003.7038617, 5672209.234083, 689019.9899276, 5672208.0482044, 689041.2566836, 5672207.0995015, 689073.2754053, 5672207.3366773, 689088.8499441, 5672209.1550244, 689105.2150685, 5672211.7639573, 689121.580193, 5672215.2425345, 689143.6143583, 5672219.2900115, 689146.3030548, 5672220.3890619, 689151.0386937, 5672220.6537948, 689166.2197529, 5672220.0103688, 689177.2539249, 5672218.5566474, 689197.3773051, 5672219.2380055, 689207.3427181, 5672219.5754247, 689207.3466499, 5672219.5761867, 689233.2260149, 5672224.5914525, 689239.9071182, 5672225.5196698, 689247.1628566, 5672226.5276634, 689251.249516, 5672226.4796659, 689254.0797907, 5672224.7193348, 689255.8714114, 5672221.372052, 689258.846377, 5672212.4758426, 689258.8467061, 5672212.4748585, 689264.1862085, 5672195.0597142, 689266.7307578, 5672186.7604582, 689267.9214248, 5672183.6492769, 689268.0521668, 5672183.3500818, 689271.0942263, 5672176.3885103, 689273.8041834, 5672170.1869346, 689287.0751811, 5672147.502818, 689292.5191401, 5672142.2847366, 689299.3829221, 5672138.5557194, 689306.2610179, 5672136.0258451, 689314.7202851, 5672135.3143179, 689348.685221, 5672134.9519443, 689396.0309948, 5672133.5363983, 689416.3354092, 5672132.6880578, 689418.3812525, 5672132.7522219, 689425.5665287, 5672132.3023716, 689464.8694628, 5672129.0514206, 689485.290895, 5672126.1333665, 689503.0850162, 5672121.6374386, 689514.0326594, 5672120.044678, 689527.0463784, 5672119.5619159, 689540.411092, 5672119.224758, 689555.9846643, 5672120.4865145, 689570.481319, 5672123.2104577, 689599.3912896, 5672129.5715281, 689624.529635, 5672135.378406, 689659.3268291, 5672144.2701246, 689680.240749, 5672148.213174, 689691.0782101, 5672147.7788539, 689691.489964, 5672147.6865915, 689702.15725, 5672145.2963554, 689703.5737165, 5672144.9789655, 689709.1463313, 5672142.8829847, 689713.3569435, 5672139.7445624, 689714.8699614, 5672137.8201391, 689715.0744592, 5672135.5114556, 689713.4941594, 5672133.6814377, 689709.3977153, 5672132.6773369, 689696.5518903, 5672135.0197776, 689695.4265584, 5672135.2249823, 689692.3509273, 5672135.3563285, 689687.6547636, 5672134.1716091, 689679.6202212, 5672130.9749773, 689669.0408472, 5672125.2467629, 689666.8969027, 5672123.6477029, 689658.8682662, 5672117.6595484, 689651.2133725, 5672111.0194657, 689649.4612871, 5672109.4996547, 689646.3016558, 5672106.2617253, 689630.9396058, 5672094.7948978, 689616.7240175, 5672089.7510428, 689597.1558766, 5672086.6352826, 689590.6261149, 5672085.5955736, 689563.7168614, 5672084.4973399, 689523.2634145, 5672087.1382932, 689472.9955469, 5672090.8834003, 689471.8472257, 5672090.9293331, 689457.1838324, 5672091.5158689, 689447.6177452, 5672091.1996346, 689438.2097751, 5672090.2509317, 689428.3274535, 5672087.4048231, 689422.4676892, 5672083.3680689, 689418.0498391, 5672077.6806187, 689432.2406549, 5672032.8963104, 689440.065904, 5672009.7467589, 689441.175454, 5672004.5414301, 689447.4418864, 5671975.9512454, 689478.3301604, 5671990.7887839, 689489.3204371, 5671996.06809, 689495.779407, 5671999.3739366, 689597.5277667, 5672052.5933863, 689602.3895798, 5672055.1858974, 689657.1801227, 5672084.4023839, 689693.9809168, 5672104.6548554, 689703.0011218, 5672109.9741831, 689743.7507426, 5672135.7990826, 689761.4802772, 5672145.829108, 689778.1616359, 5672154.3674338, 689788.7554846, 5672159.4271825, 689796.819459, 5672164.0125797, 689803.1655894, 5672170.7415994, 689807.0043986, 5672175.8017781, 689810.1788388, 5672185.1254099, 689810.0683971, 5672189.3752825, 689810.0094402, 5672191.6439821, 689809.7492257, 5672201.6572087, 689808.8538055, 5672204.3847946, 689808.6891154, 5672204.6142022, 689785.6530398, 5672273.7224291, 689762.0676297, 5672342.4904189, 689759.5582625, 5672351.4243475, 689752.7289776, 5672373.9988578, 689752.6065068, 5672375.1725361, 689748.0148942, 5672390.8581395, 689747.0752117, 5672397.6607347, 689746.4443331, 5672413.7599364, 689748.851602, 5672421.3250603, 689751.6915973, 5672432.314692, 689751.8031959, 5672434.1746685, 689748.6290872, 5672439.5971041, 689741.857233, 5672444.2274703, 689723.5849996, 5672451.4304949, 689691.3885007, 5672461.8541881, 689679.1719849, 5672448.0873248, 689671.3681308, 5672441.8442414, 689653.5307501, 5672426.6824678, 689643.0512889, 5672419.3245483, 689630.7880897, 5672413.973334, 689605.1468548, 5672405.2776109, 689562.5601083, 5672391.4536408, 689518.8585254, 5672375.8459326, 689485.0600692, 5672362.7971616, 689483.6296984, 5672362.2449299, 689480.5081568, 5672360.0152572, 689480.1867627, 5672357.926196, 689482.003957, 5672349.9285335, 689484.252284, 5672340.0094439, 689488.3521744, 5672330.4871178, 689492.5843193, 5672320.3035191, 689493.9068646, 5672314.4843198, 689490.1760856, 5672311.2632911, 689490.1798946, 5672311.2358662, 689488.6150134, 5672310.6579801, 689484.2740947, 5672309.0549467, 689482.7378294, 5672308.5098203, 689381.5106935, 5672272.3891242, 689294.5534624, 5672240.0588717, 689287.6356851, 5672237.7005385, 689274.9323435, 5672233.3698538, 689267.298928, 5672232.2527686, 689265.7906859, 5672232.0320503, 689265.5698898, 5672232.2843887, 689262.6691443, 5672235.5995265, 689258.8907546, 5672245.6267913, 689250.9848715, 5672265.8640125, 689239.8580839, 5672297.2574489, 689238.428683, 5672296.8737963, 689231.5180497, 5672295.0189758, 689214.5352266, 5672290.4983682, 689208.4881002, 5672288.8887015, 689194.6258187, 5672286.7560428, 689191.0806672, 5672285.9143327, 689178.599027, 5672282.9508707, 689586.4501435, 5672300.6988749, 689591.5451314, 5672286.3105473, 689595.3076796, 5672276.3128392, 689601.6454122, 5672255.1215352, 689605.1973598, 5672239.465882, 689611.0513018, 5672219.3139846, 689615.3658051, 5672202.2770235, 689617.0384607, 5672191.5761073, 689618.7111999, 5672184.5628324, 689612.7318988, 5672195.3933617, 689606.6365525, 5672209.0668987, 689596.0252765, 5672244.2306245, 689589.1756461, 5672268.6315194, 689579.2702821, 5672298.1285621, 689570.054728, 5672318.4776917, 689557.4642833, 5672363.369525, 689559.8011975, 5672364.2559434, 689586.4501435, 5672300.6988749))
 1062855540751902662|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(655463.756251, 5776594.6645664, 655460.229957, 5776593.6645726, 655454.2685859, 5776591.3072371, 655453.2542305, 5776590.8875038, 655433.050204, 5776584.9638101, 655405.1899461, 5776578.8153394, 655364.4451972, 5776569.8233949, 655341.0213922, 5776564.6540034, 655308.335297, 5776557.6725073, 655292.0569576, 5776554.3088709, 655274.044575, 5776550.5869245, 655266.8694416, 5776549.1043076, 655259.681051, 5776548.3426239, 655251.8690337, 5776547.3327483, 655249.3239935, 5776547.3681395, 655243.2804473, 5776547.2002632, 655235.6642698, 5776547.9407249, 655221.1379607, 5776549.7979699, 655218.8829871, 5776549.9923642, 655217.1966495, 5776550.3018819, 655212.4983965, 5776550.9025718, 655186.8995775, 5776555.5569025, 655183.3030493, 5776556.5089247, 655173.5510466, 5776558.3001088, 655165.244078, 5776559.4077046, 655165.0105957, 5776558.7072579, 655166.811325, 5776555.1057994, 655172.3293727, 5776553.5730084, 655183.9785845, 5776550.8139846, 655212.1819393, 5776546.4199836, 655236.1956654, 5776543.0478434, 655244.574923, 5776542.536913, 655252.6476224, 5776542.6390991, 655268.1799047, 5776545.2959369, 655343.8997814, 5776560.1129168, 655435.5606846, 5776580.7545026, 655459.0424116, 5776586.5490183, 655461.9820201, 5776587.0389532, 655467.989616, 5776588.0402191, 655477.8857663, 5776587.7690917, 655487.2136161, 5776587.3022762, 655490.7775477, 5776587.123918, 655493.9925512, 5776586.7127889, 655502.693924, 5776585.6000724, 655504.6543017, 5776585.6000724, 655506.1935266, 5776592.2187394, 655490.8287331, 5776594.7017507, 655490.1943268, 5776590.3666411, 655465.1352787, 5776589.8379692, 655463.756251, 5776594.6645664))

Obviously the spooled data could be in WKT form e.g. but then we were faced with the lost if information about the coordinate system (srid). Last try to generate insert statements went wrong in case of poor performance. So we decided to try loading by sql loader. The challenge is to get all properly formated.

Thank You
Frank
Re: Load WKT using sql*loader [message #666614 is a reply to message #666613] Thu, 16 November 2017 17:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I don't know what you mean by "after some basic transformation". The point of my asking for a copy and paste of what you get when you select a few simple records was to get some sample data without any transformations, in order to show what control file to use for the data that is in the format that you have, so that you don't have to make any transformations. I copied what you posted to a file named test.dat on my system. The following demonstrates how to load that data into a table. It assumes that all of your data is in the format that you posted, including the first character of each new record being a space and all sdo_point_type being null.

-- SQL*Loader control file test.ctl:
LOAD DATA
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
col1 INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
col2 COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ',',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ',',
SDO_POINT FILLER TERMINATED BY ', SDO_ELEM_INFO_ARRAY(',
SDO_ELEM_INFO VARRAY TERMINATED BY '), SDO_ORDINATE_ARRAY('
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '))'
(ordinates FLOAT EXTERNAL)
)
)

-- test table to load data into:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test
  2    ( col1      NUMBER
  3    , col2      MDSYS.SDO_GEOMETRY )
  4  /

Table created.

-- load data:
SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger CONTROL=test.ctl DATA=test.dat LOG=test.log

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Nov 16 15:01:08 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 4
Commit point reached - logical record count 5

Table TEST:
  5 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

-- result:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
  2  /

                          COL1
------------------------------
COL2(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
           1062855537641571522
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(669066.8736127, 5793671.4546435, 669105.1839686, 5793710.044637, 669091.5126
917, 5793724.5777019, 669088.8882946, 5793727.2650303, 669079.4172321, 5793736.9
634712, 669181.6056487, 5793835.49253, 669204.2388145, 5793857.3149339, 669209.2
094669, 5793855.1993833, 669215.6271489, 5793862.1332542, 669187.2532584, 579387
3.3263198, 669154.1222577, 5793886.3959927, 669110.8767115, 5793903.7067864, 669
016.6453966, 5793941.1893832, 668983.1799101, 5793954.4404563, 668967.1496823, 5
793960.8723442, 668965.1622643, 5793924.6152744, 668964.6138034, 5793903.5604697
, 668961.9109217, 5793784.03893, 668960.8006845, 5793725.0729991, 668959.2715314
, 5793667.3249793, 668958.6479436, 5793639.7499012, 668958.5266101, 5793589.4728
605, 668958.0945732, 5793555.9643007, 669066.8736127, 5793671.4546435))

           1062855537641571519
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 39, 2003, 1), SD
O_ORDINATE_ARRAY(671295.3986489, 5794146.3087128, 671294.5815726, 5794152.150808
6, 671293.1604897, 5794161.3232528, 671291.8999348, 5794162.0795857, 671291.0321
766, 5794162.4449576, 671226.3319301, 5794154.6385883, 671222.8748716, 5794154.2
505512, 671187.6127874, 5794149.8254002, 671187.6684831, 5794150.2988137, 671187
.4323954, 5794150.272314, 671180.9560352, 5794123.6799865, 671178.8238143, 57941
14.421771, 671211.6669471, 5794115.3468049, 671243.4000933, 5794117.4591011, 671
269.7744557, 5794119.1435831, 671289.3395718, 5794121.7763051, 671290.9969412, 5
794122.0846529, 671298.2614089, 5794123.0090272, 671295.3986489, 5794146.3087128
, 671226.1259999, 5794140.8129466, 671225.995095, 5794143.9723272, 671229.154479
4, 5794144.10323, 671229.2853842, 5794140.9438494, 671226.1259999, 5794140.81294
66))

            -54037204203259280
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(689559.8011975, 5672364.2559434, 689557.4642833, 5672363.369525, 689570.0547
28, 5672318.4776917, 689579.2702821, 5672298.1285621, 689589.1756461, 5672268.63
15194, 689596.0252765, 5672244.2306245, 689606.6365525, 5672209.0668987, 689612.
7318988, 5672195.3933617, 689618.7111999, 5672184.5628324, 689617.0384607, 56721
91.5761073, 689615.3658051, 5672202.2770235, 689611.0513018, 5672219.3139846, 68
9605.1973598, 5672239.465882, 689601.6454122, 5672255.1215352, 689595.3076796, 5
672276.3128392, 689591.5451314, 5672286.3105473, 689586.4501435, 5672300.6988749
, 689559.8011975, 5672364.2559434))

            -54037204203259278
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 475, 2003, 1), S
DO_ORDINATE_ARRAY(689178.599027, 5672282.9508707, 689176.4947509, 5672281.908657
5, 689165.9626015, 5672277.7271185, 689150.3159048, 5672271.7923389, 689136.3421
536, 5672268.1325362, 689115.6487595, 5672264.1984827, 689113.2314449, 5672263.8
156026, 689112.3065927, 5672263.6002931, 689108.3433661, 5672263.0413766, 689092
.1880457, 5672260.4825245, 689004.0502973, 5672246.8441851, 688987.3645809, 5672
244.0781038, 688926.5375489, 5672234.4197575, 688923.0260607, 5672234.2078573, 6
88919.9396743, 5672233.7017081, 688910.3696812, 5672232.9800268, 688897.2522081,
 5672232.6652507, 688895.1106135, 5672232.6198844, 688876.8073403, 5672231.7483,
 688858.1318895, 5672229.5511881, 688824.3995781, 5672225.2784512, 688810.412160
4, 5672223.5879136, 688786.5942759, 5672220.4459799, 688740.1520149, 5672213.253
5943, 688739.1683355, 5672213.243711, 688725.9113375, 5672210.609368, 688724.734
7724, 5672210.4209417, 688721.3567656, 5672209.5417235, 688722.2640151, 5672194.
1144568, 688722.4792962, 5672193.5259082, 688722.9254021, 5672193.1189365, 68874
0.0053419, 5672197.8904258, 688750.7150613, 5672200.4239513, 688759.8174475, 567
2201.7884995, 688767.7261477, 5672202.6629666, 688773.8902743, 5672203.0248691,
688779.5846251, 5672201.381617, 688779.9661364, 5672200.926693, 688780.0183805,
5672200.9237905, 688780.2349599, 5672200.6061407, 688782.7161693, 5672197.647481
6, 688787.3875168, 5672191.328072, 688790.7619994, 5672187.0523459, 688793.70313
53, 5672185.1499921, 688797.8589849, 5672183.4799905, 688804.6189014, 5672182.76
28309, 688822.4236433, 5672184.4825477, 688825.2771922, 5672184.9089463, 688840.
2279165, 5672187.1429952, 688840.3528318, 5672187.1661276, 688840.3929148, 56721
87.1676504, 688841.7252146, 5672187.3667326, 688857.9264851, 5672187.8337778, 68
8860.1302423, 5672187.8973071, 688862.4560599, 5672187.7978288, 688863.2910428,
5672187.4799545, 688869.7786402, 5672186.6233313, 688876.2452788, 5672185.910706
4, 688880.0369423, 5672185.4928642, 688887.3905916, 5672184.3376827, 688889.3570
255, 5672184.193872, 688894.7358622, 5672183.8005029, 688909.7162875, 5672185.87
65828, 688929.4994245, 5672195.1026968, 688934.527611, 5672199.5889372, 688944.2
518154, 5672204.7277444, 688951.6833211, 5672207.2576187, 688962.2771698, 567221
0.4199616, 688971.9223157, 5672212.3173673, 688980.5397, 5672213.2660702, 688986
.9434444, 5672213.2660702, 689003.7038617, 5672209.234083, 689019.9899276, 56722
08.0482044, 689041.2566836, 5672207.0995015, 689073.2754053, 5672207.3366773, 68
9088.8499441, 5672209.1550244, 689105.2150685, 5672211.7639573, 689121.580193, 5
672215.2425345, 689143.6143583, 5672219.2900115, 689146.3030548, 5672220.3890619
, 689151.0386937, 5672220.6537948, 689166.2197529, 5672220.0103688, 689177.25392
49, 5672218.5566474, 689197.3773051, 5672219.2380055, 689207.3427181, 5672219.57
54247, 689207.3466499, 5672219.5761867, 689233.2260149, 5672224.5914525, 689239.
9071182, 5672225.5196698, 689247.1628566, 5672226.5276634, 689251.249516, 567222
6.4796659, 689254.0797907, 5672224.7193348, 689255.8714114, 5672221.372052, 6892
58.846377, 5672212.4758426, 689258.8467061, 5672212.4748585, 689264.1862085, 567
2195.0597142, 689266.7307578, 5672186.7604582, 689267.9214248, 5672183.6492769,
689268.0521668, 5672183.3500818, 689271.0942263, 5672176.3885103, 689273.8041834
, 5672170.1869346, 689287.0751811, 5672147.502818, 689292.5191401, 5672142.28473
66, 689299.3829221, 5672138.5557194, 689306.2610179, 5672136.0258451, 689314.720
2851, 5672135.3143179, 689348.685221, 5672134.9519443, 689396.0309948, 5672133.5
363983, 689416.3354092, 5672132.6880578, 689418.3812525, 5672132.7522219, 689425
.5665287, 5672132.3023716, 689464.8694628, 5672129.0514206, 689485.290895, 56721
26.1333665, 689503.0850162, 5672121.6374386, 689514.0326594, 5672120.044678, 689
527.0463784, 5672119.5619159, 689540.411092, 5672119.224758, 689555.9846643, 567
2120.4865145, 689570.481319, 5672123.2104577, 689599.3912896, 5672129.5715281, 6
89624.529635, 5672135.378406, 689659.3268291, 5672144.2701246, 689680.240749, 56
72148.213174, 689691.0782101, 5672147.7788539, 689691.489964, 5672147.6865915, 6
89702.15725, 5672145.2963554, 689703.5737165, 5672144.9789655, 689709.1463313, 5
672142.8829847, 689713.3569435, 5672139.7445624, 689714.8699614, 5672137.8201391
, 689715.0744592, 5672135.5114556, 689713.4941594, 5672133.6814377, 689709.39771
53, 5672132.6773369, 689696.5518903, 5672135.0197776, 689695.4265584, 5672135.22
49823, 689692.3509273, 5672135.3563285, 689687.6547636, 5672134.1716091, 689679.
6202212, 5672130.9749773, 689669.0408472, 5672125.2467629, 689666.8969027, 56721
23.6477029, 689658.8682662, 5672117.6595484, 689651.2133725, 5672111.0194657, 68
9649.4612871, 5672109.4996547, 689646.3016558, 5672106.2617253, 689630.9396058,
5672094.7948978, 689616.7240175, 5672089.7510428, 689597.1558766, 5672086.635282
6, 689590.6261149, 5672085.5955736, 689563.7168614, 5672084.4973399, 689523.2634
145, 5672087.1382932, 689472.9955469, 5672090.8834003, 689471.8472257, 5672090.9
293331, 689457.1838324, 5672091.5158689, 689447.6177452, 5672091.1996346, 689438
.2097751, 5672090.2509317, 689428.3274535, 5672087.4048231, 689422.4676892, 5672
083.3680689, 689418.0498391, 5672077.6806187, 689432.2406549, 5672032.8963104, 6
89440.065904, 5672009.7467589, 689441.175454, 5672004.5414301, 689447.4418864, 5
671975.9512454, 689478.3301604, 5671990.7887839, 689489.3204371, 5671996.06809,
689495.779407, 5671999.3739366, 689597.5277667, 5672052.5933863, 689602.3895798,
 5672055.1858974, 689657.1801227, 5672084.4023839, 689693.9809168, 5672104.65485
54, 689703.0011218, 5672109.9741831, 689743.7507426, 5672135.7990826, 689761.480
2772, 5672145.829108, 689778.1616359, 5672154.3674338, 689788.7554846, 5672159.4
271825, 689796.819459, 5672164.0125797, 689803.1655894, 5672170.7415994, 689807.
0043986, 5672175.8017781, 689810.1788388, 5672185.1254099, 689810.0683971, 56721
89.3752825, 689810.0094402, 5672191.6439821, 689809.7492257, 5672201.6572087, 68
9808.8538055, 5672204.3847946, 689808.6891154, 5672204.6142022, 689785.6530398,
5672273.7224291, 689762.0676297, 5672342.4904189, 689759.5582625, 5672351.424347
5, 689752.7289776, 5672373.9988578, 689752.6065068, 5672375.1725361, 689748.0148
942, 5672390.8581395, 689747.0752117, 5672397.6607347, 689746.4443331, 5672413.7
599364, 689748.851602, 5672421.3250603, 689751.6915973, 5672432.314692, 689751.8
031959, 5672434.1746685, 689748.6290872, 5672439.5971041, 689741.857233, 5672444
.2274703, 689723.5849996, 5672451.4304949, 689691.3885007, 5672461.8541881, 6896
79.1719849, 5672448.0873248, 689671.3681308, 5672441.8442414, 689653.5307501, 56
72426.6824678, 689643.0512889, 5672419.3245483, 689630.7880897, 5672413.973334,
689605.1468548, 5672405.2776109, 689562.5601083, 5672391.4536408, 689518.8585254
, 5672375.8459326, 689485.0600692, 5672362.7971616, 689483.6296984, 5672362.2449
299, 689480.5081568, 5672360.0152572, 689480.1867627, 5672357.926196, 689482.003
957, 5672349.9285335, 689484.252284, 5672340.0094439, 689488.3521744, 5672330.48
71178, 689492.5843193, 5672320.3035191, 689493.9068646, 5672314.4843198, 689490.
1760856, 5672311.2632911, 689490.1798946, 5672311.2358662, 689488.6150134, 56723
10.6579801, 689484.2740947, 5672309.0549467, 689482.7378294, 5672308.5098203, 68
9381.5106935, 5672272.3891242, 689294.5534624, 5672240.0588717, 689287.6356851,
5672237.7005385, 689274.9323435, 5672233.3698538, 689267.298928, 5672232.2527686
, 689265.7906859, 5672232.0320503, 689265.5698898, 5672232.2843887, 689262.66914
43, 5672235.5995265, 689258.8907546, 5672245.6267913, 689250.9848715, 5672265.86
40125, 689239.8580839, 5672297.2574489, 689238.428683, 5672296.8737963, 689231.5
180497, 5672295.0189758, 689214.5352266, 5672290.4983682, 689208.4881002, 567228
8.8887015, 689194.6258187, 5672286.7560428, 689191.0806672, 5672285.9143327, 689
178.599027, 5672282.9508707, 689586.4501435, 5672300.6988749, 689591.5451314, 56
72286.3105473, 689595.3076796, 5672276.3128392, 689601.6454122, 5672255.1215352,
 689605.1973598, 5672239.465882, 689611.0513018, 5672219.3139846, 689615.3658051
, 5672202.2770235, 689617.0384607, 5672191.5761073, 689618.7111999, 5672184.5628
324, 689612.7318988, 5672195.3933617, 689606.6365525, 5672209.0668987, 689596.02
52765, 5672244.2306245, 689589.1756461, 5672268.6315194, 689579.2702821, 5672298
.1285621, 689570.054728, 5672318.4776917, 689557.4642833, 5672363.369525, 689559
.8011975, 5672364.2559434, 689586.4501435, 5672300.6988749))

           1062855540751902662
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(655463.756251, 5776594.6645664, 655460.229957, 5776593.6645726, 655454.26858
59, 5776591.3072371, 655453.2542305, 5776590.8875038, 655433.050204, 5776584.963
8101, 655405.1899461, 5776578.8153394, 655364.4451972, 5776569.8233949, 655341.0
213922, 5776564.6540034, 655308.335297, 5776557.6725073, 655292.0569576, 5776554
.3088709, 655274.044575, 5776550.5869245, 655266.8694416, 5776549.1043076, 65525
9.681051, 5776548.3426239, 655251.8690337, 5776547.3327483, 655249.3239935, 5776
547.3681395, 655243.2804473, 5776547.2002632, 655235.6642698, 5776547.9407249, 6
55221.1379607, 5776549.7979699, 655218.8829871, 5776549.9923642, 655217.1966495,
 5776550.3018819, 655212.4983965, 5776550.9025718, 655186.8995775, 5776555.55690
25, 655183.3030493, 5776556.5089247, 655173.5510466, 5776558.3001088, 655165.244
078, 5776559.4077046, 655165.0105957, 5776558.7072579, 655166.811325, 5776555.10
57994, 655172.3293727, 5776553.5730084, 655183.9785845, 5776550.8139846, 655212.
1819393, 5776546.4199836, 655236.1956654, 5776543.0478434, 655244.574923, 577654
2.536913, 655252.6476224, 5776542.6390991, 655268.1799047, 5776545.2959369, 6553
43.8997814, 5776560.1129168, 655435.5606846, 5776580.7545026, 655459.0424116, 57
76586.5490183, 655461.9820201, 5776587.0389532, 655467.989616, 5776588.0402191,
655477.8857663, 5776587.7690917, 655487.2136161, 5776587.3022762, 655490.7775477
, 5776587.123918, 655493.9925512, 5776586.7127889, 655502.693924, 5776585.600072
4, 655504.6543017, 5776585.6000724, 655506.1935266, 5776592.2187394, 655490.8287
331, 5776594.7017507, 655490.1943268, 5776590.3666411, 655465.1352787, 5776589.8
379692, 655463.756251, 5776594.6645664))


5 rows selected.
Re: Load WKT using sql*loader [message #666622 is a reply to message #666614] Fri, 17 November 2017 07:27 Go to previous messageGo to next message
frank.reimann
Messages: 8
Registered: November 2017
Junior Member
Hello Barbara,
thanks for Your response.

First, "basic transformations" means that we've to correct the output from the select statement in some kind. E.g. I've got additional blank lines we removed and the output do not fits into a single line which then is wraped. So after that operation we have two columns called id and geometrie, the second holds the geometry data.

And... I tried following Your recommendations and we could load all data successfully. So the loader control file format, especially the 'TERMINATED...' clauses was very helpfull, thanks for that.

Kind regards and have a nice weekand.
Bye Frank
Re: Load WKT using sql*loader [message #666877 is a reply to message #666622] Thu, 30 November 2017 01:44 Go to previous messageGo to next message
frank.reimann
Messages: 8
Registered: November 2017
Junior Member
Hello Barbara,
the last discussed issue works fine for the moment, but I'm faced with a new situation.
In delivered csv files different kinds of SDO_GEOMETRY data is mixed up. As You can see in my provided example data there are polygons as well as points. My first try was to separate polygon data from the point data in different files and then load them in separately executed sql loader sessions. But this works fine only for the part with the ploygon data. All point data could only be loaded replacing the decimal point by a comma in the values of x and y.
What is Your recommendation for processing (loading) the data?
Thanks in advance,
Bye Frank

1) Example Data
  -126094947963131504|SDO_GEOMETRY(2003, 4647, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(32512555.70613779873, 6025825.636290729977, 32512550.83485529944, 6025818.708380140364, 32512545.65460190177, 6025807.123500409536, 32512543.01040489972, 6025796.54514419008, 32512541.95162370056, 6025786.892361969687, 32512544.33238830045, 6025769.436931439675, 32512551.20756930113, 6025705.831552520394, 32512562.5785018988, 6025613.928488469683, 32512574.47800410166, 6025510.389547759667, 32512586.28864509985, 6025412.926016679965, 32512593.51787060127, 6025359.377372499555, 32512595.10525650159, 6025358.848599120043, 32512596.95658250153, 6025360.435900770128, 32512634.33800140023, 6025393.832178629935, 32512652.23450290039, 6025409.891077009961, 32512678.4189410992, 6025430.519918469712, 32512685.56031170115, 6025435.014912510291, 32512698.5194851011, 6025440.30552764982, 32512745.33299810067, 6025456.701080350205, 32512763.58318740129, 6025463.445011160336, 32512819.1908344999, 6025482.257517449558, 32512828.11704799905, 6025484.205474649556, 32512893.97324709967, 6025496.635563739575, 32512900.05696219951, 6025498.089879569598, 32512909.31347610056, 6025499.809057270177, 32512949.51546369866, 6025506.684701099992, 32513032.29969409853, 6025522.552019570023, 32513033.09342209995, 6025523.873427590355, 32513032.82858930156, 6025527.31190478988, 32513012.73076440021, 6025660.339014720172, 32512993.95423660055, 6025783.051627789624, 32512986.28517020121, 6025836.47393022012, 32512984.96268809959, 6025837.531464920379, 32512954.01840750128, 6025842.027569389902, 32512940.265602801, 6025843.350026310422, 32512915.66795140132, 6025847.846151470207, 32512901.12153270096, 6025850.888019329868, 32512898.21265760064, 6025851.945593480021, 32512877.31870840117, 6025858.028224219568, 32512858.53989829868, 6025861.20111544989, 32512846.10960549861, 6025860.673540069722, 32512839.23323610052, 6025861.732180359773, 32512826.2731859982, 6025863.318683080375, 32512787.39401879907, 6025867.287543689832, 32512769.14391890168, 6025865.964408989996, 32512764.01986249909, 6025865.913511140272, 32512761.71175070107, 6025866.542271340266, 32512751.01079960167, 6025865.913713710383, 32512743.87751070037, 6025866.122733259574, 32512718.27815920115, 6025862.766601050273, 32512706.52661399916, 6025862.556875119917, 32512693.51854560152, 6025862.347168579698, 32512689.0062229, 6025860.544026600197, 32512683.71620459855, 6025859.221686209552, 32512662.822015699, 6025853.139663719572, 32512639.28282539919, 6025846.792803750373, 32512636.63779349998, 6025844.94065484032, 32512631.08388070017, 6025843.883201629855, 32512619.18233110011, 6025840.445884689689, 32512588.23686730117, 6025829.603083129972, 32512585.32792830095, 6025827.35510734003, 32512581.4704034999, 6025827.939908660017, 32512579.50916180015, 6025828.676615930162, 32512569.19405639917, 6025827.619228740223, 32512560.46637370065, 6025827.884241269901, 32512555.70613779873, 6025825.636290729977))
 -126094947963131501|SDO_GEOMETRY(2003, 4647, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(32494647.07723389938, 6047361.438862780109, 32494605.25351950154, 6047464.256969889626, 32494566.37276430055, 6047556.029602379538, 32494507.12915069982, 6047539.896046349779, 32494535.43056929857, 6047473.248942770064, 32494572.45794180036, 6047386.369259879924, 32494634.93126599863, 6047243.130144639872, 32494670.85081690177, 6047160.773290139623, 32494698.35719859973, 6047095.580389400013, 32494726.12941139936, 6047030.916237530299, 32494730.36098229885, 6047017.42883421015, 32494733.00575039908, 6047004.734055100009, 32494745.16904219985, 6046971.277592049912, 32494765.79468239844, 6046923.672004629858, 32494788.80091699958, 6046894.71200618986, 32494830.58370070159, 6046846.048484040424, 32494852.7997232005, 6046846.048807200044, 32494873.42733690143, 6046825.155797810294, 32494881.89153400064, 6046831.503292440437, 32494884.40214810148, 6046837.892668030225, 32494884.80108710006, 6046838.90925018955, 32494883.47932570055, 6046846.049253280275, 32494861.85975300148, 6046864.027479980141, 32494834.81968170032, 6046886.513855709694, 32494826.09152489901, 6046890.216236299835, 32494830.05942770094, 6046895.769989670254, 32494858.81460030004, 6046870.828620689921, 32494885.85949439928, 6046847.371736220084, 32494898.02668900043, 6046858.877099599689, 32494881.29416010156, 6046877.558092970401, 32494877.48571319878, 6046877.768960709684, 32494837.28901730105, 6046923.46963045001, 32494832.74034240097, 6046929.060290849768, 32494819.72998049855, 6046945.051623810083, 32494811.69136070088, 6046958.16919447016, 32494802.5945844017, 6046976.153786879964, 32494772.55169799924, 6047047.455390949734, 32494719.86514930055, 6047178.000199080445, 32494703.1739689, 6047218.369418010116, 32494676.91208469868, 6047281.885990509763, 32494650.0393531993, 6047347.263761419803, 32494643.69158110023, 6047360.380292469636, 32494647.07723389938, 6047361.438862780109))
 -126094933102632175|SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32513401.70741016418, 6025310.709279053845, null), null, null)
 -126094944850181618|SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32493526.18633791059, 6026872.772411953658, null), null, null)


2) content of loader control file for loading only polygons (works fine)
LOAD DATA
infile poly.csv
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
APPEND
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ',',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ',',
SDO_POINT FILLER TERMINATED BY ', SDO_ELEM_INFO_ARRAY(',
SDO_ELEM_INFO VARRAY TERMINATED BY '), SDO_ORDINATE_ARRAY('
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '))'
(ordinates FLOAT EXTERNAL)
)
)

3) sql loader control file for loading points only
LOAD DATA
infile point.csv
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE PNT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
 (
  X     FLOAT EXTERNAL TERMINATED BY ', ',
  Y     FLOAT EXTERNAL TERMINATED BY ', null'
 )
)
)
Re: Load WKT using sql*loader [message #666881 is a reply to message #666877] Thu, 30 November 2017 03:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> HOST TYPE test.ctl
LOAD DATA
infile point.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X     FLOAT EXTERNAL TERMINATED BY ', ',
Y     FLOAT EXTERNAL TERMINATED BY ', null'
)
)
)

SCOTT@orcl_12.1.0.2.0> CREATE TABLE pnt
  2    (id         NUMBER,
  3     geometrie  MDSYS.SDO_GEOMETRY)
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Nov 30 01:51:12 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2

Table PNT:
  2 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM pnt
  2  /

                            ID
------------------------------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
           -126094933102632175
SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32513401.70741016418, 6025310.7092790538
45, NULL), NULL, NULL)


           -126094944850181618
SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32493526.18633791059, 6026872.7724119536
58, NULL), NULL, NULL)


2 rows selected.
Re: Load WKT using sql*loader [message #666883 is a reply to message #666881] Thu, 30 November 2017 04:11 Go to previous messageGo to next message
frank.reimann
Messages: 8
Registered: November 2017
Junior Member
Thanks for response:

my ctl file:

oracle@sv111:~/importST> cat point_3.ctl
LOAD DATA
infile point_1.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X     FLOAT EXTERNAL TERMINATED BY ', ',
Y     FLOAT EXTERNAL TERMINATED BY ', null'
)
)
)

Content of the data file:

oracle@sv111:~/importST> cat point_1.csv
 -126094933102632175|SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32513401.70741016418, 6025310.709279053845, null), null, null)
 -126094944850181618|SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32493526.18633791059, 6026872.772411953658, null), null, null)
oracle@sv111:~/importST>

I just tried:

sqlldr user/pass control=point_3.ctl

and this is what I received in the point_3.log with ora-01722 :/

oracle@sv111:~/importST> cat point_3.log

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Nov 30 11:02:51 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   point_3.ctl
Data File:      point_1.csv
  Bad File:     point_1.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:   1:1 != 0X20(character ' '), in next physical record
Path used:      Conventional

Table PNT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *           CHARACTER
    Terminator string : '|SDO_GEOMETRY('
GEOMETRIE                         DERIVED     *           COLUMN OBJECT

*** Fields in GEOMETRIE
SDO_GTYPE                            NEXT     *           CHARACTER
    Terminator string : ', '
SDO_SRID                             NEXT     *           CHARACTER
    Terminator string : ', SDO_POINT_TYPE('
SDO_POINT                         DERIVED     *           COLUMN OBJECT

*** Fields in GEOMETRIE.SDO_POINT
X                                    NEXT     *           CHARACTER
    Terminator string : ', '
Y                                    NEXT     *           CHARACTER
    Terminator string : ', null'
*** End of fields in GEOMETRIE.SDO_POINT

*** End of fields in GEOMETRIE


Record 1: Rejected - Error on table PNT, column GEOMETRIE.SDO_POINT.X.
ORA-01722: Ungultige Zahl

Record 2: Rejected - Error on table PNT, column GEOMETRIE.SDO_POINT.X.
ORA-01722: Ungultige Zahl


Table PNT:
  0 Rows successfully loaded.
  2 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  82560 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             2
Total logical records rejected:         2
Total logical records discarded:        0

Run began on Thu Nov 30 11:02:51 2017
Run ended on Thu Nov 30 11:02:51 2017

Elapsed time was:     00:00:00.06
CPU time was:         00:00:00.03
oracle@sv111:~/importST>

What did I wrong?

Thx Frank
Re: Load WKT using sql*loader [message #666884 is a reply to message #666883] Thu, 30 November 2017 06:42 Go to previous messageGo to next message
frank.reimann
Messages: 8
Registered: November 2017
Junior Member
some replacements solve the issue, see content of control file:

LOAD DATA
infile point_1.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X     TERMINATED BY ', ' "to_number(replace(:GEOMETRIE.SDO_POINT.X,'.',','))",
Y     TERMINATED BY ', null' "to_number(replace(:GEOMETRIE.SDO_POINT.Y,'.',','))"
)
)
)

But this is not an explanation, why the loader handles point data and polygons defferently. I was expecting, that the formatting of ordinates (in case of polygons) and the point data would be treated in the same manner. In both cases we have floats seperated by "." but ...

Next I try to write a control file handling both geometries, points and polygons in a single load run.

thx and bye
Frank
Re: Load WKT using sql*loader [message #666909 is a reply to message #666884] Thu, 30 November 2017 14:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Apparently your nls_numeric_characters in your data and your database are different and you found a workaround using to_number and replace.

If you want to use one control file to load points and polygons, you can use two separate INTO and WHEN clauses, as shown below. I used GEOMETRIE.SDO_GTYPE='2003' and GEOMETRIE.SDO_GTYPE='2001'. If you have more sdo_gtype's than the 2001 and 2003, then you will need to modify that. Since my system has different nls_numeric_characters than yours, you will also need to add your to_number and replace. I used your example data for the example_data.csv. Note that you have to reset the position to 1 for the first field after each INTO and WHEN clause after the first one. I also allowed for a Z point.

SCOTT@orcl_12.1.0.2.0> HOST TYPE test.ctl
LOAD DATA
infile example_data.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE test
WHEN GEOMETRIE.SDO_GTYPE='2003'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ',',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ',',
SDO_POINT FILLER TERMINATED BY ', SDO_ELEM_INFO_ARRAY(',
SDO_ELEM_INFO VARRAY TERMINATED BY '), SDO_ORDINATE_ARRAY('
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '))'
(ordinates FLOAT EXTERNAL)
)
)
INTO TABLE test
WHEN GEOMETRIE.SDO_GTYPE='2001'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID POSITION(1) INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X     TERMINATED BY ', ',
Y     TERMINATED BY ', ',
Z     TERMINATED BY ')' NULLIF GEOMETRIE.SDO_POINT.Z='null'
)
)
)

SCOTT@orcl_12.1.0.2.0> CREATE TABLE test
  2    (id         NUMBER,
  3     geometrie  MDSYS.SDO_GEOMETRY)
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Nov 30 12:45:55 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3
Commit point reached - logical record count 4

Table TEST:
  2 Rows successfully loaded.

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
  2  /

                            ID
------------------------------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
           -126094947963131504
SDO_GEOMETRY(2003, 4647, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(32512555.70613779873, 6025825.636290729977, 32512550.83485529944, 6025818.708
380140364, 32512545.65460190177, 6025807.123500409536, 32512543.01040489972, 602
5796.54514419008, 32512541.95162370056, 6025786.892361969687, 32512544.332388300
45, 6025769.436931439675, 32512551.20756930113, 6025705.831552520394, 32512562.5
785018988, 6025613.928488469683, 32512574.47800410166, 6025510.389547759667, 325
12586.28864509985, 6025412.926016679965, 32512593.51787060127, 6025359.377372499
555, 32512595.10525650159, 6025358.848599120043, 32512596.95658250153, 6025360.4
35900770128, 32512634.33800140023, 6025393.832178629935, 32512652.23450290039, 6
025409.891077009961, 32512678.4189410992, 6025430.519918469712, 32512685.5603117
0115, 6025435.014912510291, 32512698.5194851011, 6025440.30552764982, 32512745.3
3299810067, 6025456.701080350205, 32512763.58318740129, 6025463.445011160336, 32
512819.1908344999, 6025482.257517449558, 32512828.11704799905, 6025484.205474649
556, 32512893.97324709967, 6025496.635563739575, 32512900.05696219951, 6025498.0
89879569598, 32512909.31347610056, 6025499.809057270177, 32512949.51546369866, 6
025506.684701099992, 32513032.29969409853, 6025522.552019570023, 32513033.093422
09995, 6025523.873427590355, 32513032.82858930156, 6025527.31190478988, 32513012
.73076440021, 6025660.339014720172, 32512993.95423660055, 6025783.051627789624,
32512986.28517020121, 6025836.47393022012, 32512984.96268809959, 6025837.5314649
20379, 32512954.01840750128, 6025842.027569389902, 32512940.265602801, 6025843.3
50026310422, 32512915.66795140132, 6025847.846151470207, 32512901.12153270096, 6
025850.888019329868, 32512898.21265760064, 6025851.945593480021, 32512877.318708
40117, 6025858.028224219568, 32512858.53989829868, 6025861.20111544989, 32512846
.10960549861, 6025860.673540069722, 32512839.23323610052, 6025861.732180359773,
32512826.2731859982, 6025863.318683080375, 32512787.39401879907, 6025867.2875436
89832, 32512769.14391890168, 6025865.964408989996, 32512764.01986249909, 6025865
.913511140272, 32512761.71175070107, 6025866.542271340266, 32512751.01079960167,
 6025865.913713710383, 32512743.87751070037, 6025866.122733259574, 32512718.2781
5920115, 6025862.766601050273, 32512706.52661399916, 6025862.556875119917, 32512
693.51854560152, 6025862.347168579698, 32512689.0062229, 6025860.544026600197, 3
2512683.71620459855, 6025859.221686209552, 32512662.822015699, 6025853.139663719
572, 32512639.28282539919, 6025846.792803750373, 32512636.63779349998, 6025844.9
4065484032, 32512631.08388070017, 6025843.883201629855, 32512619.18233110011, 60
25840.445884689689, 32512588.23686730117, 6025829.603083129972, 32512585.3279283
0095, 6025827.35510734003, 32512581.4704034999, 6025827.939908660017, 32512579.5
0916180015, 6025828.676615930162, 32512569.19405639917, 6025827.619228740223, 32
512560.46637370065, 6025827.884241269901, 32512555.70613779873, 6025825.63629072
9977))

           -126094947963131501
SDO_GEOMETRY(2003, 4647, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(32494647.07723389938, 6047361.438862780109, 32494605.25351950154, 6047464.256
969889626, 32494566.37276430055, 6047556.029602379538, 32494507.12915069982, 604
7539.896046349779, 32494535.43056929857, 6047473.248942770064, 32494572.45794180
036, 6047386.369259879924, 32494634.93126599863, 6047243.130144639872, 32494670.
85081690177, 6047160.773290139623, 32494698.35719859973, 6047095.580389400013, 3
2494726.12941139936, 6047030.916237530299, 32494730.36098229885, 6047017.4288342
1015, 32494733.00575039908, 6047004.734055100009, 32494745.16904219985, 6046971.
277592049912, 32494765.79468239844, 6046923.672004629858, 32494788.80091699958,
6046894.71200618986, 32494830.58370070159, 6046846.048484040424, 32494852.799723
2005, 6046846.048807200044, 32494873.42733690143, 6046825.155797810294, 32494881
.89153400064, 6046831.503292440437, 32494884.40214810148, 6046837.892668030225,
32494884.80108710006, 6046838.90925018955, 32494883.47932570055, 6046846.0492532
80275, 32494861.85975300148, 6046864.027479980141, 32494834.81968170032, 6046886
.513855709694, 32494826.09152489901, 6046890.216236299835, 32494830.05942770094,
 6046895.769989670254, 32494858.81460030004, 6046870.828620689921, 32494885.8594
9439928, 6046847.371736220084, 32494898.02668900043, 6046858.877099599689, 32494
881.29416010156, 6046877.558092970401, 32494877.48571319878, 6046877.76896070968
4, 32494837.28901730105, 6046923.46963045001, 32494832.74034240097, 6046929.0602
90849768, 32494819.72998049855, 6046945.051623810083, 32494811.69136070088, 6046
958.16919447016, 32494802.5945844017, 6046976.153786879964, 32494772.55169799924
, 6047047.455390949734, 32494719.86514930055, 6047178.000199080445, 32494703.173
9689, 6047218.369418010116, 32494676.91208469868, 6047281.885990509763, 32494650
.0393531993, 6047347.263761419803, 32494643.69158110023, 6047360.380292469636, 3
2494647.07723389938, 6047361.438862780109))

           -126094933102632175
SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32513401.70741016418, 6025310.7092790538
45, NULL), NULL, NULL)

           -126094944850181618
SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32493526.18633791059, 6026872.7724119536
58, NULL), NULL, NULL)


4 rows selected.




Re: Load WKT using sql*loader [message #666911 is a reply to message #666909] Fri, 01 December 2017 01:47 Go to previous message
frank.reimann
Messages: 8
Registered: November 2017
Junior Member
Hello Barbara,
the following control file worked for me:

LOAD DATA
infile ktr_all_awk.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
WHEN GEOMETRIE.SDO_GTYPE='2003'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ',',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ',',
SDO_POINT FILLER TERMINATED BY ', SDO_ELEM_INFO_ARRAY(',
SDO_ELEM_INFO VARRAY TERMINATED BY '), SDO_ORDINATE_ARRAY('
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '))'
(ordinates FLOAT EXTERNAL)
)
)
INTO TABLE PNT
WHEN GEOMETRIE.SDO_GTYPE='2001'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID POSITION(1) INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID  INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X     TERMINATED BY ', ' "to_number(replace(:GEOMETRIE.SDO_POINT.X,'.',','))",
Y     TERMINATED BY ', ' "to_number(replace(:GEOMETRIE.SDO_POINT.Y,'.',','))",
Z     TERMINATED BY ')' NULLIF GEOMETRIE.SDO_POINT.Z='null'
)
)
)

I agree with You, that some enviroment settings do not fit the needs, You remember the "." / "," issue... If the replacement will not be included in the control file sql loader only processes the polygon data, not any point geometry, strange. But anyway for the moment I've got the import working.

Thanks a lot and kind regards
Frank
Previous Topic: Exp in Oracle 12c
Next Topic: oracle impdp command issue
Goto Forum:
  


Current Time: Fri Mar 29 06:24:59 CDT 2024