ORA-2070

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.
Kind of
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 9.2.0.8/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!