Skip to Main Content
  • Questions
  • Different behaviours in implicit conversion on 11g and 12c NVARCHAR type

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Saranga.

Asked: July 25, 2017 - 6:42 am UTC

Last updated: June 26, 2020 - 4:22 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,

We have encountered the below scenario when used with 11g and 12c respectively,

-- test data

create table t1 (tid nvarchar2(10) primary key);
insert into t1 values ('123');
insert into t1 values ('123-00');
insert into t1 values ('445');
insert into t1 values ('254');
commit;


in 11g,

select * from t1 where tid=445;

results,
445

and,
select * from t1 where tid=123;

results,
123
123-00


but, in 12c, it fails with "ORA-01722: invalid number"

So, our qustions are,
1. Why this happen? Does 12c converts the whole block / column implicitly?
2. Is it a bug fix from 11g? and is there a work-around to avoid this type of a conversion in 12c?

We believe there are quite many places that we have this kind of data in our databases.
And, our program always passes the parameters as integers.

So, it will be a great help for us to understand this and fix without a major effort.

thanks a lot..

Saranga

and Connor said...

I'm surprised you get this to work on 11g if I'm honest. It fails for me:

select * from v$version;

BANNER                                                                        
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
PL/SQL Release 11.2.0.4.0 - Production                                        
CORE 11.2.0.4.0 Production                                                    
TNS for Linux: Version 11.2.0.4.0 - Production                                
NLSRTL Version 11.2.0.4.0 - Production  

create table t1 (tid nvarchar2(10) primary key);

insert into t1 values ('123');
insert into t1 values ('123-00');
insert into t1 values ('445');
insert into t1 values ('254');

commit;

select * from t1 where tid=445;

ORA-01722: invalid number


This is because you've got an implicit conversion (n)varchar2 -> number as you say. And you're storing values that clearly aren't numbers!

To fix this you need to make your bind variables strings, not numbers.

set serveroutput off
explain plan for 
select * from t1 where tid=445;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                           
Plan hash value: 3688435342                                                 
                                                                            
--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      |     1 |    12 |     2   (0)| 00:00:01 |  
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    12 |     2   (0)| 00:00:01 |  
--------------------------------------------------------------------------  
                                                                            
Predicate Information (identified by operation id):                         
---------------------------------------------------                         
                                                                            
   1 - filter(TO_NUMBER("TID")=445)

explain plan for 
select * from t1 where tid='445';

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                   
Plan hash value: 2448883205                                                         
                                                                                    
----------------------------------------------------------------------------------  
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
----------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |              |     1 |    12 |     1   (0)| 00:00:01 |  
|*  1 |  INDEX UNIQUE SCAN| SYS_C0012526 |     1 |    12 |     1   (0)| 00:00:01 |  
----------------------------------------------------------------------------------  
                                                                                    
Predicate Information (identified by operation id):                                 
---------------------------------------------------                                 
                                                                                    
   1 - access("TID"=U'445')  

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I missed the versions, sorry

Saranga Colambage, July 26, 2017 - 1:42 am UTC

Thank you for the response Chirs! And, sorry I couldn't mention the correct versions. It works until 11.2.0.3 and fails with the later. However, it seems and as you mentioned, the best (workaround) will be to change our bind variables... thanks again!
Connor McDonald
July 26, 2017 - 3:48 am UTC

glad we could help

regarding to implicit conversion of data

Agustin Lozano, June 25, 2020 - 5:48 pm UTC

Can I avoid implicit conversion in Oracle 12c or 19c by altering any parameter?, I mean, too many selects are trying to get data from columns NUMBER using WHERE like this:

column ID-->NUMBER
SELECT * FROM table
WHERE id='1234';

in this case all explain plan is using

WHERE id=TO_NUMBER('1234')

my doubt is, how can avoid it?.... I mean, perhaps altering any parameter of database or session?

thanks in advance,

Agustín

Connor McDonald
June 26, 2020 - 4:22 am UTC

Nope.

I wish we did have such a parameter, but I also worry for how many apps would break under such conditions :-)

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.