Yet another curious syntax with little documentation: SELECT FROM RELATIONAL("schema"."table")

Have you ever seen this syntax?

SELECT * FROM RELATIONAL("SCOTT"."EMP");

For tables containing only primitive data types it returns the same results as it would the normal query:

SELECT * FROM "SCOTT"."EMP"; 

For tables containing object data types, however, it splits object's underlying attributes into separate columns:

CREATE TABLE GEOM_TABLE(
ID   NUMBER,
GEOM SDO_GEOMETRY
);

INSERT INTO GEOM_TABLE(ID, GEOM)
VALUES(1, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(1680337.2, 4973436.1, 1680337.3, 4973436.2, 1680337.2, 4973436.9, 1680337.2, 4973436.1)));

 
SELECT * FROM RELATIONAL("GEOM_TABLE");  -- I omitted the schema

	
ID
	
GEOM
	
SYS_NC00003$
	
SYS_NC00004$
	
SYS_NC00005$
	
SYS_NC00006$
	
SYS_NC00007$
	
SYS_NC00008$
	
SYS_NC00009$



	

1
02
2003
 
 
 
 
MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1)
MDSYS.SDO_ORDINATE_ARRAY(1680337.2, 4973436.1, 1680337.3, 4973436.2, 1680337.2, 4973436.9, 1680337.2, 4973436.1)
	

I didn't investigate what that 02 means in the GEOM column.

As far as I could see there is no official document describing this syntax, however it must be there since ages because I found it in queries created by an old java based framework in 2009.

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.

The strange case of the REVERSE function

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Goal: reverse the order of the characters in a string:

select reverse('hello') s from dual;

exec dbms_output.put_line(reverse('hello'));

S    
-----
olleh


Error starting at line : 11 in command -
BEGIN dbms_output.put_line(reverse('hello')); END;
Error report -
ORA-06550: line 1, column 28:
PLS-00201: identifier 'REVERSE' must be declared
ORA-06550: line 1, column 7:
 

The REVERSE function exists only in the context of a SQL statement, therefore in order to execute it in a PL/SQL block you need to put it inside a SELECT ... FROM.

Interestingly, REVERSE is nowhere to be found in the SQL Reference but the function  seems to be available since at least Oracle 11g.

Another possibility is to write your own PL/SQL REVERSE function (but with a different name).

See message translations for PLS-00201 and search additional resources.