Select to view content in your preferred language

Could not create function with ST_LINEFROMTEXT and ST_ASTEXT

3887
2
Jump to solution
07-15-2015 01:02 PM
PlinioSantos
Regular Contributor

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?

0 Kudos
1 Solution

Accepted Solutions
PlinioSantos
Regular Contributor

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.

View solution in original post

2 Replies
PlinioSantos
Regular Contributor

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.

MarceloMarques
Esri Regular Contributor

To BottomTo Bottom


In this Document

Symptoms
Cause
Solution

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.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Database Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |