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

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Saranga.

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

Answered by: Connor McDonald - Last updated: June 26, 2020 - 4:22 am UTC

Category: SQL - 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 we 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')  

and you rated our response

  (2 ratings)

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

Reviews

I missed the versions, sorry

July 26, 2017 - 1:42 am UTC

Reviewer: Saranga Colambage

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

Followup  

July 26, 2017 - 3:48 am UTC

glad we could help

regarding to implicit conversion of data

June 25, 2020 - 5:48 pm UTC

Reviewer: Agustin Lozano from Colombia

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

Followup  

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.