ORA-20004: Error generating shape from text:(-3)

[sapnas]

USING  ST_GEOMETRY function on linestring text which exceeds 4000 characters causes following error

ORA-20004: Error generating shape from text:   (-3).
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12
ORA-06512: at "SDE.ST_LINEFROMTEXT", line 59

database version details below

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production

any helps is greatly appreciated.

Here is my line string and the SRID is 32040

  'LINESTRING(1916863.10472783 147945.82102773,
1916866.41259536 147944.734735463, 1916867.00604686 147944.625821803, 1916867.23100575 147944.826739584, 1916867.73530063 147944.991273377, 1916868.44903907 147944.885503505,
1916868.46503743 147944.669496272, 1916868.75067672 147944.323867295, 1916869.16899451 147944.190119718, 1916869.17872771 147944.175045584, 1916869.00781449 147944.307875369,
1916868.71576365 147944.169388507, 1916869.02433213 147943.706469987, 1916869.13964995 147943.390862678, 1916868.82345828 147942.897734616, 1916868.5300723 147942.555263255,
1916867.29444655 147941.45677564, 1916865.83494529 147941.017853234, 1916864.85058066 147940.921635602,
1916863.8824006 147939.888789151, 1916863.14941396 147939.296271298, 1916862.94378256 147939.151989434, 1916863.03669881 147938.907670949, 1916863.67669102 147939.043199037, 1916864.47599718 147939.621924375, 1916865.09421035 147939.628589128,
1916865.1048766 147939.190384362, 1916864.99322721 147938.903672193, 1916865.07943925 147938.802263711, 1916866.04720887 147938.997803662,
1916866.03641948 147938.731298421, 1916864.18353971 147936.2300472, 1916863.899457 147935.694034551, 1916864.23116931 147935.3392534, 1916864.03319385 147934.544035886,
1916863.85209313 147934.588932012, 1916864.3760682 147934.738170598, 1916864.30907079 147934.898861859, 1916864.05084094 147935.266419385, 1916863.9995549 147935.525226567,
1916863.92414256 147935.331904386, 1916863.61059477 147934.946382497, 1916863.43904283 147934.756318067, 1916863.40616524 147934.891036008, 1916863.14564597 147934.89017484,
1916862.9853855 147934.665959332, 1916862.83934769 147934.353802655, 1916862.98826697 147934.097403501, 1916861.53023192 147934.728814099, 1916856.62843093 147937.803709004,
1916850.15668496 147943.822739575, 1916843.6152831 147951.538139794, 1916835.56445226 147959.631586049, 1916824.65710744 147968.195262883, 1916811.53008565 147977.104892705,
1916797.23483952 147985.74151418, 1916789.14649877 147990.887624715, 1916767.31476506 148030.900770162, 1916765.1042777 148041.612653707, 1916763.43047246 148053.567197774,
1916759.55518254 148080.863599751, 1916756.96987065 148096.492261861, 1916748.32898625 148148.751615499, 1916745.90046605 148167.867551778, 1916744.51917943 148188.720182393,
1916744.34766301 148210.49012563, 1916746.43778142 148244.675641988, 1916750.5056754 148279.692731832, 1916754.74532231 148315.271802876, 1916758.85929784 148354.470411275,
1916762.44892796 148392.3708019, 1916765.35494145 148433.922773335, 1916767.32607182 148475.007123921, 1916769.33041295 148518.284284566, 1916770.9655219 148561.894575093,
1916771.70069035 148607.489545796, 1916771.46864232 148658.744611714, 1916770.58342656 148699.896528218, 1916769.4742957 148743.436567281, 1916768.39841374 148795.239423726,
1916767.44657811 148839.209577535, 1916766.30757817 148890.18430898, 1916762.90132817 148985.10630605, 1916759.67697057 149081.043073628, 1916757.44599828 149175.014509175,
1916754.2213355 149270.95786855, 1916750.12939366 149364.866926168, 1916745.27304753 149460.732526753, 1916740.64240941 149555.582502339, 1916736.90525731 149650.491804097,
1916734.35219487 149745.456281636, 1916733.63074407 149840.432233785, 1916734.88239201 149935.402204488, 1916736.03469762 150030.393781636, 1916736.58185491 150125.392072652,
1916736.48240575 150220.388166402, 1916735.91039189 150315.384016011, 1916735.67827519 150410.382062886, 1916734.90756902 150506.378156636, 1916738.39316472 150601.251447652,
1916747.80755528 150695.736799214, 1916754.4144431 150790.465314839, 1916757.49632176 150886.400373433, 1916758.68970403 150981.376447652, 1916757.48779973 151076.355451558,
1916757.16453465 151171.349836324, 1916758.26687535 151266.326887105, 1916758.04885014 151362.312726949, 1916755.62868413 151457.268781636, 1916752.20661839 151552.178449605,
1916748.36250981 151647.062726949, 1916744.50128087 151741.960187886, 1916741.21140965 151836.881330464, 1916739.65374669 151932.830549214, 1916740.60668858 152027.780012105,
1916742.54161748 152121.718488668, 1916744.32337102 152216.671125386, 1916746.10543736 152311.641828511, 1916747.78823003 152406.624250386, 1916749.1537238 152502.61399648,
1916750.22315129 152597.607648824, 1916751.35060033 152692.600324605, 1916752.61067876 152787.591047261, 1916753.87077245 152882.58274648, 1916754.67491826 152977.57884023,
1916755.1889182 153072.577375386, 1916755.87695989 153167.569562886, 1916756.85509404 153262.554914449, 1916757.04572972 153357.548078511, 1916755.88514623 153452.538801168,
1916754.01995763 153547.519758199, 1916753.46150884 153572.512922261)'

