SDO_GEOM.SDO_INTERSECTION idiosyncrasy makes SDO_GEOM.SDO_BUFFER go south

Getting strange or unexpected results from SDO_GEOM.SDO_INTERSECTION?

Chances are that inverting the input geometries the result will be different.

This happens on Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0.

I did find some cases where the intersection of A with B returns a simple polygon whereas B intersect A returns a geometry collection, that is a mix of point, lines and polygons.

Now, if I feed the result to function SDO_GEOM.SDO_BUFFER with a small negative radius, in one case I still get a valid polygon whilst in the other case I get a NULL.

As you can imagine this is somewhat disturbing as it may lead to a completely wrong result.

One workaround would be to always perform both operations and compare the results, albeit it's not clear whichever is the correct one in case they differ and it's not even clear if I should rely on the area of the resulting polygons for considering them "equal" or "equivalent".

In theory I'd say that the intersection of A with B should always match the intersection of B with A, however this is not what's happening so I am left wondering if this is a bug or an unpleasant side effect of the algorithm being used.

What I believe is really a bug instead is the behavior of SDO_GEOM.SDO_BUFFER when the input geometry is one of these geometry collections (SDO_GTYPE = 2004).

I'll submit these case to Oracle Support and see what they say, luckily I can supply a couple of test cases for them to look at.

ORA-08104: this index object ... is being online built or rebuilt

If you are getting this error while trying to execute DROP INDEX after unsuccessfully trying to create an index with the ONLINE clause:

DROP INDEX xyz;

ORA-08104: this index object 983257 is being online built or rebuilt

then you might need to call function DBMS_REPAIR.ONLINE_INDEX_CLEAN to be able to retry the DROP INDEX statement, however there are two caveats:

1. DBMS_REPAIR is not granted by default to all users, so you probably need to ask the DBA for the grant execute first;
2. When I tried the operation the DBA granted temporarily DBA role to my user, just in case, so I don't know if this is really necessary as the documentation doesn't mention it. 

The first parameter of the function call is the object ID mentioned in the error message.
The second optional parameter wait_for_lock (missing in the code below) enables a retry mechanism in case the underlying table is locked for a short time by some other sessions.

set serveroutput on
DECLARE
  b boolean;
BEGIN 
  b := sys.dbms_repair.online_index_clean(983257);
  if b then
    dbms_output.put_line('cleanup successful');
  else
    dbms_output.put_line('cleanup failed');
  end if;
END;
/

In my case after running the function, I could drop the index successfully.

DBMS_XMLGEN limited workaround for converting LONG columns into CLOBs

If you landed here is because you hit the problem of dealing with LONG columns.

There are some good articles elsewhere about how to tackle this old problem, my contribution in this case consists in advising about some limitations that apparently have been overlooked when using the same technique explained below.

It's a mystery to me why after so many years we can't rid of this annoyance once for good, why not "simply" adding a CLOB column equivalent at least in the case of data dictionary columns?
Come on!

I needed to extract the content of the TEXT column from DBA_VIEWS and DBA_MVIEWS, possibly without having to pass through an INSERT into a table (using function TO_LOB), which is the best workaround in case you deal with static data, for one-off operations.

I stumbled upon an old video of Connor McDonald showing how to extract the content of a LONG column exploiting the XML API DBMS_XMLGEN.GETXMLTYPE. This trick seemed to save the day after some adaptation for my case, and actually I was almost ready to celebrate when I started hitting some errors while doing further tests.

To cut a long story short, eventually I encountered the following problems:

1. API documentation for version 19c of DBMS_XMLGEN.SETCONVERTSPECIALCHARS is incorrect as it mentions a parameter "conv" but the real parameter name is "replace". This typo is still present in the latest version of the documentation of 23ai.
   
   
2. DBMS_XMLGEN.GETXMLTYPE and DBMS_XMLGEN.GETXML won't perform special characters escaping via DBMS_XMLGEN.SETCONVERTSPECIALCHARS if the column type is LONG.
   I was getting parsing errors when using Connor's EXTRACTVALUE technique because the XML document contained < or > as spare characters in the source (as in WHERE conditions inside the query source).
   
   
