AnsweredAssumed Answered

Could not create function with ST_LINEFROMTEXT and ST_ASTEXT

Question asked by plinio on Jul 15, 2015
Latest reply on Jan 26, 2016 by mmarques-esristaff

Hi,

 

I'm writing an oracle function to convert a ST_MULTILINESTRING to a ST_LINESTRING.

To reach that goal, the function would convert the input object to WKT format, then perform some modifications to resulting string and finally create the ST_LINESTRING:

 

CREATE OR REPLACE FUNCTION MFICA_LINHA
  (MULTILINHA IN SDE.ST_MULTILINESTRING)
  RETURN SDE.ST_LINESTRING
IS
  LINHA SDE.ST_LINESTRING;
BEGIN
  SELECT SDE.ST_LINEFROMTEXT(replace(replace(replace(replace(SDE.ST_ASTEXT(MULTILINHA),'((','('),'),(',','),'))',')'),'MULTILINESTRING','LINESTRING'), 0)
    INTO LINHA
    FROM DUAL;
  RETURN LINHA;
END;

But it is getting the following error:

Error: PLS-00707: unsupported construct or internal error [2603]

Just to make sure that it youd work, I did:

SELECT SDE.ST_LINEFROMTEXT(LINE_TEXT, 0) LINE
FROM (
  SELECT replace(replace(replace(replace(MLINE_TEXT, '((','('),'),(',','),'))',')'),'MULTILINESTRING','LINESTRING') LINE_TEXT
  FROM (
    SELECT SDE.ST_ASTEXT(MLINE) MLINE_TEXT
    FROM (
      SELECT SDE.ST_MLINEFROMTEXT('MULTILINESTRING ((0 0, 1 1),(1 1, 2 2))', 0) MLINE
      FROM DUAL
    )
  )
);

And I got a ST_SINGLELINE as expected.

In order to understand what would cause the creation funtion failure, I wrote a simpler version of the funciton:

CREATE OR REPLACE FUNCTION MFICA_LINHA
  (MULTILINHA IN SDE.ST_MULTILINESTRING)
  RETURN SDE.ST_LINESTRING
IS
  LINHA SDE.ST_LINESTRING;
BEGIN
  SELECT SDE.ST_LINEFROMTEXT('', 0)
    INTO LINHA
    FROM DUAL;
  RETURN LINHA;
END;

Since it successful compiled, tryied:

CREATE OR REPLACE FUNCTION MFICA_LINHA
  (MULTILINHA IN SDE.ST_MULTILINESTRING)
  RETURN SDE.ST_LINESTRING
IS
  LINHA SDE.ST_LINESTRING;
BEGIN
  SELECT SDE.ST_LINEFROMTEXT(SDE.ST_ASTEXT(MULTILINHA), 0)
    INTO LINHA
    FROM DUAL;
  RETURN LINHA;
END;

But I got the PLS-00707 error.

Why does my function creation fails, and why can't I use both ST_ASTEXT and ST_LINEFROMSTRING nestled?

Outcomes