I have an Oracle 18c table that has an SDO_GEOMETRY column (lines): create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry); insert into a_sdo_geometry_tbl (line_id, shape) values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658)) ) ---------------- select line_id, sdo_util.to_wktgeometry(shape) as well_known_text from a_sdo_geometry_tbl; LINE_ID WELL_KNOWN_TEXT -------------------------------------------------------------------------------- 1 LINESTRING (671539.685273438 4863324.18143614, 671595.050070336 4863343.16655619, 671614.013553706 4863350.34348304, 671622.204415338 4863353.52539613) 2 LINESTRING (71534.5567096211 4863119.99180975, 671640.738468866 4863157.13274525, 671684.86211504 4863172.02299559) 3 LINESTRING (671622.204415338 4863353.52539613, 671633.326716411 4863357.84622911, 671904.061407769 4863451.28616675) 4 LINESTRING (671684.862052112 4863172.02299559, 671892.149614432 4863244.14144007, 671951.21565712 4863264.82431039, 671957.447146119 4863266.84761768, 671966.824385692 4863269.14663266) 4 rows selected. -------------------------- For each line, I want to select each vertex as a separate row in a query/resultset. I want to do this via a **recursive WITH clause** -- without using the GetVertices() function or a custom TYPE. Is there a way to do that? ---------------------- The resultset would look like this: LINE_ID VERTEX_ID X Y ---------- ---------- ---------- ---------- 1 1 671539.685 4863324.18 1 2 671595.050 4863343.17 1 3 671614.014 4863350.34 1 4 671622.204 4863353.53 2 1 71534.5567 4863119.99 2 2 671640.738 4863157.13 2 3 671684.862 4863172.02 3 1 671622.204 4863353.53 3 2 671633.327 4863357.85 3 3 671904.061 4863451.29 4 1 671684.862 4863172.02 4 2 671892.150 4863244.14 4 3 671951.216 4863264.82 4 4 671957.447 4863266.85 4 5 671966.824 4863269.15 Hints: The following functions might be useful: PointN: Returns a point that is the nth vertex in the collection of vertices GetNumVertices: Returns the number of vertices in the input geometry. ----------------

in Oracle 18 is there a way to enforce the max length of function names (and constant-names) to be 30 chars only (as it is in Oracle 11 and priors)?

