topic Could not create function with ST_LINEFROMTEXT and ST_ASTEXT in Developers Questions

Could not create function with ST_LINEFROMTEXT and ST_ASTEXT

PlinioSantos — Sat, 11 Dec 2021 21:07:23 GMT

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?

Re: Could not create function with ST_LINEFROMTEXT and ST_ASTEXT

PlinioSantos — Thu, 16 Jul 2015 12:32:48 GMT

The very same create function query which fails when using Oracle SQL Developer, works when I ran in SqlPlus.

So, it's a Oracle SQL Developler issue, 4.0.3.16 version.

Re: Could not create function with ST_LINEFROMTEXT and ST_ASTEXT

MarceloMarques — Tue, 26 Jan 2016 18:25:06 GMT

My Oracle Support

Compiling In SQL Developer Throws "PLS-00707: Unsupported Construct Or Internal Error" (Doc ID 1521171.1)

In this Document

APPLIES TO:

Oracle SQL Developer - Version 3.2 and later
Information in this document applies to any platform.

SYMPTOMS

sql developer version: 3.2.20.09
Database version: 11.2.0.3

Attempting to compile Function/Prucedure/Package in SQL Developer throws the following error:

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

This issue is not occuring in sqlplus. This issue occurs only for the 11g/11gr2 Database on SQL Developer.

CAUSE

It seems to be caused by the parameter PL/SCOPE variable when it uses the value "IDENTIFIERS:ALL".
SQL Developer during connection establishing changes the session parameter PLSCOPE_SETTINGS from IDENTIFIERS:NONE (default) to IDENTIFIERS:ALL.
Same error was received in SQL PLUS when issuing an "alter session set PLSCOPE_SETTINGS=IDENTIFIERS:ALL".

More about PL/Scope can be found here:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams178.htm

SOLUTION

1. Go to Preferences in SQL Developer.
2. Then go to Tools -> Preferences -> Database -> PL/SQL Compiler
3. Change the "PLScope identifiers" from All to None
4. Restart the SQL Developer.