[VinceAngelo]

The error was explicit -- the VARCHAR constant exceeded 4000 bytes.  You can use a CLOB type for this purpose, but getting the string into that CLOB is the trick.

-V

[sapnas]

I did use clob and bindvariable to insert into the clob as I could not directly insert the string into the column. Now I want to select the column and convert the text into line sde.st_Linefromtext. No matter what methodology I use it always causes the above error. I suspect  it has something to do with the size of the linestring that is causing error on  sde.st_Linefromtext function . 

[sapnas]

Vince,

The line string below is less than 4000 character and the sde.st_Linefromtext function  works without any issues. These strings are retrieved from non-gis system and any feedback on converting and transforming it into a different srid is greatly appreciated.  

'LINESTRING ( 2331612.67955573 342169.36893080, 2331612.90035582 342169.95718421, 2331612.83178639 342171.49720737,
2331613.34228407 342173.25343746, 2331615.05258249 342174.67436638, 2331616.63000715 342175.88007262,
2331617.57619949 342176.95323321, 2331618.52632882 342177.93781130, 2331619.18840098 342178.82232396,
2331618.79962224 342179.33741480, 2331618.14148706 342179.36595805, 2331617.17462549 342179.38039371,
2331616.46268465 342180.12514287, 2331616.64673939 342181.01129596, 2331616.94562331 342181.53360464,
2331616.59949540 342181.44994338, 2331615.86032365 342180.83806796, 2331614.82981390 342180.81739871,
2331613.18447599 342181.66746263, 2331610.05784182 342183.08970387, 2331605.72287673 342184.64613122, 2331601.79145415 342186.13333297,
2331598.39579165 342187.10741238, 2331595.98372298 342187.92565221, 2331594.34100974 342189.37282780, 2331590.45158182 342190.80359921,
2331583.93092556 342192.54703404, 2331578.45849557 342194.53849988, 2331575.31381682 342195.95942880, 2331572.40240532 342197.30457047,
2331569.58613798 342198.64118196, 2331568.44375181 342200.17890854, 2331568.38896190 342200.73402555, 2331568.29841089 342201.55620238,
2331568.33482815 342202.70022896, 2331567.90372665 342205.34228405, 2331566.35517332 342208.46005996, 2331563.85222557 342211.36392555,
2331560.45426649 342214.90558513, 2331558.85125132 342217.87080230, 2331557.44049299 342221.09881422, 2331556.23150590 342223.82420246,
2331555.30663899 342226.13161254, 2331554.45526274 342228.20050605, 2331553.56681307 342230.16310054, 2331552.78695898 342231.99118088,
2331552.26366606 342233.73363146, 2331551.85323381 342235.51020271, 2331551.59601648 342237.55711463, 2331551.79286648 342239.93473455,
2331552.37849523 342242.34877171, 2331553.02842832 342244.57612947, 2331553.59798098 342248.79429688, 2331552.14719649 342253.36384155,
2331548.25875281 342258.65024829, 2331545.86210406 342261.55345771, 2331543.70495614 342264.17484355, 2331541.84144281 342266.28737213,
2331540.14459582 342268.10331339, 2331538.54748614 342269.66958322, 2331536.07734673 342271.55639046, 2331535.10425156 342272.01636329,
2331534.11704881 342272.44746479, 2331533.14789064 342272.27456488, 2331533.28995073 342271.63677087, 2331533.26107940 342271.21748038,
2331532.91068640 342271.83493321, 2331533.49303432 342274.53079396, 2331534.60162790 342275.76930855, 2331535.59342381 342277.04194380,
2331535.94348873 342278.91398729, 2331536.09014198 342280.56227796, 2331536.20004989 342282.06424347, 2331536.45267406 342282.76831029,
2331536.58128273 342283.33589447, 2331536.57832998 342284.95170487, 2331536.43397331 342286.47302729, 2331536.15903948 342287.96285371,
2331535.78075939 342289.47958297, 2331535.20431699 342290.99204713, 2331534.43594581 342292.48548247, 2331533.53863789 342293.95431155,
2331532.51698640 342295.43167080, 2331531.33358982 342296.99630021, 2331529.96384190 342298.68855405, 2331528.48582648 342300.44478413,
2331526.94121015 342302.19478063, 2331525.28570165 342303.97856972, 2331523.53931406 342305.84733237, 2331521.69614190 342307.70100321, 2331519.41038531 342309.83452913,
2331517.87593956 342311.26169163)' 

Thanks,

Sapna

[sapnas]

The error was associated with dropping z values from the linestring. I did not encounter the error while passing original linestring Z with z value to  the ST_GEOMETRY function.

[venaktesa_kumarNatarajan]

Hi,

I am trying to read from one source and insert into SDE using Arcpy with cx_oracle.

I am able to insert lines using sde.st_linestring if the string length is lessthan 4000.

i got the same error as the string is too long.

Did you solve the issue?

Regards

Kumar

[sapnas]

you may want to use clob and bindvariable