I'm working on an Oracle Forms 11g application linked with an Oracle 18c database and I stumbled upon a strange error when compiling my form. Here's a snippet to illustrate the problem (I changed the identifiers name but it doesn't matter): PROCEDURE SOME_PROCEDURE(...) IS v_arg1 relation.c_arg1%type; v_arg2 relation.c_arg2%type; v_arg3 relation.c_arg3%type; BEGIN SELECT r.c_arg1, r.c_arg2, r.c_arg3 INTO v_arg1, v_arg2, v_arg3 FROM relation r WHERE ... -- Some more code ... END; When I'm compiling, the error I get is : Compilation errors on SOME_PROCEDURE: PL/SQL ERROR 302 at line 2, column 19 component 'C_ARG1' must be declared PL/SQL ERROR 0 at line 2, column 10 Item ignored PL/SQL ERROR 302 at line 3, column 19 component 'C_ARG2' must be declared PL/SQL ERROR 0 at line 3, column 10 Item ignored PL/SQL ERROR 302 at line 4, column 19 component 'C_ARG3' must be declared PL/SQL ERROR 0 at line 4, column 10 Item ignored relation here is a table name and is causing the problem. If, for testing purposes, I pick another table and field name, the code compiles fine e.g. v_arg1 other_table.other_field%type;. I figured maybe relation is a reserved keyword (even though I couldn't find anything in the documentation about this specific keyword, yikes), so I tried to put the schema name admin before the table name: PROCEDURE SOME_PROCEDURE(...) IS v_arg1 admin.relation.c_arg1%type; v_arg2 admin.relation.c_arg2%type; v_arg3 admin.relation.c_arg3%type; BEGIN And it works just fine. Now this is a problem, because admin is not a fixed name and will change because multiple schemas are used for multiple purposes ie development, testing, production, etc. This is where I'm stuck. I don't know how to do this the "right" way: because the %type is a VARCHAR2 with some length, I'll use something larger than usual such as VARCHAR2(100) and hopefully it downcasts without errors in the future. My question is, is it possible to come up with a way to reference this table without specifying the schema? This is for my work, I don't have much freedom in changing the table/schema name, etc. Thank you

I would like to understand in what sort of situations or what issues could cause TEMP tablespace to run out of space. - Is it too much sorting occur on TEMP? - How do I identify such issue from AWR? - And how usually it can be resolved? My Oracle Database instance is running on version 18c. I am a DBA who mainly manages the operational side of the DB. I don't do much development / code writing. I would like to understand more, so I could provide some advice to the developers.

when I tried to connect to my oracle instance with sql developer, I see this error in logs TNS-12518: TNS:listener could not hand off client connection TNS-12560: TNS:protocol adapter error TNS-00534: Failed to grant connection ownership to child 64-bit Windows Error: 10022: Unknown error Can someone give me a poit how to solve this error. Note I have 64b client installed.

I want to create a **fast refresh** materialized view (18c) on a **remote table**. The MV would also have a GEOMETRY column. Options for the GEOMETRY column datatype include: - ESRI's proprietary implementation of ST_GEOMETRY (user-defined datatype; is an 'object' datatype) - Oracle's SDO_GEOMETRY datatype ---------------- To start, I can successfully create a fast refresh MV ***without*** a GEOMETRY column: create materialized view log on maximo.workorder with primary key; --remote table grant select maximo.mlog$_workorder to schema_for_dblink; --I've given the dblink access to everything in this schema create materialized view my_gis_schema.wo_mv build immediate refresh fast start with sysdate next sysdate + (15/(60*60*24)) as select cast(workorderid as number(38,0)) as objectid, wonum, status, --other fields longitudex, latitudey from maximo.workorder@my_dblink ---------- The MV above works, but I want to store the XY coordinates from the remote table in a GEOMETRY column in the MV (right now, the coordinates are stored in number columns, not a geometry column). Unfortunately, my options for the GEOMETRY column in an MV seem pretty limited: 1. Oracle doesn’t seem to support ESRI's ST_GEOMETRY datatype in MVs (more info here and here ). - The SQL would be: sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape 2. Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs with the **fast refresh** option on a **remote table**: ORA-12015: cannot create a fast refresh materialized view from a complex query - The SQL would be: sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape --------------------- **Question:** Is there a way to include a GEOMETRY column in a materialized view on a **remote table**, using the **fast refresh** option?

My OS=CENTOS 7
Oracle=18c XE
I am trying to import a DMP file that is exported from XE 11g2 and now trying to import it in 18c XE. Here are the errors: [root@linux gsw]# impdp user/pass remap_tablespace=SYSTEM:myUserName remap_tablespace=USERS:myUserName dumpfile=myUserName.dmp directory=myUserName Import: Release 18.0.0.0.0 - Production on Thu Jun 4 01:09:34 2020 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production ORA-31626: job does not exist ORA-31633: unable to create master table "myUserName.SYS_IMPORT_FULL_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1142 ORA-01950: no privileges on tablespace 'USERS' ORA-06512: at "SYS.KUPV$FT", line 1035 ORA-06512: at "SYS.KUPV$FT", line 1023 What I did is: create TABLESPACE myUserName DATAFILE '/opt/oracle/oradata/XE/myUserName01.dbf' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M; ALTER DATABASE DATAFILE '/opt/oracle/oradata/XE/myUserName01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED; SQL> ALTER USER myUserName quota unlimited on myUserName; User altered. SQL> create user myUserName identified by pass; User created. SQL> grant connect, resource to myUserName; Grant succeeded. SQL> grant read, write on directory myUserName to myUserName; Grant succeeded. SQL> grant create database link to myUserName; Grant succeeded. SQL> grant create table to myUserName; Grant succeeded. **UPDATE-1** SQL> GRANT CREATE ANY TABLE TO myUserName; Grant succeeded. SQL> GRANT UNLIMITED TABLESPACE TO myUserName; Grant succeeded. Now the errors changed as: Import: Release 18.0.0.0.0 - Production on Thu Jun 4 01:39:50 2020 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39143: dump file "/opt/oracle/admin/XE/dpdump/myUserName/myUserName_110516.dmp" may be an original export dump file Best Regards

Using Oracle 18c on RedHat Linux 8 Connecting to CDB as 'SYS', I cannot figure out how to connect to a schema within the PDB other than as 'SYS'. Maybe I don't understand the CDB/PDB architecture well enough. Here is what I have tried after logging into the Oracle 18c CDB as 'SYS' (using sqlplus sys as sysdba): ALTER SESSION SET CONTAINER= PDB; conn myschema/mypasswd and I also tried (right from the CDB) conn myschema/mypasswd@PDB; Both approaches fail to get me connected to the schema myschema in the PDB. That is, I am still 'SYS' in the PDB. Are there any suggestions/explanations on how to resolve this? Do I have to setup some kind of access from the CDB-to-PDB for the schema myschema?

super newbie here. Trying to learn DBA stuff. I've been trying to install oracle database 18c on Oracle Linux 8 but there are two missing packages that are just not being found. I need these to complete the installation properly: nothing provides compat-libcap1 needed by oracle-database-preinstall-18c-1.0-1.el7.x86_64 - nothing provides compat-libstdc++-33 needed by oracle-database-preinstall-18c-1.0-1.el7.x86_64 These two commands do not work.

-none
rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm

-none
rpm -ivh compat-libcap1-1.10-7.el7.x86_64.rpm

I've looked a lot online. Thanks in advance.

I'm trying to deploy Oracle APEX 18 on Windows 8.1 PC using Oracle REST Data Services and Oracle Database 18c XE following this guide . But when I'm trying to run @apex_rest_config.sql I get this error - Enter: GetConsoleMode failed, LastError=|6| at Drive_letter:/DB_install_folder/dbhomeXE/perl/site/lib/Term/ReadKey.pm line 334. In CMD it looks like this: Every apex_rest_config#.log file looks similar to this: What could be causing this problem? Why @apex_rest_config.sql doesn't ask new passwords for APEX_LISTENER and APEX_REST_PUBLIC_USER, as it does in the guide? Does it have something to do with this ORA-03113: end of file on communication channel error? Is there any fix or workaround? **Update 1:** trying to implement this solution.

In an 18c Oracle database I have a tablespace that has been dropped. I need to add this tablespace anew, but when I try to do so I get this error: > ORA-01537: cannot add file 'MYTABLESPACE.DBF' - file already part of database By the way I create my tablespace like this: CREATE TABLESPACE MYTABLESPACE DATAFILE 'MYTABLESPACE.DBF' SIZE 1024M AUTOEXTEND ON NEXT 102400K I know that I can use ALTER to remove a datafile like this: ALTER TABLESPACE MYTABLESPACE DROP DATAFILE '{path}\MYTABLESPACE.DBF'; But this doesn't work, and I get: > ORA-00959: tablespace does not exist When I run: SELECT* FROM v$datafile I can see the datafile with the Id of dropped tablespace listed as online. When I query the TS$ table: SELECT * FROM TS$ I can see that the tablespace is dropped (ONLINE$ column has the value of 3). I know that I can change the name of tablespace and datafile, but for reasons I need them to stay the same. The question is, how do I get rid of this orphaned datafile?

**In the past** : When I had once the error ORA-00257: Archiver error, I had resolved it using the RMAN command: rman target sys/1234 delete archivelog all; **This time** , **when I try to connect to rman target**: rman target sys/1234 I get the error: RMAN-00554: initialization of internal recovery manager packagefailed. RMAN-04005: error from target database. ORA-28001:the password has expired. **When I try to connect with sqlplus as sys SYSDBA**, then raises the following error: ERROR: ORA-28001: the password has expired Changing password for SYS New password: **When I type the new password**, the error that comes is: ORA-00257: Archiver error. Connect AS SYSDBA only until resolved. So the problem is that.. - When I connect through sqlplus as sys SYSDBA, I cannot change the expired sys password because of archivelog RMAN. - When I try to fix RMAN error, I cannot connect to RMAN due to the expired sys password. How can I solve this?

When you first install Oracle Client, there are numerous checkboxes for various components. Once you have installed Oracle Client, how can you go back an select additional components you didn't check originally? (In my case, the Oracle Services for Microsoft Transaction Server.) Or do you have to uninstall and re-install?

According to official Oracle documentation available at https://www.oracle.com/database/technologies/appdev/xe/faq.html it has limitations: > What are the resource limits for Oracle Database XE Oracle Database XE > supports up to: > > 3 Pluggable Databases > > 2 CPUs for foreground processes > > 2GB of RAM (SGA and PGA combined) > > 12GB of user data on disk (irrespective of compression factor) When running SQL select TABLESPACE_NAME "Tablspace", FILE_NAME "Filename", BYTES/1024/1024 "Size MB" from SYS.DBA_DATA_FILES; I have the result: Tablspace Filename Size MB ------------------------------ ------------------------------------------------------- ---------- USERS C:\ORACLEXE18C\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF 11,25 UNDOTBS1 C:\ORACLEXE18C\PRODUCT\18.0.0\ORADATA\XE\UNDOTBS01.DBF 4230 SYSTEM C:\ORACLEXE18C\PRODUCT\18.0.0\ORADATA\XE\SYSTEM01.DBF 8630 SYSAUX C:\ORACLEXE18C\PRODUCT\18.0.0\ORADATA\XE\SYSAUX01.DBF 900 ## Question Which files does Oracle consider to validate the maximum size 12GB? Because if you make the sum of the 4 TableSpace I have already passed 12 GB.

I am getting NullPointerException while trying to add new database using Oracle 18c trace log is here: https://jpst.it/3cTNF Thanks for the help in advance :)

Note: Initially this was about *installing* Oracle 18c XE on a Windows machine with a long (> 15 chars) hostname. During our further tests we then noticed that **Oracle 18c XE does not work properly at all on a Windows7 or Windows 10 machine with a host name longer than 15 characters.** # Listener Problem When we switch a correctly *working* VM machine that has Oracle 18x XE installed from a short hostname/computername (e.g. MYHOST) to a **long (> 15) name** (e.g. MYVERLONGHOSTNAMEXYZ) the **listener stops being able to hand off the connection to the OracleServiceXE instance**. Specifically, the error we get is: ORA-12518: TNS: Listener konnte Client-Verbindung nicht weitergeben ORA-12518: TNS: Listener could not hand off client connection * This error occurs with both sqlplus SYSTEM/***@XE and sqlplus SYSTEM/***@XEPDB1 - bith entries are set up in tnsnames.ora and tnsping can see them. * The Oracle XE instance is up and running. * It **is** possible to connect via sqlplus SYSTEM/***(without @SID) * listener.ora, tnsnames.ora do *not* contain any hostnames, they only contain the (local) ip address. ## TL;DR Question Is anybody able to switch an Oracle Test machine with Oracle 18c (/ XE) running to a long Windows hostname (so that the hostame is then longer than the NetBIOS name) and still connect to the instance via the local listener? ------- ------- *Initial installer problem description:* We're having troubles getting Oracle 18c DBCA (XE or non-XE) working on Windows machines (both Win7 and Win10) where the machine name is longer than 15 characters (the NetBIOS limit). It **seems** the Oracle 18 dbCA tool refuses to pass some checks when the machine name is > 15 characters. (see: https://community.oracle.com/message/15361496#15361496) Now, without going into further details, I am currently investigating: * Does Oracle database 18 docs say anything about the maximum length for the computername on Windows installation? * Can someone provide a counterexample where Oracle 18c (preferrably: XE) has been successfully installed on a windows machine where the computer name is > 15 chars? ---- Note: This error halts the dbCA, effectively stopping the Oracle 18c (XE) setup from doing it's job: 18c XE setup will only install the binaries and the listener, no instance will be created and no instance can be created via dbCA afterwards. The same is true for the full 18c installer, although that one is more flexible in it's invocation options and one might get it to work somehow.

I want to suggest a few ideas to Oracle about enhancements to SDO_GEOMETRY / Oracle Spatial. Example: Requesting a function to select vertices, including the **multi-part numbers** of SDO_GEOMETRY polylines. ---------- **Question:** Do we have a way to ask Oracle for enhancements to SDO_GEOMETRY / Oracle Spatial? ----------- **What I tried:** I found the following page: How to Log an Idea (Enhancement Request) in the Customer Connect Idea Lab for Procurement Products (Doc ID 2577625.1) But unfortunately, my personal Oracle account isn't “connected” to a my organization's Oracle Support account (I'm not in IT; and my DBA doesn't want to submit ideas on my behalf). So I can't get past the "Connect your user account" step. ---------------- In a related question , it turned out that there was a way to request enhancements to a different product (SQL Developer) **right in the Oracle forum**. Which is great, because it's accessible to non-DBAs like me. So I was wondering if something similar might exist for Oracle Spatial. *And I'm not entirely sure if the "How to Log an Idea" page is the right avenue anyway. It seems like ideas might not get heard by the proper team...maybe there's a more direct way to talk to the Oracle Spatial team.*

(Yes, this is one of those question again...) I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server . I have a table I1 (called t_link in my previous question) referencing two other tables A and B (previsouly called t_parent and t_child) and want to delete rows in I1 when *both* references to A *and* B are NULL (see the addendum below for more information about why).

CREATE TABLE "A" (
    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
        PRIMARY KEY NOT NULL);

CREATE TABLE "B" (
    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
        PRIMARY KEY NOT NULL, 
    "ID_A" int,
     FOREIGN KEY ("ID_A") REFERENCES "A"("ID") ON DELETE SET NULL);

CREATE TABLE "I1" (
    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
        PRIMARY KEY NOT NULL, 
    "ID_A" int, 
    "ID_B" int,
    FOREIGN KEY ("ID_A") REFERENCES "A"("ID") ON DELETE SET NULL,
    FOREIGN KEY ("ID_B") REFERENCES "B"("ID") ON DELETE SET NULL);

CREATE OR REPLACE TRIGGER TRG_DELNULLS_A_I1_ID_A
BEFORE DELETE ON "A"
FOR EACH ROW
BEGIN
	DELETE FROM "I1" WHERE "ID_B" IS NULL AND "ID_A" = :OLD."ID";
END;
/

CREATE OR REPLACE TRIGGER TRG_DELNULLS_B_I1_ID_B
BEFORE DELETE ON "B"
FOR EACH ROW
BEGIN
	DELETE FROM "I1" WHERE "ID_A" IS NULL AND "ID_B" = :OLD."ID";
END;
/

Then I insert data:

INSERT INTO "A" VALUES (NULL);

SELECT * FROM "A";
   ID 
_____
    1

INSERT INTO "B" VALUES (NULL, 1);

SELECT * FROM "B";
   ID    ID_A 
_____ _______ 
    1       1

INSERT INTO "I1" VALUES (NULL, 1, NULL);
INSERT INTO "I1" VALUES (NULL, NULL, 1);
INSERT INTO "I1" VALUES (NULL, 1, 1);

SELECT * FROM "I1";
   ID    ID_A    ID_B 
_____ _______ _______ 
    1       1         
    2               1 
    3       1       1

Then I delete row A of ID 1 and expect that I1 of ID 1 would be deleted but *not* ID 3. That's where get the ORA-04091:

DELETE FROM "A" WHERE "ID" = 1;

Error starting at line : 1 in command -
DELETE FROM "A" WHERE "ID" = 1
Error report -
ORA-04091: table C##USER.I1 is mutating, trigger/function may not see it
ORA-06512: at "C##USER.TRG_DELNULLS_A_I1_ID_A", line 2
ORA-04088: error during execution of trigger
'C##USER.TRG_DELNULLS_A_I1_ID_A'

... which I don't understand because other questions related to the "table is mutating" error usually explicitely manipulate the trigger table in the trigger body; which I'm not doing (at least not explicitely). The same kind of trigger works fine with Postgres and I was hoping to be able to use constraints unlike with MS SQL Server so I guess there must be a tiny statement I missed somewhere (or so I hope)... --- **Addendum**: Basically, items from I1 are measurements (e.g. CPU, RAM, disk space) made at a different time in different timelines (A and B being the timelines). I1 contains two timestamp columns tsA and tsB that I omitted for brevity. As there can be several millions rows of I1 (and there are around 10 other tables like I1) I thought it would be more efficient to put those values in a single row instead of having two rows (after all, it's the same measurement). The software in charge of the database needs to handle several types of DBMS (depending on the customer): we started out with Postgres and I had to add MS SQL Server support and now Oracle, so I will be sacrificing (a bit of) performance for code simplicity... Having faced the same problem with MS SQL Server , I solved it by completely removing the foreign key constraints and putting all the logic (including the potential deletions of the foreign rows) in an AFTER DELETE trigger. I was hoping that Oracle would be different. I'm not a DB person and have a limited understanding on what runs under the hood when a delete is performed (transaction, triggers call order, reentrancy, ...).

I have a Oracle query that I use to analyze the vertices of SDO_GEOMETRY polylines: WITH lines AS (SELECT SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING ((0 5 0, 10 10 10, 30 0 33.54),(50 10 33.54, 60 10 -10000))') tline FROM dual) SELECT ct.id,ct.x, ct.y, ct.z as clcorr, vt.z as clorig FROM lines, TABLE(sdo_util.getvertices(SDO_LRS.CONVERT_TO_LRS_GEOM(SDO_CS.make_2d(tline)))) ct JOIN table(sdo_util.getvertices(tline)) vt ON ct.id=vt.id; Source: Find polyline vertices where M-value is not cumulative length ------ That query works in dbfiddle for Oracle **18c**: - [Screenshot](https://i.sstatic.net/vPLGM.png) - [Fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=6afc20f0c18d8f740cb711d8eb0cc9f1) But the same query produces an error in dbfiddle for Oracle **21c**: ORA-00904: "CT"."Z": invalid identifier - [Screenshot](https://i.sstatic.net/JSdyh.png) - [Fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=6afc20f0c18d8f740cb711d8eb0cc9f1) ------- Why doesn't the query work in dbfiddle for Oracle 21c?

I have geometries represented as strings (Oracle 18c): with cte as ( select 'LINESTRING ( 1.0 2.0, 3 4)' as txt from dual union all select 'LINESTRING M ( 1 2 3, 4 5 6.0)' as txt from dual union all select 'LINESTRING ( 1 2, 3 4, 5 6)' as txt from dual union all select 'LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)' as txt from dual union all select 'LINESTRING M ( 1 2 3.0, 4 5 6)' as txt from dual union all select 'MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))' as txt from dual ) select txt from cte TXT -------------------------------------------------------- LINESTRING ( 1.0 2.0, 3 4) LINESTRING M ( 1 2 3, 4 5 6.0) LINESTRING ( 1 2, 3 4, 5 6) LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0) LINESTRING M ( 1 2 3.0, 4 5 6) MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0)) - Ordinates are separated by spaces (X Y M). - Vertices are separated by commas. - Groups/multi-parts are wrapped in brackets and separated by commas. --------------- Using SQL, is there a way to remove the third ordinate (the "M" ordinate) from each vertex? Result: TXT -------------------------------------------------------- LINESTRING ( 1.0 2.0, 3 4) LINESTRING M ( 1 2, 4 5) LINESTRING ( 1 2, 3 4, 5 6) LINESTRING M ( 1 2, 4 5, 7 8.00) LINESTRING M ( 1 2, 4 5) MULTILINESTRING M (( 1 2, 4 5),( 7 8, 10 11)) It would be ok to round out the .0s. Or not.