3.  DBMS_XMLGEN.GETXMLTYPE and DBMS_XMLGEN.GETXML will truncate the content to the first 32K for LONG columns.
   

Problem #1 was easily solved, problem #2 was solved extracting the data using REGEXP_SUBSTR instead of EXTRACTVALUE, but this was possible because I was working on a XML document containing a single ROW tag at a time. For multiple rows this solution will not work.

  FUNCTION long2clob
     ( p_qry in clob,  -- must return a single row!
       p_col in varchar2)
  RETURN CLOB
  IS
    c        CLOB;
  BEGIN
    c := regexp_substr(
           dbms_xmlgen.getxml(p_qry),
           '(<ROW>.*<' || p_col || '>(.*)</' || p_col || '>.*</ROW>)',
           1,
           1,
           'cn'
           ,2 
         );
    return c;

  END long2clob;

Problem #3 remains, unless LONG columns are less than 32K.
Unfortunately we do have some views exceeding 32K of source, but considering the usage of this function I'll probably live with this limitation for the moment.
By the way, SQLDeveloper won't allow you to edit a view larger than 32K, and to me this sounds like an invitation to avoid such situations.

Finally, I also tried to see what happens when you supply a LONG column to function JSON_OBJECT, unfortunately it returns the exception:

ORA-40654: Input to JSON generation function has unsupported data type.

That's all folks!
(quote)

Resetting system generated sequence numbers for identity columns

Exported and imported a table containing an identity column and the last number conflicts with the data?

No problem, just execute the following:

alter table table_name modify column_name generated as identity start with limit value;

Oracle will scan the table for the maximum value and set the last number to that number + increment.

Thanks to the following article of Jonathan Lewis for explaining how to do it, I believe that the documentation of Oracle 19 is missing this info whilst Oracle 23 docs are mentioning this option.

Weird behavior of UPDATE when a SDO_GEOMETRY column needs to be updated

A few days ago I posted a comment in the SQL & PL/SQL forum regarding a strange situation that occurs when updating a SDO_GEOMETRY column with a value resulting from the execution of a user-defined function, i.e.:

UPDATE TEST_GEOM
   SET SHAPE = MY_FUNCTION(SHAPE)
 WHERE ID = 1;

It turns out that in the case of a SDO_GEOMETRY column MY_FUNCTION is executed 8 times for each row being updated.

This phenomenon is further enhanced by adding a "sleep" of 1 second inside the function, which causes the update to take 8 seconds. 

This has been verified on Oracle 12.2.0.1.0 and Oracle Version 19.18.0.0.0.

Oddly enough, if you update another non-geometry column with a second user-defined function at the same time, the latter is executed only once.

UPDATE TEST_GEOM
   SET SHAPE = MY_FUNCTION(SHAPE),
       NUM   = ANOTHER_FUNCTION(10)
 WHERE ID = 1;

You can find below the code to reproduce the problem.

Anton Scheffer, who replied to my comment, pointed out that adding DETERMINISTIC to the definition of the function would fix the problem, which is an excellent workaround, but it is still a workaround, I mean, why should the function gets executed 8 times in the first place?

And why a second numeric function doesn't need the DETERMINISTIC clause to be added but still works as expected?

These questions remain unanswered.

 

CREATE SEQUENCE DUMMY_SEQ MINVALUE 0 ORDER;
CREATE SEQUENCE DUMMY2_SEQ MINVALUE 0 ORDER;

SELECT DUMMY_SEQ.NEXTVAL FROM DUAL;
SELECT DUMMY2_SEQ.NEXTVAL FROM DUAL;


CREATE TABLE "TEST_GEOM" 
( "ID"    INTEGER, 
  "NUM"   NUMBER,
  "SHAPE" SDO_GEOMETRY
 );

