Today I received an error which was totally new to me. And totally unexpected…
For the purpose of this post, I reduced it to a minimum
CREATE TABLE LSC_T1 ( x number); CREATE TABLE LSC_T2 ( x number); with c as (select nls_charset_decl_len(1, x) from lsc_t1 join lsc_t2 using (x)) select 1 from c union select 1 from c; Error at line 1 ORA-02070: database does not support operator NLS_CHARSET_DECL_LEN in this context
My basic query was actually joining two dictionary views, and NLS_CHARSET_DECL_LEN is used in USER_TAB_COLUMNS.CHAR_COL_DECL_LENGTH.
with c as ( select table_name, column_name, constraint_name from user_tab_columns join user_constraints using (table_name) where constraint_type='P' ) select constraint_name,column_name from c union all select column_name,constraint_name from c; Error at line 1 ORA-02070: database does not support operator NLS_CHARSET_DECL_LEN in this context
I spent some time to understand the reason for getting an ORA-2070 in a simple query that did seem to be totally unrelated to charset. The database used for this test is 220.127.116.11/Solaris.
It is quite easy to avoid the bug, for instance by not using the ANSI join 😉
When analyzing such a defect, my approach is to simplify the query as much as possible. Very often on forums you see users posting queries of more than 100 lines. Those users just expect the community to debug their code. Posting a tiny reproducible test case is key to receive a proper solution!