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!

2 thoughts on “ORA-2070”

  1. Bug… Bug!

    It’s OK, If you can avoid it…
    But I’m so sad…

    ORA-00600: internal error code, arguments: [kjblreplvalid:!replay],
    .
    .
    .
    Dumping Session Wait History
    for ‘CGS wait for IPC msg’…

    That made Cluster Software… shutdown/startup instance.
    Investigate … that’s my way.

    Problem… make us enjoy to resolve ;)
    Good Luck and Enjoy

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>