SET DEFINE OFF;
Insert into TEST_GEOM (ID, SHAPE) 
values (1, MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1, 53, 2003, 1), MDSYS.SDO_ORDINATE_ARRAY(1617857.39992003, 4927454.61993996, 1617859.95997274, 4927451.20990379, 1617869.76990222, 4927448.42992377, 1617871.36989997, 4927394.54999967, 1617870.37998793, 4927351.50990073, 1617883.78998438, 4927349.87990116, 1617900.64988697, 4927346.58992017, 1617900.31990137, 4927344.38001657, 1617893.83995069, 4927309.679913, 1617915.1700947, 4927297.58996787, 1617928.12005408, 4927335.75984816, 1617940.09995791, 4927328.25990929, 1617945.94009625, 4927325.55002712, 1617943.32575782, 4927334.82798004, 1617943.28014197, 4927334.99066672, 1617944.51009718, 4927354.62002357, 1617936.85009823, 4927386.20001927, 1617932.40883821, 4927415.70410291, 1617932.61397489, 4927415.84461214, 1617938.54007733, 4927412.46993659, 1617944.46009097, 4927419.68995848, 1617951.05009863, 4927434.1299835, 1617957.74998072, 4927474.19009812, 1617887.96998141, 4927460.48009826, 1617866.40997905, 4927456.55009778, 1617857.39992003, 4927454.61993996, 1617887.6001005, 4927375.78008369, 1617887.72009935, 4927395.83989044, 1617905.43989081, 4927394.20990962, 1617903.38991087, 4927372.97011743, 1617887.6001005, 4927375.78008369)));

COMMIT;

CREATE OR REPLACE FUNCTION DUMMY2(
  P_NUM IN NUMBER
)
RETURN NUMBER
AS
 I INTEGER;
BEGIN
  I := DUMMY2_SEQ.NEXTVAL;
  RETURN P_NUM;
END;
/

CREATE OR REPLACE FUNCTION DUMMY(
  P_SHAPE IN SDO_GEOMETRY
)
RETURN SDO_GEOMETRY
AS
 I INTEGER;
BEGIN
  DBMS_SESSION.SLEEP(1);
  I := DUMMY_SEQ.NEXTVAL;
  RETURN P_SHAPE;
END;
/

SET TIMING ON
UPDATE TEST_GEOM
   SET SHAPE = DUMMY(SHAPE),
       NUM   = DUMMY2(10)
 WHERE ID = 1;

COMMIT;

SET TIMING OFF

SELECT DUMMY_SEQ.CURRVAL FROM DUAL;
SELECT DUMMY2_SEQ.CURRVAL FROM DUAL;

DROP SEQUENCE DUMMY_SEQ;
DROP SEQUENCE DUMMY2_SEQ;
DROP TABLE TEST_GEOM PURGE;

The strange case of ORA-01841 and ADD_MONTHS

Today I came across this strange behavior of the function ADD_MONTHS:
if I add a number of months between 1 and 11 to the maximum allowed date, that is December 31, 9999, I get NULL as result (!).
But if I add 12 or more, I get ORA-01841 error.

SELECT ADD_MONTHS(date'9999-12-31', 1) d FROM DUAL;

D
======
(null)

SELECT ADD_MONTHS(date'9999-12-31', 11) d FROM DUAL;
D
======
(null)


SELECT ADD_MONTHS(date'9999-12-31', 12) d FROM DUAL;


ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range

 

Oddly enough, if I add just 1 day, it will raise the error . 

SELECT date'9999-12-31' + 1 d FROM DUAL;

ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range

In my humble opinion, returning NULL in the aforementioned cases makes no sense at all.

This problem occurs also on Oracle 19c (Oracle 19.18.0.0.0) and Oracle 21c XE (21.3.0.0.0).

See message translations for ORA-01841 and search additional resources.